Raw SQL Execution

Execute raw SQL with prepared statements, transaction handling, and cross-database compatibility. Learn when and how to use direct SQL for maximum control.

Beginner 1 min read

Raw SQL Execution in Ruby

Introduction to Raw SQL

While ORMs provide convenient abstractions, sometimes you need the power and flexibility of raw SQL. Ruby provides excellent tools for executing SQL directly, giving you full control over database operations.

Why Use Raw SQL?

# Raw SQL is useful for:
# - Complex queries that are difficult to express in ORM
# - Performance-critical operations
# - Database-specific features
# - Data migration and ETL processes
# - Reporting and analytics queries
# - Working with views, stored procedures, and functions

Execute Method

The execute method is the primary way to run SQL statements. It's available in most Ruby database libraries.

Basic SQL Execution

require 'sqlite3'

# SQLite3 example
db = SQLite3::Database.new('example.db')
db.results_as_hash = true

# Create tables
db.execute <<~SQL
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    department TEXT,
    salary DECIMAL(10,2),
    hire_date DATE,
    active BOOLEAN DEFAULT 1
  )
SQL

db.execute <<~SQL
  CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER REFERENCES users(id),
    product TEXT NOT NULL,
    amount DECIMAL(10,2),
    order_date DATE,
    status TEXT DEFAULT 'pending'
  )
SQL

# Insert sample data
db.execute "INSERT INTO users (name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?)",
           ['Alice Johnson', 'alice@company.com', 'Engineering', 75000, '2022-01-15']

db.execute "INSERT INTO users (name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?)",
           ['Bob Smith', 'bob@company.com', 'Sales', 65000, '2021-03-10']

db.execute "INSERT INTO users (name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?)",
           ['Carol Davis', 'carol@company.com', 'Engineering', 80000, '2020-08-22']

# Insert orders
orders_data = [
  [1, 'Laptop', 1200.00, '2023-01-10', 'completed'],
  [2, 'Mouse', 25.00, '2023-01-12', 'completed'],
  [1, 'Keyboard', 150.00, '2023-01-15', 'pending'],
  [3, 'Monitor', 300.00, '2023-01-18', 'completed']
]

orders_data.each do |order|
  db.execute "INSERT INTO orders (user_id, product, amount, order_date, status) VALUES (?, ?, ?, ?, ?)", order
end

puts "Sample data inserted successfully"
db.close

Complex Query Execution

require 'sqlite3'

class SQLExecutor
  def initialize(database_path)
    @db = SQLite3::Database.new(database_path)
    @db.results_as_hash = true
  end

  def execute_query(sql, params = [])
    begin
      if params.empty?
        @db.execute(sql)
      else
        @db.execute(sql, params)
      end
    rescue SQLite3::Exception => e
      puts "SQL Error: #{e.message}"
      puts "SQL: #{sql}"
      puts "Params: #{params.inspect}"
      []
    end
  end

  def execute_single(sql, params = [])
    result = execute_query(sql, params)
    result.first
  end

  def execute_scalar(sql, params = [])
    result = execute_single(sql, params)
    result ? result.values.first : nil
  end

  def close
    @db.close
  end
end

executor = SQLExecutor.new('example.db')

# Complex aggregation query
sales_report = executor.execute_query(<<~SQL)
  SELECT
    u.department,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_sales,
    AVG(o.amount) as average_order,
    MAX(o.amount) as largest_order,
    MIN(o.amount) as smallest_order
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE o.status = 'completed'
  GROUP BY u.department
  ORDER BY total_sales DESC
SQL

puts "=== Sales Report by Department ==="
sales_report.each do |row|
  puts "Department: #{row['department']}"
  puts "  Orders: #{row['order_count']}"
  puts "  Total Sales: $#{row['total_sales']}"
  puts "  Average Order: $#{'%.2f' % row['average_order']}"
  puts "  Range: $#{row['smallest_order']} - $#{row['largest_order']}"
  puts
