Sqlity

Sqlity is an educational SQLite-like embedded database engine written in C# for .NET 10. The project is intentionally scoped for learning and public architecture exploration, not for production use or SQLite compatibility.

Goals

• Learn database internals by building them incrementally.
• Keep the implementation technically correct and readable.
• Show serious systems design without hiding core ideas behind unnecessary abstractions.
• Grow from storage primitives to SQL execution, ADO.NET, and EF Core integration.

Current milestone

The repository now contains a storage engine, an executable SQL layer, secondary index support with a rule-based query planner, a complete ADO.NET provider, full transaction support with crash recovery, and an EF Core 10 provider:

• a single-file database format
• a fixed 4096-byte page model
• binary file and page headers
• row serialization primitives with NULL value support
• free-page list primitives; emptied B+ tree leaf pages are recycled back to the free list
• a persisted table catalog and schema serializer (version 2 with per-column nullable flags)
• single-page table storage with ordered primary-key insertion
• slotted-page compaction on delete (correct pointer array and cell content compaction)
• in-place and resize-safe row updates
• SQL execution for CREATE TABLE, INSERT, SELECT, DELETE, UPDATE, CREATE INDEX, and CREATE UNIQUE INDEX
• nullable columns (NOT NULL constraint), NULL literals, and IS NULL / IS NOT NULL in WHERE
• INNER JOIN and LEFT JOIN with compound WHERE expressions
• secondary B+ trees with sort-preserving key encoding for all column types
• persisted index catalog (__sqlity_indexes) that survives reopen
• automatic index maintenance on INSERT, DELETE, and UPDATE
• rule-based logical/physical query planner: equality predicates on indexed columns produce an index seek; unmatched predicates become a post-filter; ORDER BY on an indexed column triggers an ordered index scan
• CREATE [UNIQUE] INDEX with duplicate-key enforcement on unique indexes
• full ADO.NET provider: SqlityConnection, SqlityCommand, SqlityDataReader, SqlityParameter
• BEGIN / BEGIN TRANSACTION / COMMIT / ROLLBACK transaction boundaries
• rollback journal: every write is journaled before it happens; a stale journal on reopen triggers automatic crash recovery
• auto-commit for statements executed outside an explicit BEGIN
• multi-statement batch execution: multiple ;-separated statements in a single Execute call
• ORDER BY (single or multi-column, ASC/DESC); index-aware: a secondary index on the sort column avoids a sort step
• LIMIT and OFFSET for result pagination
• aggregate functions: COUNT(*), COUNT(col), SUM, MIN, MAX, AVG (returns double)
• GROUP BY (single or multi-column) with strict column-validation: every non-aggregate SELECT column must appear in GROUP BY
• HAVING with a single aggregate comparison (e.g. HAVING COUNT(*) > 5)
• storage, query, CLI, ADO.NET, and EF Core test coverage

Repository layout

src/
  Sqlity.Core      Shared constants and future cross-layer primitives
  Sqlity.Storage   File format, catalog persistence, row encoding, pager, and single-page table storage
  Sqlity.Query     MVP SQL parsing, binding, and execution
  Sqlity.Ado       ADO.NET provider (DbConnection, DbCommand, DbDataReader)
  Sqlity.EFCore    EF Core 10 relational provider (`UseSqlity`)
samples/
  Sqlity.Cli       Tiny console app for opening a `.sqlity` file and executing one SQL statement
tests/
  Sqlity.Ado.Tests
  Sqlity.Cli.Tests
  Sqlity.Query.Tests
  Sqlity.Storage.Tests
docs/
  architecture.md
  storage-engine.md
  query-engine.md
  ado-provider.md
  efcore-provider.md
  next-roadmap.md

Why page-based storage first?

Databases almost always converge on page-based I/O because disks and operating systems work best with block-sized reads and writes. A page gives the engine a stable unit for caching, addressing, serialization, crash recovery, and B-tree navigation.

Sqlity uses:

• page 0 as the database header page
• pages 1..N as regular storage pages
• fixed 4096-byte pages
• little-endian binary encoding
• slotted-page ideas for B-tree-friendly layouts

Initial storage decisions

