Looking out for alternatives to SQLite

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

C# developers have used various abstraction layers to access its API in a way that is more idiomatic to .NET. When I was choosing one for my desktop app for tango DJs, Embrace, I went with SQLite-net and 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 is going to 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. What if there was an analogous database but built for .NET?

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

The basics 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.

LiteDB is - just like SQLite - serverless, has ACID transactions, uses a single file for your data and indexes but supports an external file storage mechanism for blobs > 1MB.

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

For you can do without classes and work directly on the documents like this:

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

If you appreciate SQL, you can talk to it using a very SQL-like syntax - with SELECTs, INSERTs, UPDATEs and DELETEs - as of version 5.

Or, you can use DTOs and LINQ to create and manipulate your data in a typed fashion, which is likely the indended use case.


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";
};
col.Insert(address);

// ...

var results = col.Find(x => x.City == "Venice");
}

First steps with LiteDB

You can start hacking right in Visual Studio by referencing the Nuget package.

My first step was to download the LiteDB Studio desktop app. I exported a sample collection from my local MongoDB instance, cleaned-up the JSON to get rid of ObjectId(...) and ISODate(...) references, and imported it into the app. Obviously, you can construct your JSON any way you like.

The import happens via a dialog where you specify the input file and the collection name. The program then outpus the import statement for your reference. This is how it looks like:

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

Very SQLike.

I tried a query on my sample data set:

SELECT $ FROM subaccounts WHERE goalGuaranteeLevel > 0;

Sure enough the output was what I expected with a datagrid of relevant records.

As this is a document database, I'd expect there to be no JOINs, but the documentation suggests that you can include other collections in your query like this:

SELECT $ FROM orders INCLUDE customers ORDER BY customer.name;

The v4 wiki includes more in-depth info on how the references between collections work. From my experience with Mongo, when you need JOINS you need a proper SQL database. For many schemaless (or rather, schema-loose) scenarios, the document approach works just fine.

Anyways, the Studio provides a low-friction way of tinkering with LiteDB. Next, you'll want to launch Visual Studio, include LiteDB in your project, and see how you can use it to store your data.

Finding the optimal communication method for LiteDB

There are several ways of talking to LiteDB.

You can use its ORM

LiteDB has solid ORM and object-mapping capabilities. For reasons that have more to do with esthetics, I do not prefer to use them. If I did, I'd have to use POCOs/DTOs, and I won't. Like on this example:

// Simple strongly-typed document
public class Customer
{
public ObjectId CustomerId { get; set; }
public string Name { get; set; }
public DateTime CreateDate { get; set; }
public List<Phone> Phones { get; set; }
public bool IsActive { get; set; }
}

I have rallied against "naked classes" in my February column "Making objects elegant in C#". And what else are DTOs than naked data?

Any class with public setters is not responsible for its contents and cannot keep its state consistent.

The solution is to map the DTO to a fully-fledged domain class as soon as possible, or not use ORM at all.

Personally, I find ORMs to be too much hassle as soon as I move from the HOWTO to a real-world app. The abstractions often leak at their seams.

More importantly, I find that DTOs take me too close to the imperative programming mindset that I hate. Even if I just have to copy over properties from a DTO into a constructor of a regular class, I still cringe.

Again, this is just me. I totally get why LiteDB supports this approach as it's mainstream in the .NET world (thanks for nothing, Java!)

You can kind-of use SQL

I like SQL. It's declarative and that's a good thing. I suspect I would use it extensively with LiteDB if I knew that portability was not a concern. When properly abstracted away from the domain logic, the data access layer can be slim enough that I can afford to support 2-3 data stores using their native language and avoiding ORMs.

The intial SQL support in LiteDB is 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.

Or, just work with documents

This is the way I chose for my toy project, Blogroll.

My main collection class that represents all my blogroll links implements an interface that specifies that the client wants to add, remove, and reposition a link:

public interface IBlogRoll
{
/// <summary>
/// Adds a link.
/// </summary>
void Add(ILink link);

/// <summary>
/// Removes a link.
/// </summary>
void Remove(ILink link);

/// <summary>
/// Moves a link from one position to another.
/// </summary>
void Move(int oldPosition, int newPosition);

/// <summary>
/// Returns an index of a link.
/// </summary>
int PositionOf(ILink link);

/// <summary>
/// Returns a link by its position.
/// </summary>
ILink Find(int position);

/// <summary>
/// Prints itself to the provided printer.
/// The assumption is that the implementing method will be async which is probably wrong :)
/// </summary>
Task<string> PrintedTo(IBlogRollMediaSource printer);
}

The class that represents each individual record. Admittedly, it has to dance around a lot to avoid disclosing its properties. When asked to convert itself to the domain class called BlogRollItem, it calls its constructor providing its properties:

public static implicit operator BlogRollItem(LiteDbBlogRollItem value) => value.ToBlogRollItem();

private BlogRollItem ToBlogRollItem() =>
new BlogRollItem(new Link(_document["Name"], _document["Url"], _document["FeedUrl"], _reader),
_document["Position"]);

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. I don't personally see a need to abstract it away.

In summary, working with BSON documents is my preferred way of interacting with LiteDB. When I know more about its SQL capabilities, I'll likely use them in my collection classes to implement searching, filtering, etc.