SQLite

SQLite

Upgrading from v3? Undo the old setup 🎉

v3 required a <DbProviderFactories> entry for System.Data.SQLite in machine.config, because the reader ran inside the Visual Studio T4 host. v4 does not. The efrpg tool carries Microsoft.Data.Sqlite as an ordinary NuGet dependency, so you can remove that entry. See Upgrading from v3 to v4.

Settings

Settings.DatabaseType     = DatabaseType.SQLite;
Settings.TemplateType     = TemplateType.EfCore10;   // or EfCore9, EfCore8
Settings.GeneratorType    = GeneratorType.EfCore;
Settings.ConnectionString = @"Data Source=C:\data\Northwind.db";

Your project needs the Microsoft.EntityFrameworkCore.Sqlite NuGet package. The generated OnConfiguring calls optionsBuilder.UseSqlite(...).

Paths - the thing that catches everyone out

A relative Data Source is resolved against the process working directory. When the generator runs, that process is a child of Visual Studio, and its working directory is almost certainly not your project folder. So Data Source=Northwind.db will usually create a new, empty database somewhere unhelpful rather than open yours - and SQLite does that silently, with no error. You get an empty generated file and no clue why.

Use an absolute path, or build one from Settings.Root, which is the folder containing your .tt file:

Settings.ConnectionString = "Data Source=" + Path.Combine(Settings.Root, "Northwind.db");

Two other options worth knowing:

// Read-only. A sensible default for a generator, and it turns the silent-empty-database
// failure into a proper error.
@"Data Source=C:\data\Northwind.db;Mode=ReadOnly"

// Encrypted with SQLCipher or SQLite SEE
@"Data Source=C:\data\Northwind.db;Password=***"

What gets read

Feature	Supported	Notes
Tables, views, columns	✔
Primary and foreign keys	✔	Read via PRAGMA foreign_key_list
Indexes	✔
INTEGER PRIMARY KEY autoincrement	✔
Column defaults	✔
Triggers	✔
Column comments	✘	SQLite stores none
Stored procedures and functions	n/a	SQLite has none, so none are generated
Sequences	n/a
Synonyms	n/a
Spatial types	n/a	SpatiaLite is not supported

The default schema is main.

Type mapping

SQLite has dynamic typing with five storage classes, and a column's declared type is free text - it will happily accept CREATE TABLE t (a BANANA). The reader maps the common declared spellings and falls back to string for anything it does not recognise, which mirrors SQLite's own type-affinity rules.

Declared type	C#
integer, int, int2, int8, bigint, mediumint, unsigned big int	long
smallint	int
boolean	bool
real, float, double, double precision, decimal	double
numeric	decimal
text, varchar, nvarchar, char, nchar, clob, varying character, native character	string
blob	byte[]
date, datetime	DateTime
anything else	string

Two of these surprise people, and both are deliberate:

integer maps to long, not int, because SQLite's INTEGER storage class is 64-bit. If you know a column is really 32-bit and want an int property:

Settings.UpdateColumn = delegate(Column column, Table table, List<EnumDefinition> enumDefinitions, List<JsonColumnMapping> jsonColumnMappings)
{
    if (table.DbName == "Orders" && column.DbName == "Quantity")
        column.PropertyType = "int";
};

decimal maps to double, because SQLite has no decimal storage class - a DECIMAL column is stored as a float. Mapping it to decimal would hide that and lose precision somewhere less obvious. If you need exact decimals in SQLite, store them as TEXT and convert in your own code.

SQLite as a test database

SQLite in in-memory mode is a good stand-in for a relational database in tests, and often a better one than Microsoft.EntityFrameworkCore.InMemory, because it actually enforces relational constraints:

var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();   // keep it open - closing it destroys the database

var options = new DbContextOptionsBuilder<MyDbContext>().UseSqlite(connection).Options;

using var context = new MyDbContext(options);
context.Database.EnsureCreated();

See FakeDbContext for how that compares to the generated fake context.

See also

• Connection strings
• FakeDbContext
• Settings Reference
• Microsoft.Data.Sqlite connection strings