LiteSqlServer 1.0.2

LiteSqlServer

A .NET document database that speaks LiteDB's API but stores everything in Microsoft SQL Server.

The goal is source compatibility: take LiteDB code, change using LiteDB; to using LiteSqlServer;, swap the connection string, and it keeps working — while the data now lives in a real SQL Server database that other applications, reporting tools, and DBAs can reach.

Install

dotnet add package LiteSqlServer

Supported frameworks

Target	Runs on
net8.0	.NET 8 and later
netstandard2.0	.NET Core 2.0+, Mono, Xamarin, UWP
net462	.NET Framework 4.6.2 and later

Every target uses the same Microsoft.Data.SqlClient, and .NET Framework 4.6.2 is the floor because that is the oldest framework it supports. 4.6.2 also negotiates TLS 1.2 by default, which older frameworks do not — so connecting to a SQL Server that requires encryption works without machine-wide configuration.

There are no framework-conditional code paths: one implementation runs everywhere.

using LiteSqlServer;

using var db = new LiteDatabase("Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true");

var products = db.GetCollection<Product>("products");

products.EnsureCollection();          // creates the table; writing does not
products.EnsureIndex(x => x.Name);
products.Insert(new Product { Name = "Widget", Price = 9.99m });

var cheap = products.Find(x => x.Price < 10).ToList();
var one   = products.FindOne(x => x.Name == "Widget");
var page  = products.Query().Where(x => x.IsActive).OrderBy(x => x.Name).Limit(50).ToList();

---

Why

LiteDB is excellent until the day you outgrow a single embedded file: you need several processes writing at once, a database larger than a gigabyte or two, real backups, or SQL access from other systems. Migrating usually means rewriting every repository against EF Core or Dapper.

LiteSqlServer keeps the document programming model and the call shapes, and moves the storage.

---

How documents are stored

Each collection is one table:

CREATE TABLE [dbo].[products] (
    [_id]  INT            NOT NULL PRIMARY KEY CLUSTERED,
    [_doc] NVARCHAR(MAX)  NOT NULL CHECK (ISJSON([_doc]) = 1)
);

The identifier is a real, typed primary-key column (INT, BIGINT, UNIQUEIDENTIFIER, DATETIME2, or NVARCHAR), chosen from the mapped Id member. The document is plain JSON, so it stays readable and queryable from anything else that can reach the database:

SELECT JSON_VALUE([_doc], '$.Name') AS Name,
       TRY_CAST(JSON_VALUE([_doc], '$.Price') AS DECIMAL(38,10)) AS Price
FROM   [dbo].[products]
WHERE  TRY_CAST(JSON_VALUE([_doc], '$.Price') AS DECIMAL(38,10)) < 10;

There is no metadata catalogue. A table carrying _id and a JSON _doc is a document collection, and the _id column type is the identifier type, so everything the library needs to know it reads back out of sys.columns and friends.

DDL is something you ask for

Nothing on an ordinary read or write path runs DDL. Inserting into a collection that is not there fails with an error naming it, rather than quietly issuing CREATE TABLE. The login a service runs under is very often one that may not create tables, and a library that needs to in order to function is a library that cannot be deployed there.

Creating a collection is an explicit act:

products.EnsureCollection();   // CREATE TABLE, if it is not already there
products.EnsureIndex(x => x.Name);

Both are ordinary public methods, so an installer or a migration step can call them from a login that does have rights. If you would rather have LiteDB's behaviour of creating a collection on first write, opt in with AutoCreate=true in the connection string.

Everything that can write, in full

This is the complete list. Every other call — opening the database, GetCollection, Find, FindAll, Query, Count, Exists, Min, Max, GetIndexes, GetCollectionNames, CollectionExists, SELECT through the SQL dialect, and every read on FileStorage — issues SELECT and nothing else.

