CSharpDB.Storage 1.0.0

CSharpDB

A lightweight, embedded SQL database engine written from scratch in C#. Single-file storage, WAL-based crash recovery, concurrent readers, async-first API, zero external dependencies.

Why CSharpDB?

CSharpDB is a fully self-contained database engine that runs inside your .NET application — no server process, no external dependencies, just a single .db file on disk. It provides two data-access paths: a full SQL engine with JOINs, aggregates, views, triggers, and indexes, and a NoSQL Collection API that bypasses SQL entirely for sub-microsecond key-value reads.

Use cases:

• Embedded databases for desktop, CLI, or IoT applications
• Prototyping and local development without setting up a database server
• Educational reference for understanding database internals (storage engines, B+trees, WAL, query planning)
• Cross-language interoperability via the built-in REST API

Features

Quick Start

Engine API

using CSharpDB.Engine;

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

await db.ExecuteAsync("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
await db.ExecuteAsync("INSERT INTO users VALUES (1, 'Alice', 30)");
await db.ExecuteAsync("INSERT INTO users VALUES (2, 'Bob', 25)");

await using var result = await db.ExecuteAsync(
    "SELECT * FROM users WHERE age > 26 ORDER BY name ASC");

await foreach (var row in result.GetRowsAsync())
    Console.WriteLine($"{row[0].AsInteger}: {row[1].AsText}, age {row[2].AsInteger}");
// 1: Alice, age 30

Collection API (NoSQL)

var users = await db.GetCollectionAsync<User>("users");

await users.PutAsync("user:1", new User("Alice", 30, "alice@example.com"));
var alice = await users.GetAsync("user:1");

await foreach (var kvp in users.FindAsync(u => u.Age > 25))
    Console.WriteLine(kvp.Value.Name);

record User(string Name, int Age, string Email);

ADO.NET Provider

using CSharpDB.Data;

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

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM users WHERE age > @age";
cmd.Parameters.AddWithValue("@age", 25);

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

REST API

# Start the API server
dotnet run --project src/CSharpDB.Api

# Create a table
curl -X POST http://localhost:61818/api/sql/execute \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"}'

# Insert a row
curl -X POST http://localhost:61818/api/tables/users/rows \
  -H "Content-Type: application/json" \
  -d '{"values": {"id": 1, "name": "Alice"}}'

# Browse rows
curl http://localhost:61818/api/tables/users/rows

CLI

dotnet run --project src/CSharpDB.Cli -- mydata.db

csdb> CREATE TABLE demo (id INTEGER PRIMARY KEY, name TEXT);
csdb> INSERT INTO demo VALUES (1, 'Hello');
csdb> SELECT * FROM demo;
csdb> .tables
csdb> .quit

MCP Server (AI Integration)

Connect AI assistants like Claude Desktop, Cursor, OpenAI Codex, LM Studio, or VS Code Copilot to your database:

dotnet run --project src/CSharpDB.Mcp -- --database mydata.db

Add to claude_desktop_config.json (or .mcp.json for Claude Code / Cursor):

{
  "mcpServers": {
    "csharpdb": {
      "command": "dotnet",
      "args": ["run", "--project", "path/to/src/CSharpDB.Mcp", "--", "--database", "mydata.db"]
    }
  }
}

Or for OpenAI Codex CLI (~/.codex/config.toml):

[mcp_servers.csharpdb]
command = "dotnet"
args = ["run", "--project", "path/to/src/CSharpDB.Mcp", "--", "--database", "mydata.db"]

The MCP server exposes 15 tools for schema inspection, data browsing, row mutations, and SQL execution. See the MCP Server Reference for the full tool list and configuration options for all supported clients.

Architecture

  SQL string              Collection<T> API
      │                        │
  [Tokenizer]   ── Sql     [JSON serialize]  ── Engine
      │                        │
  [Parser → AST]          (bypassed)
      │                        │
  [Query Planner]  ── Execution   (bypassed)
      │                        │
  [Operator Tree]              │
      │                        │
  [B+Tree]  ───────────────  [B+Tree]  ── Storage
      │
  [Pager + WAL]        (page cache, dirty tracking, write-ahead log)
      │
  [File I/O]           (4 KB pages, slotted page layout)
      │
  mydata.db + mydata.db.wal

The SQL path goes through tokenizer → parser → planner → operators → B+tree. The Collection API goes directly to the B+tree, bypassing SQL entirely for maximum throughput.

Project Structure

CSharpDB.slnx
├── src/
│   ├── CSharpDB.Core/        Core types: DbValue, Schema, ErrorCodes
│   ├── CSharpDB.Storage/     Pager, B+tree, WAL, file I/O
│   ├── CSharpDB.Sql/         Tokenizer, parser, AST
│   ├── CSharpDB.Execution/   Query planner, operators, expression evaluator
│   ├── CSharpDB.Engine/      Top-level Database API + Collection<T> (NoSQL)
│   ├── CSharpDB.Data/        ADO.NET provider (DbConnection, DbCommand, DbDataReader)
│   ├── CSharpDB.Cli/         Interactive REPL
│   ├── CSharpDB.Service/     Shared service layer for Admin and API
│   ├── CSharpDB.Admin/       Blazor Server admin dashboard
│   ├── CSharpDB.Api/         REST API (ASP.NET Core Minimal API)
│   └── CSharpDB.Mcp/         MCP server for AI assistant integration
├── tests/
│   ├── CSharpDB.Tests/       Engine unit + integration tests
│   ├── CSharpDB.Data.Tests/  ADO.NET provider tests
│   ├── CSharpDB.Cli.Tests/   CLI smoke tests
│   └── CSharpDB.Benchmarks/  Performance benchmarks (BenchmarkDotNet + custom)
├── samples/                   Sample SQL datasets
└── docs/                      Documentation

Supported SQL

Building and Testing

Requires .NET 10 SDK.

dotnet build
dotnet test

Performance Highlights

Benchmarks run on Intel i9-11900K, .NET 10, Windows 11. Full results in tests/CSharpDB.Benchmarks/README.md.

Samples

The samples/ directory contains ready-to-run SQL scripts for three realistic scenarios:

• ecommerce-store.sql — Northwind Electronics (customers, products, orders, reviews)
• medical-clinic.sql — Riverside Health Center (patients, doctors, appointments, billing)
• school-district.sql — Maplewood School District (students, courses, enrollments, attendance)

Each script creates 7 tables with sample data, indexes, views, and triggers. See the samples README for execution instructions.

Roadmap

See docs/roadmap.md for the full roadmap. Highlights:

Near-term

• DISTINCT keyword
• Composite (multi-column) indexes
• Index range scans (<, >, BETWEEN)
• Prepared statement cache

Mid-term

• Subqueries and EXISTS
• UNION / INTERSECT / EXCEPT
• Window functions (ROW_NUMBER, RANK)
• NuGet package publishing

Long-term

• Memory-mapped I/O (mmap) read path
• Full-text search
• JSON path querying for Collection API
• Secondary indexes for Collection API

Documentation

License

MIT