Switching the db type in a Rails app

The Ruby on Rails framework defaults to using sqlite databases for your various production, development, and test databases. This is great for simplicity, but eventually you may need to switch.

The database Rails files

In a Ruby on Rails project, the relevant files for the database are as follows:

• Dockerfile
• Gemfile
• config/database.yml
• .github/workflows/ci.yml (optional)

Postgres as our example

I’ll use Postgres as the example to switch to and show what’s different between the files when using sqlite vs Postgres. At the end, I’ll show you the built-in method to change the database type–but there’s some caveats.

Dockerfile

Here’s what you’ll find in the Dockerfile using sqlite vs Postgres.

First, using apt it installs sqlite3. With Postgres, this is changed to installing postgresql-client.

# Install base packages
RUN apt-get update -qq && \

apt-get install --no-install-recommends -y curl libjemalloc2 libvips sqlite3 && \

rm -rf /var/lib/apt/lists /var/cache/apt/archives

This next snippet installs various things with app, but doesn’t include libpq-dev, which is the development library for PostgreSQL.

# Install packages needed to build gems
RUN apt-get update -qq && \

apt-get install --no-install-recommends -y build-essential git libyaml-dev pkg-config && \

rm -rf /var/lib/apt/lists /var/cache/apt/archives

Gemfile

The Gemfile includes sqlite3 instead of pg.

# Use sqlite3 as the database for Active Record
gem "sqlite3", ">= 2.1"

Installing Postgres would look something like this in the Gemfile.

gem "pg", "~> 1.1"

Database.yml

The database.yml is a big one for the differences. This is where all the databases are configured.

With sqlite, the default settings look like this:

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

With Postgres, it would look like this:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

With sqlite, each database config looks like this:

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

With Postgres, it changes to this:

production:
  primary: &primary_production
    <<: *default
    database: my_app_production
    username: my_app
    password: <%= ENV["MY_APP_DATABASE_PASSWORD"] %>

CI.yml

Lastly, the CI.yml doesn’t include any alterations to sqlite settings (there are none), only additions when using Postgres.

The Postgres version adds this in the services section:

    services:
      postgres:
        image: postgres
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        ports:
          - 5432:5432
        options: --health-cmd="pg_isready" --health-interval=10s --health-timeout=5s --health-retries=3

The run step for Postgres includes the libpq-dev package mentioned before:

run: sudo apt-get update && sudo apt-get install --no-install-recommends -y build-essential git libpq-dev libyaml-dev pkg-config google-chrome-stable

Finally, the run tests section for Postgres adds this DATABASE_URL variable:

      - name: Run tests
        env:
          RAILS_ENV: test
          DATABASE_URL: postgres://postgres:postgres@localhost:5432
          # REDIS_URL: redis://localhost:6379/0
        run: bin/rails db:test:prepare test test:system

The built-in method

Rails includes a method to change the app’s database, as described from the documentation.

That’s done by using the following command:

rails db:system:change --to=postgresql

This overwrites database.yml with what the defaults are for when using Postgres, switches sqlite3 out of the Gemfile for pg, and adds libpq-dev to Dockerfile.

I noticed, however, that postgresql-client is not added into Dockerfile like it should be with this method.

This method also does not change the CI.yml file at all.