How SQLite and I met¶
My first experience with any RDBMS was Postgres, which I used (and still use) in my job. When I first added comments to this website, I naturally used Postgres because that was what I knew. Postgres worked fine for quite some time, but then I heard about SQLite.
I read several articles from different people comparing RDBMSes, and consistently the verdict was that SQLite was super
simple and lightweight and better for small projects that didn't need "the most advanced features". The
sqlite3 library is also standard so
I could get away from the annoyances of psycopg2 and the mildly
uncomfortable feeling of a third-party dependency, even if it's a reputable one.
I was astonished by the superior simplicity of setting up SQLite; there is no setup. You just tell it what file to use. I was thrilled to discard Postgres's conceptions of users I wasn't using and different databases in the same cluster. I soon found out that SQLite has a very limited type ontology (which I'll talk more about below), but I figured that was an acceptable price; it wouldn't be so bad to store my timestamps as Unix timestamps and aside from that I wasn't using any of Postgres's fancy stuff (if you could consider timestamps to be "fancy"...). So I wrote a little script to move all my data from Postgres to SQLite.
I was happy with it for a while. But a few weeks later I had a rapidly cascading discovery of inadequacy after inadequacy in SQLite. I was horrified and in a couple days I moved back to Postgres. Users are an acceptable cost. Storing the database in a monolithic tree of directories on disk instead of each database contained in a file was an acceptable cost. I haven't looked back. Now it's time for me to explain what I found so terribly inadequate, after my one major point of praise.
SQLite really is simple. Besides being a much smaller dependency than Postgres and probably any other RDBMS, I love how the database stays in a single file, so it's completely isolated and you know where it is. It's so much easier and simpler to move the database file to a different directory at your whim, switch out the dataset an application's using, or do backups. With Postgres, the database lives in a cluster which you can't easily inspect on the filesystem and if you have multiple databases they'll all share the cluster.
No type enforcing by default¶
SQLite doesn't enforce column data types.
This nearly defeats the point of having them and when I say "by default", I don't mean there's
an option to enable it because there isn't as far as I could find on the web, I mean that you
can get around it by adding
constraints on every column in every table. As you can see from those docs constraints are verbose and
tedious to write.
Even foreign key enforcement is "off by default"!¶
There's a pragma to enable it, but SQLite pragmas are hardly the most elegant feature to lock basic functionality behind, and even worse, this doesn't stay set as a configuration. You have to do it every time you connect to the database. Yes, I tested that.
SQLite does not support ALTER COLUMN operations. As a result there are a lot of simple changes for which you have to recreate the table and transfer over all the data.
No time data types¶
SQLite does not support any timestamp data types. If you want to store a timestamp you'll probably end up storing it as a Unix
timestamp, which besides meaning that language bindings like the Python
will have to return it as an int instead of the language's native timestamp datatype, and besides
opening the door to even more errors caused by weak typing, is not ideal if you're ever going to use the
command-line tool to interface with the DB (which is certainly the most convenient interface for quick manual stuff), because
timestamps won't be human-readable.
No array data type¶
This is somewhat more understandable than the above, but arrays are an extremely useful feature. To get them in SQLite you'll have to do something crazy like store them as text with a delimiter and presumably an escaping system or find a way of storing the array values in a separate table.
The consensus around seems to be that SQLite is great software and a really good choice for things that don't particularly need client/server model or obscure advanced features. My impression is that SQLite is a poor choice for almost anything that plans to become big. I would still recommend it for learning or teaching SQL (which people have mentioned) since the installation and usage is a snap, or for mock/testing tasks or super small-scale stuff with no intention to grow in complexity like this site has and will. But Postgres's setup really isn't that bad. It's 3 additional commands to get it set up as a service on FreeBSD.
Also, I find it funny that on the SQLite project's own Appropriate Uses For
SQLite page, they say "SQLite does not compete with client/server databases. SQLite competes with
That's a very slanted comparison. While SQLite's use case does differ in non-vertical ways from Postgres or other RDBMSs
(and note that if anything I unfairly counted that in SQLite's favor), it's still way more similar to them than it is to
anything that is not an RDBMS. I think them saying this is a bit like me writing a programming language like Python but with manual error-handling (a surprisingly good analogy
for not enforcing type correctness and foreign keys) and no generators or context managers and saying, "It doesn't compete with
Python, it competes with assembly".