1. Database header page stores file-wide metadata such as page size, page count, the system-catalog root page id, and the free-list head.
2. Regular pages start with a small generic page header and keep cell payloads at the end of the page so a slot array can grow from the front.
3. Rows are serialized manually with schema-bound type tags and length-prefixed payloads.
4. The system catalog is stored as a normal table leaf page so schemas survive reopen without inventing a second metadata format.
5. Free pages are linked together as a singly linked list for the MVP.
6. Table leaf pages already perform ordered primary-key inserts directly inside the slotted-page layout.
7. B-trees are the long-term table storage structure because they keep point lookups and ordered scans efficient while fitting the page model cleanly.

Incremental roadmap

1. Add root-page search and page split behavior for the B-tree path.
2. Add delete/compaction behavior for table leaf pages. ✅ Done — DELETE and UPDATE are fully implemented with correct slotted-page compaction.
3. Expose the engine through an ADO.NET provider. ✅ Done — SqlityConnection, SqlityCommand, SqlityDataReader, and SqlityParameter are implemented.
4. Add an EF Core provider after the ADO.NET provider is stable. ✅ Done — Sqlity.EFCore provides a full EF Core 10 relational provider with UseSqlity, LINQ query translation, and EnsureCreated/EnsureDeleted.
5. Add transactions, then WAL, once the base storage design is solid.

Documentation

• docs/architecture.md explains how the layers fit together.
• docs/storage-engine.md explains the page model, on-disk layout, and roadmap in detail.
• docs/query-engine.md explains the current SQL surface and its deliberate MVP limits.
• docs/ado-provider.md explains the ADO.NET provider API and how it wraps the query engine.
• docs/efcore-provider.md explains the EF Core provider, type mapping, and limitations.
• docs/transactions.md explains the rollback journal, crash-recovery invariants, and transaction usage.
• docs/next-roadmap.md captures the next concrete milestones.

Creating your own database

Via EF Core

The EF Core provider is the highest-level API. Configure UseSqlity with a file path and use standard EF Core patterns:

using Microsoft.EntityFrameworkCore;
using Sqlity.EFCore;

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlity("demo.sqlity");

    protected override void OnModelCreating(ModelBuilder model)
    {
        model.Entity<User>(b =>
        {
            b.ToTable("Users");
            b.Property(u => u.Id).HasColumnType("INT64").ValueGeneratedNever();
            b.Property(u => u.Name).HasColumnType("STRING");
            b.Property(u => u.Score).HasColumnType("INT64");
        });
    }
}

public class User { public long Id { get; set; } public string Name { get; set; } = ""; public long Score { get; set; } }

using var ctx = new AppDbContext();
ctx.Database.EnsureCreated();

ctx.Users.Add(new User { Id = 1, Name = "Ada", Score = 42 });
ctx.SaveChanges();

var top = ctx.Users.Where(u => u.Score > 10).OrderBy(u => u.Name).ToList();

See docs/efcore-provider.md for type mapping, limitations, and registered services.

Via ADO.NET

The ADO.NET provider is the standard way to interact with Sqlity from .NET code. Use SqlityConnection with a Data Source= connection string, then work with the familiar DbCommand and DbDataReader API.

using System.Data.Common;
using Sqlity.Ado;

using var conn = new SqlityConnection("Data Source=demo.sqlity");
conn.Open();

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = """
        CREATE TABLE users (
            id    INT64   PRIMARY KEY,
            name  STRING,
            score INT64   NOT NULL,
            active BOOLEAN
        );
        """;
    cmd.ExecuteNonQuery();
}

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "INSERT INTO users VALUES (1, 'Ada', TRUE);";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "INSERT INTO users VALUES (2, 'Linus', FALSE);";
    cmd.ExecuteNonQuery();
}

using (var cmd = conn.CreateCommand())
{
    cmd.CommandText = "SELECT id, name FROM users;";
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
        Console.WriteLine($"{reader.GetInt64(0)}: {reader.GetString(1)}");
}

Via QueryEngine (lower-level)

You can also work directly with QueryEngine. Passing a file path creates the database if it does not exist yet, then subsequent Execute(...) calls run against the same file.

using Sqlity.Query;

using var engine = new QueryEngine("demo.sqlity");

engine.Execute("""
    CREATE TABLE users (
        id INT64 PRIMARY KEY,
        name STRING,
        score INT64 NOT NULL,
        is_active BOOLEAN
    );
    """);

