Introduction

What it is for

WitDatabase is an embedded SQL database engine written entirely in C#. It was built to take the place of whatever database server your application normally talks to, in the situations where running one is impractical: test suites, demo modes, and deployments onto machines where no server is installed and none can be.

Where it came from

Commercial services are often easier to maintain when they are assembled from parts. In the ones this engine grew out of, the application never learns which database it is talking to. It talks to Entity Framework Core, and support for any particular database arrives as a plugin that calls its own With… method and carries its own migrations. If a client prefers PostgreSQL, that is a plugin. If another already pays for SQL Server, or runs MySQL, those are more of them. Adding support for a database means dropping an assembly into a folder, with nothing recompiled.

An architecture like that still needs a simple database for tests, for demo mode, and for deploying somewhere a server cannot go. SQLite was the obvious first thing to try, and its EF Core support is excellent. It did not work out. SQLite ships native binaries, and inside a plugin design that loads assemblies at runtime, those binaries could not be made to resolve. No amount of tuning was going to fix that.

The next step was to look for a fully managed .NET database, and there was one: LiteDB. It is a document store with no SQL, and its EF Core support came from an old adapter that is no longer maintained and was never complete in the first place.

At that point it became clear that no managed .NET database with proper Entity Framework Core support existed at all. WitDatabase started from there, and has been in development for close to two years.

Written from scratch

None of WitDatabase came from SQLite. The dialect borrows SQLite's spelling, since that is the spelling most .NET developers already know, and the resemblance stops there.

Building it for .NET from the ground up meant it could be built in .NET rather than around it. Span<T> and Memory<T> move pages and keys about without copying them. ArrayPool<T> and MemoryPool<T> keep buffers in circulation instead of handing them to the garbage collector. Async goes all the way down rather than sitting on top of something synchronous. The C# is current.

Which .NET versions

The framework target has moved over the years. The project started out building for .NET 6, 7, 8 and 9, with 10 added once it appeared. The older ones fell away as the code came to rely on things they did not have: first 6 and 7, then 8. For a long stretch it built for 9 and 10 together.

.NET 9 was dropped recently, and not over a missing feature, because everything the engine needs is there. It went because .NET 9 has reached the end of its support window, and because carrying a second target roughly doubled the time the test suite took to run. Paying that on every build for a runtime nobody should still be deploying onto stopped making sense.

So the packages target .NET 10 today, and nothing older. .NET Framework has never been a target and will not become one.

The two goals

A drop-in replacement for a full database. Everything a large database offers, including the parts embedded engines usually leave out: stored procedures and user-defined functions, both of which shipped in 11.0.0. Full EF Core support rather than a partial adapter. Parity is aimed at full database servers rather than at other embedded engines, and it is checked by running the same statements against real servers and comparing what comes back. The question the engine has to answer is whether an application written for a large database notices that it has been swapped out.

Speed and stability. Against SQLite, which does the same work an insert here does, the answer depends on how many rows cross the boundary in a single call. SQLite pays a fixed cost per call for crossing into native code and WitDatabase does not, so for the many small calls that most service traffic consists of, WitDatabase comes out ahead by a wide margin, on writes as well as on lookups. The crossover is lower than people expect: once a single call is returning more than a few hundred rows, native row handling wins, and by a thousand it wins comfortably.

LiteDB is the only other managed option. On reads WitDatabase is ahead nearly everywhere it has been measured, and lighter on memory. Writes are a different measurement: a LiteDB insert appends a document, where an insert here is parsed, planned, checked against a schema and its constraints, and carried through the secondary indexes under snapshot isolation. Those two timings measure different work.

The numbers, the conditions they were measured under, what is being compared in each row, and the point where WitDatabase and SQLite cross over are on the benchmarks page.

Two ways it gets used

Standing in for a full database

Your application runs against EF Core. In production that means whichever server EF Core is pointed at, and any of them will do; in tests, in demo mode, or on a machine where no server exists, it talks to WitDatabase instead and carries on as though nothing had changed.

OmnibusCloud is a large system, and its entire test suite and CI run on WitDatabase. Every CI run is therefore also a regression test of the drop-in promise: when parity with a large database slips, it shows up as a red build.

As a file format for local applications

A .witdb file can serve as your application's document or state format, the thing a user saves, moves around and backs up. The B+Tree store keeps it as a single file, it can be encrypted under a password the user chooses, and it carries a real schema instead of a hand-rolled serialization format you will come to regret.

WitDatabase Studio opens that file and shows the schema, the data, the format version, the encryption state and the storage layer underneath, which is what you want when a support request arrives with a customer's file attached. No third-party client can open one.

How it is built

Every layer is an interface, and every interface has registered implementations that you can replace without touching anything else.

Want your keys held in a hardware security module, or in Azure Key Vault? That is an ICryptoProvider. Want the database to live in the browser, or in S3? That is an IStorage. Want durability replicated across the network? That is an ITransactionJournal. You write one implementation, register it under a key, and the rest of the engine carries on unaffected.

Two shipped packages are built that way, through the same public interfaces, living outside the core. OutWit.Database.Core.IndexedDb is browser storage as a single IStorage. OutWit.Database.Core.BouncyCastle is ChaCha20-Poly1305 as a single ICryptoProvider. Blazor WebAssembly support amounts to one file implementing one interface.

The services this engine was built for are assembled from database plugins, and the database itself is assembled from provider plugins. One idea, applied at two levels.

Where the edges are

Every capability in WitDatabase is here because a real project needed it, so the coverage follows one set of requirements. The edges run here:

• Joins are behind, and the gap widens with each table added. Equi-joins on one or two tables get a hash join; anything else falls back to nested loops. If multi-table joins dominate your workload, SQLite will serve you better.
• Scans, aggregates and sorts favour native code. Anything that walks a large number of rows inside one call is work C does better, and a full scan of a thousand rows already shows it.
• The query planner keeps no selectivity statistics, so an applicable index always wins a range comparison regardless of what the predicate really selects.

The full list is on the limitations page. The gaps are kept as executable tests in the repository, so an entry disappears when the gap closes.

Where to go next

• Installation, packages, platforms, and which one you need
• First database, a working database in a few lines
• WitSQL reference, the language, in one place
• Engine, how the layers fit together and how to replace one
• Studio, the desktop client that knows this engine
• Source, the engine, its tests and its changelog