Call	What it sends
EnsureCollection()	CREATE TABLE, preceded by CREATE SCHEMA if the schema is missing
EnsureIndex(...)	ALTER TABLE ADD for the persisted computed column, CREATE INDEX, and an extended property beside the column
DropIndex(...)	DROP INDEX, then ALTER TABLE DROP COLUMN
DropCollection(...)	DROP TABLE, and only for a table this library created
RenameCollection(...)	sp_rename, and only for a table this library created
Insert, InsertBulk, Update, UpdateMany, Upsert, Delete, DeleteAll, DeleteMany	INSERT / UPDATE / DELETE
Execute("INSERT ..."), "UPDATE ...", "DELETE ..."	the same, through the SQL dialect
FileStorage.Upload, Delete, SetMetadata	INSERT / UPDATE / DELETE on the file and chunk tables
Rebuild()	ALTER INDEX ALL ... REBUILD, over the collections this library created and no other table

The only way a write becomes DDL is AutoCreate=true, which lets the first write create the collection table it is missing. It is off by default. ReadOnly=true refuses every row in this table.

A table LiteSqlServer did not create is never altered: EnsureIndex, DropIndex, DropCollection and RenameCollection all refuse one, and CRUD against it touches rows only.

None of this is taken on trust: Proving the database is left alone describes the tests that compare a full snapshot of a schema before and after every path through the library.

Document JSON is plain, not extended JSON

LiteDB writes extended JSON ({"$date": ...}, {"$guid": ...}). LiteSqlServer deliberately does not, because JSON_VALUE can only read scalars — a wrapped date would be invisible to SQL Server's query engine. Instead the encodings are chosen so SQL Server can cast them back exactly:

CLR type	Stored as
DateTime	"2026-08-19T13:45:30.0000000" — ISO-8601 UTC
Guid	"3f2504e0-4f89-11d3-9a0c-0305e82c3301"
ObjectId	"507f1f77bcf86cd799439011" — 24 hex chars
byte[]	base64
decimal	a JSON number, digits preserved

Round-tripping into a POCO is lossless, because the mapper knows the target type. Reading into a raw BsonDocument returns these as strings, since a bare document carries no type information.

---

Indexes actually index

EnsureIndex creates a persisted computed column over the JSON path, then a normal index on it:

ALTER TABLE [dbo].[products]
    ADD [_ix_Name] AS TRY_CAST(JSON_VALUE([_doc], '$.Name') AS NVARCHAR(450)) PERSISTED;

CREATE INDEX [IX_products_Name] ON [dbo].[products]([_ix_Name]);

An index's logical name and expression cannot be recovered from the column — the column name is sanitised (Warehouse.City becomes _ix_Warehouse_City) and the definition is SQL — so they live in an extended property on the column, which SQL Server drops along with it. That is what GetIndexes reports.

SQL Server will substitute a persisted computed column for a matching expression in a query, so a LINQ predicate on the same field seeks rather than scans. That substitution is textual, which makes one rule central to this codebase:

The index DDL and the query translator must emit byte-identical SQL for a field projection.

Both therefore go through the single method SqlPath.Emit, and nothing else builds these strings by hand. IndexTests.Indexed_queries_seek_instead_of_scanning asserts the payoff by checking sys.dm_db_index_usage_stats for a real seek after a 3,000-row query.

Projections use TRY_CAST / TRY_CONVERT rather than CAST: a document store is schemaless, so a field holding a number in one document and text in another must yield NULL, not abort the query.

Index keys are limited to 900 bytes, so text projections are truncated to 450 characters. Comparing against a string that long or longer automatically adds an untruncated guard predicate, so results stay exact.

---

Working without a POCO

Two things need no C# class at all.

Untyped document collections

db.GetCollection(name) gives you BsonDocument, and indexer predicates translate to SQL and use indexes just like typed ones:

var col = db.GetCollection("products");

col.Find(x => x["Price"].AsDecimal < 10);
col.Find(x => x["Name"].AsString.StartsWith("Wid"));
col.Find(x => x["Warehouse"]["City"] == "Oslo");   // nested

