Introduction

What is WitDatabase?

WitDatabase is a high-performance embedded SQL database engine built entirely in C# for the .NET ecosystem. It provides full relational database functionality — SQL queries, ACID transactions, ADO.NET and Entity Framework Core support — without any native dependencies.

Not a Port — Built from Scratch

WitDatabase is not a port of SQLite or any other database. The entire codebase was written from scratch in modern C#, leveraging .NET-specific capabilities:

• Span<T> and Memory<T> — zero-copy data handling, reduced allocations
• ArrayPool<T> and MemoryPool<T> — memory reuse, less GC pressure
• Async/await throughout — non-blocking I/O operations
• Modern C# features — pattern matching, records, nullable reference types

While WitDatabase uses a SQL dialect inspired by SQLite (due to its popularity and familiarity), the implementation is completely original:

The Problem

.NET developers face a difficult choice for embedded databases:

SQLite is excellent but:

• Requires native binaries for each platform (Windows x64, ARM, Linux, macOS...)
• P/Invoke overhead for every database call
• Encryption requires paid extensions or complex setup

LiteDB is pure .NET but:

• NoSQL only — no SQL queries
• No ADO.NET provider
• No Entity Framework Core support
• No JOINs, window functions, or CTEs
• Can't share code with server databases

The Solution

WitDatabase provides SQLite-level functionality in a pure .NET package:

✅ Full SQL support (DDL, DML, JOINs, CTEs, window functions)
✅ Complete ADO.NET provider (DbConnection, DbCommand, DbDataReader)
✅ Full Entity Framework Core provider (DbContext, migrations, LINQ)
✅ Zero native dependencies
✅ Cross-platform (Windows, Linux, macOS, Blazor WebAssembly)
✅ Built-in encryption (AES-256-GCM, ChaCha20-Poly1305)
✅ Multiple storage engines (B+Tree, LSM-Tree)
✅ Modular architecture — replace any component

Core Concepts

Modularity and Extensibility

WitDatabase is built on a provider-based architecture. Every major component is defined by an interface and can be replaced with a custom implementation:

[[Svg Src="./WitDatabaseBuilder.svg" Alt="Fluent API for configuration"]]

Pluggable Interfaces

Custom Implementation Example

// Register a custom encryption provider
ProviderRegistry.Instance.Register<ICryptoProvider>("my-hsm-crypto", 
    parameters => new HsmCryptoProvider(parameters.GetRequired<string>("keyId")));

// Use it in your database
var db = new WitDatabaseBuilder()
    .WithFilePath("secure.witdb")
    .WithBTree()
    .WithEncryption("my-hsm-crypto", new ProviderParameters()
        .Set("keyId", "my-hardware-key"))
    .Build();

This allows you to:

• Integrate with existing infrastructure — use your company's HSM, key vault, or custom storage
• Optimize for specific workloads — write a specialized cache or index
• Extend functionality — add features without modifying core code

Striving for Full Feature Parity

WitDatabase aims to support all features of major server databases (SQL Server, PostgreSQL, MySQL) to enable seamless provider switching. This is an ongoing effort:

v1 (Current) — Production Ready

v2 (Planned) — Enhanced Compatibility

The goal is to make WitDatabase a complete drop-in replacement for development, testing, and portable scenarios.

Primary Use Cases

Use Case 1: Embedded Database for .NET Applications

The most straightforward use case — WitDatabase as a fast, reliable file-based database for applications where a single client connects to the data.

Perfect Fit For

• Desktop applications — WPF, WinForms, Avalonia, MAUI
• Console tools and utilities — data processing, ETL scripts
• Single-user web applications — personal dashboards, local servers
• IoT and edge devices — data collection and local storage
• Games — save files, leaderboards, inventory systems
• CLI applications — configuration, caching, state persistence

Simple and Familiar

// Just point to a file — that's it
using var connection = new WitDbConnection("Data Source=myapp.witdb");
connection.Open();

// Create tables with full SQL
connection.Execute("""
    CREATE TABLE IF NOT EXISTS Products (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        Name VARCHAR(100) NOT NULL,
        Price DECIMAL(10,2) NOT NULL,
        Stock INTEGER DEFAULT 0,
        CreatedAt DATETIME DEFAULT NOW()
    )
""");

// Standard ADO.NET patterns
connection.Execute(
    "INSERT INTO Products (Name, Price, Stock) VALUES (@name, @price, @stock)",
    new { name = "Widget", price = 29.99m, stock = 100 });

// Query with familiar SQL
var products = connection.Query<Product>(
    "SELECT * FROM Products WHERE Price < @maxPrice ORDER BY Name",
    new { maxPrice = 50.00m });

Or Use Entity Framework Core

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Customer> Customers => Set<Customer>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseWitDb("Data Source=shop.witdb");
}

// Use like any other EF Core database
using var context = new AppDbContext();
context.Database.EnsureCreated();

