Skip to content

Overview

Jump to bottom
github-actions[bot] edited this page Sep 2, 2026 · 5 revisions

Overview

A short tour of SQLite.Framework on one page. Each section links to a deeper guide if you want more detail.

What it is

A small ORM that lets you use LINQ on a SQLite database. If you have used Entity Framework Core before, most things will feel familiar. The main difference is that this library has no automatic change tracker and no navigation properties. The heavier features have lightweight stand-ins. Write hooks cover before-save logic and a versioned migration runner handles schema versioning.

db.Table<T>() returns a SQLiteTable<T>. That class implements IQueryable<T>, so any LINQ method works on it.

The framework keeps the generated SQL close to the shape of the LINQ query you wrote. It adds a subquery when a LINQ operation needs a SQL query boundary, such as composition after a set operation or filtering on a window value. If a method cannot be mapped safely, you get a clear NotSupportedException instead of a silently wrong result.

Packages

Package What it offers
SQLite.Framework Default. Uses the OS-provided SQLite on desktop and iOS, bundles its own on Android.
SQLite.Framework.Bundled Ships its own SQLite binary, independent of the OS version.
SQLite.Framework.Cipher SQLCipher for encrypted databases.
SQLite.Framework.Base No SQLite provider included. You bring your own.
SQLite.Framework.DependencyInjection AddSQLiteDatabase for IServiceCollection.
SQLite.Framework.SourceGenerator Build-time entity, projection and JSON collection materializers. Required for AOT.

The first four packages expose the same API and assembly name, so you can swap between them without changing your code. Make sure all installed packages have the same version.

See Getting Started for installation and the first run.

Setup

Plain console:

SQLiteOptions options = new SQLiteOptionsBuilder("app.db")
    .UseMinimumSqliteVersion(SQLiteMinimumVersion.V3_36) // declare the SQLite floor
    .UseWalMode()                     // optional, allows concurrent writes
    .UseGeneratedMaterializers()      // requires SQLite.Framework.SourceGenerator
    .DisableReflectionFallback()      // throws if a query needs runtime reflection
    .Build();

using SQLiteDatabase db = new(options);
await db.Schema.CreateTableAsync<Project>();

With dependency injection:

services.AddSQLiteDatabase<AppDatabase>(b =>
{
    b.DatabasePath = dbPath;
    b.UseWalMode()
        .UseGeneratedMaterializers()
        .DisableReflectionFallback();
});

The default lifetime is Singleton, which is the right choice for desktop and mobile apps. See Dependency Injection for more.

A custom subclass keeps your tables in one place and gives you a hook for schema setup:

public class AppDatabase : SQLiteDatabase
{
    public AppDatabase(SQLiteOptions options) : base(options) { }

    public SQLiteTable<Project> Projects => Table<Project>();
    public SQLiteTable<ProjectTask> Tasks => Table<ProjectTask>();

    public async Task InitializeAsync()
    {
        await Schema.CreateTableAsync<Project>();
        await Schema.CreateTableAsync<ProjectTask>();
    }
}

// At app startup, after the service provider is built:
await services.GetRequiredService<AppDatabase>().InitializeAsync();

Defining models

A model is a plain class. The attributes come from System.ComponentModel.DataAnnotations, System.ComponentModel.DataAnnotations.Schema and SQLite.Framework.Attributes.

[Table("Project")]
public class Project
{
    [Key]
    [AutoIncrement]
    public int Id { get; set; }

    [Required]
    public required string Name { get; set; }

    [Indexed]
    public required int CategoryId { get; set; }
}

Keep entity classes to the columns of the table. There are no navigation properties. To load related rows, query them yourself or build a DTO with a Select projection or a join.