An As* accessor tells the translator the type. Without one — x["Age"] > 30 — the type is taken from the literal, so the comparison stays numeric rather than degenerating into a string compare.

Pre-existing relational tables

Point a collection at a table this library did not create, with ordinary columns and no _doc:

CREATE TABLE [dbo].[Customers] (
  [CustomerId] INT IDENTITY(1,1) PRIMARY KEY,
  [Name]       NVARCHAR(100) NOT NULL,
  [Balance]    DECIMAL(18,2) NOT NULL,
  [Created]    DATETIME2 NULL);

var customers = db.GetCollection("Customers");     // detected automatically

var rich = customers.Find(x => x["Balance"].AsDecimal > 1000).ToList();
var one  = customers.FindById(42);

customers.Insert(doc);   // IDENTITY assigned by SQL Server and returned
customers.Update(doc);
customers.DeleteMany(x => x["Balance"].AsDecimal <= 0);

Each column becomes a document field with its own type, and predicates compile to plain column references ([Balance] > @p0) — no casts, no truncation — so they use whatever indexes the table already has. The primary key is also exposed as _id so the usual LiteDB idioms keep working.

GetCollection goes by the shape of the table: _id plus a JSON _doc is a document collection, anything else is your table. If you would rather an unknown name keep meaning "no such collection", switch detection off with RelationalTables=false.

What is deliberately refused, rather than doing something surprising to a table you own:

Operation	Why
DropCollection, RenameCollection	It is your table; change it with SQL
EnsureIndex, DropIndex	Create indexes with CREATE INDEX; queries will use them. GetIndexes reports the real ones
Include	References are stored as { $id, $ref } documents, which a column cannot hold
FindById, Update, Delete on a composite-key or keyless table	They cannot identify a row; Find and DeleteMany still work
A field that is not a column	Named in the error, rather than silently dropped — including in a predicate, where a scan would otherwise hide the typo

Mapping a class to a table

A class works too, giving compile-time checking instead of string field names:

public class Customer
{
    public int CustomerId { get; set; }      // -> the primary key, whatever it is called
    public string Name { get; set; }
    public decimal Balance { get; set; }

    [BsonField("legacy_notes")]              // when the names differ
    public string Notes { get; set; }
}

var customers = db.GetCollection<Customer>("Customers");

var rich = customers.Find(x => x.Balance > 1000 && x.CustomerId != 1).ToList();

Properties map to columns by name; [BsonField] or BsonMapper.Entity<T>().Field(...) handles the rest.

A class may cover only part of the table. An update then writes only the columns it maps and leaves the others exactly as they were, so a summary class cannot blank the columns it knows nothing about. A raw BsonDocument still replaces the whole row, because it carries the whole row.

Properties with no single-column equivalent — a nested class, a List<> — are reported by name rather than written; use [BsonIgnore] or flatten them.

Identifiers that are not called Id

The key member is found in this order, and any of them maps to the primary key under its real column name, so filtering, ordering, FindById, Update and Delete all work:

How the key is named	Example
[BsonId], or BsonMapper.Entity<T>().Id(x => x.Ref)	[BsonId] public int Ref { get; set; }
The Id / <Type>Id convention	public int Id, public int CustomerId
A member named after the key column	public int CustomerKey over [CustomerKey]

The last one needs no attribute at all: the table already says which column is its primary key, so the member mapped to that column is the identifier. It applies to a pre-existing table, which is the only place a key column has a name of its own — a document collection always stores its key as _id, so there [BsonId] is what names an unconventional key member.

Insert returns the new key, whatever the column is called and however the class named the member, and assigns it back to that member:

var customer = new Customer { Name = "Edsger", Balance = 42.50m };

var id = customers.Insert(customer);        // -> the key SQL Server assigned
                                            //    customer.CustomerId now holds it too