var topProducts = await context.Products
    .Where(p => p.Stock > 0)
    .OrderByDescending(p => p.Price)
    .Take(10)
    .ToListAsync();

Why WitDatabase for Desktop/Single-User Apps?

Performance Expectations

For typical desktop/single-user workloads, WitDatabase provides excellent performance:

• Point queries: Sub-millisecond (0.2ms for PK lookup)
• Small transactions: 5-10ms for typical CRUD operations
• Bulk inserts: ~7ms per 1000 rows in transaction
• Table scans: ~0.5ms per 1000 rows

This is more than sufficient for applications with datasets up to millions of rows and typical interactive response time requirements.

Use Case 2: Testing and Development

WitDatabase excels as a drop-in replacement for server databases during testing and development.

The Challenge

Your production application uses SQL Server, PostgreSQL, or MySQL. For testing, you need:

• Fast test execution (no network latency)
• Isolated test databases (no shared state)
• CI/CD without database infrastructure
• Developer machines without installing heavy databases

The Solution: Swappable Database Provider

With Entity Framework Core, you can use the same DbContext with different providers:

// Your DbContext — works with ANY provider
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) 
        : base(options) { }
    
    public DbSet<User> Users => Set<User>();
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<Product> Products => Set<Product>();
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasIndex(e => e.Email).IsUnique();
            entity.Property(e => e.CreatedAt).HasDefaultValueSql("NOW()");
        });
        
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasOne(o => o.User)
                  .WithMany(u => u.Orders)
                  .HasForeignKey(o => o.UserId)
                  .OnDelete(DeleteBehavior.Cascade);
        });
    }
}

// Program.cs — Production (SQL Server)
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("Production")));

// Program.cs — Development (WitDatabase file)
services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb("Data Source=dev.witdb"));

// Test setup — WitDatabase in-memory
services.AddDbContext<AppDbContext>(options =>
    options.UseWitDbInMemory());

Complete Testing Example

public class OrderServiceTests : IDisposable
{
    private readonly AppDbContext _context;
    private readonly OrderService _service;
    
    public OrderServiceTests()
    {
        // Each test gets a fresh in-memory database
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseWitDbInMemory()
            .Options;
        
        _context = new AppDbContext(options);
        _context.Database.EnsureCreated();
        
        _service = new OrderService(_context);
    }
    
    [Fact]
    public async Task CreateOrder_WithValidUser_ReturnsOrder()
    {
        // Arrange
        var user = new User { Name = "John", Email = "john@example.com" };
        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        
        // Act
        var order = await _service.CreateOrderAsync(user.Id, 99.99m);
        
        // Assert
        Assert.NotNull(order);
        Assert.Equal(user.Id, order.UserId);
        Assert.Equal(99.99m, order.TotalAmount);
    }
    
    [Fact]
    public async Task GetUserOrders_WithMultipleOrders_ReturnsAll()
    {
        // Arrange
        var user = new User { Name = "Jane", Email = "jane@example.com" };
        _context.Users.Add(user);
        _context.Orders.AddRange(
            new Order { User = user, TotalAmount = 10m },
            new Order { User = user, TotalAmount = 20m },
            new Order { User = user, TotalAmount = 30m }
        );
        await _context.SaveChangesAsync();
        
        // Act
        var orders = await _service.GetUserOrdersAsync(user.Id);
        
        // Assert
        Assert.Equal(3, orders.Count);
        Assert.Equal(60m, orders.Sum(o => o.TotalAmount));
    }
    
    public void Dispose() => _context.Dispose();
}

Benefits Over EF Core InMemory Provider

EF Core has a built-in UseInMemoryDatabase(), but it has significant limitations:

WitDatabase tests behave like production — constraints are enforced, transactions work correctly, and SQL executes as expected.

Use Case 3: Portable and Demo Deployment

WitDatabase is perfect for scenarios where you can't or don't want to install a server database.

Same Product, Different Deployments

A powerful pattern: one codebase, multiple deployment options. Your application can support both enterprise server installation and single-user portable mode:

