First database

quickstarttutorialef-coreado-netgetting-started

This page builds one small thing and finishes it: a model, a database file, rows written, rows read back. It uses Entity Framework Core, since that is how most projects will reach for WitDatabase. The same work is shown afterwards in ADO.NET and in the core API, so you can see what each layer costs you.

You will need OutWit.Database.EntityFramework. See installation if you have not added it yet.

A model and a context

Nothing here is specific to WitDatabase. It is an ordinary EF Core model and an ordinary DbContext.

csharp
using Microsoft.EntityFrameworkCore;

public class Note
{
    public long Id { get; set; }
    public string Title { get; set; } = "";
    public string Body { get; set; } = "";
    public DateTime CreatedAt { get; set; }
}

public class NotesContext : DbContext
{
    public DbSet<Note> Notes => Set<Note>();

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

UseWitDb is the only line that names the provider. Swap it for UseNpgsql, UseSqlServer, UseMySql or whichever provider your production database uses, and the rest of this file compiles unchanged. That property is what the whole engine is built around.

Creating the database and writing to it

csharp
using var context = new NotesContext();
context.Database.EnsureCreated();

context.Notes.Add(new Note
{
    Title = "First note",
    Body = "The database created itself when EnsureCreated ran.",
    CreatedAt = DateTime.UtcNow
});

context.SaveChanges();

EnsureCreated builds the schema from the model and is the quickest way to get moving. For anything that will outlive an afternoon, use migrations instead: dotnet ef migrations add Initial followed by context.Database.Migrate(). The provider supports the full migration pipeline, including scaffolding an existing database back into a model.

Reading it back

csharp
using var context = new NotesContext();

var recent = context.Notes
    .Where(note => note.CreatedAt > DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(note => note.CreatedAt)
    .Take(20)
    .ToList();

foreach (var note in recent)
    Console.WriteLine($"{note.CreatedAt:u}  {note.Title}");

The Where, OrderByDescending and Take are translated to SQL and executed by the engine rather than evaluated in memory afterwards.

The same database, in memory, for tests

csharp
var options = new DbContextOptionsBuilder<NotesContext>()
    .UseWitDbInMemory()
    .Options;

using var context = new NotesContext(options);
context.Database.EnsureCreated();

No file, no path, no cleanup between test runs, and every call makes its own database so two fixtures in one suite cannot see each other.

Use UseWitDbInMemory() rather than UseWitDb("Data Source=:memory:"). An in-memory database is private to the connection that opened it, and EF Core opens and closes a connection for every operation, so a connection string hands each operation a fresh empty database: EnsureCreated returns true and the next SaveChanges fails with Table not found. UseWitDbInMemory() opens a connection and keeps it, which is the same recipe SQLite's provider documents and for the same reason.

That connection lives as long as the options object. When the lifetime has to be in your hands, open a WitDbConnection yourself and pass it to the overload that takes one.

Constraints are enforced, transactions roll back, SQL is parsed and planned, and a query that would fail against your production server fails here too. EF Core's in-memory provider enforces none of that and will accept a save a real database rejects.

The same work in ADO.NET

If you would rather write the SQL yourself, drop a layer. Everything below comes from OutWit.Database.AdoNet, which the EF Core package already brought in.

csharp
using OutWit.Database.AdoNet;

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

using var command = connection.CreateCommand();
command.CommandText = """
    CREATE TABLE IF NOT EXISTS Notes (
        Id        BIGINT PRIMARY KEY AUTOINCREMENT,
        Title     VARCHAR(200) NOT NULL,
        Body      TEXT,
        CreatedAt DATETIME NOT NULL
    )
    """;
command.ExecuteNonQuery();

command.CommandText = """
    INSERT INTO Notes (Title, Body, CreatedAt)
    VALUES (@title, @body, @createdAt)
    RETURNING Id
    """;
command.Parameters.AddWithValue("@title", "First note");
command.Parameters.AddWithValue("@body", "Written through ADO.NET.");
command.Parameters.AddWithValue("@createdAt", DateTime.UtcNow);

var id = (long)command.ExecuteScalar()!;
Console.WriteLine($"Inserted note {id}");

RETURNING hands back the generated key as part of the insert, so there is no second round trip to find out what it was. Pass values as parameters rather than concatenating them into the statement.

Reading is the ordinary DbDataReader loop:

csharp
command.Parameters.Clear();
command.CommandText = "SELECT Id, Title, CreatedAt FROM Notes ORDER BY CreatedAt DESC";

using var reader = command.ExecuteReader();
while (reader.Read())
    Console.WriteLine($"[{reader.GetInt64(0)}] {reader.GetString(1)}");

When you have no use for SQL

Underneath the SQL engine is an ordered key-value store, and you can use it on its own. This is the right layer for a cache, a session store, or anything where a schema would only be in the way.

csharp
using OutWit.Database.Core.Builder;

using var db = WitDatabase.CreateOrOpen("cache.witdb");

db.Put("session:8f21"u8.ToArray(), "alice"u8.ToArray());

var value = db.Get("session:8f21"u8.ToArray());
Console.WriteLine(Encoding.UTF8.GetString(value!));

Keys are ordered, so Scan over a key prefix gives you a range in sorted order, and transactions work the same way they do above.

What ended up on disk

notes.witdb is one file. It holds the schema, the data, the indexes and the catalogue, and it moves between machines by being copied. There is no directory of side files to keep together unless you asked for the LSM store, which uses a folder instead.

WitDatabase Studio opens the file, shows the tables, runs queries against them, and reports the format version and encryption state. That matters when a .witdb file is your application's document format and a customer sends you one.

Where to go next