Skip to content
September 26, 2007 / Bob Black

Embedded Databases

As I noted in an earlier post, I’ve been experimenting with SQLite, a super-small super-fast SQL database engine. SQlite is a small C library that supports transactions, implements most of SQL-92, supports terabyte-sized databases, and weighs in at only 150KB. I’m finding that it’s just perfect for desktop applications that need to store and quickly retrieve data from disk.

SQLite databases are made up of one file, which you can name whatever you want, and which can be encrypted. There are tons of GUI tools (almost all selling for $60 or more) for creating and configuring SQLite databases, but (for now) I prefer the free SQlite Administrator, which is just about as good as it gets. This isn’t an Enterprise RDBMS here; I just want to create some tables, add some indexes and primary keys, and go develop something cool, and this gets the job done. And you can’t beat the price.

I’m using .Net, so I needed a .Net provider, and I Google’d across this bodacious ADO.Net 2 provider for SQLite, which amazingly can be used either as a .Net provider, or as a “regular ol'” SQlite engine with no linker dependency on the .Net runtime. That’s really neat. Not that I would ever need that – but that’s cool anyway.

I can’t offer my esteemed opinion on performance metrics or whatnot, other than to say that, so far, it’s fast enough. It handles some of Code Toaster’s Intellisense queries lightning fast, against a database of roughly 20 MB in size.

There are other alternatives in the world of embedded databases, most notably SQL Server 2005 Compact Edition, which is also free, and also lightweight, weighing in at a mere 1 MB. The feature-set offered by this database engine is waaay more than SQlite could ever offer (being that SQL Server has more resources and more developers and so forth behind it), but SQLite wins hands down over simplicity. Deployment of SQLite is just the engine (~160KB) and a database file. I haven’t personally tried, but I’m guessing deploying SQL Server Compact Edition requires messing with a merge module or some nonsense that I don’t care to worry with right now (I know, I’m being a lazy blogger. Shame on me.).

The big downside to SQLite, and perhaps a big reason why you would want to consider SQL Server Compact Edition, is this whole locking of the database thing. You can’t write and read at the same time, lest you (and your users) get a nasty error message. Which means if your application is threaded in any way shape or form, you’ll probably need to use a flag, a Mutex, or somethin’ to keep from blowing your app to the moon.

That sounded bad. It’s really not that bad. I like SQLite. Seriously, overall I think SQLite is a simple, sleek little database engine machine thingy, with a wonderfully small footprint that will more than foot the bill if you can afford to take a little extra care in your coding.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: