The Rails database.yml File
Tags: ruby, rails, database
Published: 2023-07-09 13:12:37

I was browsing through the ["The Rails 7 Way"](https://leanpub.com/therails7way) 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](https://www.youtube.com/watch?v=Gzj723LkRJY):

![The original database.yml](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pnnr044hg7b8g6cnwiva.png)

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

```yaml
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:

```yaml
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](https://www.amazon.com/Rails-Recipes-Pragmatic-Programmers-Fowler/dp/0977616606). I think David tries to do something similar in the 15 minute video (at [3:09](https://youtu.be/Gzj723LkRJY?t=189)), 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](https://github.com/rails/rails/pull/15189)). 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`](https://capistranorb.com/documentation/getting-started/configuration/)) 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`](https://guides.rubyonrails.org/4_1_release_notes.html#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](https://guides.rubyonrails.org/5_1_release_notes.html#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](https://github.com/rails/rails/pull/30067) and deprecated `config/secrets.yml.enc`
* Rails 6.0 added support for [encrypted credentials for multiple environments](https://github.com/rails/rails/pull/33521)

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

```yaml
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](https://github.com/rails/rails/pull/13582). 

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:

```yaml
# 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`:

```yaml
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:

```yaml
# 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...):

```sql
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:

```yaml
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
```

And that's about it!