Effortless SQLite Backups for Rails with Litestream

SQLite has experienced a remarkable renaissance in recent years, particularly in the Rails community. Since Rails 8, it is the default database for newly generated Rails applications. With its simplicity, performance, and zero-configuration setup, SQLite has become an attractive choice for many applications that don’t require the complexity of a dedicated database server. However, one challenge that developers face when using SQLite in production is implementing robust backup strategies.

Enter Litestream – a standalone streaming replication tool for SQLite that continuously backs up your database to cloud storage services like Amazon S3. Unlike traditional backup solutions that create periodic snapshots, Litestream provides real-time streaming replication, ensuring that your data is continuously protected with minimal overhead.

This guide will walk you through setting up Litestream for your Rails application, configuring Amazon S3 as your backup destination, and implementing best practices for SQLite backup and recovery.

Setting Up Litestream with Rails

To get started with replicating your SQLite database to Amazon S3, you’ll need to configure both your AWS environment and your Rails application. This chapter will guide you through creating a S3 bucket, setting up an IAM user with the necessary permissions, and configuring Litestream within your Rails app to enable continuous streaming of database changes. By the end, you’ll have a robust setup that ensures your data is securely backed up to S3.

AWS Setup

First, you need to create a new S3 bucket in your AWS account. We’ll use AWS CLI for creating the necessary resources but you can also do it using the AWS Console. In this example, the bucket will be called “my-app-database-backup” and is created in the AWS region “North Virginia” (us-east-1).

aws s3api create-bucket --bucket my-app-database-backups --region us-east-1

Next, we need to create an IAM User that can authorize to the AWS account. This step is only necessary if you do use any AWS services in your application yet. For this user, we are also creating AWS Access Credentials:

aws iam create-user --user-name my-app
aws iam create-access-key --user-name my-app

This will output the AccessKeyId and SecretAccessKey. These credentials need to be added to the Rails secrets. For your Rails application, run rails credentials:edit to add the access credentials:

aws:
  access_key_id: <AWS_ACCESS_KEY_ID>
  secret_access_key: <AWS_SECRET_ACCESS_KEY>

Finally, we need to grant permissions to the user so that he can manage files in the S3 bucket. First, create a policy document (e.g., s3-backup-policy.json) with the following content:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListBucket"
            ],
            "Resource": "arn:aws:s3:::my-app-database-backups"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::my-app-database-backups/*"
        }
    ]
}

Next, attach the policy to the user:

aws iam put-user-policy \
    --user-name my-app \
    --policy-name s3-backup-policy \
    --policy-document file://s3-backup-policy.json

Now, we have everything in place on AWS to store the backups from our application.

If you are using Terraform for managing your infrastructure on AWS, you can use this template to set up all the resources.

Litestream Setup

Setting up Litestream in the Rails application is pretty straight-forward. First, the gem needs to be added and Litestream needs to be installed:

bundle add litestream
rails generate litestream:install

The install script generates the files config/litestream.yml and config/initializers/litestream.rb. Both of the files might need to be adjusted.

Remove all databases from config/litestream.yml that you do not want to be backed up. For standard Rails 8 applications using SQLite, there are databases for solid_cable, solid_cache and solid_queue that contain temporary data that you probably do not want to back up and restore. You are primarily interested in backing up your application data. So Litestream should only back up this database. The config file will look something like this:

dbs:
  - path: storage/production.sqlite3
    replicas:
      - type: s3
        bucket: my-app-database-backups
        path: production.sqlite3
        region: us-east-1

In the initializer config/initializers/litestream.rb, Litestream must be configured to use the AWS Access Credentials stored in the Rails credentials for authenticating against AWS. The initializer should look something like this:

Rails.application.configure do
  # An example of using Rails encrypted credentials to configure Litestream.
  litestream_credentials = Rails.application.credentials.aws

  # Replica-specific authentication key.
  # Litestream needs authentication credentials to access your storage provider bucket.
  # In this example, we are using Rails encrypted credentials to store the access key ID.
  config.litestream.replica_key_id = litestream_credentials&.access_key_id

  # Replica-specific secret key.
  # Litestream needs authentication credentials to access your storage provider bucket.
  # In this example, we are using Rails encrypted credentials to store the secret access key.
  config.litestream.replica_access_key = litestream_credentials&.secret_access_key
end

Finally, Litestream must be started when the Rails application is running so that it can continously pick up changes to the application database and stream it to Amazon S3 for backup. For standard Rails applications using Puma as the application server, the easiest way to do this is to use the Puma Plugin. In config/puma.rb, add the plugin when running the application in production:

# Run litestream only in production.
plugin :litestream if ENV.fetch("RAILS_ENV", "production") == "production"

When you are running your application in production now, you will see log messages from Litestream in the logs telling you that your database is replicated.

Restore

Disasters can strike unexpectedly, but with Litestream and S3, recovering your SQLite database is straightforward. This chapter will walk you through the process of restoring your Rails application’s database from an S3 backup in the event of a failure. We’ll cover how to retrieve the replicated data and get your application back up and running with minimal downtime.

Follow this step-by-step process to restore the database for your application.

Before restoring the database, ensure your application is not running to prevent any new writes to the database during the process. This avoids potential data inconsistencies.

Next, use the litestream restore command to download and reconstruct your SQLite database from the latest backup stored in S3. For the backup set up before, the command is

bin/rails litestream:restore -- --database=storage/production.sqlite3

Once the restore is complete, start your Rails application again. Your app will now use the restored database.

With these steps, you can confidently recover from unexpected failures and ensure your application’s data remains safe and available.

Add these instructions to the Operations Playbook for the application and regularly test it to make sure you know what to do in case you need to restore the data.

Summary

The combination of Rails and SQLite with Litestream offers a compelling stack for applications that value simplicity, performance, and cost-effectiveness. By following the practices outlined in this guide, you can confidently deploy SQLite-backed Rails applications to production with robust backup and recovery capabilities.

Start small, monitor carefully, and enjoy the simplicity of SQLite without sacrificing data durability. Your future self will thank you when you need to recover from that inevitable hardware failure or human error.