The Rails database.yml File

Andrei Maxim — Sun, 09 Jul 2023 13:12:37 +0000

I was browsing through the "The Rails 7 Way" and I read the following paragraph:

An old best practice within the Rails community has been not to store config/database.yml in version control. Foremost, if a hacker gained access to the application repository, they would have all the connection settings to your production database. Furthermore, developers on the team might have a different development and test database settings. This allows each developer working on the project to have their own copy of config/database.yml that is not stored in version control.

This is definitely correct: most of the Rails developers I've worked with in the last 15 years thought it was a best practice to not store config/database.yml in the version control system. But is it still a best practice for modern Rails applications?

Let's explore.

A bit of history

First, let's see how the file looked like in the initial version of Rails. This is a screenshot taken from DHH's now famous 15 minute blog video from 2005:

The initial database.yml was very simple (most likely on purpose) and basically contained just this:



development:
  adapter: mysql
  database: blog_development
  host: localhost
  username: root
  password:

test:
  adapter: mysql
  database: blog_test
  host: localhost
  username: root
  password:

production:
  adapter: mysql
  database: blog_production
  host: localhost
  username: root
  password:

However, people quickly noticed that there is a lot of repetition so it became common to use YAML merge keys to remove duplication, so the above database.yml would be converted to this:



defaults: &defaults
  adapter: mysql
  username: root
  password:
  host: localhost

development:
  <<: *defaults
  database: blog_development

test:
  <<: *defaults
  database: blog_test

production:
  <<: *defaults
  database: blog_production

This approach became popular and it was included in the "Rails Recipes" book by Chad Fowler. I think David tries to do something similar in the 15 minute video (at 3:09), but messes up the YAML syntax.

Now, the obvious issue is that you need a username and a password to connect to a database for development, but not all developers use the root database account and it was a significant security issue to share the production database password via source control.

In order to tackle this problem, developers generally opted for one of the following approaches:

check in a config/database.yml.example file and configure the version control software to ignore config/database.yml
check in the config/database.yml file, but use Erb to fill values like username or password from environment variables.

In both cases, the differences were minimal since most teams had some sort of automation in place (like using the bin/setup script). However, I've seen a preference for the first approach because:

Developers couldn't accidentally commit credentials to the config/database.yml since it was ignored
Capistrano had built-in support for symlink-ing the database configuration after deployment (the linked_files variable documentation specifically mentions database.yml) which would fail if there was a file already present

Changes in the last decade

The issue of storing secrets like passwords was not limited to databases as more and more applications started integrating with third-party services like Amazon S3.

Storing credentials required several iterations:

Rails 4.1 added support for config/secrets.yml, as a unified interface for interacting with secrets
Based on the work done on the sekrets gem, Rails 5.1 introduced encrypted secrets, so you could check in an encrypted config/secrets.yml.enc with all the required secrets and use a master key to decode it
Rails 5.2 switched to credentials and deprecated config/secrets.yml.enc
Rails 6.0 added support for encrypted credentials for multiple environments

Since the credentials are loaded before the database.yml file is read, we could revisit the defaults block so it uses the encrypted credentials:



defaults: &defaults
  adapter: mysql
  username: root
  password: <%= Rails.application.credentials.database_password %>
  host: localhost

However, since Rails 4.1 there has been support for the DATABASE_URL environment variable which allowed you to override the config/database.yml settings.

