Comparison of databases for UWP apps: SQLite, Realm & LiteDB

Object-oriented applications usually need object-oriented storage. Relations are good at mapping pointers, but many technical limitations are hidden under documentation crust. It is not easy to choose the right database technology because the pros are written everywhere, and the cons are spread across many GitHub issues. It is like buying a new car. You know the price, power, and fuel consumption, but you must inquire into reliability and maintenance costs.

SQLite

• Impossible to rename or remove an existing table column.

SQLite is written in C, which limits UWP processor architectures to those that SQLite supports because Windows does not have SQLite built-in. It is not a big deal, but LiteDB does not have this limitation. SQLite requires the SQLite for Universal Windows Platform Visual Studio extension, which updates automatically, but you need to manually recreate the project reference to the SDK every time it happens. The database version can be tracked by the user_version pragma statement, which is both simple and effective. When you want to use Entity Framework Core, you must target at least Windows 10 Fall Creators Update, in which UWP supports .NET Standard 2.0.

Realm

• Doesn’t support Unicode characters in file paths.
• Doesn’t support foreign keys in queries.
• Object properties are accessible only inside the using block of the database context.
• The query doesn’t support multiple values in the where clause.
• It is impossible to use await inside the using block of the database context.
• Once set, the primary key cannot be modified.
• The query doesn’t support projection.

Realm lacks UTF-8 character support for file paths. This means that Realm is not able to open a database file when the user profile name contains characters outside the ASCII table. This bug has persisted for more than one year after the beginning of UWP support, and no fix is planned. Realm is usable only for Android or iOS environments. It also relies on Fody, which injects some code changes inside the build process. When Fody updates, builds break.

LiteDB

• When the query has more and operations, the engine will run only one condition with an index, and other conditions will use a full scan.

LiteDB is written in C#, which means it nicely fits the Visual Studio, NuGet, and .NET environments. It requires .NET Standard 1.3, which has been implemented since UWP 10.0.10240. It is very useful for simple applications, but when data manipulation requires complex rules and performance has a high priority, it turns out that LiteDB needs some speed optimizations.