Persist Your Data with SQLite and EF Core

Mastering Data Persistence with SQLite and EF Core in Your Minimal API

Throughout development, you’ve relied on an in-memory database for quick and easy data storage. However, this approach doesn’t offer persistence — data disappears upon application restart. This exercise tackles data persistence by upgrading your application to utilize a relational database, specifically SQLite.

Essential Tools and Packages:

SQLite EF Core Provider: This package grants access to various databases through provider libraries. In this case, it acts as the bridge between EF Core and SQLite. Install it using

dotnet add package Microsoft.EntityFrameworkCore.Sqlite - version 8.0

EF Core Tools: These tools empower you with development tasks like migration creation, application, and model generation based on existing databases. Install them globally using:

dotnet tool install - global dotnet-ef

Microsoft.EntityFrameworkCore.Design: This package encompasses the design-time logic for EF Core to create your database. Install it with:

dotnet add package Microsoft.EntityFrameworkCore.Design - version 8.0

Enabling Database Creation:

To establish a database, configure the connection string. Subsequently, migrate your data model to the SQLite database. In Program.cs, integrate a connection string beneath var builder = WebApplication.CreateBuilder(args);.

var connectionString = builder.Configuration.GetConnectionString("Pizzas") ?? "Data Source=Pizzas.db";

This code snippet retrieves a connection string named “Pizzas” from the configuration provider. If unavailable, it defaults to “Data Source=Pizzas.db”. SQLite maps this string to a corresponding file.

Switching to a Persistent Database:

Recall the in-memory database implementation from the CRUD section (builder.Services.AddDbContext<PizzaDb>(options => options.UseInMemoryDatabase(“items”));). Replace it with the SQLite implementation:

builder.Services.AddSqlite<PizzaDb>(connectionString);

Generating and Applying Migrations:

Leveraging the EF Core migration tool, create your initial migration named “InitialCreate”. Execute the following command after saving changes:

dotnet ef migrations add InitialCreate

This command establishes a Migrations folder within your project directory. It houses files containing code representing database migrations.

Following migration creation, utilize it to construct your database and schema. Run the database update command in the terminal:

dotnet ef database update

A newly created Pizzas.db file should appear in your project directory.

Running and Testing the Application:

With a persistent database in place, your modifications are now preserved. Test your application as usual with dotnet run and the Swagger UI. Terminate the application using Ctrl+C and then relaunch it. Verify that your changes remain accessible within Pizzas.db.

Congratulations! You’ve successfully integrated a database into your minimal API.

More Articles

Follow me on

C# Publication, LinkedIn, Instagram, Twitter, Dev.to