This is useful for platforms like Heroku because now they can provide a DATABASE_URL environment variable with the entire connection path and override the database name (for example) in you config/database.yml (previously Heroku's approach was to write a new file, which can cause other issues).

Improving the defaults

Ruby on Rails has been around for almost two decades and many defaults changed quite a lot, including the database settings, in order to make the developer experience great out of the box.

I'd argue we can trade a bit of convenience when it comes to the initial setup for the ability to allow colleagues to run the database however they see fit since some might not be comfortable with a passwordless MySQL root account and some might not want the extra hassle of using a password.

MySQL

If you create a new Rails 7.0 application, the default config/database.yml looks like this, assuming you have selected MySQL as the database:



# MySQL. Versions 5.5.8 and up are supported.
#
# Install the MySQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem "mysql2"
#
# And be sure to use new-style password hashing:
#   https://dev.mysql.com/doc/refman/5.7/en/password-hashing.html
#
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /var/run/mysqld/mysqld.sock

development:
  <<: *default
  database: blog_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: blog_test

# As with config/credentials.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password or a full connection URL as an environment
# variable when you boot the app. For example:
#
#   DATABASE_URL="mysql2://myuser:mypass@localhost/somedatabase"
#
# If the connection URL is provided in the special DATABASE_URL environment
# variable, Rails will automatically merge its configuration values on top of
# the values provided in this file. Alternatively, you can specify a connection
# URL environment variable explicitly:
#
#   production:
#     url: <%= ENV["MY_APP_DATABASE_URL"] %>
#
# Read https://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full overview on how database connection configuration can be specified.
#
production:
  <<: *default
  database: blog_production
  username: blog
  password: <%= ENV["BLOG_DATABASE_PASSWORD"] %>

Here are some thoughts:

We don't need the first eleven lines of comments after the initial application setup since the mysql2 gem will be added to the Gemfile
We can move the username, password and socket settings to the default ~/.my.cnf configuration file, allowing developers to set them up as they see fit
In most cases, we'll define a DATABASE_URL in the production environment, overriding whatever settings we have in our database configuration file, so the username and password section can be removed, keeping the comments so people looking for the credentials will know what to look for

Here's the slightly better config/database.yml:



default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  default_file: ~/.my.cnf

development:
  <<: *default
  database: blog_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: blog_test

# As with config/credentials.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password or a full connection URL as an environment
# variable when you boot the app. For example:
#
#   DATABASE_URL="mysql2://myuser:mypass@localhost/somedatabase"
#
# If the connection URL is provided in the special DATABASE_URL environment
# variable, Rails will automatically merge its configuration values on top of
# the values provided in this file. Alternatively, you can specify a connection
# URL environment variable explicitly:
#
#   production:
#     url: <%= ENV["MY_APP_DATABASE_URL"] %>
#
# Read https://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full overview on how database connection configuration can be specified.
#
production:
  <<: *default
  database: blog_production

Here's a sample ~/.my.cnf file:



[client]
user = andrei
password = AMERCE_sewage9borsch
socket = /var/run/mysqld/mysqld.sock

PostgreSQL

The Rails generated config/database.yml for a PostgreSQL database looks like this:



# PostgreSQL. Versions 9.3 and up are supported.
#
# Install the pg driver:
#   gem install pg
# On macOS with Homebrew:
#   gem install pg -- --with-pg-config=/usr/local/bin/pg_config
# On macOS with MacPorts:
#   gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
#   gem install pg
#       Choose the win32 build.
#       Install PostgreSQL and put its /bin directory on your path.
#
# Configure Using Gemfile
# gem "pg"
#
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 } %>

development:
  <<: *default
  database: blog_development

  # The specified database role being used to connect to postgres.
  # To create additional roles in postgres see `$ createuser --help`.
  # When left blank, postgres will use the default role. This is
  # the same name as the operating system user running Rails.
  #username: blog

  # The password associated with the postgres role (username).
  #password:

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  #host: localhost

  # The TCP port the server listens on. Defaults to 5432.
  # If your server runs on a different port number, change accordingly.
  #port: 5432

  # Schema search path. The server defaults to $user,public
  #schema_search_path: myapp,sharedapp,public

  # Minimum log levels, in increasing order:
  #   debug5, debug4, debug3, debug2, debug1,
  #   log, notice, warning, error, fatal, and panic
  # Defaults to warning.
  #min_messages: notice

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: blog_test

# As with config/credentials.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password or a full connection URL as an environment
# variable when you boot the app. For example:
#
#   DATABASE_URL="postgres://myuser:mypass@localhost/somedatabase"
#
# If the connection URL is provided in the special DATABASE_URL environment
# variable, Rails will automatically merge its configuration values on top of
# the values provided in this file. Alternatively, you can specify a connection
# URL environment variable explicitly:
#
#   production:
#     url: <%= ENV["MY_APP_DATABASE_URL"] %>
#
# Read https://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full overview on how database connection configuration can be specified.
#
production:
  <<: *default
  database: blog_production
  username: blog
  password: <%= ENV["blog_DATABASE_PASSWORD"] %>

There are slightly more comments than the default MySQL version, but this is most likely because the out-of-the-box setup with PostgreSQL and Rails might not work.

The first thing you need to do is create a superuser account that matches your system username, so you can create and destroy databases (there are more granular permissions, but we're talking about a development machine, so...):



create user andrei superuser login password 'accursed5ground_BACILLI';

Just replace andrei with your account name.

Then, create a ~/.pgpass file that stores the password. Here's an example:



localhost:*:*:andrei:accursed5ground_BACILLI

Just keep in mind that the .pgpass file does not behave the same as the ~/.my.cnf file, meaning that connecting to the localhost will not force the username and password, but instead it will provide the password when the connection host, port, database and usename match a line.

Then, we can apply the same treatment to the config/database.yml file as we did before, however we don't have to specify a default_file since Postgres uses the system account and picks up the .pgpass file automatically:



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 } %>

development:
  <<: *default
  database: blog_development


# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: blog_test

# As with config/credentials.yml, you never want to store sensitive information,
# like your database password, in your source code. If your source code is
# ever seen by anyone, they now have access to your database.
#
# Instead, provide the password or a full connection URL as an environment
# variable when you boot the app. For example:
#
#   DATABASE_URL="postgres://myuser:mypass@localhost/somedatabase"
#
# If the connection URL is provided in the special DATABASE_URL environment
# variable, Rails will automatically merge its configuration values on top of
# the values provided in this file. Alternatively, you can specify a connection
# URL environment variable explicitly:
#
#   production:
#     url: <%= ENV["MY_APP_DATABASE_URL"] %>
#
# Read https://guides.rubyonrails.org/configuring.html#configuring-a-database
# for a full overview on how database connection configuration can be specified.
#
production:
  <<: *default
  database: blog_production