end

# Window functions (advanced SQL)
user_rankings = executor.execute_query(<<~SQL)
  SELECT
    name,
    department,
    salary,
    RANK() OVER (ORDER BY salary DESC) as overall_rank,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    LAG(salary) OVER (ORDER BY salary DESC) as prev_salary,
    salary - LAG(salary) OVER (ORDER BY salary DESC) as salary_diff
  FROM users
  ORDER BY salary DESC
SQL

puts "=== Employee Rankings ==="
user_rankings.each do |row|
  puts "#{row['name']} (#{row['department']})"
  puts "  Salary: $#{row['salary']}"
  puts "  Overall Rank: #{row['overall_rank']}"
  puts "  Department Rank: #{row['dept_rank']}"
  if row['prev_salary']
    puts "  Salary difference from next highest: $#{row['salary_diff']}"
  end
  puts
end

# Common Table Expressions (CTE)
recursive_data = executor.execute_query(<<~SQL)
  WITH RECURSIVE monthly_sales AS (
    SELECT
      DATE('2023-01-01') as month_start,
      DATE('2023-01-31') as month_end,
      1 as month_num
    UNION ALL
    SELECT
      DATE(month_start, '+1 month'),
      DATE(month_end, '+1 month'),
      month_num + 1
    FROM monthly_sales
    WHERE month_num < 3
  ),
  sales_by_month AS (
    SELECT
      ms.month_num,
      ms.month_start,
      ms.month_end,
      COALESCE(SUM(o.amount), 0) as total_sales,
      COUNT(o.id) as order_count
    FROM monthly_sales ms
    LEFT JOIN orders o ON o.order_date BETWEEN ms.month_start AND ms.month_end
    GROUP BY ms.month_num, ms.month_start, ms.month_end
  )
  SELECT * FROM sales_by_month
  ORDER BY month_num
SQL

puts "=== Monthly Sales Analysis ==="
recursive_data.each do |row|
  puts "Month #{row['month_num']} (#{row['month_start']} to #{row['month_end']})"
  puts "  Sales: $#{row['total_sales']}"
  puts "  Orders: #{row['order_count']}"
  puts
end

executor.close

Prepared Statements

Prepared statements improve performance and security by pre-compiling SQL and using parameterized queries.

Advanced Prepared Statements

require 'sqlite3'

class PreparedStatementManager
  def initialize(database_path)
    @db = SQLite3::Database.new(database_path)
    @db.results_as_hash = true
    @statements = {}
  end

  def prepare(name, sql)
    @statements[name] = @db.prepare(sql)
  end

  def execute(name, *params)
    statement = @statements[name]
    raise "Statement '#{name}' not found" unless statement

    begin
      statement.execute(*params)
    rescue SQLite3::Exception => e
      puts "Error executing #{name}: #{e.message}"
      []
    end
  end

  def execute_single(name, *params)
    result = execute(name, *params)
    result.first
  end

  def execute_batch(name, param_sets)
    statement = @statements[name]
    raise "Statement '#{name}' not found" unless statement

    results = []
    param_sets.each do |params|
      results << statement.execute(*params).to_a
    end
    results
  end

  def close_all
    @statements.each_value(&:close)
    @statements.clear
    @db.close
  end
end

# Usage example
stmt_manager = PreparedStatementManager.new('example.db')

# Prepare commonly used statements
stmt_manager.prepare(:find_user_by_email, "SELECT * FROM users WHERE email = ?")
stmt_manager.prepare(:find_users_by_department, "SELECT * FROM users WHERE department = ? ORDER BY name")
stmt_manager.prepare(:update_user_salary, "UPDATE users SET salary = ? WHERE id = ?")
stmt_manager.prepare(:insert_order, "INSERT INTO orders (user_id, product, amount, order_date, status) VALUES (?, ?, ?, ?, ?)")