// Startup configuration based on deployment mode
public static void ConfigureDatabase(IServiceCollection services, IConfiguration config)
{
    var deploymentMode = config["DeploymentMode"]; // "Server" or "Portable"
    
    if (deploymentMode == "Server")
    {
        // Enterprise installation — SQL Server with full infrastructure
        services.AddDbContext<AppDbContext>(options =>
            options.UseSqlServer(config.GetConnectionString("SqlServer")));
    }
    else
    {
        // Portable/single-user — WitDatabase, no installation required
        var dbPath = Path.Combine(AppContext.BaseDirectory, "data", "app.witdb");
        services.AddDbContext<AppDbContext>(options =>
            options.UseWitDb(

        
        

            
            Loading...
        
quot;Data Source={dbPath};Encryption=aes-gcm;Password={userKey}"));
    }
}

Benefits of this approach:

• Single codebase — no separate "lite" version to maintain
• Same EF Core DbContext — all business logic works identically
• Customer choice — enterprise customers get SQL Server, individual users get portable version
• Easy trials — prospects can evaluate without IT involvement
• Gradual upgrade path — start portable, migrate to server when needed

Portable Applications

// Application stores data alongside the executable
var dbPath = Path.Combine(AppContext.BaseDirectory, "appdata.witdb");

services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(

        
        

            
            Loading...
        
quot;Data Source={dbPath}"));

• USB-portable applications — run from any drive
• Kiosk applications — self-contained installation
• Field applications — work offline, sync later

Demo and Trial Versions

// Demo version with embedded sample data
services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb("Data Source=demo.witdb;Mode=ReadOnly"));

• Trade show demos — no setup required
• Customer trials — instant evaluation
• Training environments — consistent starting state

Desktop Applications

// User data in application data folder
var userDataPath = Path.Combine(
    Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
    "MyApp",
    "userdata.witdb"
);

services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(

        
        

            
            Loading...
        
quot;Data Source={userDataPath};Encryption=aes-gcm;Password={userKey}"));

• Document-based apps — each document is a .witdb file
• Personal databases — contacts, notes, collections
• Local caching — offline-first with eventual sync

Use Case 4: Cross-Platform and Blazor WebAssembly

WitDatabase runs anywhere .NET runs — including in the browser:

// Blazor WebAssembly — data persisted to IndexedDB
var db = new WitDatabaseBuilder()
    .WithIndexedDbStorage("MyAppDatabase", JSRuntime)
    .WithBTree()
    .WithTransactions()
    .Build();

This enables:

• Offline-first web applications — work without internet
• Client-side data processing — reduce server load
• Privacy-focused apps — data never leaves the browser

Note: There are other projects working on WebAssembly database support (including SQLite's own WASM build). WitDatabase's advantage is unified API across all platforms — the same code works on server, desktop, and browser.

Comparison with Alternatives

vs SQLite

Choose WitDatabase over SQLite when:

• You want zero native dependencies
• You need built-in encryption
• Deployment simplicity matters more than raw performance
• You want to extend/customize components

Choose SQLite over WitDatabase when:

• Maximum performance on large datasets is critical
• You have complex analytical queries with many JOINs
• Native dependencies are acceptable

vs LiteDB

Choose WitDatabase over LiteDB when:

• You need SQL queries
• You want ADO.NET or EF Core
• You need relational features (JOINs, constraints)
• You're sharing code with a server database

Choose LiteDB over WitDatabase when:

• Document model fits your data better
• You prefer LINQ-only API
• You don't need relational features

vs EF Core InMemory

Use WitDatabase for tests when:

• You want tests to behave like production
• You use raw SQL queries
• Constraint validation matters
• Transaction behavior matters

Use EF Core InMemory when:

• Speed is the only concern
• You only test simple CRUD
• You don't use any SQL features

Technical Specifications

Supported Platforms

.NET Versions

SQL Compatibility

WitDatabase implements a SQL dialect compatible with SQLite, with enhancements:

• DDL: CREATE/ALTER/DROP TABLE, INDEX, VIEW, TRIGGER, SEQUENCE
• DML: SELECT, INSERT, UPDATE, DELETE, MERGE
• Queries: JOINs, subqueries, CTEs (WITH), UNION/INTERSECT/EXCEPT
• Functions: 60+ built-in functions (string, math, date/time, JSON)
• Advanced: Window functions, RETURNING clause, UPSERT (ON CONFLICT)
• Transactions: BEGIN, COMMIT, ROLLBACK, SAVEPOINT
• v2: User-defined functions, stored procedures (planned)

Storage Options

Encryption Options

Summary: Positioning in the .NET Ecosystem

WitDatabase fills a unique position in the .NET ecosystem:

[[Svg Src="./dotnet-embedded-databases-comparison.svg" Alt=".NET Embedded Databases"]]

WitDatabase is the right choice when you need:

• Full SQL database in pure .NET
• Zero native dependencies
• Drop-in testing replacement for server databases
• Portable deployment without database servers
• Extensible architecture for custom integrations
• Consistent API across desktop, server, and browser

Key Features

WitDatabase is packed with features that make it a complete embedded database solution for .NET applications. This section provides a detailed overview of the main capabilities.

Full SQL Support

WitDatabase implements a comprehensive SQL dialect based on SQLite syntax, with enhancements for .NET compatibility.

Data Definition Language (DDL)

-- Tables with all constraint types
CREATE TABLE Orders (
    Id INTEGER PRIMARY KEY AUTOINCREMENT,
    CustomerId INTEGER NOT NULL,
    OrderDate DATETIME DEFAULT NOW(),
    TotalAmount DECIMAL(18,2) NOT NULL,
    Status VARCHAR(20) DEFAULT 'pending',
    Version ROWVERSION,
    
    CONSTRAINT FK_Orders_Customers 
        FOREIGN KEY (CustomerId) REFERENCES Customers(Id)
        ON DELETE CASCADE,
    CONSTRAINT CHK_Orders_Amount 
        CHECK (TotalAmount >= 0)
);

-- Indexes (unique, composite, partial, covering)
CREATE INDEX IX_Orders_Date ON Orders(OrderDate);
CREATE UNIQUE INDEX IX_Orders_Code ON Orders(OrderCode);
CREATE INDEX IX_Orders_Active ON Orders(Status) WHERE Status != 'cancelled';
CREATE INDEX IX_Orders_Cover ON Orders(CustomerId) INCLUDE (TotalAmount, Status);

-- Views
CREATE VIEW ActiveOrders AS
SELECT o.*, c.Name AS CustomerName
FROM Orders o
JOIN Customers c ON o.CustomerId = c.Id
WHERE o.Status != 'cancelled';

-- Triggers
CREATE TRIGGER TR_Orders_Audit
AFTER UPDATE ON Orders
BEGIN
    INSERT INTO OrderAudit (OrderId, ChangedAt) VALUES (NEW.Id, NOW());
END;

-- Sequences
CREATE SEQUENCE OrderNumberSeq START WITH 1000 INCREMENT BY 1;

Data Manipulation Language (DML)

-- Standard CRUD
INSERT INTO Customers (Name, Email) VALUES ('John', 'john@example.com');
UPDATE Customers SET Email = 'new@example.com' WHERE Id = 1;
DELETE FROM Customers WHERE Id = 1;

-- RETURNING clause (get affected rows)
INSERT INTO Customers (Name, Email) 
VALUES ('Jane', 'jane@example.com')
RETURNING Id, CreatedAt;

UPDATE Products SET Price = Price * 1.1 
WHERE Category = 'Electronics'
RETURNING Id, Name, Price;

-- UPSERT (INSERT ... ON CONFLICT)
INSERT INTO Products (SKU, Name, Price)
VALUES ('ABC-123', 'Widget', 29.99)
ON CONFLICT (SKU) DO UPDATE SET Price = EXCLUDED.Price;

-- MERGE statement
MERGE INTO TargetTable t
USING SourceTable s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Value = s.Value
WHEN NOT MATCHED THEN INSERT (Id, Value) VALUES (s.Id, s.Value);

-- Bulk operations
TRUNCATE TABLE Logs;

Query Features

-- JOINs (all types)
SELECT c.Name, o.TotalAmount
FROM Customers c
INNER JOIN Orders o ON c.Id = o.CustomerId
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId;

-- Subqueries
SELECT * FROM Products 
WHERE Price > (SELECT AVG(Price) FROM Products);

-- Common Table Expressions (CTEs)
WITH TopCustomers AS (
    SELECT CustomerId, SUM(TotalAmount) AS Total
    FROM Orders
    GROUP BY CustomerId
    HAVING SUM(TotalAmount) > 10000
)
SELECT c.Name, tc.Total
FROM Customers c
JOIN TopCustomers tc ON c.Id = tc.CustomerId;

-- Recursive CTEs
WITH RECURSIVE CategoryTree AS (
    SELECT Id, Name, ParentId, 0 AS Level
    FROM Categories WHERE ParentId IS NULL
    UNION ALL
    SELECT c.Id, c.Name, c.ParentId, ct.Level + 1
    FROM Categories c
    JOIN CategoryTree ct ON c.ParentId = ct.Id
)
SELECT * FROM CategoryTree;

-- Window Functions
SELECT 
    Name,
    Department,
    Salary,
    ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank,
    AVG(Salary) OVER (PARTITION BY Department) AS DeptAvg,
    LAG(Salary) OVER (ORDER BY Salary) AS PrevSalary
FROM Employees;

-- Set operations
SELECT Name FROM Customers
UNION
SELECT Name FROM Suppliers;

Built-in Functions

WitDatabase includes 60+ built-in functions across multiple categories:

Dual Storage Engines

Choose the right storage engine for your workload:

B+Tree Engine (Default)

Optimized for read-heavy workloads and general-purpose use.

var db = new WitDatabaseBuilder()
    .WithFilePath("data.witdb")
    .WithBTree()                    // B+Tree engine
    .WithCacheSize(2000)            // 2000 pages in cache
    .WithPageSize(4096)             // 4KB pages
    .Build();

Characteristics:

• Balanced read/write performance
• Excellent for random access patterns
• Efficient range queries
• Good for OLTP workloads
• Lower memory usage

Best for: General applications, desktop apps, random reads/writes

LSM-Tree Engine

Optimized for write-heavy workloads and sequential writes.

var db = new WitDatabaseBuilder()
    .WithLsmTree("./lsm-data", opts =>
    {
        opts.EnableWal = true;                          // Write-ahead log
        opts.EnableBlockCache = true;                   // Block caching
        opts.BlockCacheSizeBytes = 64 * 1024 * 1024;   // 64MB cache
        opts.MemTableSizeLimit = 4 * 1024 * 1024;      // 4MB memtable
        opts.BackgroundCompaction = true;              // Background compaction
    })
    .Build();

Characteristics:

• Extremely fast sequential writes
• Write amplification reduced via compaction
• Bloom filters for fast negative lookups
• Higher throughput for bulk inserts
• Higher memory usage

Best for: Logging, time-series data, event sourcing, IoT data collection

Comparison

ACID Transactions

Full transaction support with multiple isolation levels.

Basic Transactions

using var connection = new WitDbConnection("Data Source=app.witdb");
connection.Open();

using var transaction = connection.BeginTransaction();
try
{
    using var cmd = connection.CreateCommand();
    cmd.Transaction = transaction;
    
    // Transfer money between accounts
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1";
    cmd.ExecuteNonQuery();
    
    cmd.CommandText = "UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2";
    cmd.ExecuteNonQuery();
    
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

Savepoints

Partial rollback within a transaction:

using var transaction = connection.BeginTransaction();

// First operation
cmd.CommandText = "INSERT INTO Orders ...";
cmd.ExecuteNonQuery();

// Create savepoint
transaction.Save("before_items");

try
{
    // Second operation (might fail)
    cmd.CommandText = "INSERT INTO OrderItems ...";
    cmd.ExecuteNonQuery();
}
catch
{
    // Rollback only to savepoint, keep the order
    transaction.Rollback("before_items");
}

transaction.Commit(); // Order is saved, items are rolled back

Isolation Levels

WitDatabase supports 5 isolation levels:

// Set isolation level
using var transaction = connection.BeginTransaction(IsolationLevel.Serializable);

// Or via connection string
var conn = new WitDbConnection("Data Source=app.witdb;IsolationLevel=Snapshot");

Multi-Version Concurrency Control (MVCC)

MVCC allows readers to never block writers and vice versa.

var db = new WitDatabaseBuilder()
    .WithFilePath("data.witdb")
    .WithMvcc()                                         // Enable MVCC
    .WithDefaultIsolationLevel(IsolationLevel.Snapshot) // Snapshot isolation
    .Build();

How it works:

• Each write creates a new version of the data
• Readers see a consistent snapshot from transaction start
• No read locks needed — excellent for read-heavy workloads
• Background garbage collection removes old versions

Row-Level Locking for fine-grained control:

-- Exclusive lock (FOR UPDATE)
SELECT * FROM Orders WHERE Id = 1 FOR UPDATE;

-- Shared lock (FOR SHARE)
SELECT * FROM Orders WHERE Status = 'pending' FOR SHARE;

-- Non-blocking variants
SELECT * FROM Orders WHERE Id = 1 FOR UPDATE NOWAIT;
SELECT * FROM Orders WHERE Status = 'pending' FOR UPDATE SKIP LOCKED;

Built-in Encryption

Protect your data at rest with industry-standard encryption algorithms.

AES-256-GCM (Default)

Hardware-accelerated on modern CPUs:

// Via builder
var db = new WitDatabaseBuilder()
    .WithFilePath("secure.witdb")
    .WithEncryption("MySecretPassword123!")
    .Build();

// Via connection string
var conn = new WitDbConnection(
    "Data Source=secure.witdb;Encryption=aes-gcm;Password=MySecretPassword123!");

ChaCha20-Poly1305

Ideal for Blazor WebAssembly and ARM devices without AES-NI:

// Requires OutWit.Database.Core.BouncyCastle package
var db = new WitDatabaseBuilder()
    .WithFilePath("secure.witdb")
    .WithBouncyCastleEncryption("MySecretPassword123!")
    .Build();

// Via connection string
var conn = new WitDbConnection(
    "Data Source=secure.witdb;Encryption=chacha20-poly1305;Password=MySecretPassword123!");

Encryption Features

ADO.NET Provider

Full ADO.NET 2.0 compatible implementation:

// Standard ADO.NET patterns
using var connection = new WitDbConnection("Data Source=app.witdb");
connection.Open();

using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Users WHERE Email = @email";
cmd.Parameters.AddWithValue("@email", "john@example.com");

using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(

        
        

            
            Loading...
        
quot;{reader["Id"]}: {reader["Name"]}");
}

Features:

• WitDbConnection, WitDbCommand, WitDbDataReader
• WitDbParameter with named parameters (@param)
• WitDbTransaction with all isolation levels
• WitDbConnectionStringBuilder
• WitDbProviderFactory for DI/ADO.NET factory pattern
• Full async/await support (OpenAsync, ExecuteReaderAsync, etc.)
• Connection pooling
• Schema information (GetSchema)

Entity Framework Core Provider

Complete EF Core integration with migrations support:

public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseWitDb("Data Source=app.witdb");
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasIndex(e => e.Email).IsUnique();
            entity.Property(e => e.Version).IsRowVersion();
        });
        
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasOne(o => o.User)
                  .WithMany(u => u.Orders)
                  .HasForeignKey(o => o.UserId);
        });
    }
}

