Introduction

✅ 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

// 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();

// 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 });

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();

// 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());

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();
}

// 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($"Data Source={dbPath};Encryption=aes-gcm;Password={userKey}"));
    }
}

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

services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb($"Data Source={dbPath}"));

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

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

services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb($"Data Source={userDataPath};Encryption=aes-gcm;Password={userKey}"));

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

-- 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;

-- 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;

-- 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;

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

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();

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;
}

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

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

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

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

-- 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;

// 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!");

// 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!");

// 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($"{reader["Id"]}: {reader["Name"]}");
}

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")));

using OutWit.Database.EntityFramework.Extensions;

// Bulk Insert — 3x faster than AddRange + SaveChanges
var users = Enumerable.Range(1, 10000)
    .Select(i => new User { Name = $"User{i}", Email = $"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($"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);

// 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();

// 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");

// 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);

// 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

// 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);

// 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();

// ❌ 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();

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

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

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

// ❌ 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();

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

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

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

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