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:
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.
# Current database configuration
If you have an empty
config/database.ymlfile but your
ENV['DATABASE_URL']is present, then Rails will connect to the database via your environment variable
If you have a
ENV['DATABASE_URL']then this file will be used to connect to your database:
If you have both
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:
Here the connection information in
ENV['DATABASE_URL'] is ignored, note the different adapter and database name.
(Recommended) Embed ENV[‘DATABASE_URL’] in config/database.yml
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).
Now the behavior is clear, that we are only using the connection information in
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:
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:
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.
If you choose to use PostgreSQL, your
config/database.yml will be customized to use PostgreSQL databases:
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:
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.
# The PostgreSQL adapter works with the native C (https://github.com/ged/ruby-pg) driver.
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:
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
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.
rails db:system:change --to=mysql
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
 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)