Ruby Database Access

Databases are core components of modern applications, and almost all applications need to interact with databases to store and retrieve data. Ruby provides multiple ways to access databases, from native database drivers to advanced ORM frameworks. This chapter will explain in detail the various methods and best practices for database access in Ruby.

🎯 Database Access Basics

Database Drivers

Ruby communicates with various database systems through database drivers. Each database system has a corresponding Ruby driver.

ruby

# SQLite database access
# First install sqlite3 gem: gem install sqlite3

require 'sqlite3'

# Connect to database (will be created automatically if doesn't exist)
db = SQLite3::Database.new("example.db")

# Execute SQL statements
db.execute <<-SQL
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
SQL

# Insert data
db.execute("INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
           ["John", "john@example.com", 25])

# Query data
results = db.execute("SELECT * FROM users")
results.each do |row|
  puts "ID: #{row[0]}, Name: #{row[1]}, Email: #{row[2]}, Age: #{row[3]}"
end

# Use hash mode to get results
db.results_as_hash = true
results = db.execute("SELECT * FROM users")
results.each do |row|
  puts "ID: #{row['id']}, Name: #{row['name']}, Email: #{row['email']}"
end

# Close database connection
db.close

MySQL Database Access

ruby

# MySQL database access
# First install mysql2 gem: gem install mysql2

require 'mysql2'

# Connect to MySQL database
client = Mysql2::Client.new(
  host: "localhost",
  username: "root",
  password: "password",
  database: "myapp_development"
)

# Create table
client.query <<-SQL
  CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  )
SQL

# Insert data
client.query("INSERT INTO products (name, price, category) VALUES ('Apple', 5.50, 'Fruits')")

# Use parameterized queries to prevent SQL injection
client.query("INSERT INTO products (name, price, category) VALUES (?, ?, ?)",
             ["Banana", 3.00, "Fruits"])

# Query data
results = client.query("SELECT * FROM products WHERE price > ?", [4.00])
results.each do |row|
  puts "Product: #{row['name']}, Price: #{row['price']}, Category: #{row['category']}"
end

# Update data
client.query("UPDATE products SET price = ? WHERE name = ?", [6.00, "Apple"])

# Delete data
client.query("DELETE FROM products WHERE name = ?", ["Banana"])

# Get query information
puts "Affected rows: #{client.affected_rows}"
puts "Last insert ID: #{client.last_id}"

# Close connection
client.close

PostgreSQL Database Access

ruby

# PostgreSQL database access
# First install pg gem: gem install pg

require 'pg'

# Connect to PostgreSQL database
conn = PG.connect(
  host: "localhost",
  port: 5432,
  dbname: "myapp_development",
  user: "postgres",
  password: "password"
)

# Create table
conn.exec <<-SQL
  CREATE TABLE IF NOT EXISTS employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary NUMERIC(10, 2),
    hire_date DATE DEFAULT CURRENT_DATE
  )
SQL

# Insert data
conn.exec_params("INSERT INTO employees (name, department, salary) VALUES ($1, $2, $3)",
                 ["John", "Engineering", 15000.00])

# Query data
result = conn.exec_params("SELECT * FROM employees WHERE salary > $1", [10000])
result.each do |row|
  puts "Employee: #{row['name']}, Department: #{row['department']}, Salary: #{row['salary']}"
end

# Update data
conn.exec_params("UPDATE employees SET salary = $1 WHERE name = $2", [16000.00, "John"])

# Delete data
conn.exec_params("DELETE FROM employees WHERE name = $1", ["John"])

# Use transaction
conn.transaction do |txn|
  txn.exec_params("INSERT INTO employees (name, department, salary) VALUES ($1, $2, $3)",
                  ["Jane", "Sales", 12000.00])
  txn.exec_params("INSERT INTO employees (name, department, salary) VALUES ($1, $2, $3)",
                  ["Mike", "HR", 11000.00])
end

# Close connection
conn.close

🏗️ Database Connection Management

Connection Pool

ruby

# Use connection pool to manage database connections
require 'sqlite3'
require 'thread'

class DatabasePool
  def initialize(database_path, pool_size = 5)
    @database_path = database_path
    @pool_size = pool_size
    @pool = Queue.new
    @mutex = Mutex.new

    # Initialize connection pool
    @pool_size.times do
      @pool << create_connection
    end
  end

  def execute(sql, params = [])
    conn = acquire_connection
    begin
      conn.execute(sql, params)
    ensure
      release_connection(conn)
    end
  end

  def query(sql, params = [])
    conn = acquire_connection
    begin
      conn.query(sql, params)
    ensure
      release_connection(conn)
    end
  end

  private

  def create_connection
    SQLite3::Database.new(@database_path)
  end

  def acquire_connection
    @pool.pop
  end

  def release_connection(conn)
    @pool << conn
  end
end

# Using connection pool
# pool = DatabasePool.new("example.db")
# pool.execute("INSERT INTO users (name, email) VALUES (?, ?)", ["Test User", "test@example.com"])

Database Configuration Management

ruby

# Database configuration management
require 'yaml'
require 'erb'

