Setting Up and Managing Databases in Ruby on Rails

When working with Ruby on Rails, configuring and managing your database is essential for a smooth development process. This guide will walk you through setting up your database, performing basic operations, and adding initial data.

Connecting to a Database

Rails uses the `config/database.yml` file to set up the database connection. The configuration varies depending on whether you’re using SQLite or MySQL.

SQLite Configuration

As for SQLite, your `config/database.yml` might look like this:

default: &default
  adapter: sqlite3
  pool: 5
  timeout: 5000

development:
  <<: *default
  database: db/development.sqlite3

test:
  <<: *default
  database: db/test.sqlite3

production:
  <<: *default
  database: db/production.sqlite3

MySQL Configuration

For MySQL, the configuration will be slightly different:

default: &default
  adapter: mysql2
  encoding: utf8
  pool: 5
  username: root
  password:
  socket: /tmp/mysql.sock

development:
  <<: *default
  database: sample_development

test:
  <<: *default
  database: sample_test

production:
  <<: *default
  database: sample_production

Explanation of Key Settings:

adapter: Type of database (e.g., `sqlite3`, `mysql2`).

database: The name of the database.

host: Server name or IP address (used with databases like PostgreSQL or MySQL).

socket: Socket path (e.g., `/tmp/mysql.sock` for MySQL).

port: Port number (for databases like PostgreSQL or MySQL).

username: Username for database access.

password: Password for database access.

encoding: Character encoding (e.g., `utf8`).

pool: Number of connection pools.

Basic Database Operations

Managing your database in Rails involves several key operations:

Create a Database

To create the database based on your configuration, run:

rails db:create #In modern versions of Rails (Rails 4.0 and later)
or 
rake db:create #In older versions of Rails (before Rails 4.0)

It generates the below output

Created database 'db/development.sqlite3'
Created database 'db/test.sqlite3'

Drop the Database

To delete the database, use:

rails db:drop #In modern versions of Rails (Rails 4.0 and later)
or 
rake db:drop #In older versions of Rails (before Rails 4.0)

Output:

 Dropped database 'db/development.sqlite3'
 Dropped database 'db/test.sqlite3'

Reset the Database

This command drops and recreates the database and restores the schema from `db/schema.rb`:

rails db:reset #In modern versions of Rails (Rails 4.0 and later)
or 
rake db:reset #In older versions of Rails (before Rails 4.0)

Output:

Dropped database 'db/development.sqlite3'
Dropped database 'db/test.sqlite3'
Created database 'db/development.sqlite3'
Created database 'db/test.sqlite3'

Adding Initial Data

To populate your database with initial data, you can use the `db/seeds.rb` file. For example:

# db/seeds.rb

Model.create(name: "RailsCookBook 1")
Model.create(name: "RailsCookBook 2")

Run the following command to execute the seed file:

rails db:seed #In modern versions of Rails (Rails 4.0 and later)
or 
rake db:seed #In older versions of Rails (before Rails 4.0

This command will insert the records defined in `db/seeds.rb` into the database.

This guide should help you get started with setting up and managing your Rails database effectively. Feel free to adjust the configuration and operations according to your specific needs!