[Ruby on Rails (RoR)] Datbase Configuration in Rails

Posted on Edited on In , , , Views: Word count in article: 11k Reading time ≈ 10 mins.

Database Configuration in Rails

Since there are two ways to configure your connection (using config/database.yml or using an environment variable ENV['DATABASE_URL']) it is important to understand how they can interact.

If you look at the options of the application generator, you will see that one of the options is named --database. This option allows you to choose an adapter from a list of the most used relational databases. You can even run the generator repeatedly:

1
$ cd .. && rails new blog --database=mysql

When you confirm the overwriting of the config/database.yml file, your application will be configured for MySQL instead of SQLite. Detailed examples of the common database connections are below.

1
2
3
4
5
6
7
8
9
10
11
# Current database configuration
> ActiveRecord::Base.connection_db_config

# DEPRECATION WARNING: connection_config is deprecated and will be removed from Rails 6.2 (Use connection_db_config instead)
# > ActiveRecord::Base.connection_config

# Current database configuration
> Rails.configuration.database_configuration[Rails.env]

# All database configuration
> Rails.configuration.database_configuration

Connection Preference

  • If you have an empty config/database.yml file but your ENV['DATABASE_URL'] is present, then Rails will connect to the database via your environment variable ENV['DATABASE_URL'].

  • If you have a config/database.yml but no ENV['DATABASE_URL'] then this file will be used to connect to your database:

  • If you have both config/database.yml and ENV['DATABASE_URL'] set then Rails will merge the configuration together. To better understand this we must see some examples.

    • When duplicate connection information is provided the environment variable will take precedence

    • If non-duplicate information is provided you will get all unique values, environment variable still takes precedence in cases of any conflicts.

The only way to explicitly not use the connection information in ENV['DATABASE_URL'] is to specify an explicit URL connection using the url sub key:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$ cat config/database.yml
development:
  url: sqlite3:NOT_my_database

$ echo $DATABASE_URL
postgresql://localhost/my_database

$ bin/rails runner 'puts ActiveRecord::Base.configurations'
#<ActiveRecord::DatabaseConfigurations:0x00007fd50e209a28>

$ bin/rails runner 'puts ActiveRecord::Base.configurations.inspect'
#<ActiveRecord::DatabaseConfigurations:0x00007fc8eab02880 @configurations=[
  #<ActiveRecord::DatabaseConfigurations::UrlConfig:0x00007fc8eab020b0
    @env_name="development", @spec_name="primary",
    @config={"adapter"=>"sqlite3", "database"=>"NOT_my_database"}
    @url="sqlite3:NOT_my_database">
  ]

Here the connection information in ENV['DATABASE_URL'] is ignored, note the different adapter and database name.

Since it is possible to embed ERB in your config/database.yml it is best practice to explicitly show you are using the ENV['DATABASE_URL'] to connect to your database. This is especially useful in production since you should not commit secrets like your database password into your source control (such as Git).

1
2
3
$ cat config/database.yml
production:
  url: <%= ENV['DATABASE_URL'] %>

Now the behavior is clear, that we are only using the connection information in ENV['DATABASE_URL'].

See 3.19 Connection Preference - https://guides.rubyonrails.org/configuring.html#connection-preference to learen more.

Connection Options

MySQL

If you choose to use MySQL or MariaDB instead of the shipped SQLite3 database, your config/database.yml will look a little different. Here’s the development section:

1
2
3
4
5
6
7
8
development:
  adapter: mysql2
  encoding: utf8mb4
  database: blog_development
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

If your development database has a root user with an empty password, this configuration should work for you. Otherwise, change the username and password in the development section as appropriate.

If your MySQL version is 5.5 or 5.6 and want to use the utf8mb4 character set by default, please configure your MySQL server to support the longer key prefix by enabling innodb_large_prefix system variable.

Advisory Locks are enabled by default on MySQL and are used to make database migrations concurrent safe. You can disable advisory locks by setting advisory_locks to false:

1
2
3
production:
  adapter: mysql2
  advisory_locks: false

You can see MySQL connection options from brianmario/mysql2: A modern, simple and very fast Mysql library for Ruby - binding to libmysql - https://github.com/brianmario/mysql2#connection-options.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Mysql2::Client.new(
  :host,
  :username,
  :password,
  :port,
  :database,
  :socket = '/path/to/mysql.sock',
  :flags = REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION | MULTI_STATEMENTS,
  :encoding = 'utf8',
  :read_timeout = seconds,
  :write_timeout = seconds,
  :connect_timeout = seconds,
  :connect_attrs = {:program_name => $PROGRAM_NAME, ...},
  :reconnect = true/false,
  :local_infile = true/false,
  :secure_auth = true/false,
  :ssl_mode = :disabled / :preferred / :required / :verify_ca / :verify_identity,
  :default_file = '/path/to/my.cfg',
  :default_group = 'my.cfg section',
  :default_auth = 'authentication_windows_client'
  :init_command => sql
  )

PostgreSQL

If you choose to use PostgreSQL, your config/database.yml will be customized to use PostgreSQL databases:

1
2
3
4
5
development:
  adapter: postgresql
  encoding: unicode
  database: blog_development
  pool: 5

By default Active Record uses database features like prepared statements and advisory locks. You might need to disable those features if you’re using an external connection pooler like PgBouncer:

1
2
3
4
5
6
7
8
9
10
11
12
production:
  adapter: postgresql
  prepared_statements: false
  advisory_locks: false
```  

If enabled, Active Record will create up to 1000 prepared statements per database connection by default. To modify this behavior you can set `statement_limit` to a different value:

```yaml
production:
  adapter: postgresql
  statement_limit: 200

The more prepared statements in use: the more memory your database will require. If your PostgreSQL database is hitting memory limits, try lowering statement_limit or disabling prepared statements.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# The PostgreSQL adapter works with the native C (https://github.com/ged/ruby-pg) driver.
#
# Options:
#
# * <tt>:host</tt> - Defaults to a Unix-domain socket in /tmp. On machines without Unix-domain sockets,
#   the default is to connect to localhost.
# * <tt>:port</tt> - Defaults to 5432.
# * <tt>:username</tt> - Defaults to be the same as the operating system name of the user running the application.
# * <tt>:password</tt> - Password to be used if the server demands password authentication.
# * <tt>:database</tt> - Defaults to be the same as the username.
# * <tt>:schema_search_path</tt> - An optional schema search path for the connection given
#   as a string of comma-separated schema names. This is backward-compatible with the <tt>:schema_order</tt> option.
# * <tt>:encoding</tt> - An optional client encoding that is used in a <tt>SET client_encoding TO
#   <encoding></tt> call on the connection.
# * <tt>:min_messages</tt> - An optional client min messages that is used in a
#   <tt>SET client_min_messages TO <min_messages></tt> call on the connection.
# * <tt>:variables</tt> - An optional hash of additional parameters that
#   will be used in <tt>SET SESSION key = val</tt> calls on the connection.
# * <tt>:insert_returning</tt> - An optional boolean to control the use of <tt>RETURNING</tt> for <tt>INSERT</tt> statements
#   defaults to true.
#
# Any further options are used as connection parameters to libpq. See
# https://www.postgresql.org/docs/current/static/libpq-connect.html for the
# list of parameters.
#
# In addition, default connection parameters of libpq can be set per environment variables.
# See https://www.postgresql.org/docs/current/static/libpq-envars.html .

Configuring an SQLite3 Database

Rails comes with built-in support for SQLite3, which is a lightweight serverless database application. While a busy production environment may overload SQLite, it works well for development and testing. Rails defaults to using an SQLite database when creating a new project, but you can always change it later.

Here’s the section of the default configuration file (config/database.yml) with connection information for the development environment:

1
2
3
4
5
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

Rails uses an SQLite3 database for data storage by default because it is a zero configuration database that just works. Rails also supports MySQL (including MariaDB) and PostgreSQL “out of the box”, and has plugins for many database systems. If you are using a database in a production environment Rails most likely has an adapter for it.

See Sample config/database.yml from Rails. Postgres, MySQL, and SQLite - https://gist.github.com/jwo/4512764 and [Rails Database yml examples for postgres sqlite and mysql - https://gist.github.com/datt/e12fa0da294e7a8f3ac96abee346a098](https://gist.github.com/datt/ e12fa0da294e7a8f3ac96abee346a098) to learn more exmaples.

Multi Database Support

See Multiple Databases with Active Record — Ruby on Rails Guides - https://guides.rubyonrails.org/active_record_multiple_databases.html
to learn more.

Change Database Support

Rails 6 provide a command has been added to change database adapter automatically.

Let’s say our app has started with SQLite and now we have to switch to MySQL.

1
2
3
4
5
6
$ rails db:system:change --to=mysql
    conflict  config/database.yml
Overwrite /Users/cloudolife/example_app/config/database.yml? (enter "h" for help) [Ynaqdhm] Y
       force  config/database.yml
        gsub  Gemfile
        gsub  Gemfile

Our database.yml is now changed to contain the configuration for MySQL database and the Gemfile also gets updated automatically with addition of mysql2 gem in place of sqlite3.

References

[1] Configuring Rails Applications — Ruby on Rails Guides - https://guides.rubyonrails.org/configuring.html#configuring-a-database(https://guides.rubyonrails.org/configuring.html#configuring-a-database)

[2] Getting Started with Rails — Ruby on Rails Guides - https://guides.rubyonrails.org/getting_started.html

[3] Configuring Rails Applications — Ruby on Rails Guides - https://guides.rubyonrails.org/configuring.html#database-pooling

[4] Multiple Databases with Active Record — Ruby on Rails Guides - https://guides.rubyonrails.org/active_record_multiple_databases.html

[5] Configuring and connecting to a database | BigBinary - https://www.bigbinary.com/learn-rubyonrails-book/configure-and-connect-to-database

[6] Sample config/database.yml from Rails. Postgres, MySQL, and SQLite - https://gist.github.com/jwo/4512764

[7] Rails Database yml examples for postgres sqlite and mysql - https://gist.github.com/datt/e12fa0da294e7a8f3ac96abee346a098

[8] Rails 6 has added a way to change the database of the app | BigBinary Blog - https://www.bigbinary.com/blog/rails-6-has-added-a-way-to-change-the-database-of-the-app

[9] Add rails db:system:change command by gmcgibbon · Pull Request #34832 · rails/rails - https://github.com/rails/rails/pull/34832

[10] Part 2: Multi-db improvements, Refactor Active Record configurations by eileencodes · Pull Request #33637 · rails/rails - https://github.com/rails/rails/pull/33637

[11] rails/postgresql_adapter.rb at main · rails/rails - https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb

[12] ged/ruby-pg: A PostgreSQL client library for Ruby - https://github.com/ged/ruby-pg

[13] brianmario/mysql2: A modern, simple and very fast Mysql library for Ruby - binding to libmysql - https://github.com/brianmario/mysql2#connection-options

[14] brianmario/mysql2: A modern, simple and very fast Mysql library for Ruby - binding to libmysql - https://github.com/brianmario/mysql2

[15] sparklemotion/sqlite3-ruby: Ruby bindings for the SQLite3 embedded database - https://github.com/sparklemotion/sqlite3-ruby

[16] SQLite Home Page - https://www.sqlite.org/index.html