Skip to main content

SQLite

SQLite is a lightweight, file-based database perfect for development and small deployments.

Package​

SQLite support is included in the core hosting package:

dotnet add package AvantiPoint.Packages.Database.Sqlite

Configuration​

appsettings.json:

{
  "Database": {
    "Type": "Sqlite"
  },
  "ConnectionStrings": {
    "Sqlite": "Data Source=packages.db"
  }
}

Program.cs:

builder.Services.AddNuGetPackageApi(options =>
{
    options.AddSqliteDatabase("Sqlite");
});

Connection String Options​

Data Source=packages.db;Cache=Shared;Mode=ReadWriteCreate
  • Data Source: Path to the database file (relative or absolute)
  • Cache: Shared (default) or Private
  • Mode: ReadWriteCreate (default), ReadWrite, or ReadOnly

Notes​

  • The database file is created automatically if it doesn't exist
  • Store the database file on reliable storage
  • SQLite is single-writer, so it may not perform well under high concurrent write loads
  • Great for simple deployments, local testing, and development
  • Maximum database size: ~281 TB (in practice, limited by file system)
  • Recommended for deployments with < 100 concurrent users

Performance Considerations​

Write-Ahead Logging (WAL)​

Enable WAL mode for better concurrent read performance:

{
  "ConnectionStrings": {
    "Sqlite": "Data Source=packages.db;Mode=ReadWriteCreate;Pooling=true"
  }
}

Then set WAL mode at startup:

if (app.Environment.IsDevelopment())
{
    using var scope = app.Services.CreateScope();
    using var db = scope.ServiceProvider.GetRequiredService<IContext>();
    db.Database.ExecuteSqlRaw("PRAGMA journal_mode=WAL;");
}

Connection Pooling​

Enable connection pooling for better performance:

{
  "ConnectionStrings": {
    "Sqlite": "Data Source=packages.db;Pooling=true;Max Pool Size=25"
  }
}

See Also​