Getting Started with WitDatabase: A Hands-On Console Application Tutorial

If you've ever wished for an embedded database that "just works" with .NET — no native binaries, no P/Invoke headaches, full SQL support — WitDatabase might be exactly what you're looking for.

But documentation can only take you so far. The best way to learn a new database is to run actual code and see it in action. That's why we've created an interactive console application that demonstrates five core features of WitDatabase:

1. Basic CRUD Operations — The foundation of any database work
2. Transactions & Savepoints — ACID guarantees with partial rollback support
3. Encryption — AES-256-GCM encryption with password protection
4. LSM-Tree Storage — Write-optimized engine for logs and time-series
5. Bulk Operations — High-performance batch processing

By the end of this tutorial, you'll have hands-on experience with each feature and understand when to use them in your own projects.

============================================================
|         WitDatabase Console Sample Application           |
|              Pure .NET Embedded Database                  |
============================================================

Select an example to run:

  1. Basic CRUD Operations
  2. Transactions & Savepoints
  3. Encryption Demo
  4. LSM-Tree Storage
  5. Bulk Operations
  6. Exit

Enter choice (0-5): _

Let's dive in!

Prerequisites & Setup

Requirements

• .NET 9.0 or .NET 10.0 SDK
• Any operating system: Windows, Linux, or macOS
• A terminal or command prompt
• About 5 minutes of your time

Running the Sample

Getting started takes just three commands:

# Clone the repository
git clone https://github.com/dmitrat/WitDatabase.git

# Navigate to the sample project
cd WitDatabase/Samples/OutWit.Database.Samples.ConsoleApp

# Build and run
dotnet run

Once the application starts, you'll see an interactive menu. Simply enter the number of the example you want to run (1-5) and press Enter. After each example completes, press any key to return to the menu.

💡 Tip: The examples are designed to be run in any order. Each one is self-contained and cleans up after itself.

Project Structure

The sample is organized into separate example files for clarity:

[[Svg Src="./witdatabase-consoleapp-project-structure.svg" Alt="witdatabase-consoleapp-project-structure"]]

Now let's explore each example in detail.

Example 1: Basic CRUD Operations

The first example demonstrates the fundamental database operations that every application needs: Create, Read, Update, and Delete.

Setting Up the Database

WitDatabase uses a fluent builder pattern for configuration:

var database = new WitDatabaseBuilder()
    .WithMemoryStorage()    // In-memory for this demo
    .WithBTree()            // B+Tree storage engine
    .Build();

using var engine = new WitSqlEngine(database, ownsStore: true);

For production, you'd use .WithFilePath("myapp.witdb") instead of .WithMemoryStorage().

Creating Tables

WitDatabase supports full DDL with constraints, defaults, and check expressions:

engine.Execute("""
    CREATE TABLE IF NOT EXISTS Users (
        Id BIGINT PRIMARY KEY AUTOINCREMENT,
        Name VARCHAR(100) NOT NULL,
        Email VARCHAR(255) UNIQUE,
        Age INT CHECK (Age >= 0 AND Age <= 150),
        Salary DECIMAL(10, 2),
        IsActive BOOLEAN DEFAULT TRUE,
        CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """);

INSERT with RETURNING

One of WitDatabase's powerful features is the RETURNING clause, which gives you back the inserted data — including auto-generated values:

