This content originally appeared on DEV Community and was authored by Camillo Visini
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.
Whether you are running a small personal project or scaling up, SQLite's improvements 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.
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 our 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. 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 application 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 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.
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
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
pggem from yourGemfile - Remove
libpq-devand other no longer needed Postgres-related dependencies from the Dockerfile - Remove the Postgres acessory from the Kamal
deploy.ymlfile, 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.
This migration allows you to reduce complexity in your stack, and 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.
This content originally appeared on DEV Community and was authored by Camillo Visini
Camillo Visini | Sciencx (2024-10-19T18:53:29+00:00) Streamlining Your Rails 8 App: Migrating from Postgres to SQLite. Retrieved from https://www.scien.cx/2024/10/19/streamlining-your-rails-8-app-migrating-from-postgres-to-sqlite/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.