engine.Execute("INSERT INTO users VALUES (1, 'Ada', TRUE);");
engine.Execute("INSERT INTO users VALUES (2, 'Linus', FALSE);");

var result = engine.Execute("SELECT id, name FROM users WHERE id = 2;");

foreach (var row in result.Rows)
{
    Console.WriteLine($"{row[0]}, {row[1]}");
}

engine.Execute("UPDATE users SET name = 'Ada Lovelace' WHERE id = 1;");
engine.Execute("DELETE FROM users WHERE id = 2;");

Current limits to keep in mind:

• supported statements are CREATE TABLE, INSERT, SELECT, DELETE, UPDATE, CREATE INDEX, CREATE UNIQUE INDEX, BEGIN / BEGIN TRANSACTION, COMMIT, and ROLLBACK; multiple statements can be batched in a single call
• WHERE supports any column with full AND/OR composition and IS NULL / IS NOT NULL; equality predicates on indexed columns use a secondary B+ tree seek; primary-key equality uses a primary B+ tree point lookup; unmatched predicates apply as a post-filter
• ORDER BY (single or multi-column, ASC/DESC); a secondary index on the leading sort column triggers an index-ordered scan instead of an in-memory sort
• LIMIT and OFFSET for pagination, combinable with ORDER BY
• aggregate functions COUNT(*), COUNT(col), SUM, MIN, MAX, AVG with optional GROUP BY and HAVING
• no subqueries in projections, window functions, or CTEs

That file path is the database. If my-db.sqlity does not exist, Sqlity creates it; if it exists, Sqlity reopens it.

How you can create your own DB and query it today:

using Sqlity.Query;

using var engine = new QueryEngine("my-db.sqlity");
engine.Execute("CREATE TABLE users (id INT64 PRIMARY KEY, name STRING, is_active BOOLEAN);");
engine.Execute("INSERT INTO users VALUES (1, 'Ada', TRUE);");

var result = engine.Execute("SELECT id, name FROM users WHERE id = 1;");

Tiny CLI workflow

There is also a small runnable console app in samples/Sqlity.Cli for the same workflow:

dotnet run --project samples/Sqlity.Cli -- demo.sqlity "CREATE TABLE users (id INT64 PRIMARY KEY, name STRING, is_active BOOLEAN);"
dotnet run --project samples/Sqlity.Cli -- demo.sqlity "INSERT INTO users VALUES (1, 'Ada', TRUE);"
dotnet run --project samples/Sqlity.Cli -- demo.sqlity "SELECT id, name FROM users WHERE id = 1;"

You can also pipe SQL through standard input:

echo "SELECT id, name FROM users WHERE id = 1;" | dotnet run --project samples/Sqlity.Cli -- demo.sqlity

For a larger set of executable command/output examples that is generated from tests, see samples/Sqlity.Cli/README.md.

Running tests

dotnet test Sqlity.slnx

Sqlity Studio

Sqlity Studio is a lightweight developer GUI for the Sqlity embedded database engine, built with .NET 10, Avalonia 11, and CommunityToolkit.Mvvm.

Features

Feature	Description
Database opening	Open or create .sqlity files; recent database list; auto-reopen last database on startup
Schema explorer	Left-panel tree showing Tables, Views, and Indexes populated directly from the Sqlity storage engine
Table browser	Double-click a table to instantly run SELECT * … ORDER BY <pk> LIMIT 100 and display results in a grid
SQL editor	Multi-tab query editor with SQL syntax highlighting (AvaloniaEdit), Ctrl+Enter execution
Results grid	Column headers, sortable rows, clipboard copy, execution time and row count
Schema viewer	Inspect columns, types, nullability, primary key, and indexes for any selected table
Status bar	Shows current file path, table/view/index counts, file size, and global transaction indicator

Running

dotnet run --project samples/Sqlity.Studio

Architecture

• Services/SqlitySession — thin wrapper that owns a StorageEngine + QueryEngine pair and serializes async engine calls
• Services/AppSettings — JSON settings file (recent databases, last opened path) stored in %APPDATA%/Sqlity.Studio/
• ViewModels/ — MVVM ViewModels using CommunityToolkit.Mvvm source generation
• Views/ — Avalonia AXAML views

The Studio references the Sqlity engine directly (Sqlity.Query, Sqlity.Storage) with no networking or plugin layer.