Originally published on my blog: Streamlining Your Rails 8 App: Migrating from Postgres to SQLite

With Rails 8's shift to SQLite as the new default database, developers now have the opportunity to simplify their application stack while retaining power and flexibility.

SQLite's recent improvements for Rails make it a great option for production use. After all SQLite, which was initially released in 2004, has come a long way and is now a powerful, reliable, and performant database engine – perfect for many applications. With the Rails 8 release, SQLite has become the default database for new Rails applications, and comes with a host of performance tuning by default.

In this guide, I describe how to migrate an existing Rails 8 app from Postgres to SQLite to supercharge your application, while at the same time compressing your stack. Keep in mind that the process I describe below requires some downtime, so plan accordingly.

In the application I'm migrating, I'm using Rails 8 and Kamal for deployment. If you're using a different deployment tool, the steps might be different.

Create a Backup of the Postgres Database

Before migrating your Rails application from Postgres to SQLite, it's essential to create a backup of your Postgres database. This backup will ensure that you have a copy of your data in case anything goes wrong during the migration process.

In my case, Postgres is running in a Docker container. First, let's access the Postgres container using the following command:

export POSTGRES_CONTAINER_ID=$(docker ps | grep postgres | awk '{print $1}' | head -n 1)
docker exec -it $POSTGRES_CONTAINER_ID bash

Next, create a backup of the Postgres database using the pg_dump command:

pg_dump -U postgres -d myapp_production -f database.dump

This command will create a dump of the Postgres database in the file database.dump. Now, exit the Docker container. You can copy this file to the host machine using the following command:

docker cp $POSTGRES_CONTAINER_ID:/database.dump ./database.dump

To also download the dump file from the server to your local machine, you can use the scp command:

scp user@server:/path/to/database.dump ~/Downloads

Now, you have a backup of your Postgres database. You can use this backup to restore the data if the migration process fails.

Update the Rails Configuration

Ensure your application has been upgraded to Rails 8 before continuing. In order to point your Rails application to the SQLite database, you need to update the database configuration in the config/database.yml file. This is how the configuration used to look like with Postgres:

default: &default
  adapter: postgresql
  encoding: unicode
  port: 5432
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  host: localhost
  database: myapp_development
  user: postgres
  password: password

test:
  <<: *default
  host: localhost
  database: myapp_test
  user: postgres
  password: password

production:
  <<: *default
  host: <%= ENV.fetch("POSTGRES_HOST") { "host" } %>
  database: myapp_production
  user: <%= ENV.fetch("POSTGRES_USER") { "user" } %>
  password: <%= ENV.fetch("POSTGRES_PASSWORD") { "password" } %>

And with SQLite, the configuration will look like this:

default: &default
  adapter: sqlite3
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  <<: *default
  database: storage/development.sqlite3

test:
  <<: *default
  database: storage/test.sqlite3

production:
  primary:
    <<: *default
    database: storage/production.sqlite3

Deploy with the New Configuration

Now, we can deploy the updated Rails application. In the app I'm migrating, I'm using Kamal. If you're using something else, be sure to re-deploy your application so the new configuration is loaded in your application. When starting for the first time, the Rails Docker entrypoint will create the production SQLite database and prepare it for use (via bin/rails db:prepare, which will set up and migrate the database).

kamal deploy -d production

Verify on the server that the SQLite database has been created and the application is running as expected.

docker exec -it $RAILS_CONTAINER_ID ls storage

This should list the SQLite database file named production.sqlite3 – if it doesn't, there might be an issue with the deployment. Check the logs to identify the problem.

kamal app logs -d production

As soon as you can see the SQLite database file, you can proceed with the next steps.

Import the Postgres Data into SQLite

Now that the Rails application is running with the (empty) SQLite database, we can import the data from Postgres. Unfortunately, there is no direct way to import a Postgres dump into SQLite.

In my research I discovered tools like pg2sqlite, but they fail to correctly prepare the SQLite database with the required column data types and constraints. It's best to start from a "clean slate" and import the data separately after Rails has set up the SQLite database with the proper schema.