A key member left at its default — 0, Guid.Empty, null, "" — means "not set". When the column can produce its own value, it is left out of the INSERT and read back with OUTPUT INSERTED, so an IDENTITY, a DEFAULT NEWID(), and a DEFAULT (NEXT VALUE FOR ...) sequence all come back correctly rather than storing the empty value. Set the member and that value is used instead — except on an IDENTITY column, where supplying a key is refused by name rather than silently dropped.

A composite key has no single column to assign, so its columns are written straight from the document like any others; FindById, Update and Delete still refuse, naming the columns.

Other limits

Update on a raw document replaces the whole row, so a field left out becomes NULL (use UpdateMany for partial updates); InsertBulk inserts row by row because bulk copy cannot return generated keys; DATETIME2 values round-trip at millisecond precision and are treated as UTC; and only tables in the connection's Schema with word-character names are reachable.

Generating a class from a table

A companion Visual Studio extension adds a Generate DTO command to table nodes in Server Explorer, writing a class mapped to that table and adding it to your project - [BsonId] where the key convention would miss it, [BsonField] where a column name is not a legal C# name, nullable types for nullable columns.

Its Generate Repository command, on the Tables folder, does a whole database at once: tick the tables you want and it writes CRUD over ILiteCollection<T>, typed by each table's own primary key, along with the DTOs. Either as a <Table>Repository per table - with, if you want, one aggregate class handing them all out - or as a single repository covering every table on its own.

See tools/LiteSqlServer.Vsix for building, installing and its verification status.

Queries

Predicates are translated to SQL. What is supported:

Category	Examples
Comparison	==, !=, <, <=, >, >=, null checks
Logic	&&, \|\|, !, bare bool fields
Strings	StartsWith, EndsWith, Contains, Equals, ToLower, ToUpper, Trim, Substring, Length, IsNullOrEmpty
Numbers	+, -, *, /, %
Dates	comparison, .Year, .Month, .Day, .Hour, .Date
Nested	x.Warehouse.City == "Oslo"
Arrays	x.Tags.Contains("steel"), x.Lines.Any(l => l.Quantity > 3), .All(...), .Count
Sets	list.Contains(x.Name) becomes an IN clause
Identifiers	x.Id == 5 uses the primary-key column directly

Captured locals become parameters, so plans are reused and injection is not possible.

Untranslatable predicates

Anything with no SQL equivalent falls back to evaluating the compiled delegate in memory over a collection scan. Results stay correct, but the query is slow and silent about it. To find these:

ClientEval=false      # in the connection string — untranslatable predicates now throw

Query().GetPlan() returns the generated SQL, its parameters, and whether a client filter was used.

---

Differences from LiteDB

Things that behave differently are worth knowing up front.

Area	LiteDB	LiteSqlServer
Storage	one embedded file	one SQL Server table per collection, or a pre-existing table
Creating a collection	implicit on first write	explicit: EnsureCollection(), or AutoCreate=true
String comparison	case-insensitive	follows the database collation (case-insensitive by default)
Sort order	BSON type order	SQL Server order for the projected type
Transactions	per thread	per execution flow (AsyncLocal, survives await)
Select()	server-side projection	materialises the document, then projects in memory
Encryption	AES via Password=	use SQL Server TDE or Always Encrypted
Array indexes	multi-key indexes	not indexable; array predicates use OPENJSON and scan
Max document	16 MB	NVARCHAR(MAX), effectively 2 GB
SQL dialect	full LiteDB SQL	a practical subset (see below)

Collection names must be word characters ([A-Za-z_][A-Za-z0-9_]*, max 100). Names become quoted SQL identifiers rather than parameters, and this restriction is the boundary that keeps a collection name from becoming an injection vector.

---

Connection string

Any SQL Server connection string, plus these optional keys:

Key	Default	Meaning
Schema	dbo	Schema holding the collection tables
RelationalTables	true	Bind an unknown collection name to a physical table of that name
AutoCreate	false	Let a write create the collection it is missing. Off by default, so writing never runs DDL; EnsureCollection and EnsureIndex create regardless, since they are asking outright
ClientEval	true	Allow in-memory fallback for untranslatable predicates
ReadOnly	false	Reject all writes
CommandTimeout	30	Seconds

