Nautilus.DataProvider.Sqlite 1.0.0-alpha

Nautilus.DataProvider.Sqlite

A SQLite data provider for the Nautilus ecosystem. It implements DataProviderBase from Nautilus.Base and adds SQLite-specific migrations, type mapping, CRUD operations, bulk insert, and metadata queries.

Features

• CRUD operations with sync and async variants.
• Filtered queries, pagination with LIMIT and OFFSET, and record counts.
• Bulk insert in one transaction.
• Migrations with primary keys, composite keys, AUTOINCREMENT, defaults, foreign keys with cascade, and IF NOT EXISTS.
• Identifier validation and double-quote quoting.
• [Table], [Column], [Ignore], [PrimaryKey], [AutoIncrement], [NotNull], [Default], [ConstraintDefault], and [ConstraintReferenceForeignKey].
• File databases and named shared in-memory databases.
• PRAGMA foreign_keys=ON and a 30-second busy timeout on every connection.

Installation

dotnet add package Nautilus.DataProvider.Sqlite

The package targets net10.0 and depends on Nautilus.Base 0.0.11-preview and Microsoft.Data.Sqlite 10.0.13.

Quick start

using Nautilus.Base.Data;
using Nautilus.Base.Data.Attributes;
using Nautilus.Base.Data.Provider;
using Nautilus.DataProvider.Sqlite;

[Table("widgets")]
public class Widget
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }

    [NotNull, NVarchar(100)]
    public string Name { get; set; } = string.Empty;

    public decimal Price { get; set; }
}

using var database = new SqliteDatabase(
    "main",
    "Data Source=app.db;Pooling=False");

var migrator = new SqliteMigrator(
    DbProviderType.Sqlite,
    database.CreateConnection,
    database.CreateDatabase);

migrator.AddTable<Widget>().Run();

var widget = new Widget { Name = "Hammer", Price = 12.50m };
var id = database.Save(widget);

widget.Price = 13.00m;
database.Update(widget);

var loaded = database.Get<Widget>(widget.Id);
var all = database.GetAll<Widget>().ToList();
var page = database.ExecuteQuery<Widget>(new PaginationOptions(1, 20));

database.Delete<Widget>(widget.Id);

Storage convention

SQLite uses dynamic typing. The provider uses the official Microsoft.Data.Sqlite type mapping. Column types are only INTEGER, REAL, TEXT, and BLOB.

DateTime values are converted to UTC before they are written. The read converter returns DateTimeKind.Utc. The stored text has no Z suffix. DateTimeOffset keeps its offset. SQL ordering on DateTimeOffset compares local times, not instants.

decimal values are stored as text so they keep exact precision. SQL SUM, AVG, and ORDER BY on a decimal column need CAST(value AS REAL) or application-side math.

The full official mapping is at https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/types.

In-memory databases

SQLite in-memory databases need a named shared cache. This provider rejects Data Source=:memory: because each connection would open a separate database.

Use this connection string:

var name = $"app-{Guid.NewGuid():N}";
using var database = new SqliteDatabase(
    "main",
    $"Data Source={name};Mode=Memory;Cache=Shared");

The provider keeps one master connection open so the database survives between operations.

Migrations

var migrator = new SqliteMigrator(
    DbProviderType.Sqlite,
    database.CreateConnection,
    database.CreateDatabase);

migrator
    .AddTable<Author>()
    .AddTable<Book>()
    .Run();

The migrator:

• Creates tables in foreign key dependency order.
• Detects circular foreign key dependencies.
• Adds primary keys, composite keys, AUTOINCREMENT, NOT NULL, defaults, and foreign keys with ON DELETE CASCADE and ON UPDATE CASCADE.
• Uses CREATE TABLE IF NOT EXISTS and skips tables that already exist.
• Honors [Table], [Column], [Ignore], [PrimaryKey], [AutoIncrement], [NotNull], [Default], [ConstraintDefault], and [ConstraintReferenceForeignKey].

[Default] and [ConstraintDefault] values are raw SQL. Quote string values yourself, for example [Default("'none'")].

SQLite has no schemas. The Schema value on [Table] is ignored.

Limitations

• Stored procedures are not supported. SQLite has no stored procedures.
• Schemas are not supported. Use attached databases instead.
• Composite primary keys work in migrations and Save. Update, Get by key, and Delete by key require a single primary key.
• SQLite allows one writer at a time. The provider sets a 30-second busy timeout.
• SQLite does not enforce column length or precision facets. The provider validates [NVarchar] length in Save and SaveAsync.
• SaveBulk uses one transaction. It is not a native bulk copy.

Tests

The repository has two test projects:

• Nautilus.DataProvider.Sqlite.UnitTest
• Nautilus.DataProvider.Sqlite.IntegrationTest

The integration tests use temporary SQLite files and named shared in-memory databases. They do not need Docker or Testcontainers.

Analysis

The feature parity analysis and the remaining gaps are in docs/sqlite-parity-analysis.md.

License

Business Source License 1.1. The Change Date is December 31, 2028. The Change License is MIT.