Migrating to SQLite

Sqlite Logo

Small. Fast. Reliable. Choose any three.

I made the decision this week to migrate my personal sites and several other sites I host onto SQLite. Previously almost everything I hosted had been using Postgresql. The move was motivated by a couple factors:

At times it has seemed to me that there is a tacit agreement within the Flask / Django communities that if you're using SQL you should be using Postgresql. Postgresql is an amazing piece of engineering. I have spent the last five years of my career working exclusively with it, and I am continually impressed by its performance and the constant stream of great new features.

So why change things?

Well, as my list indicates, there are a handful of reasons. But the primary reason was that I wanted something lightweight. I'm running a fairly low-traffic, read-heavy site, so Postgresql was definitely overkill. My blog is deployed on a VPS with very limited resources, so every MB of RAM counts. Additionally, I wasn't using any special Postgresql features so there was nothing holding me back.

SQLite is awesome

Python devs are probably familiar with SQLite because it is baked into the Python standard library. You can even write your own functions, collations and aggregates in Python, then call them from your SQL queries. The source to pysqlite is very approachable, as well. If you want to go low-level, there's also a neat advanced python driver named APSW which you can use to unlock some of SQLite's more interesting features.

Some critics of SQLite point to database-locking writes as a big reason it is unsuitable for web applications. Recent versions of Oracle's BerkeleyDB come with a SQLite-compatible SQL API, which even supports extensions like full-text search. If you want to use SQLite and are anticipating lots of concurrent writes, BerkeleyDB can offer a large boost in transactions-per-second. If you want to use BerkeleyDB's SQL API from Python, you can compile pysqlite to use BerkeleyDB.

SQLite also has some cool extensions, such as full-text search, encryption, compression, and the GIS SpatiaLite library.

Peewee and SQLite

Peewee (python ORM) has excellent support for SQLite, as well as an APSW-compatible implementation. Peewee makes it easy to use full-text search, create user-defined functions, manage transactions and savepoints, and more. SQLite full-text search now powers my blog's site-search so give it a try -- it even supports simple boolean expressions!

Reading more

Thanks for taking the time to read this post, I hope you found it interesting!

Here are some other blog posts you may find interesting:

Comments (0)

Commenting has been closed, but please feel free to contact me