This site and a few other small sites are hosted on a small VPS. Historically, I’ve been using MySQL as my database server. I recently did a system upgrade and my configuration files were replaced which resulted in MySQL frequently being killed by OOMKiller. After fixing this problem, I wanted to explore using SQLite as my production database. With only a single server, and a small amount of data (under 100MB) SQLite could make operating my sites simpler as there is one fewer component to manage.

Converting the database

The first step of changing out my database would be to get a dump from MySQL and then massage it to be compatible with SQLite. While both MySQL and SQLite are ‘SQL databases’ they support different dialects of SQL and have different schema types and quoting rules.

The first step I did was get a dump of the current database and copy it to my local machine with scp. Then I performed a set of search and replace operations on the dump file. The things I needed to change were:

1. Remove all the LOCK statements. MySQL dumps include LOCK statements that are not valid in SQLite, so I deleted them all.
2. Remove all the ENGINE and COLLATE options on tables. I don’t use any exotic collations in my applications, so removing these was not loss. While SQLite doesn’t have table collation, it does have collation on TEXT columns, but the collation options are much more limited than in MySQL.
3. All the KEY clauses of CREATE TABLE statements, had to become CREATE INDEX statements that are run after the table was created.
4. Update integer primary keys to be INTEGER PRIMARY KEY AUTOINCRMENT to retain behavior with MySQL’s AUTO_INCREMENT option. SQLite support one and only one way to define integer primary keys, which is different than MySQL’s. Notably, there is no such thing as a BIGINTEGER in SQLite.
5. Update tinyint(1) to boolean. While MySQL supports boolean when creating a table, under the hood MySQL uses tinyint(1) for booleans, which is then output in schema dumps.
6. Update all the string literal values. – MySQL escapes quotes in strings with \' where as SQLite wants to use ''. – MySQL includes literal \n and \r\n in dump files. These are converted into the correct bytes when the strings are inserted. With SQLite, the literal values are preserved as text, and you have to use string concatenation expresions like 'foo' || X'0A' || 'bar' to include a newline in a string. To preserve \r\n I used X'0D0A'.

The application databases I did this process with are fairly simple and small, and additional steps may be required for a larger, more complex database.

Testing the conversion

With the conversion process complete, I tested locally by importing my converted dump file with:

Once I was able to get an import to succeed, I validated the application’s behavior by running their test suites. Thankfully, I wasn’t using many functions/features that were are only available in MySQL, but from other work I’ve done with SQLite date & time functions are totally different, and more limited in SQLite. If you make use of date/time functions, you’ll need to rework that logic into the application coe as the behavior and functionality offered by the databases is quite different.

Deploying SQLite

With my database converted, and application logic tested against SQLite, it was time to deploy the new database. Getting SQLite to work on my server required me to make a few changes:

1. Change ownership and file permissions so that db is writable by the webserver. chmod g+wx app.sqlite I had to make the database file owned by the www-data user + group so that my webserver user could write to the file. PHP’s SQLite bindings will raise an exception when the database isn’t writable, making this easy to diagnose and fix.
2. If you have cron jobs or background jobs that need access to the database, those processes would also have to run as the user that has ownership of the database.
3. Enable journal mode and foreign keys PRAGMA journal_mode=WAL, PRAGMA foreign_keys=ON. This is critical if you want concurrent reads & writes to be enabled, and foreign keys to be enforced.

I also placed my SQLite databases in a directory outside of the webserver root, and had application code reference the database via an absolute path. For example, this site is deployed into /opt/markstory/$timestamp, and symlinked to /opt/markstory/current. The webserver root directory is set to /opt/markstory/current/webroot, and I’ve placed my SQLite database in /opt/markstory/shared. This directory structure ensures that the database file cannot be accessed via the URL.

Operating SQLite

Having operated this SQLite setup for a few months now, I’m quite happy with it. SQLite may not be a good fit if your application needs more complex data types, like decimals, big integers, and datetimes with timezones. SQLite is not a great solution if you need more than a single server. I’ve also found that in a high-write volume scenarios, the single process / thread write lock will eventually limit throughput on write heavy workloads.

For the use case of small sites with simple requirements, SQLite provides fantastic performance and simple maintenance. I don’t have to worry about maintaining a database server anymore. Backups are very simple as there are only a few files that need to be backed up. I’ll certainly consider using SQLite again in future projects that fit its capabilities.