The most common attributes:

  • [Key] plus [AutoIncrement]. SQLite assigns the id and writes it back to the entity after Add.
  • [Required]. The column is NOT NULL. Nullable types like string? or int? map to nullable columns.
  • [Indexed]. Creates an index. You can make it unique, give it a name or make it composite by using the same name on more than one column.
  • [Column("...")] and [Table("...")]. Rename a column or a table.
  • [WithoutRowId]. A class-level attribute. The primary key must not be [AutoIncrement].
  • [StrictTable]. A class-level attribute. SQLite enforces declared column types on every write. Requires SQLite 3.37.0 or newer.
  • [NotMapped]. Leaves a property out of the database. The property stays on the class but maps to no column.

Schema setup is safe to call on every startup because it uses CREATE TABLE IF NOT EXISTS. Track migrations with the versioned migration runner, db.Schema.Migrations(). See Defining Models for the full list.

Foreign keys

Mark a foreign key column with [ReferencesTable(typeof(Parent))]. The framework emits an inline REFERENCES "Parent"("Id") clause and infers the parent's primary key.

public class Book
{
    [Key]
    public int Id { get; set; }
    public required string Title { get; set; }

    [ReferencesTable(typeof(Author), OnDelete = SQLiteForeignKeyAction.Cascade)]
    public int AuthorId { get; set; }
}

Set OnDelete, OnUpdate or Deferred for additional behavior. SetNull requires the column to be nullable. Pass a column name to target a non-primary-key column with [ReferencesTable(typeof(Country), nameof(Country.Code))].

For composite keys, declare the foreign key in OnModelCreating:

protected override void OnModelCreating(SQLiteModelBuilder builder)
{
    builder.Entity<OrderLine>()
        .ForeignKey<Order>(
            l => new { l.OrderId, l.OrderVersion },
            o => new { o.Id, o.Version },
            onDelete: SQLiteForeignKeyAction.Cascade);
}

Data types

.NET SQLite Notes
int, long, short, byte, bool, enum INTEGER
DateTime, DateOnly, TimeOnly, TimeSpan, DateTimeOffset INTEGER Stored as ticks. DateTimeOffset does not preserve the offset, so save it in a separate column if you need it.
float, double REAL
decimal REAL Stored as double, so values past about 15 to 16 digits can lose precision.
string, char TEXT
Guid TEXT Lowercase hyphenated.
byte[] BLOB

All types also work as nullable. See Data Types for the full list.

CRUD

SQLiteTable<Project> projects = db.Table<Project>();

await projects.AddAsync(new Project { ... });            // INSERT, sets Id back
await projects.AddRangeAsync(items);                     // wrapped in a transaction
await projects.AddOrUpdateAsync(item);                   // INSERT OR REPLACE
await projects.UpdateAsync(item);                        // by primary key
await projects.RemoveAsync(item);                        // by primary key
await projects.RemoveRangeAsync(items);
await projects.ClearAsync();                             // delete all rows
await db.Schema.DropTableAsync<Project>();               // DROP TABLE

Bulk operations skip the round-trip through .NET:

await projects.Where(p => p.CategoryId == 5).ExecuteDeleteAsync();

await projects.Where(p => p.CategoryId == 5)
    .ExecuteUpdateAsync(s => s
        .Set(p => p.Name, "Renamed")
        .Set(p => p.Description, p => p.Description + " (archived)"));

Upsert with ON CONFLICT:

await projects.UpsertAsync(p, c => c.OnConflict(x => x.Id).DoUpdateAll());
await projects.UpsertAsync(p, c => c.OnConflict(x => new { x.Id, x.CategoryId }).DoUpdate(x => x.Name));
await projects.UpsertAsync(p, c => c.OnConflict(x => x.Id).DoNothing());

Insert from another query:

await db.Table<ProjectArchive>().InsertFromQueryAsync(
    db.Table<Project>()
        .Where(p => !p.IsActive)
        .Select(p => new ProjectArchive { Id = p.Id, Name = p.Name }));

See CRUD Operations and Bulk Operations.

Querying

db.Table<T>() returns SQLiteTable<T>, which is an IQueryable<T>. You chain LINQ methods on it. The terminal method runs the query.