using (var result = engine.Execute("""
    INSERT INTO Users (Name, Email, Age, Salary)
    VALUES ('Jane Smith', 'jane@example.com', 28, 82000.00)
    RETURNING Id, Name, CreatedAt
    """))
{
    if (result.Read())
    {
        Console.WriteLine(

        
        

            
            Loading...
        
quot;Inserted: Id={result.CurrentRow["Id"]}, " +
                          

        
        

            
            Loading...
        
quot;Name={result.CurrentRow["Name"]}, " +
                          

        
        

            
            Loading...
        
quot;CreatedAt={result.CurrentRow["CreatedAt"]}");
    }
}

Output:

Inserted: Id=2, Name=Jane Smith, CreatedAt=2025-01-15 14:32:15

No need for a separate query to get the auto-increment ID!

Parameterized Queries

Always use parameters to prevent SQL injection:

engine.Execute(
    "SELECT * FROM Users WHERE Age > @minAge AND Salary < @maxSalary",
    new Dictionary<string, object?>
    {
        { "@minAge", 25 },
        { "@maxSalary", 100000m }
    });

Aggregations

The example also demonstrates common aggregate functions:

using (var result = engine.Execute("""
    SELECT 
        COUNT(*) AS TotalUsers,
        AVG(Age) AS AvgAge,
        SUM(Salary) AS TotalSalaries,
        MIN(Salary) AS MinSalary,
        MAX(Salary) AS MaxSalary
    FROM Users
    """))
{
    // Process aggregate results...
}

What You'll See

When you run this example, you'll see output like:

=== Basic CRUD Example ===

1. Creating table 'Users'...
   [OK] Table created

2. Inserting single user...
   [OK] User inserted

3. Inserting user with RETURNING clause...
   [OK] Inserted: Id=2, Name=Jane Smith, CreatedAt=2025-01-15 14:32:15

4. Bulk inserting users...
   [OK] 3 users inserted

5. Querying all users...
   Id=1, Name=John Doe, Email=john@example.com, Age=30, Salary=75000.50
   Id=2, Name=Jane Smith, Email=jane@example.com, Age=28, Salary=82000.00
   ...

6. Aggregation query...
   Total: 5 users, Avg Age: 32, Total Salaries: $427,000.50

Example 2: Transactions & Savepoints

Transactions ensure that a group of operations either all succeed or all fail — there's no in-between state. This is critical for data integrity, especially in financial applications.

The Classic Example: Money Transfer

Imagine transferring $200 from Alice to Bob. This requires two operations:

1. Subtract $200 from Alice's account
2. Add $200 to Bob's account

If the system crashes between these operations, you'd lose money into thin air! Transactions prevent this.

Basic Transaction Flow

// Initial balances: Alice=$1000, Bob=$500, Carol=$250

engine.Execute("BEGIN TRANSACTION");

engine.Execute("UPDATE Accounts SET Balance = Balance - 200 WHERE Name = 'Alice'");
engine.Execute("UPDATE Accounts SET Balance = Balance + 200 WHERE Name = 'Bob'");

engine.Execute("COMMIT");

// Final balances: Alice=$800, Bob=$700, Carol=$250

Both updates happen atomically. If anything fails before COMMIT, all changes are automatically rolled back.

Explicit Rollback

Sometimes you need to cancel a transaction programmatically:

engine.Execute("BEGIN TRANSACTION");

engine.Execute("UPDATE Accounts SET Balance = Balance - 500 WHERE Name = 'Bob'");
engine.Execute("UPDATE Accounts SET Balance = Balance + 500 WHERE Name = 'Carol'");

// Oops, we changed our mind!
engine.Execute("ROLLBACK");

// Balances unchanged: Alice=$800, Bob=$700, Carol=$250

Savepoints: Partial Rollback

Here's where WitDatabase shines. Savepoints let you create checkpoints within a transaction and roll back to them without losing everything:

engine.Execute("BEGIN TRANSACTION");

// Transfer 1: Alice → Bob (we want to keep this)
engine.Execute("UPDATE Accounts SET Balance = Balance - 100 WHERE Name = 'Alice'");
engine.Execute("UPDATE Accounts SET Balance = Balance + 100 WHERE Name = 'Bob'");

// Create a savepoint before the next operation
engine.Execute("SAVEPOINT before_carol");

// Transfer 2: Bob → Carol (we might want to undo this)
engine.Execute("UPDATE Accounts SET Balance = Balance - 50 WHERE Name = 'Bob'");
engine.Execute("UPDATE Accounts SET Balance = Balance + 50 WHERE Name = 'Carol'");

// Changed our mind about Transfer 2 only
engine.Execute("ROLLBACK TO SAVEPOINT before_carol");

// Now commit Transfer 1 only
engine.Execute("COMMIT");

// Final: Alice=$700, Bob=$800 (Transfer 1 kept)
// Carol=$250 (Transfer 2 undone)

Visual Flow

[[Svg Src="./witdatabase-savepoint-transfers.svg" Alt="witdatabase-savepoint-transfers"]]

What You'll See

=== Transaction & Savepoint Example ===

1. Creating 'Accounts' table...
   [OK] Table created

2. Inserting initial accounts...
   Alice: $1000.00 | Bob: $500.00 | Carol: $250.00

3. Transaction with COMMIT (transfer $200 from Alice to Bob)...
   BEGIN TRANSACTION
   UPDATE Alice: -$200
   UPDATE Bob: +$200
   COMMIT
   Alice: $800.00 | Bob: $700.00 | Carol: $250.00

4. Transaction with ROLLBACK (transfer $500, then cancel)...
   BEGIN TRANSACTION
   UPDATE Bob: -$500
   UPDATE Carol: +$500
   ROLLBACK
   Alice: $800.00 | Bob: $700.00 | Carol: $250.00  (unchanged!)

5. Transaction with SAVEPOINT (partial rollback)...
   BEGIN TRANSACTION
   Transfer 1: Alice → Bob $100
   SAVEPOINT before_carol
   Transfer 2: Bob → Carol $50
   ROLLBACK TO SAVEPOINT before_carol
   COMMIT
   Alice: $700.00 | Bob: $800.00 | Carol: $250.00

Example 3: Encryption Demo

When your database contains sensitive information — API keys, passwords, personal data — encryption isn't optional. WitDatabase provides AES-256-GCM encryption built right in, with no additional dependencies.

Creating an Encrypted Database

Enabling encryption is a single line in the builder:

var database = new WitDatabaseBuilder()
    .WithFilePath("encrypted_demo.witdb")
    .WithBTree()
    .WithEncryption("MySecurePassword123!")  // ← That's it!
    .WithTransactions()
    .Build();

Everything written to disk is now encrypted. The password is used to derive a 256-bit encryption key using PBKDF2.

Storing Sensitive Data

Once encrypted, you use the database exactly as before:

engine.Execute("""
    CREATE TABLE SecretData (
        Id BIGINT PRIMARY KEY AUTOINCREMENT,
        Category VARCHAR(50) NOT NULL,
        Secret TEXT NOT NULL
    )
    """);

engine.Execute("""
    INSERT INTO SecretData (Category, Secret) VALUES
        ('API Key', 'sk-prod-abc123xyz789-secret-key'),
        ('Password', 'SuperSecretP@ssw0rd!'),
        ('Credit Card', '4111-1111-1111-1111'),
        ('SSN', '123-45-6789')
    """);

What's Actually on Disk?

The example shows you a hex dump of the raw database file:

Raw file contents (first 256 bytes):
---------------------------------------------------------------
57 41 4C 45 00 10 00 00  A3 7F 2B 8C 4E 91 D2 F6  |WALE......+.N...|
E8 4A B3 15 C7 89 2D 61  5F 33 AA F0 1B 6E 82 C4  |.J....-a_3...n..|
9D 75 E2 58 3C B1 07 6F  DA 46 93 28 7E C5 1A 84  |.u.X<..o.F.(~...|
F2 5B 69 AD 0E 73 BE 40  D7 8F 24 61 95 C8 3A 0F  |.[i..s.@..$a..:.|
...
---------------------------------------------------------------

Notice "WALE" at the start — this is the encrypted database magic number. Everything after the header is indecipherable without the password.

Opening with the Correct Password

// Close and reopen with the same password
database.Dispose();

var reopened = new WitDatabaseBuilder()
    .WithFilePath("encrypted_demo.witdb")
    .WithBTree()
    .WithEncryption("MySecurePassword123!")  // Same password
    .Build();

using var engine2 = new WitSqlEngine(reopened, ownsStore: true);

// Data is accessible again!
var result = engine2.Execute("SELECT * FROM SecretData");

Opening with the Wrong Password

try
{
    var wrongDb = new WitDatabaseBuilder()
        .WithFilePath("encrypted_demo.witdb")
        .WithBTree()
        .WithEncryption("WrongPassword")  // Oops!
        .Build();
}
catch (CryptographicException ex)
{
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Access denied: {ex.Message}");
}

Output:

Access denied: The computed authentication tag did not match the input.

The database is completely inaccessible without the correct password.

Encryption Options

WitDatabase supports two encryption algorithms:

For Blazor WebAssembly, use ChaCha20:

.WithBouncyCastleEncryption("password")  // ChaCha20-Poly1305

What You'll See

=== Encryption Example ===

1. Creating encrypted database with AES-GCM...
   Database file: encrypted_demo.witdb
   Password: **********************

2. Creating table for sensitive data...
   [OK] Table created

3. Inserting sensitive data...
   [OK] 5 secrets inserted

4. Querying encrypted data (decrypted automatically)...
   [1] API Key: sk-prod-abc123...
   [2] Password: SuperSecretP...
   [3] Credit Card: 4111-1111-...
   ...

5. Viewing raw encrypted file...
   [Shows hex dump - all data is encrypted]

6. Reopening with correct password...
   [OK] All data accessible

7. Attempting to open with wrong password...
   [EXPECTED] CryptographicException: Authentication tag mismatch

🔐 Security Note: Never hardcode passwords in source code. Use environment variables, configuration files, or a secrets manager in production.

Example 4: LSM-Tree Storage Engine

WitDatabase offers two storage engines: B+Tree (the default) and LSM-Tree. Each excels at different workloads.

When to Use LSM-Tree

LSM-Tree (Log-Structured Merge-Tree) is optimized for:

• Write-heavy workloads (>80% writes)
• Sequential write patterns (logs, events, metrics)
• Time-series data (IoT sensors, monitoring)
• Append-mostly data (audit trails, activity logs)

B+Tree vs LSM-Tree

Configuring LSM-Tree

var database = new WitDatabaseBuilder()
    .WithLsmTree("./lsm_data", opts =>
    {
        opts.EnableWal = true;              // Write-ahead log for durability
        opts.EnableBlockCache = true;       // Cache frequently read blocks
        opts.BlockCacheSizeBytes = 16 * 1024 * 1024;  // 16MB cache
        opts.MemTableSizeLimit = 1 * 1024 * 1024;     // Flush every 1MB
        opts.BackgroundCompaction = true;   // Merge SSTables in background
    })
    .WithTransactions()
    .Build();

How LSM-Tree Works

[[Svg Src="./witdatabase-lsm-read-write-paths.svg" Alt="witdatabase-lsm-read-write-paths"]]

1. Writes go to memory first (MemTable) — extremely fast
2. Periodic flush to disk as immutable SSTable files
3. Background compaction merges SSTables for read efficiency
4. Bloom filters skip SSTables that don't contain the key

The Example: Event Logging

The demo creates a logging table and inserts 1000 events:

engine.Execute("""
    CREATE TABLE EventLogs (
        Id BIGINT PRIMARY KEY AUTOINCREMENT,
        EventTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        Level VARCHAR(10) NOT NULL,
        Source VARCHAR(100) NOT NULL,
        Message TEXT NOT NULL
    )
    """);

// Bulk insert 1000 log entries
engine.Execute("BEGIN TRANSACTION");
for (int i = 0; i < 1000; i++)
{
    var level = (i % 10 == 0) ? "ERROR" : (i % 3 == 0) ? "WARNING" : "INFO";
    engine.Execute(
        "INSERT INTO EventLogs (Level, Source, Message) VALUES (@level, @source, @msg)",
        parameters);
}
engine.Execute("COMMIT");

LSM File Structure

After running, you'll see the LSM directory structure:

lsm_demo_data/
├── wal/
│   └── 000001.wal          # Write-ahead log
├── sst/
│   ├── 000001.sst          # SSTable Level 0
│   ├── 000002.sst          # SSTable Level 0
│   └── manifest.json       # SSTable metadata
└── meta/
    └── current             # Current manifest pointer

Querying Time-Series Data

// Recent errors
engine.Execute("""
    SELECT Id, Source, Message
    FROM EventLogs
    WHERE Level = 'ERROR'
    ORDER BY Id DESC
    LIMIT 10
    """);

// Aggregations
engine.Execute("""
    SELECT 
        Level,
        COUNT(*) AS Count
    FROM EventLogs
    GROUP BY Level
    ORDER BY Count DESC
    """);

What You'll See

=== LSM-Tree Storage Engine Example ===

1. Creating LSM-Tree database...
   Directory: /path/to/lsm_demo_data

2. Creating time-series logging table...
   [OK] Table created

3. Bulk inserting 1000 log entries...
   [OK] Inserted 1000 rows in 45ms
   [OK] Rate: 22,222 rows/sec

4. Log level distribution...
   INFO: 600 entries
   WARNING: 300 entries
   ERROR: 100 entries

5. Querying recent ERROR logs...
   [1000] AuthService: Authentication failed for user admin
   [990] DatabaseService: Connection timeout after 30s
   ...

6. Database statistics...
   Total logs: 1000
   First event: 2025-01-15 14:35:00
   Last event: 2025-01-15 14:35:01

7. LSM-Tree file structure:
   lsm_demo_data/
   ├── wal/000001.wal (12 KB)
   ├── sst/000001.sst (45 KB)
   └── meta/current

Example 5: Bulk Operations

When you need to insert, update, or delete thousands of rows, the approach matters. This example demonstrates high-performance batch processing techniques.

The Golden Rule: Use Transactions

Without a transaction, each INSERT is a separate disk operation:

// ❌ Slow: 5000 separate disk writes
for (int i = 0; i < 5000; i++)
{
    engine.Execute("INSERT INTO Products ...", params);
}

With a transaction, all writes batch together:

// ✅ Fast: Single disk write at COMMIT
engine.Execute("BEGIN TRANSACTION");
for (int i = 0; i < 5000; i++)
{
    engine.Execute("INSERT INTO Products ...", params);
}
engine.Execute("COMMIT");

The difference? 10-100x faster.

The Example Setup

First, create a table with indexes:

engine.Execute("""
    CREATE TABLE Products (
        Id BIGINT PRIMARY KEY AUTOINCREMENT,
        SKU VARCHAR(50) NOT NULL,
        Name VARCHAR(200) NOT NULL,
        Category VARCHAR(50) NOT NULL,
        Price DECIMAL(10, 2) NOT NULL,
        Stock INT NOT NULL DEFAULT 0,
        CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
    )
    """);

// Indexes improve query performance
engine.Execute("CREATE UNIQUE INDEX IX_Products_SKU ON Products(SKU)");
engine.Execute("CREATE INDEX IX_Products_Category ON Products(Category)");

Bulk INSERT

var stopwatch = Stopwatch.StartNew();

engine.Execute("BEGIN TRANSACTION");

for (int i = 0; i < 5000; i++)
{
    var category = (i % 5) switch
    {
        0 => "Electronics",
        1 => "Clothing",
        2 => "Books",
        3 => "Home & Garden",
        _ => "Sports"
    };
    
    engine.Execute(
        "INSERT INTO Products (SKU, Name, Category, Price, Stock) " +
        "VALUES (@sku, @name, @category, @price, @stock)",
        new Dictionary<string, object?>
        {
            { "@sku", 

        
        

            
            Loading...
        
quot;SKU-{i:D6}" },
            { "@name", 

        
        

            
            Loading...
        
quot;Product {i} - {category}" },
            { "@category", category },
            { "@price", 10.00m + (i % 100) * 5.50m },
            { "@stock", 100 + (i % 50) }
        });
}

engine.Execute("COMMIT");

stopwatch.Stop();
Console.WriteLine(

        
        

            
            Loading...
        
quot;Inserted 5000 rows in {stopwatch.ElapsedMilliseconds}ms");
Console.WriteLine(

        
        

            
            Loading...
        
quot;Rate: {5000.0 / stopwatch.Elapsed.TotalSeconds:F0} rows/sec");

Typical output:

Inserted 5000 rows in 180ms
Rate: 27,778 rows/sec

Aggregation Queries

Once you have data, aggregate queries become essential:

// Category breakdown
engine.Execute("""
    SELECT 
        Category,
        COUNT(*) AS ProductCount,
        AVG(Price) AS AvgPrice,
        SUM(Stock) AS TotalStock,
        MIN(Price) AS MinPrice,
        MAX(Price) AS MaxPrice
    FROM Products
    GROUP BY Category
    ORDER BY ProductCount DESC
    """);

Bulk UPDATE

Update prices for an entire category:

engine.Execute("BEGIN TRANSACTION");

engine.Execute("""
    UPDATE Products 
    SET Price = Price * 1.10,
        Stock = Stock + 10
    WHERE Category = 'Electronics'
    """);

engine.Execute("COMMIT");

Bulk DELETE

Remove low-stock items:

engine.Execute("BEGIN TRANSACTION");

engine.Execute("""
    DELETE FROM Products 
    WHERE Stock < 110 
      AND Category = 'Clothing'
    """);

engine.Execute("COMMIT");

Performance Tips

What You'll See

=== Bulk Operations Example ===

1. Creating 'Products' table with indexes...
   [OK] Table and indexes created

2. Bulk inserting 5000 products (in transaction)...
   [OK] Inserted 5000 rows in 180ms
   [OK] Rate: 27,778 rows/sec

3. Aggregation by category...
   Electronics:  1000 products, Avg $285.25, Stock: 124,500
   Clothing:     1000 products, Avg $285.25, Stock: 124,500
   Books:        1000 products, Avg $285.25, Stock: 124,500
   Home & Garden:1000 products, Avg $285.25, Stock: 124,500
   Sports:       1000 products, Avg $285.25, Stock: 124,500

4. Price range query...
   Found 450 products priced $100-$200

5. Bulk UPDATE (10% price increase for Electronics)...
   [OK] Updated 1000 rows in 45ms

6. Bulk DELETE (low stock Clothing items)...
   [OK] Deleted 200 rows in 12ms

7. Final statistics...
   Total products: 4800
   Total inventory value: $1,425,750.00

Supported SQL Features

WitDatabase implements a comprehensive SQL dialect. Here's what you can use:

DDL (Data Definition)

• CREATE TABLE with constraints, defaults, check expressions
• ALTER TABLE (add/drop columns, rename)
• DROP TABLE
• CREATE INDEX / DROP INDEX (unique and non-unique)
• CREATE VIEW / DROP VIEW

DML (Data Manipulation)

• SELECT with WHERE, ORDER BY, LIMIT, OFFSET
• INSERT with RETURNING clause
• UPDATE with WHERE
• DELETE with WHERE
• MERGE (upsert)

Joins & Subqueries

• INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN
• Correlated and non-correlated subqueries
• IN, EXISTS, ANY, ALL

Advanced Features

• Window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), etc.
• CTEs: WITH clause (recursive and non-recursive)
• Aggregations: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT
• 60+ built-in functions: string, numeric, date/time, JSON

Transactions

• BEGIN TRANSACTION / COMMIT / ROLLBACK
• SAVEPOINT / ROLLBACK TO SAVEPOINT
• Multiple isolation levels (Read Uncommitted → Serializable)

What's Next?

You've now seen the core features of WitDatabase in action. Here are some paths to explore next:

Try Other Samples

Get the Code

git clone https://github.com/dmitrat/WitDatabase.git
cd WitDatabase/Samples/OutWit.Database.Samples.ConsoleApp
dotnet run

Summary

In this tutorial, we covered five essential WitDatabase features:

WitDatabase gives you the power of a full SQL database with the simplicity of an embedded solution — no servers, no native dependencies, just NuGet packages.

Happy coding! 🚀

Have questions or feedback? Open an issue on GitHub or star the repo if you found this useful!