// Use with DI
services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(Configuration.GetConnectionString("Default")));

Supported Features:

Bulk Extensions

High-performance bulk operations, similar to EFCore.BulkExtensions:

using OutWit.Database.EntityFramework.Extensions;

// Bulk Insert — 3x faster than AddRange + SaveChanges
var users = Enumerable.Range(1, 10000)
    .Select(i => new User { Name = 

        
        

            
            Loading...
        
quot;User{i}", Email = 

        
        

            
            Loading...
        
quot;user{i}@test.com" });

int inserted = await context.BulkInsertAsync(users);

// Bulk Insert with progress reporting
var options = new BulkOptions 
{ 
    BatchSize = 1000,
    BatchProgress = count => Console.WriteLine(

        
        

            
            Loading...
        
quot;Inserted {count} rows")
};
await context.BulkInsertAsync(users, options);

// Bulk Update — update entities by primary key
var usersToUpdate = await context.Users.Take(1000).ToListAsync();
foreach (var user in usersToUpdate)
    user.Status = "Active";

await context.BulkUpdateAsync(usersToUpdate);

// Bulk Delete — delete entities by primary key
var usersToDelete = await context.Users
    .Where(u => u.Status == "Inactive")
    .ToListAsync();
await context.BulkDeleteAsync(usersToDelete);

// Bulk Delete with predicate
await context.BulkDeleteAsync<User>(u => u.Name.StartsWith("Test"));

// Bulk InsertOrUpdate (Upsert) — insert new or update existing
var mixedUsers = new[] {
    new User { Id = 1, Name = "Updated1" },   // update if exists
    new User { Id = 999, Name = "New999" }    // insert if not exists
};
await context.BulkInsertOrUpdateAsync(mixedUsers);

Available Bulk Methods:

LINQ Method Translations:

// All these LINQ methods translate to SQL
context.Users
    .Where(u => u.Email.Contains("@gmail.com"))          // LIKE '%@gmail.com%'
    .Where(u => u.CreatedAt.Year == 2024)                // YEAR(CreatedAt) = 2024
    .Where(u => EF.Functions.Like(u.Name, "J%"))         // LIKE 'J%'
    .OrderBy(u => u.Name.Length)                         // LENGTH(Name)
    .Select(u => new { 
        u.Name, 
        UpperEmail = u.Email.ToUpper(),                  // UPPER(Email)
        Age = DateTime.Now.Year - u.BirthDate.Year       // YEAR(NOW()) - YEAR(BirthDate)
    })
    .ToList();

Parallel and Concurrent Access

WitDatabase supports thread-safe concurrent access for multi-threaded applications.

Parallel Modes

// Via builder
var db = new WitDatabaseBuilder()
    .WithFilePath("data.witdb")
    .WithBTree()
    .WithParallelMode(ParallelMode.Auto)  // Thread-safe mode
    .Build();

// Via connection string
var conn = new WitDbConnection(
    "Data Source=data.witdb;ParallelMode=Auto");

Available Modes:

Concurrent Access Example

// Multiple threads can safely access the database
var db = new WitDatabaseBuilder()
    .WithFilePath("concurrent.witdb")
    .WithBTree()
    .WithParallelMode(ParallelMode.Auto)
    .WithTransactions()
    .Build();

// Parallel reads are lock-free with MVCC
var tasks = Enumerable.Range(0, 10).Select(i => Task.Run(() =>
{
    using var tx = db.BeginTransaction(IsolationLevel.Snapshot);
    var value = db.Get(key, tx);
    // Process value...
    tx.Commit();
}));

await Task.WhenAll(tasks);

Connection Pooling with EF Core

// EF Core manages connection pooling automatically
services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb("Data Source=app.witdb;ParallelMode=Auto"));

// Multiple requests can access the database concurrently
// Each request gets its own connection from the pool

Note: Parallel mode provides thread safety, not parallelism speedup. Embedded databases are I/O bound — parallel writes to the same file don't improve throughput. Use parallel mode when you need concurrent access from multiple threads (e.g., web applications, background services).

Cross-Platform Support

WitDatabase runs everywhere .NET runs:

Blazor WebAssembly

Run WitDatabase entirely in the browser:

// In Blazor component
@inject IJSRuntime JSRuntime

var db = new WitDatabaseBuilder()
    .WithIndexedDbStorage("MyAppDatabase", JSRuntime)
    .WithBTree()
    .WithEncryption("user-password")  // Data encrypted in browser
    .WithTransactions()
    .Build();

await db.InitializeAsync();

// Now use like any other database
await db.PutAsync(key, value);
var result = await db.GetAsync(key);

Browser Features:

• Data persisted to IndexedDB
• Survives page refresh and browser restart
• Encryption works in browser (AES-GCM or ChaCha20)
• Full MVCC and transaction support

Modular Architecture

Every component can be replaced with a custom implementation:

// Register custom providers
ProviderRegistry.Instance.Register<ICryptoProvider>("my-hsm", 
    p => new HsmCryptoProvider(p.GetRequired<string>("keyId")));

ProviderRegistry.Instance.Register<IStorage>("cloud-storage",
    p => new S3Storage(p.GetRequired<string>("bucket")));

// Use custom providers
var db = new WitDatabaseBuilder()
    .WithStorage("cloud-storage", new ProviderParameters()
        .Set("bucket", "my-database-bucket"))
    .WithEncryption("my-hsm", new ProviderParameters()
        .Set("keyId", "production-key-001"))
    .Build();

Pluggable Interfaces:

Performance

This section provides an honest assessment of WitDatabase performance, including benchmarks, comparisons with alternatives, and guidance on what to expect.

Performance Philosophy

Before diving into numbers, it's important to understand the context:

1. SQLite is native C code — comparing managed .NET code to native C isn't apples-to-apples. SQLite will generally be faster for large data operations.

2. The fair comparison is with LiteDB — both are pure managed .NET embedded databases. This is where WitDatabase truly competes.

3. WitDatabase optimizes for .NET workloads — using Span<T>, ArrayPool<T>, and modern .NET features to minimize allocations and maximize throughput for typical application patterns.

Benchmark Environment

All benchmarks were run with:

• OS: Windows 11
• CPU: AMD Ryzen 9 5950X
• Runtime: .NET 10.0
• Tool: BenchmarkDotNet v0.15.8

Executive Summary

Understanding the Pattern

WitDatabase is faster than SQLite when:

• Operations are small (few rows) — P/Invoke overhead dominates SQLite's cost
• Many small transactions — SQLite's overhead per transaction is significant
• Point queries by PK — single B+Tree lookup, no P/Invoke

SQLite is faster than WitDatabase when:

• Operations involve many rows — native C code processes data faster
• Complex queries (JOINs, aggregations) — SQLite's mature optimizer shines
• Large table scans — native iteration is faster than managed code

The fair comparison is with LiteDB — both are pure managed .NET. WitDatabase consistently outperforms LiteDB while providing full SQL support that LiteDB lacks.

Detailed Benchmarks

INSERT Performance

WitDatabase performs well on write operations, particularly for small batches where P/Invoke overhead would dominate SQLite's performance.

INSERT in Transaction

Key findings:

• WitDatabase is comparable to SQLite for transactional inserts (slightly faster due to no P/Invoke)
• WitDatabase is much faster than SQLite for non-transactional inserts (SQLite does fsync per row — this is a SQLite design choice, not a fair comparison)
• WitDatabase is 5.8× faster than LiteDB without transaction
• WitDatabase supports RETURNING clause (LiteDB doesn't)

Bulk INSERT Performance

Key findings:

• For small batches (100 rows), WitDatabase is competitive
• For larger batches, SQLite is faster — native C code advantage becomes significant
• WitDatabase remains comparable to LiteDB across all batch sizes

UPDATE Performance

WitDatabase's streaming UPDATE optimization delivers excellent results.

Key findings:

• WitDatabase is 2× faster than LiteDB for PK updates
• WitDatabase is 4× faster than LiteDB for bulk updates
• UPDATE ... RETURNING is 10× faster than SQLite

DELETE Performance

Optimized DELETE with fast-path constraint validation.

Key findings:

• WitDatabase DELETE is faster than both SQLite and LiteDB
• ~20× faster than SQLite for batch deletes

Transaction Performance

WitDatabase's in-memory transaction staging combined with no P/Invoke overhead makes small transactions very fast.

Key findings:

• WitDatabase is 10× faster than SQLite for small transaction operations — P/Invoke overhead dominates SQLite's cost
• WitDatabase is 2× faster than LiteDB for mixed transactions
• Savepoints are very fast (LiteDB doesn't support savepoints)

Note: The large difference vs SQLite is primarily due to P/Invoke overhead per operation. For transactions with thousands of operations, the gap narrows significantly as actual data processing time dominates.

SELECT Performance

Point Queries (Primary Key Lookup)

Key findings:

• WitDatabase shows excellent results here — but context matters
• The large difference vs SQLite is due to P/Invoke overhead per query — each SQLite call crosses the managed/native boundary
• For small operations like single-row lookups, this overhead dominates
• WitDatabase is 10× faster than LiteDB (fair managed-to-managed comparison)
• B+Tree index implementation is efficient

Full Table Scans

Key findings:

• SQLite is faster for full scans (native code advantage)
• WitDatabase is 2.7× faster than LiteDB for full scans
• WitDatabase is 1.7× faster than LiteDB for ORDER BY

Large Table Performance (10,000 rows)

Key findings:

• For large tables, SQLite's native code advantage is more pronounced
• WitDatabase still beats LiteDB by 1.6× on full scans
• ORDER BY on large tables favors LiteDB (1.9× faster)

Aggregation Performance

Key findings:

• SQLite dominates aggregations (native code + optimized execution)
• WitDatabase is 1.4× faster than LiteDB for GROUP BY
• WitDatabase beats LiteDB in all aggregation scenarios

JOIN Performance

JOINs are currently WitDatabase's weakest area.

Key findings:

• JOINs are 10-50× slower than SQLite
• JOINs are 5-14× slower than LiteDB
• Current nested loop implementation doesn't use hash joins
• This is a known area for future optimization

Memory Usage

Key findings:

• SQLite uses minimal managed memory (native code)
• WitDatabase uses 7× less memory than LiteDB for bulk inserts
• WitDatabase uses 3.5× less memory than LiteDB for updates
• Memory usage is comparable to or better than LiteDB in all scenarios

Performance by Use Case

When WitDatabase Excels

When WitDatabase is Competitive

When to Consider Alternatives

Storage Engine Performance

B+Tree vs LSM-Tree

General guidance:

• B+Tree (default): Best for most applications
• LSM-Tree: Best for logging, time-series, event sourcing

Optimization Tips

1. Use Transactions for Batch Operations

// ❌ Slow — each insert is a separate transaction
foreach (var item in items)
    connection.Execute("INSERT INTO T VALUES (@v)", new { v = item });

// ✅ Fast — single transaction
using var tx = connection.BeginTransaction();
foreach (var item in items)
    connection.Execute("INSERT INTO T VALUES (@v)", new { v = item }, tx);
tx.Commit();

2. Use Bulk Operations in EF Core

// ❌ Slow — N database round trips
context.AddRange(entities);
await context.SaveChangesAsync();

// ✅ Fast — single optimized operation
await context.BulkInsertAsync(entities);

3. Create Indexes for Frequent Queries

-- Speeds up WHERE and JOIN on these columns
CREATE INDEX IX_Orders_CustomerId ON Orders(CustomerId);
CREATE INDEX IX_Orders_Date ON Orders(OrderDate);

4. Use LIMIT for Large Results

// ❌ May load millions of rows
var all = context.Orders.ToList();

// ✅ Paginated — predictable performance
var page = context.Orders
    .OrderBy(o => o.Id)
    .Skip(offset)
    .Take(100)
    .ToList();

5. Choose the Right Storage Engine

// For read-heavy workloads (default)
.WithBTree()

// For write-heavy workloads (logging, events)
.WithLsmTree()

6. Tune Cache Size

// More cache = fewer disk reads (memory tradeoff)
.WithCacheSize(5000)  // 5000 pages (~20MB with 4KB pages)

Benchmark Methodology

All benchmarks use BenchmarkDotNet with:

• Multiple warmup iterations
• Statistical analysis (mean, median, standard deviation)
• Memory allocation tracking
• GC collection counting

To run benchmarks yourself:

cd Benchmarks/OutWit.Database.Benchmarks
dotnet run -c Release

# Specific benchmark
dotnet run -c Release -- --filter "*InsertBenchmarks*"

# Export results
dotnet run -c Release -- --exporters json csv markdown

Future Optimizations

The following optimizations are planned for future releases:

Summary

Performance Reality

WitDatabase vs SQLite:

• For small operations (few rows, point queries, small transactions): WitDatabase can be faster due to no P/Invoke overhead
• For large operations (bulk processing, complex queries, table scans): SQLite is faster due to native C code
• This is the fundamental managed vs native tradeoff

WitDatabase vs LiteDB (fair comparison — both managed .NET):

• WitDatabase is consistently faster across most operations
• WitDatabase uses less memory for bulk operations
• WitDatabase provides full SQL while LiteDB is NoSQL only

When to Choose WitDatabase

✅ Good fit:

• Pure .NET requirement (no native dependencies)
• Typical OLTP patterns (CRUD, small transactions)
• Testing/development database replacement
• Desktop/mobile applications with moderate data sizes
• When you need SQL but can't use SQLite

⚠️ Consider carefully:

• Large datasets (>100K rows) with complex queries
• JOIN-heavy analytical workloads
• Maximum raw performance is critical

❌ Consider SQLite instead:

• Complex multi-table JOINs dominate workload
• Heavy analytical/OLAP queries
• Native dependencies are acceptable
• Absolute maximum performance required

Bottom line: WitDatabase offers the best SQL database option for pure .NET scenarios, outperforming LiteDB while providing SQL features it lacks. For scenarios where native dependencies are acceptable and raw performance on large datasets is critical, SQLite remains the faster choice.