WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
In today's world of mobile apps, it's not an unreasonable expectation to want a database that's small, lightweight, and travels with the app from device to device.
In some cases, it's even a requirement for a server based system, where for various reasons you may not be able to install a full scale DBMS such as SqlServer or Postgres or some other heavy iron stand alone system.
If there's one thing that .NET has no shortage of, it's small scale offerings that fill this gap just nicely.
This week's post is going to take a quick look at what's available out there, and a few of the pros and cons of each.
Before I get started though, I will be mentioning a couple of data stores that are not truly portable here, but in my mind I think they qualify because even though they are not as portable as some, they are almost zero configuration, and easy to wrap up with your application (besides the term portable is quite subjective; to some portable means being able to pick up the database file and hand it around, to others being portable just means the database stays with the device). Whatever your point of view the data is essentially portable in some form.
What's not to love about the world's favourite portable database, SQLite is not only super portable (Requires 2 or 3 Dll's) and is extremely fast.
SQLite has been officially supported on .NET for a few years now, and supports not only plain old ADO.NET but in recent versions, entity framework too.
The best part about SQLite is how cross platform it is. It's supported on Classic Windows Mobile, Windows CE, Windows Phone, Android, iOS, Linux, Windows, Unix and just about every other platform I've ever used.
Originally written as a C based unmanaged library, it now has managed bindings that call an un-managed stub (Usually called SqliteInterop.dll), which means it can be used with both un-managed and managed applications on the Windows platform.
Installing it into your application is as simple as searching for it on NuGet and hitting install; programming it is done using plain ADO.Net with 'SQLite' being the object prefix, EG: 'SQLiteConnection' or 'SQLiteDataReader' or using the entity framework bindings to use it as part of the EF-Orm.
It's also well catered for in extensions, where for example it can use the 'Spatialite' extension to provide GIS features, usually only found in larger databases such as Postgres.
The engine is acid compliant to the full SQL96 standard and offers built in strong encryption as well as many other features.
It does have one or two problems on the .NET platform though, and they mainly step from its unmanaged ancestry. In order for SQLite to work correctly, there has to be an unmanaged portion provided with your app.
As well as 'System.Data.SQLite' you'll also see a file called 'SQLite.interop.dll', this low level stub is responsible for the actual real SQLite code that the managed assembly calls, and without it you'll get some quite weird exceptions when using it.
This stub also causes problems for platform size, and has separate versions for 32bit vs. 64bit. Using the wrong version on the wrong platform will cause endless hours of frustration trying to debug it.
SQL Server Compact Edition
Microsoft's own answer to the portable database market comes in the form of 'SQL Server CE', originally intended for distribution on the CE / Mobile operating system, it very quickly was realized that it also filled a very niche gap in the web application market too.
Almost overnight, many web developers realized that they could use CE to replace the larger SQL Server product, especially when developing applications. For a lot of developers this meant they didn't have to have SQL Server installed and could just develop applications locally, knowing they could then just deploy the database file with the web application, making it possible to move from server to server without any reconfiguration.
SQL Server CE, comes in 2 different versions, and unfortunately for us there are major differences between them; in practice you’re much better off for new projects using the V4 CE runtime as opposed to V3.5, but be aware that database files created using V4 can only be opened and managed in SQL Server Management Studio 2008r2 or higher.
One of the great things about SQL Server CE, is its full blown SQL Server compatibility and acid compliance; just about anything you can do in the full blown SQL Server model, is at least mostly possible in SQL CE and because it's an MS product, using it with ORMs like entity framework, just works straight off the bat too.
Files unfortunately have a 4 GB size limit, but that's more of an underlying file system limit than a database limit, but files can be encrypted to protect against casual snooping, and can be used on all Windows variations.
I can hear the groans already, but the fact remains, Access, even today, is a useable portable DBMS technology that's easy to use.
The destination machine doesn't need Office or anything like that installed, unless you intend to modify the database file; all that's needed to allow the database to be used is the Jet Database Engine, which in most cases you can be pretty certain will be installed on most machines since windows XP.
Access however is not without its quirks, and really doesn't scale very well once you start using it for more than simple table access.
Because it has its roots in office though, it can do some very clever things. For instance, any forms or VB script functionality that exist in the file can be exported for use in your .NET application, it also has built in locking and sharing to allow the database itself to be placed on a standard network share in a workgroup, allowing access by more than one application at a time.
Unfortunately though, it's not fast by any stretch of the imagination, and while it offers some interesting options, it's best used only in desktop applications and nowhere else.
You also need a full version of an Office suite in order to create the initial files, making management much harder than it needs to be.
Browser Data Stores
The good old browser. Humble mainstay of unlimited access to the world internet of cat pictures, and an increasingly powerful tool in today's modern HTML5 world.
All of the current up to date browsers, IE, Firefox, Chrome, Safari, Opera and Maxthon support some kind of browser based database. Depending on your version, and browser manufacturer this might be as simple as a key value store, or in the case of Chrome a full Web-SQL data store is provided.
For example, Chromium is available as a standalone DLL library, that you can load into your Windows desktop app. This DLL is then used to provide access to an underlying web browser instance, where your app can use HTML (and subsequently any built in browser databases) for its user interface, giving you the best of both worlds.
The actual data store itself however is usually not accessible by the end user, which means that unlike the previous candidates, you can't physically pick the file up and copy it to another system, but it is very easy to extract the data, save it in another format, in a medium such as a cloud drive, then pick that stored version up on another device and restore it easily.
File Based Databases
While not exactly classed as portable, a quick scan of places like codeplex will quickly reveal a plethora of data stores provided in single .NET assemblies, whose sole purpose is to persist any data you may wish to save into quite simple high speed flat files.
There are even text based interfaces to use simple CSV files and XML files as an ad-hoc database, which can then be synced with a larger store when connection is available.
Because file access is also so easy in .NET, this is also a very practical route if you find yourself needing to store information locally, but for whatever reasons can't install any third party products. Particularly, if you need cross platform portability onto systems like Mono, and can't use anything previously mentioned, you may feel that a flat text file is a good approach to take.
There are many downsides of course, mostly the security and encryption side.
You’re ultimately 100% responsible for keeping any data that needs to be secure, secure. And you'll need to do a lot more interfacing code yourself too, the trade off however is in ultimate flexibility, and if all you’re storing is a small amount until you can sync with a larger store, it's often one of the easiest routes to take.
Mongo falls into the category (technically anyway) of static database server, rather than portable database. However in my mind at least, it has a small footprint and is as easy as copying a few exe files to a folder to install it.
Since it requires no complicated setup, it's easy to bundle the mongo DB runtime files as part of your applications setup process, then have them copied into your application’s install folder at setup time.
Controlling it is just as easy too. All you need to do is make use of the .NET process running classes within your app to launch and run the server as/when you need it. You don't need any configuration saved, as any options needed to run it can be specified directly on the command line, allowing you for example to specify exactly where it's database files should be stored.
Mongo however is very, very flexible in what and how it can store data, and ultimately very fast too, and is a good choice for standalone servers that you might deploy as middleware on standalone platforms, and is reasonable cross platform too.
Is This the Only Choices You Have?
Not at all, these are just the ones that I would consider should be at the top of your list, as a .NET developer. There are many more out there, but most of them require some extensive work to be useable from within the .NET environment.
If you want to dig deeper, there's BerkleyDB, Druid, CouchDB or if you want a system similar to Mongo that's easy to install with your app, you could look at Redis, Firebird and Maria-Portable.
As with any technology choice, which you use ultimately has to be what's best for your project, its budget, intended audience and a lot of other factors.
If you have any burning questions, or are struggling with a topic in .NET that you can't find explained feel free to hunt me down. Most of the time you can find me floating around in the Lidnug .NET users group on Linked-in and hanging around in Twitter as @shawty_ds , I have a Google+ page, a Linked-in page and all sorts of other ways of getting in touch, let me hear your thoughts and ideas and I'll do what I can to incorporate them in future posts.