Server=.;Database=Shop;Integrated Security=true;TrustServerCertificate=true;Schema=lite;ClientEval=false

---

Transactions

db.BeginTrans();

try
{
    orders.Insert(newOrder);
    inventory.Update(item);

    db.Commit();
}
catch
{
    db.Rollback();
    throw;
}

The transaction is ambient for the current execution flow, so it survives await and stays isolated between concurrent requests sharing one LiteDatabase. Disposing with a transaction still open rolls it back.

The schema is created outside any user transaction — a rollback must never be able to remove something every collection shares — while collection tables created inside a transaction roll back with it.

---

Concurrency

LiteDatabase is thread-safe and designed to be a singleton, like LiteDB in ASP.NET Core. Connections come from the SqlClient pool.

Sequential identifiers are allocated server-side in the same round trip as the insert, so parallel writers cannot hand out the same value. Allocation and collection creation each take a named sp_getapplock first, which is what keeps them deadlock-free: without it, several sessions scanning MAX([_id]) over an empty range all hold compatible locks and then deadlock trying to convert them for the insert. A deadlock victim is retried with backoff as a backstop.

InsertBulk reserves a contiguous identifier range once under the same lock, then writes the batch with SqlBulkCopy.

builder.Services.AddSingleton<ILiteDatabase>(_ =>
    new LiteDatabase(builder.Configuration.GetConnectionString("Documents")));

---

File storage

db.FileStorage.Upload("$/images/photo.jpg", @"C:\uploads\photo.jpg");

using var stream = db.FileStorage.OpenRead("$/images/photo.jpg");
await stream.CopyToAsync(response.Body);

Metadata lives in a normal collection; content is chunked into a VARBINARY(MAX) table at 1 MB per chunk, so large files stream instead of loading whole. GetStorage<TId>("files", "chunks") gives independent storages.

Those two tables follow the same rule as everything else: the first upload creates them only under AutoCreate=true, and otherwise says so. Reading and deleting files never create anything.

---

SQL dialect

db.Execute(...) supports a practical subset, rewritten into SQL Server queries:

SELECT $ FROM products WHERE $.Price < 10 ORDER BY $.Name LIMIT 20 OFFSET 10
SELECT COUNT(*) FROM products WHERE $.Category = 1
SELECT SUM($.Price) FROM orders            -- also MIN, MAX, AVG
INSERT INTO products VALUES { "Name": "Drill", "Price": 55 }
UPDATE products SET { Price: $.Price * 1.1 } WHERE $.IsActive = true
DELETE products WHERE $.Price < 1
SELECT $ FROM $cols | $indexes | $database

The same expression language works from C#:

col.Find("$.Price BETWEEN 8 AND 13");
col.Find("LOWER($.Name) = 'hammer'");
col.EnsureIndex("name_lower", "LOWER($.Name)");
col.UpdateMany("{ Price: $.Price * 1.1 }", "$.IsActive = true");

Because a string expression carries no CLR type, the projected type is taken from the mapped entity when there is one. Otherwise it is inferred from the literal, with numeric literals widened to DECIMAL — projecting to INT would make TRY_CAST('12.50' AS INT) return NULL and silently drop every fractional value.

---

Mapping

The LiteDB conventions all apply: a member named Id or <Type>Id, or [BsonId]; [BsonField], [BsonIgnore], [BsonRef]; and fluent configuration.

BsonMapper.Global.Entity<Order>()
    .Id(x => x.OrderCode)
    .Field(x => x.Total, "amount")
    .Ignore(x => x.Scratch)
    .DbRef(x => x.Customer, "customers");

var orders = db.GetCollection<Order>("orders").Include(x => x.Customer);

A key member called neither Id nor <Type>Id is named by [BsonId] or .Id(...), as above, and Insert returns and assigns it as usual. Over a pre-existing table it need not be named at all — see Identifiers that are not called Id.

