SQLite with Entity Framework Core - AspNetRun Microservices

The Discount service uses SQLite as a lightweight, embedded database for managing coupon data. This is ideal for simple, read-heavy workloads that don’t require a full database server.

Why SQLite?

SQLite is perfect for the Discount service because:

Zero Configuration: No database server to install or manage
Lightweight: Single file database
Read-Heavy: Optimized for frequent coupon lookups
Simple Schema: Straightforward coupon data model
Portable: Database file can be easily backed up and moved
Perfect for gRPC: Minimal overhead for high-frequency calls

Configuration

Connection String

src/Services/Discount/Discount.Grpc/appsettings.json

{
  "ConnectionStrings": {
    "Database": "Data Source=discountdb"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Information"
    }
  },
  "AllowedHosts": "*",
  "Kestrel": {
    "EndpointDefaults": {
      "Protocols": "Http2"
    }
  }
}

Connection String Format:

Data Source=discountdb - Creates file discountdb in app directory
Data Source=./data/discountdb - Custom directory path
Data Source=:memory: - In-memory database (testing)

Service Configuration

Program.cs Setup

src/Services/Discount/Discount.Grpc/Program.cs

using Discount.Grpc.Data;
using Discount.Grpc.Services;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddGrpc();

builder.Services.AddDbContext<DiscountContext>(opts =>
    opts.UseSqlite(builder.Configuration.GetConnectionString("Database")));

var app = builder.Build();

// Configure the HTTP request pipeline.
app.UseMigration();
app.MapGrpcService<DiscountService>();

app.MapGet("/", () => "Communication with gRPC endpoints must be made through a gRPC client.");

app.Run();

Key Points:

UseSqlite(): Configures EF Core for SQLite
UseMigration(): Applies migrations on startup
gRPC-specific: Http2 protocol, no Swagger/HTTP endpoints

DbContext Implementation

src/Services/Discount/Discount.Grpc/Data/DiscountContext.cs

using Discount.Grpc.Models;
using Microsoft.EntityFrameworkCore;

namespace Discount.Grpc.Data;

public class DiscountContext : DbContext
{
    public DbSet<Coupon> Coupons { get; set; } = default!;

    public DiscountContext(DbContextOptions<DiscountContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Coupon>().HasData(
            new Coupon 
            { 
                Id = 1, 
                ProductName = "IPhone X", 
                Description = "IPhone Discount", 
                Amount = 150 
            },
            new Coupon 
            { 
                Id = 2, 
                ProductName = "Samsung 10", 
                Description = "Samsung Discount", 
                Amount = 100 
            }
        );
    }
}

Features:

Seed Data: Initial coupons via HasData()
Simple Model: Single entity for coupons
Type Safety: DbSet provides strongly-typed queries

Entity Model

namespace Discount.Grpc.Models;

public class Coupon
{
    public int Id { get; set; }
    public string ProductName { get; set; } = default!;
    public string Description { get; set; } = default!;
    public int Amount { get; set; }
}

Automatic Migration

Extension Method

src/Services/Discount/Discount.Grpc/Data/Extentions.cs

using Microsoft.EntityFrameworkCore;

namespace Discount.Grpc.Data;

public static class Extentions
{
    public static IApplicationBuilder UseMigration(this IApplicationBuilder app)
    {
        using var scope = app.ApplicationServices.CreateScope();
        using var dbContext = scope.ServiceProvider.GetRequiredService<DiscountContext>();
        dbContext.Database.MigrateAsync();

        return app;
    }
}

What It Does:

Creates a service scope
Resolves DiscountContext
Applies pending migrations
Creates database file if it doesn’t exist
Seeds initial data

Migration Files

Migrations are stored in Migrations/ directory:

Migrations/
├── 20240202091837_InitialCreate.cs
├── 20240202091837_InitialCreate.Designer.cs
└── DiscountContextModelSnapshot.cs

Usage in gRPC Service

using Discount.Grpc.Data;
using Discount.Grpc.Models;
using Grpc.Core;
using Microsoft.EntityFrameworkCore;

namespace Discount.Grpc.Services;

public class DiscountService : DiscountProtoService.DiscountProtoServiceBase
{
    private readonly DiscountContext _dbContext;
    private readonly ILogger<DiscountService> _logger;

