Database Setup

Before starting with this chapter, make sure your database server is up and running. Ruby on Rails recommends to create three databases – a database each for development, testing, and production environment. According to convention, their names should be −

• library_development
• library_production
• library_test

You should initialize all three of them and create a user and password for them with full read and write privileges.

We shall learn how to set up MySQL and PostgreSQL databases for use with your Rails app.

Install MySQL/PostgreSQL

On Ubuntu (or inside WSL on Windows), install MySQL with following command −

sudo apt install mysql-server mysql-client

This installs the MySQL database server, and the client for interacting with MySQL.

After installation, start and enable the MySQL service −

sudo systemctl start mysql
sudo systemctl enable mysql

To install PostgreSQL, use the command −

sudo apt install postgresql postgresql-contrib

This installs PostgreSQL along with additional contributed utilities. Start and enable the server with the following command:

Start PostgreSQL −

sudo systemctl start postgresql
sudo systemctl enable postgresql

For Windows, install MySQL by downloading the installer ((mysql-installer-community-8.0.41.0.msi)) from https://dev.mysql.com/downloads/installer/.

To install PostgreSQL, download and run the installer (postgresql-17.4-1-windows-x64.exe) from https://www.postgresql.org/download/windows/.

Database Setup for MySQL

you can manually create the databases inside a MySQL console, using the root user ID for our application.

mysql>createdatabase library_development;
Query OK,1row affected (0.01 sec)

mysql>grantallprivilegeson library_development.*to'root'@'localhost' identified by'password';
Query OK,0rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK,0rows affected (0.00 sec)

You can do the same thing for two more databases library_production and library_test.

When you create a new Rails application from the command line, you can specify the database type with -d option

rails new library -d mysql

Configuring database.yml

At this point, you need to let Rails know about the user name and password for the databases. You do this in the file database.yml, available in the library\config subdirectory of Rails Application you created. This file has live configuration sections for MySQL databases. In each of the sections you use, you need to change the username and password lines to reflect the permissions on the databases you’ve created.

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /var/run/mysqld/mysqld.sock
development:
  <<: *defaultdatabase: library_development
test:
  <<: *defaultdatabase: library_test
production:
  primary: &primary_production
&lt;&lt;: *defaultdatabase: library_production
username: library
password: &lt;%= ENV["LIBRARY_DATABASE_PASSWORD"]%&gt;
  cache:
&lt;&lt;: *primary_production
database: library_production_cache
migrations_paths: db/cache_migrate
  queue:
&lt;&lt;: *primary_production
database: library_production_queue
migrations_paths: db/queue_migrate
  cable:
&lt;&lt;: *primary_production
database: library_production_cable
migrations_paths: db/cable_migrate

Database Setup for PostgreSQL

If you want to use the PostgreSQL database for your library project, you can create the required databases with PgAdmin the popular PostgreSQL management tool.

Similarly, create the test and production databases.

Create a new Rails project named as library with RubyMine, and choose PostgreSQL database.

Configuring database.yml

When the application is created, the file database.yml, available in the library\config subdirectory of Rails Application you created. This file has live configuration sections for PostgreSQL databases. In each of the sections, you need to change the username and password lines to reflect the permissions on the databases you’ve created.

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *defaultdatabase: library_development
  username: library
  password:
  host: localhost
  port: 5432

test:
  <<: *defaultdatabase: library_test

production:
  primary: &primary_production
&lt;&lt;: *defaultdatabase: library_production
username: library
password: &lt;%= ENV["LIBRARY_DATABASE_PASSWORD"]%&gt;
  cache:
&lt;&lt;: *primary_production
database: library_production_cache
migrations_paths: db/cache_migrate
  queue:
&lt;&lt;: *primary_production
database: library_production_queue
migrations_paths: db/queue_migrate
  cable:
&lt;&lt;: *primary_production
database: library_production_cable
migrations_paths: db/cable_migrate

What is Next?

The next two chapters explain how to model your database tables and how to manage those using Rails Migrations.