Empty identifiers are generated on insert: int/long sequentially, Guid and ObjectId client side. References store { "$id": ..., "$ref": "collection" } and are resolved by Include, which batches lookups rather than issuing one query per row.

---

Building and testing

dotnet build
dotnet test

The library has 245 tests covering the document model, mapper, LINQ translation, real index seeks, transactions, references, file storage, the SQL dialect, untyped queries, pre-existing relational tables (as documents and as mapped classes), concurrent access, and the guarantee that nothing but an explicit call writes to the database. They run against both .NET 8 and .NET Framework, so the Framework-specific code paths are exercised rather than merely compiled. The DTO generator behind the Visual Studio extension has 73 more, on .NET 8 — 563 executions in total. They pass identically under dotnet test and the Visual Studio test runner.

Proving the database is left alone

NoDatabaseModificationTests is the test for the promise in Everything that can write, in full. It builds a schema entirely from raw SQL — an identity table with a foreign key, a check constraint, a default, an extra index, an extended property and a view, plus a hand-written document table — the way a DBA would leave it, and then compares a DatabaseFootprint taken before and after each path through the library.

A footprint is the whole schema, not just its table names: objects, columns and their computed definitions, indexes and their key columns, check and default constraints, foreign keys, extended properties, module bodies, triggers, sequences, identity seeds, row counts and row checksums. So a column quietly added to someone else's table, an index built behind their back, a burnt identity value and a rewritten row all fail the same way, and the failure prints the diff.

Nine of the tests are the control. They make each forbidden change directly — add a column, create an index, drop a constraint, update a row — and assert the comparison catches it, because a test that cannot fail proves nothing about the ones that pass.

One exclusion is deliberate: statistics SQL Server creates for itself under AUTO_CREATE_STATISTICS appear on any table anything queries, from any client, and no library can suppress them short of query hints. Only user_created statistics are recorded, so one this library asked for would still show up.

LocalDB

The tests need SQL Server LocalDB at (localdb)\MSSQLLocalDB. Start it with:

sqllocaldb start MSSQLLocalDB

Each run rebuilds a scratch database — LiteSqlServerTests_net and LiteSqlServerTests_netfx, one per framework so concurrent runs cannot drop each other's — and gives every test its own schema. The rebuild is deliberate: reusing the database would leave one schema per test behind on every run.

A LocalDB instance can also lose a database while its .mdf and .ldf remain on disk. After that CREATE DATABASE fails on every later run, and guarding it with IF DB_ID(...) IS NULL does not help — the guard passes, because as far as the instance is concerned the database really is gone. tests/Shared/LocalDbFiles.cs clears such files by attaching and dropping them, so SQL Server removes its own files cleanly, and deletes them directly only if that fails. Both test projects compile that one file rather than carrying a copy each, since the DTO generator's tests keep their own LiteSqlServerDtoTests database and hit the same trap.

The sample app

samples/LiteSqlServer.Demo is a runnable walkthrough of the whole API:

dotnet run --project samples/LiteSqlServer.Demo

Packaging

dotnet pack src/LiteSqlServer/LiteSqlServer.csproj -c Release

This writes LiteSqlServer.<version>.nupkg and a matching .snupkg symbol package to artifacts/. The package carries both target frameworks, XML documentation for IntelliSense, this readme, and the licence.

Set the version with -p:Version=1.1.0, or edit <Version> in the project file. Note that NuGet caches a package by version: when re-packing the same version while testing locally, clear ~/.nuget/packages/litesqlserver/<version> first, or the consuming project will keep using the copy it already restored.

To publish:

dotnet nuget push artifacts/LiteSqlServer.1.0.0.nupkg --source https://api.nuget.org/v3/index.json --api-key <key>

PackageProjectUrl and RepositoryUrl are commented out in the project file — fill them in once the project has a public home, so the listing links back to the source.

Licence

MIT — Copyright (c) 2026 Jordan Duerksen.