List<Project> all    = await projects.ToListAsync();
Project[]     arr    = await projects.ToArrayAsync();
Project?      one    = await projects.FirstOrDefaultAsync(p => p.Id == id);
Project       single = await projects.SingleAsync(p => p.Id == id);
int           count  = await projects.CountAsync(p => p.CategoryId == 1);
bool          any    = await projects.AnyAsync(p => p.Name == "x");
decimal       sum    = await db.Table<ProjectTask>().SumAsync(t => t.SortOrder);
Dictionary<int,string> dict =
    await projects.ToDictionaryAsync(p => p.Id, p => p.Name);

Compose freely:

var page = await projects
    .Where(p => p.CategoryId == catId)
    .OrderBy(p => p.Name)
    .Skip((pageIndex - 1) * pageSize)
    .Take(pageSize)
    .Select(p => new { p.Id, p.Name })
    .ToListAsync();

Use Contains for IN:

int[] ids = [1, 2, 3];
await projects.Where(p => ids.Contains(p.Id)).ToListAsync();

See Querying for everything else.

Joins

There are no navigation properties, joins are written explicitly:

var rows = await (
    from t in db.Table<ProjectTask>()
    join p in db.Table<Project>() on t.ProjectId equals p.Id
    where p.CategoryId == catId
    select new { t.Title, ProjectName = p.Name }
).ToListAsync();

For a left join, add into and DefaultIfEmpty(). For a cross join, chain froms without join. You can mix inner and left joins freely. See Joins.

Subqueries

Any IQueryable can be used inside a Where clause as a subquery. Contains produces IN (SELECT ...). Aggregates on a subquery (such as Max, Min or Count) become a scalar value. An inner query can read columns from the outer row, which is a correlated subquery. See Subqueries.

Grouping

LINQ query syntax produces SQL GROUP BY:

var stats = await (
    from p in db.Table<Project>()
    group p by p.CategoryId into g
    where g.Count() > 1            // becomes HAVING
    select new { CategoryId = g.Key, Count = g.Count() }
).ToListAsync();

If you call db.Table<T>().GroupBy(...).ToListAsync(), the rows come back without a SQL GROUP BY and the framework builds the groups in memory. The source generator handles common key shapes such as a single property, an anonymous type or simple arithmetic. See Grouping and Aggregates.

Expressions translated to SQL

Inside Where and Select you can use:

  • Arithmetic: +, -, *, /, %.
  • Strings: Length, ToUpper, ToLower, Trim, Contains, StartsWith, EndsWith, Replace, Substring, IndexOf, + and Concat, string.Join, string.IsNullOrEmpty and string.IsNullOrWhiteSpace. StringComparison.OrdinalIgnoreCase works on Contains, StartsWith and EndsWith.
  • Math: Math.Abs, Round, Floor, Ceiling, Pow, Sqrt, Exp, Log, Log10, Sign, Max, Min.
  • DateTime, DateOnly, TimeOnly, DateTimeOffset and TimeSpan parts (Year, Month, Day, Hour, DayOfWeek and so on) plus arithmetic methods (AddDays, Subtract and friends).
  • DateTime.IsLeapYear over a constant, captured year or translated integer expression.
  • Binary data: byte[] length, equality and Contains of one byte.
  • The ?? operator turns into COALESCE.
  • Captured local variables become parameters automatically.

See Expressions for the full list.

Transactions

await using SQLiteTransaction tx = await db.BeginTransactionAsync();
await db.Table<Project>().AddAsync(p);
await db.Table<ProjectTask>().AddRangeAsync(tasks, runInTransaction: false);
await tx.CommitAsync();
// no Commit -> auto rollback on dispose

Nested transactions use SQLite savepoints. Keep transactions short because they hold the write lock. See Transactions.

Raw SQL

