Backup SQLite from a Containerized Rails App Deployed with Kamal

Since Kamal 2 was released, I've been using it to host a few applications and websites for my clients (yes - Rails is fantastic and perfectly fine for plain old marketing websites) on multiple Linux servers.

Most of them use SQLite as database, and Rails + Hotwire + SQLite combo has been a fantastic alternative against the traditional WordPress + MySQL (or even static) sites one might use for a simple marketing site that needs some lightweight CMS functionality, which I'm always happy to hand-code in Ruby.

Anyway, when deploying Rails applications that use SQLite with Kamal, the SQLite database lives inside a Docker container on my production Linux server (although mapped to a persistent storage volume via Kamal). For the first few months, I just used to run a few commands to take the backup of the SQLite file - but recently I 'automated' it with first bash script and then Ruby + Rake, and thought I'd share the solution, in case others might find it useful, and also as a reference for the future me.

First, the manual way...

Here're the steps you might follow to backup the SQLite database running in a container:

1. Connect to the container running on the remote server
2. Access the database file inside the container
3. Create a backup while the database is in use (in the container)
4. Copy the backup from the container to the host
5. Transfer it to our local machine

If you just want to get the backup and be done with it, here are the commands you'd run for the above steps:

# Connect to the Server
ssh user@server-ip

# Get the container ID
CONTAINER_ID=$(docker ps --filter name=your-app-web --format '{{.ID}}')

# Create backup inside container
docker exec $CONTAINER_ID sqlite3 /rails/storage/production.sqlite3 ".backup /tmp/backup.sqlite3"

# Copy from container to host
docker cp $CONTAINER_ID:/tmp/backup.sqlite3 /root/backup.sqlite3

# Copy from host to local machine (run it on local machine)
scp root@your-server:/root/backup.sqlite3 ./backup.sqlite3

Let's try to understand what each of the above command is doing:

• Get the container ID

CONTAINER_ID=$(docker ps --filter name=your-app-web --format '{{.ID}}')

First, we'll get the ID of the container for our web application by running the docker ps command which lists all running Docker containers and filtering them by the container name. Then, we format the output to only display the container ID. This container ID is then stored in the CONTAINER_ID variable.

• Create backup inside container

docker exec $CONTAINER_ID sqlite3 /rails/storage/production.sqlite3 ".backup /tmp/backup.sqlite3"

This command runs an SQLite backup operation inside our container, using the docker exec command. This backup is stored at /tmp/backup.sqlite3, inside the container.

• Copy from container to host

docker cp $CONTAINER_ID:/tmp/backup.sqlite3 /root/backup.sqlite3

Next, we copy the backup from the running Docker container to the host server (not your local computer yet! we'll do that in the next step). The docker cp command is used to copy files between the container and the host machine. The backup is stored at /root/backup.sqlite3 on the server.

• Copy from host to local machine

scp root@your-server:/root/backup.sqlite3 ./backup.sqlite3

Finally, we copy the backup file from the remote server to the local machine using SCP command (Secure Copy Protocol). The backup will be copied to the current working directory on your local computer.

At this step, I hope you were able to generate the backup successfully. However, you don't backup the database once and are done with it. You have to take the backup frequently. It'd be nice to have a simple script that you could run (or even better, a CRON job would run) at periodic intervals.

So, let's scriptify our commands.

Automating with a Shell Script

Let's create a shell script that handles the entire backup process. Create a bash script named /backup-db.sh under the /bin directory in your Rails project. Don't forget to make this script executable:

$ touch bin/backup-db.sh
$ chmod +x bin/backup-db.sh

Here's the backup script you can copy + paste (replace the placeholders with your environment values). I've added some enhancements like logging and saving the backup file with a timestamp value, so it's easier to keep track of the database files. The script also cleans up the backups taken before 30 days + the temporary backups generated on the server, to keep everything neat and tidy.

When you need to take the backup, you can run the backup script as follows:

$ bin/backup-db.sh

After I wrote the script (with some help from Cursor, which is just fantastic with Shell commands), I decided to go one step further and convert the above shell script into a Ruby program, to make it more readable and maintainable. Having them in Ruby code will also let us run the script via a Rake task.

Thinking now, I should have started with the Ruby program, but writing the Shell script was a nice exercise to workout my Shell muscles, with help from AI.

Converting Shell to Ruby Script

Let's create a Ruby class under the /lib folder. Why under /lib? Because taking backup is something not related to your core application - and the lib directory is perfect for such utility programs.

Finally, I added a Rake task so I could connect the database backup step with other tasks in a pipeline, such as first taking a backup and then restoring the development database with the production data.

# lib/tasks/backup.rake
namespace :db do
  desc 'Backup production database from remote server'
  task backup: :environment do
    DatabaseBackup.run
  end
end

Now you can run backup as follows:

# Take a backup
bin/rails db:backup

You can also write a CRON job that will take the backups periodically, but I'll leave that as an exercise for the reader.

That's a wrap. I hope you found this article helpful and you learned how to take SQLite backups from a containerized Rails app.

As always, if you have any questions or feedback, didn't understand something, or found a mistake, please leave a comment below or send me an email. I reply to all emails I get from developers, and I look forward to hearing from you.

If you'd like to receive future articles directly in your email, please subscribe to my blog. Your email is respected, never shared, rented, sold or spammed. If you're already a subscriber, thank you.