    public DiscountService(DiscountContext dbContext, ILogger<DiscountService> logger)
    {
        _dbContext = dbContext;
        _logger = logger;
    }

    public override async Task<CouponModel> GetDiscount(GetDiscountRequest request, ServerCallContext context)
    {
        var coupon = await _dbContext.Coupons
            .FirstOrDefaultAsync(x => x.ProductName == request.ProductName);

        if (coupon == null)
        {
            return new CouponModel
            {
                ProductName = "No Discount",
                Amount = 0,
                Description = "No Discount Available"
            };
        }

        _logger.LogInformation("Discount retrieved for ProductName: {ProductName}, Amount: {Amount}", 
            coupon.ProductName, coupon.Amount);

        return new CouponModel
        {
            Id = coupon.Id,
            ProductName = coupon.ProductName,
            Description = coupon.Description,
            Amount = coupon.Amount
        };
    }

    public override async Task<CouponModel> CreateDiscount(CreateDiscountRequest request, ServerCallContext context)
    {
        var coupon = new Coupon
        {
            ProductName = request.Coupon.ProductName,
            Description = request.Coupon.Description,
            Amount = request.Coupon.Amount
        };

        _dbContext.Coupons.Add(coupon);
        await _dbContext.SaveChangesAsync();

        _logger.LogInformation("Discount created for ProductName: {ProductName}", coupon.ProductName);

        return new CouponModel
        {
            Id = coupon.Id,
            ProductName = coupon.ProductName,
            Description = coupon.Description,
            Amount = coupon.Amount
        };
    }

    public override async Task<DeleteDiscountResponse> DeleteDiscount(DeleteDiscountRequest request, ServerCallContext context)
    {
        var coupon = await _dbContext.Coupons
            .FirstOrDefaultAsync(x => x.ProductName == request.ProductName);

        if (coupon == null)
        {
            return new DeleteDiscountResponse { Success = false };
        }

        _dbContext.Coupons.Remove(coupon);
        await _dbContext.SaveChangesAsync();

        _logger.LogInformation("Discount deleted for ProductName: {ProductName}", request.ProductName);

        return new DeleteDiscountResponse { Success = true };
    }
}

Database Operations

Query Operations

// Get by product name
var coupon = await _dbContext.Coupons
    .FirstOrDefaultAsync(c => c.ProductName == productName);

// Get all coupons
var coupons = await _dbContext.Coupons.ToListAsync();

// Filter by amount
var bigDiscounts = await _dbContext.Coupons
    .Where(c => c.Amount > 100)
    .ToListAsync();

// Check existence
var exists = await _dbContext.Coupons
    .AnyAsync(c => c.ProductName == productName);

Write Operations

// Create
var coupon = new Coupon { /* ... */ };
_dbContext.Coupons.Add(coupon);
await _dbContext.SaveChangesAsync();

// Update
var coupon = await _dbContext.Coupons.FindAsync(id);
if (coupon != null)
{
    coupon.Amount = 200;
    await _dbContext.SaveChangesAsync();
}

// Delete
var coupon = await _dbContext.Coupons.FindAsync(id);
if (coupon != null)
{
    _dbContext.Coupons.Remove(coupon);
    await _dbContext.SaveChangesAsync();
}

Migrations

Create Migration

cd src/Services/Discount/Discount.Grpc

dotnet ef migrations add MigrationName

Apply Migration

# Applied automatically on startup via UseMigration()
# Or manually:
dotnet ef database update

Remove Last Migration

dotnet ef migrations remove

Docker Configuration

Dockerfile

FROM mcr.microsoft.com/dotnet/aspnet:8.0 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443

FROM mcr.microsoft.com/dotnet/sdk:8.0 AS build
WORKDIR /src
COPY ["Services/Discount/Discount.Grpc/Discount.Grpc.csproj", "Services/Discount/Discount.Grpc/"]
RUN dotnet restore "Services/Discount/Discount.Grpc/Discount.Grpc.csproj"
COPY . .
WORKDIR "/src/Services/Discount/Discount.Grpc"
RUN dotnet build "Discount.Grpc.csproj" -c Release -o /app/build

FROM build AS publish
RUN dotnet publish "Discount.Grpc.csproj" -c Release -o /app/publish

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
ENTRYPOINT ["dotnet", "Discount.Grpc.dll"]

Docker Compose

