CSharpDB 2.7.0

CSharpDB

A zero-dependency embedded database engine for .NET with full SQL support, a typed NoSQL Collection API, built-in full-text search, ETL pipelines, and an ADO.NET provider — all in a single NuGet package.

Installation

dotnet add package CSharpDB

Quick Start

await using var db = await Database.OpenAsync("myapp.db");

// SQL
await db.ExecuteAsync("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)");
await db.ExecuteAsync("INSERT INTO users VALUES (1, 'Alice', 'alice@example.com')");
var rows = await db.QueryAsync("SELECT * FROM users WHERE name = 'Alice'");

// NoSQL Collections
var customers = await db.GetCollectionAsync<Customer>();
await customers.PutAsync("c1", new Customer("Alice", "alice@example.com"));
var alice = await customers.GetAsync("c1");

// Full-Text Search
await db.EnsureFullTextIndexAsync("fts_users", "users", ["name", "email"]);
var hits = await db.SearchAsync("fts_users", "alice");

Features

Database Modes

CSharpDB supports three storage modes to fit different use cases:

// File-based — persistent storage with WAL
await using var db = await Database.OpenAsync("app.db");

// In-memory — fast, ephemeral, ideal for testing
await using var db = await Database.OpenInMemoryAsync();

// Hybrid — lazy-resident with file-backed persistence
await using var db = await Database.OpenHybridAsync("app.db");

SQL Support

A full SQL engine with DDL, DML, JOINs, aggregates, CTEs, subqueries, views, triggers, and stored procedures.

DDL

await db.ExecuteAsync("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL)");
await db.ExecuteAsync("CREATE UNIQUE INDEX idx_email ON users (email)");
await db.ExecuteAsync("CREATE VIEW active_users AS SELECT * FROM users WHERE active = 1");
await db.ExecuteAsync("ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1");

DML & Queries

await db.ExecuteAsync("INSERT INTO orders VALUES (1, 1, 99.50)");
await db.ExecuteAsync("UPDATE orders SET total = 109.50 WHERE id = 1");
await db.ExecuteAsync("DELETE FROM orders WHERE id = 1");

