Ruby on Rails - Database Setup

Ruby on Rails - Modern Database Setup

Before you begin, ensure your database server (like PostgreSQL or MySQL) is running. Ruby on Rails typically uses separate databases for development, testing, and production environments. By convention, if your application is named ‘myapp’, these databases would be:

• myapp_development
• myapp_production
• myapp_test

While you can create these manually, Rails provides Rake tasks to create and manage them based on your config/database.yml file. For development, you’ll typically need a database user with privileges to create databases and tables.

Modern Database Configuration with config/database.yml

Rails uses the config/database.yml file to connect to your database. When you create a new Rails application (e.g., rails new myapp -d postgresql), Rails generates this file with sensible defaults. It’s highly recommended to use environment variables for sensitive credentials like passwords, especially for production.

Below is an example for PostgreSQL. MySQL setup is similar, just change the adapter.

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  host: <%= ENV.fetch("DATABASE_HOST") { 'localhost' } %>
  username: <%= ENV.fetch("DATABASE_USERNAME") { 'your_db_user' } %>
  password: <%= ENV['DATABASE_PASSWORD'] %> # Store password in an environment variable

development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: myapp_production
  username: myapp_prod_user # Potentially different user for production
  password: <%= ENV['MYAPP_DATABASE_PASSWORD'] %>

Key changes and best practices:

• Adapters: Common choices are postgresql, mysql2, or sqlite3 (default for new apps if no -d flag is specified).
• Environment Variables: Using ENV['YOUR_VARIABLE_NAME'] or ENV.fetch("YOUR_VARIABLE_NAME") { 'default_value' } for settings like host, username, and especially password, enhances security and flexibility. Tools like dotenv-rails gem can help manage environment variables in development.
• YAML Anchors: &default and <<: *default reduce repetition.
• Connection Pooling: pool specifies the number of database connections. RAILS_MAX_THREADS is often used here.

Creating Databases with Rails

Once config/database.yml is set up and your database server is running with a user that has permission to create databases (or the databases already exist and the user has access), you can use Rails commands:

# To create development and test databases:
$ bin/rails db:create

# To run migrations (which create tables based on your db/migrate files):
$ bin/rails db:migrate

# To set up the test database schema (useful for testing):
$ bin/rails db:test:prepare

Manual Database and User Creation (Example for PostgreSQL)

If you prefer to create users and databases manually, here’s a PostgreSQL example. First, create a database user (e.g., myapp_user):

$ sudo -u postgres createuser --interactive --pwprompt
Enter name of role to add: myapp_user
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Then, you can let Rails create the databases using bin/rails db:create with this user configured in database.yml, or create them manually:

$ sudo -u postgres psql
postgres=# CREATE DATABASE myapp_development OWNER myapp_user;
postgres=# CREATE DATABASE myapp_test OWNER myapp_user;
postgres=# CREATE DATABASE myapp_production OWNER myapp_user; # Usually on a separate production server
postgres=# \q

For MySQL, the commands would be different (e.g., CREATE USER, GRANT PRIVILEGES, CREATE DATABASE). Refer to MySQL documentation for specifics.

Database Choice

SQLite is the default for new Rails applications because it’s file-based and requires no separate server, making it easy for beginners. However, for development that mirrors production more closely and for production itself, PostgreSQL and MySQL are more robust and common choices. Choose the database that best fits your project needs and operational environment.

What’s Next?

With your database configured, the next steps involve defining your application’s data structure through models and migrations. These will be covered in subsequent chapters.