SQL Databases

SQL Databases

Five providers, one model. Birko creates your tables from attributes, so there is no schema file to maintain to get started.

Provider	Store class	Settings
SQLite	SQLiteStore<T> / AsyncSQLiteStore<T>	SqLiteSettings(location, name)
PostgreSQL	PostgreSQLStore<T>	PostgreSqlSettings
MySQL	MySQLStore<T>	MySqlSettings
SQL Server	MSSqlStore<T>	MSSqlSettings
TimescaleDB	TimescaleDBStore<T>	TimescaleDBSettings

All settings classes live in their provider's namespace, e.g. Birko.Data.SQL.SqLite.Stores.

Mapping a model

using Birko.Data.Models;
using Birko.Data.SQL.Attributes;

[Table("Products")]
[CompositeIndex("ux_product_sku", nameof(TenantGuid), nameof(Sku), IsUnique = true)]
public class Product : AbstractDatabaseModel
{
    [MaxLengthField(200), RequiredField]
    public string Name { get; set; } = string.Empty;

    [MaxLengthField(50), IndexedField]
    public string Sku { get; set; } = string.Empty;

    [PrecisionField(18), ScaleField(2)]
    public decimal Price { get; set; }

    [UtcField]
    public DateTime PublishedAt { get; set; }

    public Guid TenantGuid { get; set; }

    [IgnoreField]
    public string DisplayLabel => $"{Sku} — {Name}";
}

Attribute reference

Attribute	Effect
[Table("name")]	table name
[MaxLengthField(n)]	VARCHAR(n) rather than unbounded text
[PrecisionField(p)] + [ScaleField(s)]	DECIMAL(p,s)
[RequiredField]	NOT NULL
[PrimaryField]	primary key
[UniqueField]	unique constraint
[IncrementField]	auto-increment
[IndexedField]	single-column index
[CompositeIndex(name, …props, IsUnique =)]	multi-column index, declared on the class
[NamedField("col")]	map to a different column name
[UtcField]	store a true instant; reads back as Kind = Utc
[IgnoreField]	do not persist

Three traps worth knowing up front

⚠️ [PrecisionField] without [ScaleField] gives scale 0 on SQL Server and MySQL — money truncated to whole units — and REAL on SQLite. Always use both.

⚠️ Plain DateTime is a wall clock, not an instant. It stores the components you give it and reads back as Unspecified. If you mean a moment in time, use [UtcField].

⚠️ The schema is created, never altered. CREATE TABLE IF NOT EXISTS runs on first use; changing a model afterwards does not change an existing table. Use a migration, or drop the table in development. Birko.Health.Data.SQL's schema-drift check reports the mismatch.

Transactions

using var uow = SqlUnitOfWork.FromStore(store);
await uow.BeginAsync();
try
{
    await store.CreateAsync(a);
    await store.CreateAsync(b);
    await uow.CommitAsync();
}
catch { await uow.RollbackAsync(); throw; }

Stores inside the boundary join it automatically, including bulk writes.

💡 MySQL commits implicitly around DDL. If a store's first ever call happens inside your transaction, its CREATE TABLE commits the transaction on MySQL. Touch your stores once at startup if that matters.

Migrations

For controlled schema change, Birko.Data.Migrations.SQL:

public class AddProductSku : IMigration
{
    public long Version => 20260101_0001;

    public void Up(IMigrationContext ctx) =>
        ctx.Schema.AlterCollection("Products").AddField("Sku", FieldType.String, maxLength: 50).Build();
}

The runner records applied versions in its own table. Migration backends also exist for MongoDB, ElasticSearch, RavenDB, CosmosDB, InfluxDB and TimescaleDB.

⚠️ Declare a width or precision in migrations too. A FieldDescriptor without maxLength produces unbounded text; without precision and scale, a bare DECIMAL.

Views

Birko.Data.SQL.View builds SQL views from attribute-mapped view classes, including aggregates. Persistent views are created in the database; on-the-fly views are composed per query.

Provider differences worth knowing

	Note
PostgreSQL	folds unquoted identifiers to lower case — Birko handles this, but it is why identifier handling looks fussy in the source
MySQL	no partial indexes; commits implicitly around DDL
SQL Server	a limited read needs an ORDER BY; Birko synthesises one when you don't supply it
SQLite	runs in WAL mode by default (set JournalMode to change); serialises writes at the file level
TimescaleDB	set TimeColumn in settings and tables become hypertables automatically

Next

• Data and Stores — the non-SQL backends
• Core Concepts — stores, settings, filters