Ruby Database Access

Ruby Database Access

This tutorial will guide you through accessing databases using Ruby. While older libraries like Ruby/DBI existed, modern Ruby applications typically use specific adapter gems for each database system (e.g., pg for PostgreSQL, mysql2 for MySQL, sqlite3 for SQLite). These gems provide a direct interface to the database.

For more complex applications, Object-Relational Mappers (ORMs) like ActiveRecord (part of Ruby on Rails) or Sequel are commonly used. ORMs provide a higher-level abstraction over SQL. This tutorial will focus on using a direct adapter gem, sqlite3, for its simplicity and ease of setup (no separate database server needed).

Supported Databases (via Gems):

• SQLite (via sqlite3 gem)
• PostgreSQL (via pg gem)
• MySQL/MariaDB (via mysql2 gem)
• Oracle (via ruby-oci8 gem)
• SQL Server (via tiny_tds gem)
• And many others through specific gems.

Architecture with Adapter Gems

When using an adapter gem, your Ruby script communicates directly with the database through the gem’s API. The gem handles the low-level communication protocol specific to that database.

Ruby Application -> Adapter Gem (e.g., sqlite3) -> Database (e.g., SQLite file)

Prerequisites:

To follow this tutorial, you’ll need Ruby installed and the sqlite3 gem. You might also need SQLite3 development libraries installed on your system for the gem to compile.

Install the gem using the command line:

gem install sqlite3

Database Connection:

First, require the gem and then create a database connection. For SQLite, this involves creating or opening a database file.

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'sqlite3'

begin
  # Open a database. If 'test.db' doesn't exist, it will be created.
  db = SQLite3::Database.new 'test.db'
  db.results_as_hash = true # Get results as hashes instead of arrays

  puts "Opened database successfully"

  # Example: Get SQLite version
  version = db.get_first_value 'SELECT SQLITE_VERSION()'
  puts "SQLite version: #{version}"

rescue SQLite3::Exception => e
  puts "Database error: #{e.message}"
  puts "Exception class: #{e.class}"
ensure
  # Always close the database connection if it was opened
  db.close if db
end

If the connection is established, an SQLite3::Database object is returned. The ensure block guarantees that db.close is called, releasing resources.

Creating a Table:

The execute method is used to run SQL statements that don’t return rows, like CREATE TABLE.

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'sqlite3'

begin
  db = SQLite3::Database.new 'company.db'
  db.execute "DROP TABLE IF EXISTS employees"
  db.execute <<~SQL
    CREATE TABLE employees (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      first_name TEXT NOT NULL,
      last_name TEXT,
      age INTEGER,
      position TEXT,
      salary REAL
    );
  SQL
  puts "Table 'employees' created successfully."
rescue SQLite3::Exception => e
  puts "Error creating table: #{e}"
ensure
  db.close if db
end

INSERT Operation:

To insert data, use the execute method with an INSERT statement. It’s crucial to use parameterized queries (placeholders like ?) to prevent SQL injection vulnerabilities.

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'sqlite3'

begin
  db = SQLite3::Database.new 'company.db'
  # Create table if it doesn't exist (assuming previous script ran or add creation here)
  # db.execute "CREATE TABLE IF NOT EXISTS employees (...);"

  # Insert a single record using placeholders
  db.execute("INSERT INTO employees (first_name, last_name, age, position, salary) VALUES (?, ?, ?, ?, ?)",
             'John', 'Doe', 30, 'Developer', 60000.0)

  # Insert multiple records
  employees_data = [
    ['Jane', 'Smith', 28, 'Designer', 55000.0],
    ['Mike', 'Johnson', 35, 'Manager', 75000.0]
  ]

  employees_data.each do |emp|
    db.execute("INSERT INTO employees (first_name, last_name, age, position, salary) VALUES (?, ?, ?, ?, ?)",
               emp[0], emp[1], emp[2], emp[3], emp[4])
  end

  puts "#{db.changes} records inserted in the last operation."
  # Note: db.changes returns rows affected by the most recent DML statement (INSERT, UPDATE, DELETE)
  # For total rows inserted in the loop, you'd sum them or count separately.

  puts "Records created successfully"

rescue SQLite3::Exception => e
  puts "Error inserting records: #{e}"
ensure
  db.close if db
end

READ Operation:

To fetch data, use execute with a SELECT statement. If db.results_as_hash = true was set, each row is a hash. Otherwise, it’s an array.

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'sqlite3'

begin
  db = SQLite3::Database.new 'company.db'
  db.results_as_hash = true

  puts "Employees earning more than 58000:"
  # Using placeholders for query parameters
  min_salary = 58000.0
  db.execute("SELECT id, first_name, last_name, position, salary FROM employees WHERE salary > ?", min_salary) do |row|
    puts "ID: #{row['id']}, Name: #{row['first_name']} #{row['last_name']}, Position: #{row['position']}, Salary: #{row['salary']}"
  end

  # Get a single value
  count = db.get_first_value "SELECT COUNT(*) FROM employees"
  puts "\nTotal employees: #{count}"

rescue SQLite3::Exception => e
  puts "Error reading records: #{e}"
ensure
  db.close if db
end

UPDATE Operation:

Use execute with an UPDATE statement and placeholders.

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'sqlite3'

begin
  db = SQLite3::Database.new 'company.db'

  # Give John Doe a raise
  new_salary = 65000.0
  target_id = 1 # Assuming John Doe's ID is 1
  db.execute("UPDATE employees SET salary = ? WHERE id = ?", new_salary, target_id)

  puts "Updated #{db.changes} record(s)."
rescue SQLite3::Exception => e
  puts "Error updating records: #{e}"
ensure
  db.close if db
end

DELETE Operation:

Use execute with a DELETE statement and placeholders.

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'sqlite3'

begin
  db = SQLite3::Database.new 'company.db'

  # Remove an employee by ID (e.g., employee with ID 2)
  employee_id_to_delete = 2
  db.execute("DELETE FROM employees WHERE id = ?", employee_id_to_delete)

  puts "Deleted #{db.changes} record(s)."
rescue SQLite3::Exception => e
  puts "Error deleting records: #{e}"
ensure
  db.close if db
end

Performing Transactions:

Transactions ensure that a series of database operations either all succeed or all fail, maintaining data consistency. The properties are Atomicity, Consistency, Isolation, Durability (ACID).

The sqlite3 gem provides a transaction block method, which is the recommended way to handle transactions. It automatically handles COMMIT on success or ROLLBACK on exception.

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'sqlite3'

db = SQLite3::Database.new 'company.db'

begin
  # Example: Transferring a fictional 'project budget' between departments (conceptual)
  # Assume 'departments' table with 'name' and 'budget' columns
  # db.execute "CREATE TABLE IF NOT EXISTS departments (name TEXT, budget REAL);"
  # db.execute "INSERT INTO departments VALUES ('Sales', 10000);"
  # db.execute "INSERT INTO departments VALUES ('Marketing', 5000);"

  db.transaction do
    # This block is executed within a transaction
    puts "Starting transaction..."
    # Decrease Sales budget
    db.execute("UPDATE employees SET salary = salary - 1000 WHERE first_name = ?", 'John')
    puts "Decreased John's salary."

    # Increase Marketing budget (simulating an error here)
    # If this line raises an error, the entire transaction will be rolled back.
    # db.execute("UPDATE employees SET salary = salary + 1000 WHERE non_existent_column = ?", 'Jane') # Intentional error
    db.execute("UPDATE employees SET salary = salary + 1000 WHERE first_name = ?", 'Jane')
    puts "Increased Jane's salary."

    puts "Transaction operations completed within block."
    # If the block completes without an exception, COMMIT is automatic.
  end
  puts "Transaction committed successfully."

rescue SQLite3::Exception => e
  puts "Transaction failed and was rolled back: #{e.message}"
rescue StandardError => e
  puts "An unexpected error occurred during transaction: #{e.message}"
ensure
  db.close if db
end

You can also manage transactions manually with db.execute 'BEGIN TRANSACTION', db.execute 'COMMIT', and db.execute 'ROLLBACK', but the block form is safer and more idiomatic.

Further Steps:

This tutorial covered basic database operations with the sqlite3 gem. For larger applications, consider exploring:

• Other database systems and their specific gems (e.g., pg for PostgreSQL, mysql2 for MySQL).
• ORMs like ActiveRecord or Sequel for a higher-level, object-oriented way to interact with your database.
• Advanced topics like connection pooling, database migrations, and more complex query building.