Interact with SQLite database using EF Core

Make your data persistent across restarts!

Forhad Rahman

5 min read

·

May 10, 2026

--

Listen

Share

Press enter or click to view image in full size

Note: Please refer to my previous article about in-memory databases first since this is a continuation of that post.

EF Core is basically an SQL translator. It translates C# code into SQL automatically so you don’t have to mess with another language. At least that’s what I can understand so far.

Anyway. Enough.

To make the data persistent, or save it to disk, we have to use a real database, like SQLite. It’s fast. It’s small. And it’s one of the most used databases in the world.

Create and open a Web API project in VS Code using this:

dotnet new web

Package Installation

Follow “What is an API and how to set up a minimal API” for .NET installation if you’re starting fresh.

For more info, follow this.

dotnet add package Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore
dotnet add package NSwag.AspNetCore
dotnet add package Swashbuckle.AspNetCore
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design

We’re working with an SQLite database here.

The plan is simple. Setup a blueprint class for the kinds of data we want to work with. Then connect it with the database using the DbContext class. And finally, write business logic in the program. cs.

We’ll set up a coffee management system where users can order, view, or delete their coffee of choice. There’s no frontend with fancy UI for now. Just plain JSON and API calling back and forth.

Create a Model.cs and write the following:

namespace CoffeeModel
{
    public class Coffee
    {
        public int Id { get; set; }
        public string? Name { get; set; }
        public char Size { get; set; }
        public int Quantity { get; set; }
        public double Price { get; set; }
        public Coffee(int Id, string Name, char Size, int Quantity, double Price)
        {
            this.Id = Id;
            this.Name = Name;
            this.Size = Size;
            this.Quantity = Quantity;
            this.Price = Price;
        }
    }
}

This will serve as our blueprint class. Next we’ll create a Data.cs file that’ll inherit the DbContext class:

using CoffeeModel;
using Microsoft.EntityFrameworkCore;
namespace CoffeeData
{
    public class CoffeeDb : DbContext
    {
        public CoffeeDb(DbContextOptions<CoffeeDb> options)
        : base(options) { }
        public DbSet<Coffee> Coffees { get; set; }
    }
}

This one will interact with the database and keep track of changes in the tables. Right now we have a table named “Coffees” that stores all the information.

Lastly, Program. cs:

using Microsoft.EntityFrameworkCore;
using CoffeeData;
using CoffeeModel;
var builder = WebApplication.CreateBuilder(args);

After these lines, set up a connection string and add SQLite as the database provider.

var connectionString = builder.Configuration.GetConnectionString("Coffees") ?? "Data Source=Coffees.db";
builder.Services.AddSqlite<CoffeeDb>(connectionString);

Go to appsettings.json and add this snippet at the end (between the comma and closing bracket):

"ConnectionStrings": {
    "Coffees": "Data Source=Coffees.db"
}

This will ensure data is being stored on disk in a file named “Coffees.db.” Our data is now persistent after restart!

After this, run the following command to create the database and manage migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update

You run these 2 commands every time you make changes to your entity classes (e.g., add a new property, change the data type of a property, etc.)

Instead of adding contents from Swagger, we can also add from a JSON file. For this, we have to ensure a database is created and add contents from that JSON file. Let’s create a JSON file and put some data in it:

[
{
"Id": 1,
"Name": "Cappuccino",
"Size": "M",
"Quantity": 20,
"Price": 4.25
},
{
"Id": 2,
"Name": "Latte",
"Size": "L",
"Quantity": 15,
"Price": 4.75
},
{
"Id": 3,
"Name": "Mocha",
"Size": "S",
"Quantity": 12,
"Price": 3.5
},
{
"Id": 4,
"Name": "Americano",
"Size": "M",
"Quantity": 18,
"Price": 3.25
},
{
"Id": 5,
"Name": "Macchiato",
"Size": "L",
"Quantity": 8,
"Price": 4
},
{
"Id": 8,
"Name": "Frappuccino",
"Size": "L",
"Quantity": 9,
"Price": 5
},
{
"Id": 10,
"Name": "Espresso",
"Size": "S",
"Quantity": 10,
"Price": 2.5
}
]

Note: Make sure the JSON file’s key case sensitivity is same as the blueprint class’s variable names, e.g. both have Name and not name. Otherwise the mapping won’t be successful.

.NET already has a built-in JSON module; add this at the top of Program.cs:

using System.Text.Json;

Then add this snippet at the top of the first CRUD function. Basically, this reads contents from the JSON file and adds them to the database.

var jsonPath = Path.GetFullPath("Coffees.json");
var json = File.ReadAllText(jsonPath);
var coffees = JsonSerializer.Deserialize<List<Coffee>>(json) ?? [];

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<CoffeeDb>();
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
    db.Coffees.AddRange(coffees);
    db.SaveChanges();
}

Configure the remaining code as it is by setting up Swagger UI and the rest of the CRUD operation. Here’s the full code:

using Microsoft.EntityFrameworkCore;
using System.Text.Json;
using CoffeeData;
using CoffeeModel;

var builder = WebApplication.CreateBuilder(args);
var connectionString = builder.Configuration.GetConnectionString("Coffees") ?? "Data Source=Coffees.db";
builder.Services.AddSqlite<CoffeeDb>(connectionString);

builder.Services.AddDatabaseDeveloperPageExceptionFilter();
builder.Services.AddEndpointsApiExplorer();

builder.Services.AddOpenApiDocument(config =>
{
    config.DocumentName = "TodoAPI";
    config.Title = "TodoAPI v1";
    config.Version = "v1";
});

builder.Services.AddCors();
var app = builder.Build();
app.UseCors(policy => policy.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());

if (app.Environment.IsDevelopment())
{
    app.UseOpenApi();
    app.UseSwaggerUi(config =>
    {
        config.DocumentTitle = "TodoAPI";
        config.Path = "/swagger";
        config.DocumentPath = "/swagger/{documentName}/swagger.json";
        config.DocExpansion = "list";
    });
}

var jsonPath = Path.GetFullPath("Coffees.json");
var json = File.ReadAllText(jsonPath);
var coffees = JsonSerializer.Deserialize<List<Coffee>>(json) ?? [];

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<CoffeeDb>();
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();
    db.Coffees.AddRange(coffees);
    db.SaveChanges();
}

// ----------------------------- CRUD ----------------------------- //
// Retrieve all coffees
app.MapGet("/coffee", (CoffeeDb db) =>
{
    return db.Coffees.ToList();
});

// Retrieve one coffee
app.MapGet("/coffee/{name}", async (string name, CoffeeDb db) =>
    await db.Coffees.FirstOrDefaultAsync(c => c.Name == name)
        is Coffee coffee 
            ? Results.Ok(coffee) 
            : Results.NotFound());

// Add a coffee
app.MapPost("/coffee/add", (Coffee coffee, CoffeeDb db) =>
{
    db.Coffees.Add(coffee);
    db.SaveChanges();
    return Results.Created($"/coffees/{coffee.Id}", coffee);
});

// Update a coffee
app.MapPut("/coffee/update/{id}", (int id, Coffee coffee, CoffeeDb db) =>
{
    var newCoffee = db.Coffees.Find(id);
    if (newCoffee is null) return Results.NotFound();
    newCoffee.Name = coffee.Name;
    newCoffee.Size = coffee.Size;
    newCoffee.Quantity = coffee.Quantity;
    newCoffee.Price = coffee.Price;
    db.SaveChanges();
    return Results.NoContent();
});

// Delete a coffee
app.MapDelete("/coffee/delete/{id}", (int id, CoffeeDb db) =>
{
    var deleteCoffee = db.Coffees.Find(id);
    if (deleteCoffee is null) return Results.NotFound();
    db.Coffees.Remove(deleteCoffee);
    db.SaveChanges();
    return Results.NoContent();
});

app.Run();

Open localhost:5000/swagger, and test the API endpoints. Now your data should persist on that database file after restart.

You can interact with sqlite3 directly via the terminal. Make sure to install sqlite3 on your system. Then in the terminal, type “sqlite3 <file.db>.” Here’s an example for our case:

sqlite3 Coffees.db

Now we’re in the sqlite3 terminal. You can type standard SQL commands here. For example, typing the following command will show all the items in the Coffee table.

select * from Coffees;

For more reference, follow official documentation from Microsoft and SQLite.

Note: This post is a personal reference while learning backend development. Some information may be inaccurate or incomplete. Please research it yourself as well.