stmt_manager.prepare(:user_order_summary, <<~SQL)
  SELECT
    u.name,
    u.email,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.amount), 0) as total_spent,
    MAX(o.order_date) as last_order_date
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.id = ?
  GROUP BY u.id, u.name, u.email
SQL

# Use prepared statements
puts "=== Using Prepared Statements ==="

# Find user
alice = stmt_manager.execute_single(:find_user_by_email, 'alice@company.com')
puts "Found user: #{alice['name']}"

# Find users by department
engineers = stmt_manager.execute(:find_users_by_department, 'Engineering')
puts "Engineers: #{engineers.map { |u| u['name'] }.join(', ')}"

# Update salary
stmt_manager.execute(:update_user_salary, 78000, alice['id'])
puts "Updated Alice's salary"

# Batch operations
new_orders = [
  [alice['id'], 'Tablet', 500.00, '2023-02-01', 'pending'],
  [alice['id'], 'Case', 50.00, '2023-02-01', 'pending'],
  [alice['id'], 'Charger', 30.00, '2023-02-01', 'completed']
]

stmt_manager.execute_batch(:insert_order, new_orders)
puts "Inserted #{new_orders.length} new orders"

# Get user summary
summary = stmt_manager.execute_single(:user_order_summary, alice['id'])
puts "\nUser Summary for #{summary['name']}:"
puts "  Total Orders: #{summary['order_count']}"
puts "  Total Spent: $#{summary['total_spent']}"
puts "  Last Order: #{summary['last_order_date']}"

stmt_manager.close_all

Transaction Handling

Transactions ensure data consistency and allow you to group multiple operations into atomic units.

Advanced Transaction Management

require 'sqlite3'