class DatabaseConfig
  def self.load(config_file = 'config/database.yml')
    erb = ERB.new(File.read(config_file)).result
    YAML.load(erb)
  end

  def self.connect(environment = 'development')
    config = load
    env_config = config[environment]

    case env_config['adapter']
    when 'sqlite3'
      require 'sqlite3'
      SQLite3::Database.new(env_config['database'])
    when 'mysql2'
      require 'mysql2'
      Mysql2::Client.new(
        host: env_config['host'],
        username: env_config['username'],
        password: env_config['password'],
        database: env_config['database'],
        port: env_config['port']
      )
    when 'postgresql'
      require 'pg'
      PG.connect(
        host: env_config['host'],
        port: env_config['port'],
        dbname: env_config['database'],
        user: env_config['username'],
        password: env_config['password']
      )
    end
  end
end

# database.yml configuration file example
=begin
development:
  adapter: sqlite3
  database: db/development.sqlite3

test:
  adapter: sqlite3
  database: db/test.sqlite3

production:
  adapter: postgresql
  host: localhost
  port: 5432
  database: myapp_production
  username: myapp
  password: <%= ENV['DATABASE_PASSWORD'] %>
=end

🎯 ORM Framework - ActiveRecord

ActiveRecord Basics

ruby

# ActiveRecord is the ORM framework of Ruby on Rails
# First install activerecord gem: gem install activerecord

require 'active_record'
require 'sqlite3'

# Configure database connection
ActiveRecord::Base.establish_connection(
  adapter: 'sqlite3',
  database: 'example.db'
)

# Define models
class User < ActiveRecord::Base
end

class Post < ActiveRecord::Base
  belongs_to :user
end

# Create tables (using migrations)
class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :name, null: false
      t.string :email, null: false
      t.integer :age
      t.timestamps
    end

    add_index :users, :email, unique: true
  end
end

class CreatePosts < ActiveRecord::Migration[7.0]
  def change
    create_table :posts do |t|
      t.string :title, null: false
      t.text :content
      t.references :user, null: false, foreign_key: true
      t.timestamps
    end

    add_index :posts, :user_id
  end
end

# Run migrations
# CreateUsers.new.change
# CreatePosts.new.change

# CRUD operations
# Create record
user = User.create(name: "John", email: "john@example.com", age: 25)
puts "Created user: #{user.name}"

# Query records
all_users = User.all
puts "Total users: #{all_users.count}"

user_by_id = User.find(1)
puts "User: #{user_by_id.name}"

user_by_email = User.find_by(email: "john@example.com")
puts "Email user: #{user_by_email.name}"

# Conditional query
young_users = User.where("age < ?", 30)
young_users.each { |user| puts "Young user: #{user.name}" }

# Update record
user.update(name: "John Doe", age: 30)
puts "Updated user: #{user.name}, Age: #{user.age}"

# Delete record
# user.destroy

# Association query
post = Post.create(title: "First Post", content: "This is the content", user: user)
user_posts = user.posts
puts "User posts count: #{user_posts.count}"

ActiveRecord Advanced Features

ruby

# Advanced queries
class User < ActiveRecord::Base
  has_many :posts
  has_many :comments
  scope :adults, -> { where("age >= ?", 18) }
  scope :recent, -> { order(created_at: :desc) }

  validates :name, presence: true
  validates :email, presence: true, uniqueness: true
  validates :age, numericality: { greater_than: 0 }
end

class Post < ActiveRecord::Base
  belongs_to :user
  has_many :comments
  scope :published, -> { where.not(published_at: nil) }

  validates :title, presence: true
  validates :user, presence: true
end

class Comment < ActiveRecord::Base
  belongs_to :user
  belongs_to :post

  validates :content, presence: true
end

# Complex queries
# Joining tables
User.joins(:posts).where(posts: { title: "Hello" })

# Eager loading
users = User.includes(:posts).where("posts.published_at > ?", Time.now)

# Aggregations
User.group(:age).count

# Raw SQL
User.find_by_sql("SELECT * FROM users WHERE age > ?", [18])

# Transactions
ActiveRecord::Base.transaction do
  user.save!
  post.save!
end

# Callbacks
class User < ActiveRecord::Base
  before_save :normalize_name

  private

  def normalize_name
    self.name = name.downcase.titleize
  end
end

📚 Next Steps

After mastering Ruby database access, we recommend continuing to learn:

Ruby Web Services - Learn web service development
Ruby Testing - Master testing practices
Ruby Performance Optimization - Learn optimization techniques

Continue your Ruby learning journey!

Ruby Database Access ​

🎯 Database Access Basics ​

Database Drivers ​

MySQL Database Access ​

PostgreSQL Database Access ​

🏗️ Database Connection Management ​

Connection Pool ​

Database Configuration Management ​

🎯 ORM Framework - ActiveRecord ​

ActiveRecord Basics ​

ActiveRecord Advanced Features ​

📚 Next Steps ​

Ruby Database Access

🎯 Database Access Basics

Database Drivers

MySQL Database Access

PostgreSQL Database Access

🏗️ Database Connection Management

Connection Pool

Database Configuration Management

🎯 ORM Framework - ActiveRecord

ActiveRecord Basics

ActiveRecord Advanced Features

📚 Next Steps