Using SQLite for a Django application on fly.io

I'd like to run a few small apps I've been developing for learning purposes (to do list, etc.) on a cheap or free platform as a service (PaaS) provider so I don't have to worry about a full deployment setup like I have on my main project. These projects aren't going to be used by many people (likely just me), and the data is not important. Since managed databases are usually a significant cost, I wanted a cheaper and simpler solution.

After reading about SQLite from fly.io, and seeing some other folks discussing using SQLite for real workloads like this talk from DjangoCon Europe and Simon Willison discussing it at DjangoCon US last year, I thought it would be interesting to use SQLite as a database for a few small apps. With that blog post from fly.io, I assumed it would be easy to get up and running, but it was...not.

I recognize that the fly.io solution for some of this is LiteFS, but for a small app, I'd rather stick with a simpler (and cheaper) solution. I do want to try that eventually and I'll write that up if I find something interesting. Here is a great write up on Litestream and LiteFS on fly if you want to start there.

Setting up the app server

The first issue I ran into is that fly.io defaults to spinning up two app servers, which is the right thing to do for a real production site to avoid downtime. But that means we can't use a single local SQLite file. I think there is a way to share permanent storage between two servers on fly, but the other factor is that I just don't need the redundancy (or complexity) of multiple servers for these apps.

If you already have an app deployed, you can run fly scale count 1 to scale down to a single machine. If you haven't deployed yet, you can use fly launch --ha=false to start with a single app machine. I would prefer if there were a way to add this setting to fly.toml, but I didn't see any way to do that.

Attached Volume Storage

Now that we have a single server, we need to create permanent storage so that we don't lose the database every time we deploy. Here is how you can do that if you have a new app, but remember to add --ha=false to the deploy step or you'll end up with two volumes and have to delete the volumes and app and start over. Since I already had the app set up I followed these instructions. I added the following to fly.toml:

[mounts]
  source="myapp_data"
  destination="/data"

And ran

fly volumes create myapp_data -s 1 -r xxx

where xxx is the region code. and -s 1 is to make the volume 1 GB instead of the default 3 GB. I'm doing this because my application data is going to be very small. You can decide if you'd prefer less or more storage. For some reason if you just add [mounts] to fly.toml and let deploy create the volume for you, it defaults to 1 GB. Also, if you think your app might grow beyond 1 GB, but it will start small, you can always extend your volume with the fly CLI.

Once you create the volume, you'll have to deploy again to get the volume to attach to your server.

Important clarification that I didn't understand immediately from the docs: "myapp_data" is the name of the volume/persistant storage created on fly.io. "/data" is where that volume will be mounted on your machine. That means if you run fly ssh console, you can navigate to your new volume by running cd /data. If you use different names for those settings, remember them for the future commands in this post and refer back here for which is which.

A note on redundancy: Snapshots of permanent volumes are taken once per day (not guaranteed to be at the same time each day) and kept for 5 days. That's good enough for me for these kinds of projects. You can restore a snapshot with the instructions here. If you want a little more backup, you can backup the file locally using fly's sftp command:

flyctl ssh sftp get /data/db.sqlite3 ./db_y_m_d.sqlite3

If you really want to get creative, you could write a cron job to run that command then ship the file to S3/whatever other blob storage. The Litestream docs have some good tips for that.

SQLite

Ok, we have persistant storage that we can access from our server. Now we have to make a few tweaks to use that storage with SQLite properly.

First, we need to tell Django to use our volume storage for the database. For this project I am using environs, so my database setting looks like the following in settings.py:

DATABASES = {
    "default": env.dj_db_url("DATABASE_URL", default="sqlite:///db.sqlite3"),
}

I'd recommend being able to test locally with a SQLite database, so we want to keep the default setting as is. We'll need to update the environment variable in fly by running fly secrets set DATABASE_URL=sqlite:////data/db.sqlite3.

Migrations

Great! One last set of updates. We need to set up the migrate step for our deployment.

If you try to run deploy now, you might get an error django.db.utils.OperationalError: unable to open database file because of this in fly.toml:

[deploy]
  release_command = "python manage.py migrate"

I had this in one project I created but not another. If this isn't in fly.toml, don't worry and create startup.sh as described in the next paragraph.

This runs when the machine does not have access to the persistent volume we created, so we need a way to run migrate later in the process. I removed the line release_command = "python manage.py migrate" in fly.toml and moved the migrate step into my Dockerfile. To do this, I created a shell script at the top level of my project folder (next to Dockerfile, fly.toml, etc.) called startup.sh:

#!/bin/sh
python manage.py migrate
sqlite3 /data/db.sqlite3 'PRAGMA journal_mode=WAL;'
sqlite3 /data/db.sqlite3 'PRAGMA synchronous=1;'
gunicorn --bind :8000 --workers 2 config.wsgi

Note: the last line assumes that wsgi.py is in a folder named 'config'. If you ran django-admin startproject with some other name, substitute that for config.

The sqlite3 lines are from the previously mentioned talk from DjangoCon Europe and require installing sqlite3 and running startup.sh in the Dockerfile:

RUN apt-get update && apt-get install -y \
    sqlite3 \
    && rm -rf /var/lib/apt/lists/*

RUN python manage.py collectstatic --noinput
RUN chmod +x startup.sh
EXPOSE 8000
ENTRYPOINT ["./startup.sh"]

Note: If you have a line in your Dockerfile like CMD ["gunicorn", "--bind", ":8000",...], you'll need to remove it because it is handled in the shell script.

That should be it. Now we can run fly deploy and our app should be using a SQLite file on our permanent volume storage and running migrate each time we deploy!

If you want to make sure everything is set up properly, add some data to your database and run fly deploy again. If you aren't using permanent storage, you're database will be reset. If you are, everything should still be there.

P.S. Thanks to billy for their help on the fly.io community forum for helping me fix a few of the issues I ran into trying to get this all working.