// JOINs, aggregates, CTEs, subqueries
var rows = await db.QueryAsync(@"
    WITH top_customers AS (
        SELECT customer_id, SUM(total) AS spend
        FROM orders
        GROUP BY customer_id
        HAVING spend > 500
    )
    SELECT u.name, tc.spend
    FROM users u
    INNER JOIN top_customers tc ON u.id = tc.customer_id
    ORDER BY tc.spend DESC
    LIMIT 10");

Supported SQL Features

• Column types: INTEGER, REAL, TEXT, BLOB
• Constraints: PRIMARY KEY, IDENTITY, NOT NULL, UNIQUE
• JOINs: INNER, LEFT OUTER, RIGHT OUTER, CROSS
• Set operations: UNION, INTERSECT, EXCEPT
• Expressions: LIKE, IN, BETWEEN, IS NULL, EXISTS
• Aggregates: COUNT, SUM, AVG, MIN, MAX
• Pagination: LIMIT / OFFSET
• Common Table Expressions (WITH clause)
• Subqueries (scalar, IN, EXISTS)
• Views, Triggers, Stored Procedures
• ANALYZE for statistics collection

Collection API (NoSQL)

A typed document API that serializes C# objects to JSON and stores them in B+trees with secondary indexing.

public record Customer(string Name, string Email, Address Address, string[] Tags);
public record Address(string City, string State, string Zip);

var customers = await db.GetCollectionAsync<Customer>();

// CRUD
await customers.PutAsync("c1", new Customer("Alice", "alice@example.com",
    new("Portland", "OR", "97201"), ["premium"]));
var alice = await customers.GetAsync("c1");
await customers.DeleteAsync("c1");
var count = await customers.CountAsync();

// Secondary indexes on any field path
await customers.EnsureIndexAsync(c => c.Email);
var results = await customers.FindByIndexAsync(c => c.Email, "alice@example.com");

// Nested path indexes
await customers.EnsureIndexAsync(c => c.Address.City);
var portlanders = await customers.FindByIndexAsync(c => c.Address.City, "Portland");

Full-Text Search

Built-in full-text search with Unicode-aware tokenization, automatic index maintenance on INSERT/UPDATE/DELETE, and term-intersection queries — all stored in the same database file with zero external dependencies.

// Create a table and index
await db.ExecuteAsync("CREATE TABLE articles (id INTEGER PRIMARY KEY, title TEXT, body TEXT)");
await db.ExecuteAsync("INSERT INTO articles VALUES (1, 'Getting Started', 'Learn to build fast apps')");

await db.EnsureFullTextIndexAsync("fts_articles", "articles", ["title", "body"]);

// Search (AND semantics — all terms must match)
var hits = await db.SearchAsync("fts_articles", "fast apps");

foreach (var hit in hits)
    Console.WriteLine($"Row {hit.RowId}, Score {hit.Score:F2}");

// Index options
await db.EnsureFullTextIndexAsync("fts_articles", "articles", ["title", "body"],
    new FullTextIndexOptions
    {
        Normalization = NormalizationForm.FormKC,
        LowercaseInvariant = true,
        StorePositions = true,
    });

// Drop with standard SQL
await db.ExecuteAsync("DROP INDEX fts_articles");

Transactions

ACID transactions with WAL-based durability and single-writer concurrency.

await db.BeginTransactionAsync();
try
{
    await db.ExecuteAsync("INSERT INTO orders VALUES (1, 1, 50.00)");
    await db.ExecuteAsync("UPDATE users SET balance = balance - 50 WHERE id = 1");
    await db.CommitAsync();
}
catch
{
    await db.RollbackAsync();
    throw;
}

ETL Pipelines

Built-in pipeline runtime with CSV/JSON connectors, transforms, validation, checkpoint/resume, and run history.

var orchestrator = new PipelineOrchestrator(componentFactory, checkpointStore, runLogger);

await orchestrator.ExecuteAsync(new PipelineRunRequest
{
    Package = new PipelinePackageDefinition
    {
        Name = "import-customers",
        Source = new() { Type = "csv", Properties = { ["path"] = "customers.csv" } },
        Destination = new() { Type = "json", Properties = { ["path"] = "output.json" } },
        ErrorMode = ErrorMode.SkipBadRows,
        MaxRejects = 100,
    },
    Mode = PipelineRunMode.Run,
});

ADO.NET Provider

Standard System.Data.Common implementation for compatibility with ORMs, Dapper, and existing data access code.

await using var connection = new CSharpDbConnection("Data Source=myapp.db");
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = "SELECT name, email FROM users WHERE id = @id";
command.Parameters.Add(new CSharpDbParameter("@id", 1));

await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine($"{reader.GetString(0)} — {reader.GetString(1)}");

Indexes

Multiple index types for optimal query performance:

Maintenance & Diagnostics

// WAL checkpoint
await db.CheckpointAsync();

// Export / backup
await db.SaveToFileAsync("backup.db");

// Storage inspection (via Client SDK)
var report = await client.InspectStorageAsync("myapp.db");
var walReport = await client.CheckWalAsync("myapp.db");
var indexReport = await client.CheckIndexesAsync("myapp.db");

// Vacuum
await client.VacuumAsync();

Ecosystem

CSharpDB ships with a full set of tools beyond the core library:

Architecture

┌──────────────────────────────────────────────────┐
│                  CSharpDB (meta-package)         │
├──────────────────────────────────────────────────┤
│  Client SDK  │  ADO.NET Provider  │  Diagnostics │
├──────────────────────────────────────────────────┤
│                 Engine (Database API)            │
│   SQL · Collections · Full-Text Search · Pipes   │
├──────────────────────────────────────────────────┤
│          Execution (Query Planner & Ops)         │
├──────────────────────────────────────────────────┤
│       SQL Parser (Lexer · Parser · AST)          │
├──────────────────────────────────────────────────┤
│              Storage Engine                      │
│   B+Tree · Pager · WAL · Checkpointing · Index   │
├──────────────────────────────────────────────────┤
│           Primitives (DbType · DbValue · Schema) │
└──────────────────────────────────────────────────┘

Client Transports

The Client SDK supports multiple transport modes for different deployment scenarios:

// Direct (in-process)
var client = CSharpDbClient.Create(new CSharpDbClientOptions
{
    Transport = CSharpDbTransport.Direct,
    DataSource = "myapp.db",
});

// HTTP (REST API)
var client = CSharpDbClient.Create(new CSharpDbClientOptions
{
    Transport = CSharpDbTransport.Http,
    Endpoint = "https://localhost:5001",
});

// gRPC
var client = CSharpDbClient.Create(new CSharpDbClientOptions
{
    Transport = CSharpDbTransport.Grpc,
    Endpoint = "https://localhost:5002",
});

Included Packages

The CSharpDB meta-package pulls in the full library set:

License

MIT — see LICENSE.