LiteDB vs SQLite: what works better for .NET developers?

SQLite has long been the gold standard for desktop, local, or embedded databases. It's been around forever, is unit-tested from top to bottom, and performs exceptionally well when used as intended.

C# developers have used various abstraction layers to access its APIs more in a way that would be more idiomatic. When I looked for one I could use in my desktop app for tango DJs, Bewitched, I went with SQLite-net.

I use it to this day. Anything Frank Krueger creates is beyond awesome! :)

Because SQLite is written in C, it isn't a native citizen of the .NET ecosystem. That is perfectly fine in practice, as the native library will be included in your build, and your domain code doesn't need to know it's talking to a non-dotnet code.

Still, it bugged me occasionally. Could there be a comparable database built for .NET?

Well, there is. It's called LiteDB, and it's pretty amazing.

The fundamentals of LiteDB

LiteDB is 100% C# for .NET 4.5 / NETStandard 1.3/2.0. You run Install-Package LiteDB, and you get a single DLL. No interop assemblies, no x32/x64 hassles. The advantage of this is hard to overstate.

Just like SQLite, LiteDB:

• is serverless,
• has ACID transactions,
• uses a single file for your data and indexes

In addition, it supports an external file storage mechanism for blobs greater than 1MB.

Unlike SQLite, LiteDB is a document database.

SQLite has tables, which have rows and columns. You define the schema, decide on the data types and choose which fields are required and which can have NULLs.

LiteDB has collections, just like MongoDB. Collections of documents. Each document has to have an ID, but otherwise it's up to you to define what the document should contain.

Its mindset is closer to MongoDB, and if you've done any work on the MEAN stack, it will feel very familiar to you.

Its API is document-oriented. BUT - if you prefer SQL, you can talk to it using a very SQL-like syntax - with SELECTs, INSERTs, UPDATEs, and DELETEs - as of version 5.

The SQL capabilities of LiteDB are impressive. The SELECT keyword has WHERE, GROUP BY, HAVING clauses, and you can order your results as well as limit how many are to be returned.

First steps with LiteDB

You can start hacking in Visual Studio or VS Code by referencing the Nuget package: Install-Package LiteDB.

Don't have any code yet? No problem! Download the LiteDB Studio instead, and play with the database engine directly.

What you need to get started is just some JSON you have lying around.

I exported a sample collection from my local MongoDB instance, cleaned up the JSON to remove ObjectId(...) and ISODate(...) references, and imported it into the app. Obviously, you can construct your JSON any way you like.

When you enter the input file and choose the collection name, the program outputs the import statement for your reference.

As you will see below, it reads very much like regular SQL:

SELECT $
INTO addresses
FROM $file('C:/Users/Tomas/Downloads/addresses.json');

I tried a query on my sample data set:

SELECT $ FROM addresses WHERE city = 'Venice';

Sure enough, the output was what I expected with a data grid of relevant records.

As this is a document database, you won't get JOINs, but the documentation explains how you can include other collections in your query like this:

SELECT $ FROM addresses INCLUDE countries ORDER BY country.name;

The docs include more in-depth info on how the references between collections work. It's a bit more work than defining foreign keys in traditional SQL databases like SQLite.

From my experience with Mongo, when you need referential integrity that you can take to the bank, you need a proper SQL database. For many schemaless (or rather, schema-loose) scenarios, the document approach works just fine.

Programming your app with LiteDB

How do you code your persistence layer using LiteDB?

You don't need DTOs (Data Transfer Objects). Instead of dummy classes, you can work directly with documents like this:

var address = new BsonDocument();
address["street"] = "Palm Street";
address["houseNumber"] = 35;
address["city"] = "Venice";
address["state"] = "CA";

using(var db = new LiteDatabase("database.db"))
{
    var col = db.GetCollection("addresses");
    
    // Insert the document into the database. This will create the collection if it does not exist yet.
    col.Insert(address);
    // ... do some more stuff
    // Look up your document
    var dbAddress = col.FindOne("$.city = 'Venice'");
}

As you can see, it's super straightforward to work with "raw" instances of BsonDocument, which itself is like a Dictionary of a string and one of BSON types.

Or, you can use classes and LINQ to create and manipulate your data in a strongly typed fashion:

public class Address
{
  public string Street { get; set;}
  public string HouseNumber { get; set;}
  public string City { get; set;}
}

using(var db = new LiteDatabase(@"Addressbook.db"))
{
    var col = db.GetCollection<Address>("addresses");
    var address = new Address
    {
      Street = "Palm Street",
      HouseNumber = 35,
      City = "Venice",
      State = "CA",
    };
    col.Insert(address);

    // ... do more stuff
    // Look up your document
    var dbAddress = col.FindOne(x => x.City == "Venice");
}

Starting with version 5, you can annotate a class constructor with [BsonCtor] to tell LiteDB how to construct your class instance. Its properties then no longer need public setters:

public class Address {
    public ObjectId AddressId { get; }

    public string Street { get; }

    public string HouseNumber { get; }

    public string City { get; }

    public string State { get; }

    public DateTime CreateDate { get; }

    public string StreetAddress => $"{Street} {HouseNumber}";

    public Address(string street, string houseNumber, string city, string state) {
        AddressId = Guid.NewGuid();
        Street = street;
        HouseNumber = houseNumber;
        City = city;
        State = state;
        CreateDate = DateTime.now;
    }

    [BsonCtor] // <- here
    public Address(ObjectId addressId, string street, string houseNumber, string city, string state, DateTime createDate) {
        AddressId = addressId;
        Street = street;
        HouseNumber = houseNumber;
        City = city;
        State = state;
        CreateDate = createDate;
    }
}

This way, you tell LiteDB how to construct your domain object without having to use a DTO. That's a big win in my book.

Alternatively, there is a fluent API. This means you can avoid having this database-specific constructor, which does not belong in the domain. Instead, you tell LiteDB how to construct the domain object somewhere in your infra/persistence layer:

var mapper = BsonMapper.Global;

mapper.Entity<Address>()
    .Id(x => x.TAddressId) // set your document ID
    .Ignore(x => x.StreetAddress) // ignore this property (do not store)
    .Field(x => x.CreateDate, "CreatedAt"); // rename a field

For a complete reference, look here

Which is better for .NET developers - LiteDB or SQLite?

There is no definite answer to this question. Other than it depends, of course.

In most cases, you can't go wrong with SQLite. You may think that having a schema is too constraining, but in the end, you'll be glad you have a schema once you've figured out the right model for your domain.

Then again, LiteDB can be just as welcoming to your domain objects. And, it can be more friendly to you while you are mapping it out.

No need to worry about migrations, for example. Assuming your persistence layer is forgiving enough about missing fields and such.

I would say that in the end, it's almost a question of esthetic preferences. Both databases will do a great job where they perform best, which would primarily be a desktop or mobile app.

There, the trade-offs are very much different compared to a web app that may receive updates several times a day and is being used by dozens of hundreds or users concurrently.

And if the .NET-native API of LiteDB appeals to you, why not give it a shot? Chances are indeed very good that it will delight you!

Published on 07 Apr 2020 , updated on 30 Jul 2022

What do you think? Sound off in the comments!