// Wrapped in a subquery, so it selects every mapped column.
var rows = await db.FromSql<Project>(
    "SELECT * FROM Project WHERE CategoryId = @cat",
    new SQLiteParameter { Name = "@cat", Value = 5 }
).ToListAsync();

// Direct execution, no wrapping. Column names must match property names. Alias them in the SQL if they do not.
var dtos = await db.QueryAsync<MyDto>(
    "SELECT Name AS Title, Id FROM Project WHERE CategoryId = @cat",
    new { cat = 5 });

int affected = await db.ExecuteAsync("DELETE FROM Project WHERE Id = @id", new { id = 5 });
int count = (await db.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM Project"))!;

The direct methods are Query, QueryFirst, QueryFirstOrDefault, QuerySingle, QuerySingleOrDefault, ExecuteScalar and Execute.

You can also see the SQL that LINQ would produce:

string sql = db.Table<Project>().Where(p => p.CategoryId == 5).ToSql();
SQLiteCommand cmd = db.Table<Project>().Where(p => p.CategoryId == 5).ToSqlCommand();

See Raw SQL.

Hooks and global filters

Configure them on the options builder:

.OnAdd<Project>(p => p.CreatedAt = DateTime.UtcNow)
.OnUpdate<Project>(p => p.UpdatedAt = DateTime.UtcNow)
.OnRemove<Project>((db, p) =>
{
    p.IsDeleted = true;
    db.Table<Project>().Update(p);
    return false;
})
.AddQueryFilter<ISoftDelete>(e => !e.IsDeleted)

AddQueryFilter<T> runs on every query for matching entities, plus on ExecuteUpdate and ExecuteDelete. The registration type can be an interface, so you cover many entity types in one line. To skip filters in a single query, call .IgnoreQueryFilters().

OnAction is the cross-entity hook that works well with AOT. Your hook returns the action to actually run, like Add, Update, Remove, AddOrUpdate or Skip.

Multi-threading

A single shared SQLiteDatabase is safe to use across many threads. Every command takes an internal lock. With WAL mode on, reads never wait for writers and many writers can run at the same time. Do not pass a SQLiteTransaction between threads. It belongs to the async flow that opened it. See Multi-threading.

AOT and the source generator

When you publish with PublishAot=true:

  1. Reference SQLite.Framework.SourceGenerator and call .UseGeneratedMaterializers() on the builder.
  2. Add a TrimmerRootDescriptor.xml that preserves every type listed in the data types table above. The trimmer would otherwise remove them.
  3. Methods that build Select projections directly may produce IL2026 warnings at publish time. Suppress them with [UnconditionalSuppressMessage("AOT", "IL2026", Justification = "...")].
  4. Use .DisableReflectionFallback() in tests and CI to fail fast on shapes the generator does not cover.

The generator runs once per project. Each project that builds queries needs its own reference and its own call to .UseGeneratedMaterializers(). See Native AOT and Source Generator.

Common pitfalls

  • AddAsync always lets SQLite assign the [AutoIncrement] id. Any value you set on the entity is overwritten. Use AddOrUpdateAsync to insert at a specific id.
  • [NotMapped] collections are not loaded by queries. Fill them yourself.
  • decimal loses precision past about 15 digits because it is stored as double. For exact arithmetic, store the value as a string.
  • DateTimeOffset round trips drop the offset.
  • A SQLiteTransaction is bound to the async flow that opened it. Do not pass it across threads.
  • Inside an outer transaction, pass runInTransaction: false to AddRangeAsync, UpdateRangeAsync and RemoveRangeAsync to avoid a redundant savepoint.
  • FromSql<T> wraps your SQL in a subquery and selects every mapped column. If your SQL is missing a column, it throws. Either select all columns, project into a smaller type or use Query<T>, which does not wrap.

Working with an AI agent

If you write code with an AI coding agent, give it this Overview page as a reference. See AI Assistance for how to wire it into Claude Code, Cursor, Copilot and other agents.

Clone this wiki locally