The big attraction of SQLite

February 3, 2014

Recently I came an obvious in retrospect realization about what is to me the big and game changing feature of SQLite. I'll put it this way:

SQLite gives you a powerful relational database that doesn't require you to run a server.

Running a separate database server is all sorts of hassle. You have to configure it, set up access to it, arrange for it to be started when the system boots up and then kept running, monitor it to make sure that it's still running, and so on and so forth. Often this will need either the involvement of your system administrator or even more hacking on scripts and configurations to run your own copy of the entire database server infrastructure. Then multiply this work for development and test database servers (because you're not developing or testing against the production database, are you), and so on and so forth.

(One of the many effects that this has is that it's simply not worth using a SQL relational database for small projects even when that might be the easiest approach if you had one handy. It's just too much bureaucracy.)

SQLite throws all of that hassle out of the window. There is no server and basically no configuration. You 'configure' SQLite by telling it the filename to store things in and access control is done by ordinary Unix permissions. Need a development or test database? Change the filename of the database in your configuration and you're done. SQLite can even run entirely in memory for the ultimate disposable quick database.

(And if you're developing as a different Unix user than the production application runs on, permissions will probably insure that you can't accidentally touch the production database even if you screw up. Not to mention the ease of setting up a copy of the production database; most of the time you can just copy the SQLite database file.)

At the macro level what this has done is make it practical to give even small low-hassle things access to a powerful relational database (and as a bonus it speaks SQL and you can do ad-hoc queries against it). If you think you need relational features there is now basically no reason not to use them. And it will probably be relatively easy to scale your program up to using a full sized server based SQL database if you turn out to need one of the big guns after all.

Or the real summary version: SQLite has made SQL databases into a lightweight thing.

(Well, of course. It's right there in the name. I'm slow so things take some time to sink in.)

I've been conditioned to think of SQL databases as very heavyweight things, but SQLite has changed this. With this in mind I think I'm going to be much more willing to assume and use a relational database in future projects, rather than try to glue together some sort of ad-hoc equivalent based on eg plain files. I'm not entirely convinced that I'll carry through on this because I like plain text files, but there's an inflection point where the filesystem makes an increasingly bad database engine (also).

PS: Yes of course if you need a fully featured SQL engine, SQLite is not really the thing for you. The same is true if you need something that stands up to high concurrency and high load and so on. I tend to think of SQLite as a relational database engine that happens to speak basic SQL for convenience.

Comments on this page:

By at 2014-02-04 03:44:15:

My colleagues are using SQLite as their database backend while running unit tests.

Allegedly, it's faster than running tests against standard MySQL.

SQLite also has the benefit that it's very easy to completely get away from disk IO. Simply mount a tmpfs and drop the db file in there.

However, it is imperative that the tests are run against MySQL at least once before merging new code.

(otherwise we can easily fool ourselves with code that depends on foreign keys and transactions).

My blahg engine used to use (Linux) extended attributes for metadata. It worked pretty well, but it ended up biting me when I made a filesystem dump without xattrs present and then reinstalled the whole system. I'm still missing some pieces of metadata :(

This snafu made me move all my metadata into a sqlite3 database. Not only did this stopped my reliance of xattrs, but it allowed me to do some things very easily thanks to having SQL at my disposal. (E.g., selecting all tags used at least twice in alpha insensitively) The one annoying trait of using sqlite as the primary metadata store is simple... you need to write and execute SQL to update it. (Every new post needs to update the db, every new comment, etc.)

So, I came to the conclusion (somewhat inspired by your posts about DWiki) that the primary metadata should live in the post text file itself and use special tags which I can extract from the AST. Then, these values can be inserted into sqlite automatically. Essentially, I'm planning on using sqlite as a cache that gives me all this power, but the authoritative version of everything is in one place - the post text file.

Written on 03 February 2014.
« Technological progress and efficiency
An interesting internal Django error we just got »

Page tools: View Source, View Normal, Add Comment.
Login: Password:
Atom Syndication: Recent Comments.

Last modified: Mon Feb 3 21:40:51 2014
This dinky wiki is brought to you by the Insane Hackers Guild, Python sub-branch.