discount.grpc:
  image: discountgrpc:latest
  build:
    context: .
    dockerfile: src/Services/Discount/Discount.Grpc/Dockerfile
  ports:
    - "5052:80"
    - "5053:443"
  environment:
    - ASPNETCORE_ENVIRONMENT=Development
    - ASPNETCORE_URLS=https://+:443;http://+:80
  volumes:
    - ${APPDATA}/Microsoft/UserSecrets:/root/.microsoft/usersecrets:ro
    - ${APPDATA}/ASP.NET/Https:/root/.aspnet/https:ro
    # Persist SQLite database
    - discount_data:/app/data

volumes:
  discount_data:

Important: Mount volume to persist SQLite database file.

Performance Tips

Connection Pooling

builder.Services.AddDbContext<DiscountContext>(opts =>
    opts.UseSqlite(
        builder.Configuration.GetConnectionString("Database"),
        sqliteOptions => sqliteOptions.CommandTimeout(30)));

Read-Only Queries

var coupons = await _dbContext.Coupons
    .AsNoTracking()
    .ToListAsync();

Compiled Queries

For frequently executed queries:

private static readonly Func<DiscountContext, string, Task<Coupon?>> GetCouponQuery =
    EF.CompileAsyncQuery(
        (DiscountContext context, string productName) =>
            context.Coupons.FirstOrDefault(c => c.ProductName == productName));

public async Task<Coupon?> GetCouponAsync(string productName)
{
    return await GetCouponQuery(_dbContext, productName);
}

Limitations

Concurrent Writes

SQLite has limited concurrent write support:

// Enable WAL mode for better concurrency
builder.Services.AddDbContext<DiscountContext>(opts =>
    opts.UseSqlite(
        builder.Configuration.GetConnectionString("Database"),
        sqliteOptions => sqliteOptions.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)));

File Locking

Be aware of file locking:

Only one writer at a time
Multiple readers allowed
Perfect for read-heavy workloads (like coupon lookups)

Backup and Restore

Manual Backup

# Copy database file
cp discountdb discountdb.backup

# Or use SQLite backup command
sqlite3 discountdb ".backup discountdb.backup"

Automated Backup

public async Task BackupDatabaseAsync(string backupPath)
{
    var sourcePath = "discountdb";
    await Task.Run(() => File.Copy(sourcePath, backupPath, overwrite: true));
}

Troubleshooting

View Database Contents

# Open SQLite CLI
sqlite3 discountdb

# List tables
.tables

# View coupons
SELECT * FROM Coupons;

# Check schema
.schema Coupons

# Exit
.quit

Database Locked Error

SQLite Error 5: 'database is locked'

Solutions:

Enable WAL mode in connection string
Reduce concurrent writes
Increase busy timeout

builder.Services.AddDbContext<DiscountContext>(opts =>
    opts.UseSqlite(
        "Data Source=discountdb;Mode=ReadWriteCreate;Cache=Shared"));

Missing Database File

If database file doesn’t exist:

# Run migrations
dotnet ef database update

# Or let app create it on startup via UseMigration()

Production Considerations

When to Use SQLite

Good For:

Read-heavy workloads
Simple schemas
Embedded/edge scenarios
Development/testing
Low write concurrency

Not Good For:

High write concurrency
Large datasets (>100GB)
Network access required
Multiple writers

When to Migrate

Consider migrating to PostgreSQL/SQL Server if:

Write operations increase
Need horizontal scaling
Require replication
Dataset grows significantly

Database Overview

Learn about polyglot persistence

SQLite Documentation

Official SQLite documentation

Building Blocks

API Gateway

Messaging

Databases

​Why SQLite?

​Configuration

​Connection String

​Service Configuration

​Program.cs Setup

​DbContext Implementation

​Entity Model

​Automatic Migration

​Extension Method

​Migration Files

​Usage in gRPC Service

​Database Operations

​Query Operations

​Write Operations

​Migrations

​Create Migration

​Apply Migration

​Remove Last Migration

​Docker Configuration

​Dockerfile

​Docker Compose

​Performance Tips

​Connection Pooling

​Read-Only Queries

​Compiled Queries

​Limitations

​Concurrent Writes

​File Locking

​Backup and Restore

​Manual Backup

​Automated Backup

​Troubleshooting

​View Database Contents

​Database Locked Error

​Missing Database File

​Production Considerations

​When to Use SQLite

​When to Migrate

​Related Resources