class TransactionManager
  def initialize(database_path)
    @db = SQLite3::Database.new(database_path)
    @db.results_as_hash = true
  end

  def simple_transaction
    @db.transaction do
      yield @db
    end
  end

  def manual_transaction
    @db.execute("BEGIN TRANSACTION")
    begin
      yield @db
      @db.execute("COMMIT")
    rescue => e
      @db.execute("ROLLBACK")
      raise e
    end
  end

  def savepoint_transaction(savepoint_name)
    @db.execute("SAVEPOINT #{savepoint_name}")
    begin
      yield @db
      @db.execute("RELEASE SAVEPOINT #{savepoint_name}")
    rescue => e
      @db.execute("ROLLBACK TO SAVEPOINT #{savepoint_name}")
      raise e
    end
  end

  def nested_transactions
    @db.transaction do
      puts "Outer transaction started"

      # First operation
      @db.execute("INSERT INTO users (name, email, department, salary, hire_date) VALUES (?, ?, ?, ?, ?)",
                 ['David Wilson', 'david@company.com', 'Marketing', 60000, '2023-02-01'])

      user_id = @db.last_insert_row_id
      puts "Inserted user with ID: #{user_id}"

      # Nested savepoint
      begin
        savepoint_transaction('order_creation') do
          puts "Savepoint transaction started"

          # Insert multiple orders
          orders = [
            [user_id, 'Business Cards', 100.00, '2023-02-02', 'pending'],
            [user_id, 'Brochures', 250.00, '2023-02-02', 'pending']
          ]

          orders.each do |order|
            @db.execute("INSERT INTO orders (user_id, product, amount, order_date, status) VALUES (?, ?, ?, ?, ?)", order)
          end

          # Simulate error condition
          raise "Order processing failed" if orders.length > 1

          puts "Orders inserted successfully"
        end
      rescue => e
        puts "Savepoint rolled back: #{e.message}"
        # Outer transaction continues
      end

      # Add a successful order outside the savepoint
      @db.execute("INSERT INTO orders (user_id, product, amount, order_date, status) VALUES (?, ?, ?, ?, ?)",
                 [user_id, 'Website Design', 2000.00, '2023-02-03', 'completed'])

      puts "Final order inserted"
    end

    puts "Outer transaction completed"
  end

  def concurrent_transaction_example
    # Simulate concurrent access with proper locking
    threads = []

    # Create account balances table
    @db.execute <<~SQL
      CREATE TABLE IF NOT EXISTS accounts (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        balance DECIMAL(10,2) DEFAULT 0
      )
    SQL

    # Initialize accounts
    @db.execute("INSERT OR REPLACE INTO accounts (id, name, balance) VALUES (1, 'Account A', 1000)")
    @db.execute("INSERT OR REPLACE INTO accounts (id, name, balance) VALUES (2, 'Account B', 500)")

    # Simulate money transfer with proper locking
    5.times do |i|
      threads << Thread.new do
        transfer_money(1, 2, 100, "Transfer #{i + 1}")
      end
    end

    threads.each(&:join)

    # Check final balances
    balances = @db.execute("SELECT * FROM accounts ORDER BY id")
    puts "\nFinal Balances:"
    balances.each do |account|
      puts "#{account['name']}: $#{account['balance']}"
    end
  end

  def performance_transaction_comparison
    require 'benchmark'

    # Create test table
    @db.execute "DROP TABLE IF EXISTS test_items"
    @db.execute <<~SQL
      CREATE TABLE test_items (
        id INTEGER PRIMARY KEY,
        name TEXT,
        value INTEGER
      )
    SQL

    puts "=== Transaction Performance Comparison ==="

    # Without transaction
    time_without = Benchmark.measure do
      1000.times do |i|
        @db.execute("INSERT INTO test_items (name, value) VALUES (?, ?)", ["Item #{i}", i])
      end
    end

    @db.execute("DELETE FROM test_items")

    # With single transaction
    time_with = Benchmark.measure do
      @db.transaction do
        1000.times do |i|
          @db.execute("INSERT INTO test_items (name, value) VALUES (?, ?)", ["Item #{i}", i])
        end
      end
    end

    puts "Without transaction: #{time_without.real.round(3)}s"
    puts "With transaction: #{time_with.real.round(3)}s"
    puts "Performance improvement: #{(time_without.real / time_with.real).round(1)}x faster"

    # Batch insert comparison
    @db.execute("DELETE FROM test_items")

    time_batch = Benchmark.measure do
      values = 1000.times.map { |i| "(#{i + 1}, 'Batch Item #{i}', #{i})" }.join(', ')
      @db.execute("INSERT INTO test_items (id, name, value) VALUES #{values}")
    end

    puts "Batch insert: #{time_batch.real.round(3)}s"
    puts "Batch vs transaction: #{(time_with.real / time_batch.real).round(1)}x faster"
  end

  def close
    @db.close
  end

  private

  def transfer_money(from_account, to_account, amount, description)
    @db.transaction do
      # Lock accounts to prevent race conditions
      from_balance = @db.execute("SELECT balance FROM accounts WHERE id = ?", [from_account]).first
      to_balance = @db.execute("SELECT balance FROM accounts WHERE id = ?", [to_account]).first

      raise "Insufficient funds" if from_balance['balance'] < amount

      # Update balances
      @db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from_account])
      @db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to_account])

      puts "#{description}: Transferred $#{amount} from account #{from_account} to #{to_account}"

      # Small delay to increase chance of race conditions (for demonstration)
      sleep(0.01)
    end
  rescue => e
    puts "#{description}: Failed - #{e.message}"
  end
end

# Usage examples
tx_manager = TransactionManager.new('example.db')

puts "=== Simple Transaction Example ==="
tx_manager.simple_transaction do |db|
  db.execute("UPDATE users SET salary = salary * 1.05 WHERE department = 'Engineering'")
  puts "Engineering salaries increased by 5%"
end

puts "\n=== Nested Transactions with Savepoints ==="
tx_manager.nested_transactions

puts "\n=== Concurrent Transaction Example ==="
tx_manager.concurrent_transaction_example

puts "\n=== Performance Comparison ==="
tx_manager.performance_transaction_comparison

tx_manager.close

Cross-Database Raw SQL