One option is to create a one-off migration script to connect to both the Postgres and SQLite databases and transfer the data over to the newly created SQLite database.

Below is an example script to transfer the data. You might place this file in script/migrate_data.rb. For simple applications, this might be enough. For more complex scenarios, you might need to add additional logic to handle data transformations or relationships. Run this script while inside the container via bundle exec ruby script/migrate_data.rb

require "active_record"
require "pg"
require "sqlite3"

# PostgreSQL database configuration
postgres_config = {
  adapter: "postgresql",
  host: ENV["POSTGRES_HOST"],
  username: ENV["POSTGRES_USER"],
  password: ENV["POSTGRES_PASSWORD"],
  database: "myapp_production"
}

# SQLite database configuration
sqlite_config = {
  adapter: "sqlite3",
  database: "storage/production.sqlite3"
}

# Establish connections
ActiveRecord::Base.establish_connection(postgres_config)
postgres_connection = ActiveRecord::Base.connection
ActiveRecord::Base.establish_connection(sqlite_config)
sqlite_conn = ActiveRecord::Base.connection

# Define the order of tables to migrate
tables_to_migrate = [
  "posts",
  "comments",
  # Add all tables in the correct order (due to foreign key constraints)
]

tables_to_migrate.each do |table_name|
  puts "Migrating table: #{table_name}"

  # Fetch data from PostgreSQL
  data = postgres_connection.select_all("SELECT * FROM #{table_name}")

  # Insert data into SQLite
  data.rows.each do |row|
    attributes = data.columns.zip(row).to_h
    begin
      sqlite_conn.insert_fixture(attributes, table_name)
    rescue => e
      puts "Error inserting into #{table_name}: #{e.message}"
    end
  end

  puts "Table #{table_name} migrated successfully."
end

puts "Migration completed successfully!"

To run the import script again (e.g., after making changes), you can nuke the SQLite database and run the script again:

docker exec -it $RAILS_CONTAINER_ID bin/rails db:reset
docker exec -it $RAILS_CONTAINER_ID bin/rails runner script/migrate_data.rb

After importing the data, your Rails application should now be running as before with the original data from the Postgres database, but now from within SQLite! You can verify this by checking the data in the application or running queries against the SQLite database. Alternatively, download the SQLite database file to your local machine and inspect it using a SQLite database viewer.

To ensure your app is now talking to the SQLite database, you can stop the Postgres container and see if the Rails application is still running as expected.

docker stop $POSTGRES_CONTAINER_ID

Check the logs to confirm everything is working as expected. To tail the logs, you can using the following command:

kamal app logs -d production -f

Congrats! Your Rails application is now running on SQLite.

Adding Backups with Litestream

To ensure that your SQLite database is backed up regularly, you might rely on Litestream. Litestream is a tool that continuously backs up SQLite databases to cloud storage providers like AWS S3. For this, we can use the handy litestream-ruby gem. Read about how to install and configure it in the GitHub repository.

Stopping accessory and cleaning up

After you've confirmed that your Rails application is running smoothly with the SQLite database, you can stop the Postgres container and clean up the volumes. Here are the steps to do so:

docker stop $POSTGRES_CONTAINER_ID
docker rm $POSTGRES_CONTAINER_ID

To identify the volume name, you can use docker volume ls. Then, remove the volume using the following command:

docker volume rm <volume_name>

Alternatively, use Kamal to remove the Postgres accessory:

kamal accessory remove postgres -d production

Also, don't forget to...

• Remove the pg gem from your Gemfile
• Remove libpq-dev and other no longer needed Postgres-related dependencies from the Dockerfile
• Remove the Postgres acessory from the Kamal deploy.yml file, if applicable
• Remove any Postgres-related secrets from Kamal secrets, if applicable
• Remove the migration script and the Postgres dump file

Conclusion

By migrating your Rails 8 application from Postgres to SQLite, you’ve simplified your setup while maintaining performance and reliability. SQLite’s integration with Rails 8 offers a straightforward, efficient solution for many use cases.

With proper backup strategies in place, you can confidently rely on SQLite in production. Following the steps outlined in this guide, your Rails app is now running smoothly on SQLite.