Migrating to SQLite
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:
- SQLite is awesome!
- Self-contained: does not require a separate server process
- Data is stored in a single file, simplifying backups
- Excellent Python (and peewee) support
- Full-text search
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!
- O'Reilly published an excellent book on SQLite which I recommend to anyone interested in a doing a deep dive.
- SQLite: Small. Fast. Reliable. Choose any three
- Writeup of BerkeleyDB's SQL API if you're interested in performance details.
- peewee SQLite documentation
Here are some other blog posts you may find interesting:
- SQLite: Small. Fast. Reliable. Choose any three
- Guide to extending SQLite with Python, which includes an example Virtual Table implementation exposing a Redis Server as a SQLite table.
- Using SQLite's full-text search with Python, including how to use user-defined functions to sort search results by relevance.
- Building an encrypted diary using Peewee and SQLCipher
- Oracle recently released SQLite-compatible libraries that are backed by BerkeleyDB. Lots of interesting stuff there, but the big one is support for concurrent writes.
Comments (0)
Commenting has been closed.