Techniques for writing database-agnostic raw SQL and handling database-specific features.

Database-Agnostic SQL Execution

require 'pg'
require 'mysql2'
require 'sqlite3'

class MultiDatabaseExecutor
  def initialize(db_type, connection_params)
    @db_type = db_type.to_sym
    @connection_params = connection_params
    @connection = nil
    connect
  end

  def connect
    case @db_type
    when :postgresql
      @connection = PG.connect(@connection_params)
    when :mysql
      @connection = Mysql2::Client.new(@connection_params)
    when :sqlite
      @connection = SQLite3::Database.new(@connection_params[:database])
      @connection.results_as_hash = true
    else
      raise "Unsupported database type: #{@db_type}"
    end
  end

  def execute_raw(sql, params = [])
    case @db_type
    when :postgresql
      if params.empty?
        @connection.exec(sql)
      else
        @connection.exec_params(sql, params)
      end
    when :mysql
      if params.empty?
        @connection.query(sql)
      else
        stmt = @connection.prepare(sql)
        result = stmt.execute(*params)
        stmt.close
        result
      end
    when :sqlite
      if params.empty?
        @connection.execute(sql)
      else
        @connection.execute(sql, params)
      end
    end
  end

  def get_database_info
    case @db_type
    when :postgresql
      version = @connection.exec("SELECT version()").first['version']
      { type: 'PostgreSQL', version: version }
    when :mysql
      version = @connection.query("SELECT VERSION() as version").first['version']
      { type: 'MySQL', version: version }
    when :sqlite
      version = @connection.execute("SELECT sqlite_version()").first[0]
      { type: 'SQLite', version: version }
    end
  end

  def create_users_table
    sql = case @db_type
    when :postgresql
      <<~SQL
        CREATE TABLE IF NOT EXISTS users (
          id SERIAL PRIMARY KEY,
          name VARCHAR(100) NOT NULL,
          email VARCHAR(255) UNIQUE NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      SQL
    when :mysql
      <<~SQL
        CREATE TABLE IF NOT EXISTS users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          name VARCHAR(100) NOT NULL,
          email VARCHAR(255) UNIQUE NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB
      SQL
    when :sqlite
      <<~SQL
        CREATE TABLE IF NOT EXISTS users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT NOT NULL,
          email TEXT UNIQUE NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      SQL
    end

    execute_raw(sql)
  end

  def insert_user(name, email)
    sql = case @db_type
    when :postgresql
      "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id"
    when :mysql, :sqlite
      "INSERT INTO users (name, email) VALUES (?, ?)"
    end

    result = execute_raw(sql, [name, email])

    case @db_type
    when :postgresql
      result.first['id']
    when :mysql
      @connection.last_id
    when :sqlite
      @connection.last_insert_row_id
    end
  end

  def find_users_with_pagination(page = 1, per_page = 10)
    offset = (page - 1) * per_page

    sql = case @db_type
    when :postgresql
      "SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2"
    when :mysql, :sqlite
      "SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?"
    end

    execute_raw(sql, [per_page, offset])
  end

  def get_table_schema(table_name)
    case @db_type
    when :postgresql
      execute_raw(<<~SQL, [table_name])
        SELECT
          column_name,
          data_type,
          is_nullable,
          column_default
        FROM information_schema.columns
        WHERE table_name = $1
        ORDER BY ordinal_position
      SQL
    when :mysql
      execute_raw("DESCRIBE #{table_name}")
    when :sqlite
      execute_raw("PRAGMA table_info(#{table_name})")
    end
  end

  def close
    case @db_type
    when :postgresql, :mysql, :sqlite
      @connection.close
    end
  end
end

# Example usage with different databases
databases = [
  {
    type: :sqlite,
    params: { database: ':memory:' },
    name: 'SQLite In-Memory'
  }
  # You can add other databases:
  # {
  #   type: :postgresql,
  #   params: { host: 'localhost', dbname: 'test', user: 'postgres', password: 'password' },
  #   name: 'PostgreSQL'
  # },
  # {
  #   type: :mysql,
  #   params: { host: 'localhost', database: 'test', username: 'root', password: 'password' },
  #   name: 'MySQL'
  # }
]

databases.each do |db_config|
  puts "=== Testing #{db_config[:name]} ==="

  begin
    executor = MultiDatabaseExecutor.new(db_config[:type], db_config[:params])

    # Get database info
    info = executor.get_database_info
    puts "Connected to #{info[:type]} version #{info[:version]}"

    # Create table
    executor.create_users_table
    puts "Users table created"

    # Insert users
    user_id1 = executor.insert_user('Alice Johnson', 'alice@example.com')
    user_id2 = executor.insert_user('Bob Smith', 'bob@example.com')
    puts "Inserted users with IDs: #{user_id1}, #{user_id2}"

    # Query users
    users = executor.find_users_with_pagination(1, 10)
    puts "Found #{users.respond_to?(:count) ? users.count : users.length} users"

    # Get schema
    schema = executor.get_table_schema('users')
    puts "Table schema has #{schema.respond_to?(:count) ? schema.count : schema.length} columns"

    executor.close
    puts "#{db_config[:name]} test completed successfully\n"

  rescue => e
    puts "Error testing #{db_config[:name]}: #{e.message}\n"
  end
end

SQL Query Builder

Sometimes you need dynamic SQL generation. Here's a simple query builder for complex scenarios.

Dynamic SQL Query Builder

class SQLQueryBuilder
  def initialize(db_type = :sqlite)
    @db_type = db_type
    @select_fields = []
    @from_table = nil
    @joins = []
    @where_conditions = []
    @group_by_fields = []
    @having_conditions = []
    @order_by_fields = []
    @limit_value = nil
    @offset_value = nil
    @params = []
  end

  def select(*fields)
    @select_fields.concat(fields.map(&:to_s))
    self
  end

  def from(table)
    @from_table = table.to_s
    self
  end

  def join(table, condition)
    @joins << "JOIN #{table} ON #{condition}"
    self
  end

  def left_join(table, condition)
    @joins << "LEFT JOIN #{table} ON #{condition}"
    self
  end

  def where(condition, *params)
    @where_conditions << condition
    @params.concat(params)
    self
  end

  def group_by(*fields)
    @group_by_fields.concat(fields.map(&:to_s))
    self
  end

  def having(condition, *params)
    @having_conditions << condition
    @params.concat(params)
    self
  end

  def order_by(field, direction = :asc)
    @order_by_fields << "#{field} #{direction.to_s.upcase}"
    self
  end

  def limit(count)
    @limit_value = count
    self
  end

  def offset(count)
    @offset_value = count
    self
  end

  def to_sql
    sql_parts = []

    # SELECT clause
    if @select_fields.empty?
      sql_parts << "SELECT *"
    else
      sql_parts << "SELECT #{@select_fields.join(', ')}"
    end

    # FROM clause
    raise "FROM table is required" unless @from_table
    sql_parts << "FROM #{@from_table}"

    # JOIN clauses
    sql_parts.concat(@joins) unless @joins.empty?

    # WHERE clause
    unless @where_conditions.empty?
      sql_parts << "WHERE #{@where_conditions.join(' AND ')}"
    end

    # GROUP BY clause
    unless @group_by_fields.empty?
      sql_parts << "GROUP BY #{@group_by_fields.join(', ')}"
    end

    # HAVING clause
    unless @having_conditions.empty?
      sql_parts << "HAVING #{@having_conditions.join(' AND ')}"
    end

    # ORDER BY clause
    unless @order_by_fields.empty?
      sql_parts << "ORDER BY #{@order_by_fields.join(', ')}"
    end

    # LIMIT and OFFSET
    if @limit_value
      case @db_type
      when :postgresql
        sql_parts << "LIMIT #{@limit_value}"
        sql_parts << "OFFSET #{@offset_value}" if @offset_value
      when :mysql, :sqlite
        if @offset_value
          sql_parts << "LIMIT #{@offset_value}, #{@limit_value}"
        else
          sql_parts << "LIMIT #{@limit_value}"
        end
      end
    end

    sql_parts.join(' ')
  end

  def params
    @params
  end

  def reset
    @select_fields.clear
    @from_table = nil
    @joins.clear
    @where_conditions.clear
    @group_by_fields.clear
    @having_conditions.clear
    @order_by_fields.clear
    @limit_value = nil
    @offset_value = nil
    @params.clear
    self
  end
end

# Usage examples
puts "=== SQL Query Builder Examples ==="

# Simple query
builder = SQLQueryBuilder.new(:sqlite)
query1 = builder
  .select(:name, :email, :department)
  .from(:users)
  .where("active = ?", true)
  .order_by(:name)
  .to_sql

puts "Simple Query:"
puts query1
puts "Params: #{builder.params.inspect}"
puts

# Complex query with joins and aggregation
builder.reset
query2 = builder
  .select("u.name", "u.department", "COUNT(o.id) as order_count", "SUM(o.amount) as total_amount")
  .from("users u")
  .left_join("orders o", "u.id = o.user_id")
  .where("u.active = ?", true)
  .where("u.hire_date > ?", '2020-01-01')
  .group_by("u.id", "u.name", "u.department")
  .having("COUNT(o.id) > ?", 0)
  .order_by("total_amount", :desc)
  .limit(10)
  .to_sql

puts "Complex Query with Joins and Aggregation:"
puts query2
puts "Params: #{builder.params.inspect}"
puts

# Dynamic query building based on conditions
def build_user_search_query(filters = {})
  builder = SQLQueryBuilder.new(:sqlite)
  builder.select(:id, :name, :email, :department, :salary).from(:users)

  if filters[:department]
    builder.where("department = ?", filters[:department])
  end

  if filters[:min_salary]
    builder.where("salary >= ?", filters[:min_salary])
  end

  if filters[:max_salary]
    builder.where("salary <= ?", filters[:max_salary])
  end

  if filters[:name_contains]
    builder.where("name LIKE ?", "%#{filters[:name_contains]}%")
  end

  if filters[:hired_after]
    builder.where("hire_date > ?", filters[:hired_after])
  end

  case filters[:sort_by]
  when 'name'
    builder.order_by(:name)
  when 'salary'
    builder.order_by(:salary, :desc)
  when 'hire_date'
    builder.order_by(:hire_date, :desc)
  else
    builder.order_by(:id)
  end

  if filters[:page] && filters[:per_page]
    offset = (filters[:page] - 1) * filters[:per_page]
    builder.limit(filters[:per_page]).offset(offset)
  end

  builder
end

# Example dynamic queries
search_filters = [
  { department: 'Engineering', min_salary: 70000, sort_by: 'salary' },
  { name_contains: 'Alice', hired_after: '2021-01-01' },
  { min_salary: 60000, max_salary: 80000, page: 1, per_page: 5 }
]

search_filters.each_with_index do |filters, index|
  builder = build_user_search_query(filters)
  puts "Dynamic Query #{index + 1}:"
  puts "Filters: #{filters.inspect}"
  puts "SQL: #{builder.to_sql}"
  puts "Params: #{builder.params.inspect}"
  puts
end

Raw SQL Best Practices

  • Always use parameterized queries to prevent SQL injection attacks
  • Use transactions for operations that must be atomic
  • Prepare statements for frequently executed queries
  • Handle database-specific SQL differences when targeting multiple databases
  • Use proper error handling and logging for SQL operations
  • Monitor query performance and use EXPLAIN to optimize slow queries
  • Close database connections and prepared statements properly
  • Use connection pooling for multi-threaded applications
  • Consider using views for complex queries that are used frequently
  • Document complex SQL queries with comments explaining the business logic