MySQL #

# Ubuntu / Debian
sudo apt install libmysqlclient-dev

# macOS with Homebrew
brew install mysql-client

# CentOS / RHEL / Fedora
sudo dnf install mysql-devel

# Pure MySQL driver — for direct access without an ORM
gem install mysql2

# Or in the Gemfile

# Gemfile
gem 'mysql2', '~> 0.5'        # MySQL driver

# Optional — a query builder without a full ORM
gem 'sequel', '~> 5.75'

# Or Rails with MySQL
gem 'rails',  '~> 7.1'
gem 'mysql2', '~> 0.5'

require 'mysql2'

# Create a connection
client = Mysql2::Client.new(
  host:     "localhost",
  port:     3306,
  username: "root",
  password: ENV["MYSQL_PASSWORD"],
  database: "store_db",
  encoding: "utf8mb4",          # important for emoji and special characters
  reconnect: true,              # auto-reconnect if the connection drops
  connect_timeout: 10,          # connection timeout in seconds
  read_timeout:    30,          # read timeout
  write_timeout:   30,          # write timeout
  ssl_mode: :verify_identity    # use SSL in production
)

puts "MySQL version: #{client.server_info[:version]}"

# Always close the connection when done
client.close

require 'mysql2'
require 'connection_pool'  # gem 'connection_pool'

# Create a pool with a maximum of 5 connections
POOL = ConnectionPool.new(size: 5, timeout: 5) do
  Mysql2::Client.new(
    host:      "localhost",
    username:  "root",
    password:  ENV["MYSQL_PASSWORD"],
    database:  "store_db",
    encoding:  "utf8mb4",
    reconnect: true
  )
end

# Use a connection from the pool
POOL.with do |client|
  result = client.query("SELECT COUNT(*) AS total FROM products")
  puts result.first["total"]
end

# Thread-safe — each thread gets a connection from the pool
10.times.map do
  Thread.new do
    POOL.with do |client|
      client.query("SELECT SLEEP(0.1)")
    end
  end
end.each(&:join)

require 'mysql2'

client = Mysql2::Client.new(host: "localhost", username: "root",
                            password: ENV["MYSQL_PASSWORD"], database: "store_db")

# Simple query — returns a Mysql2::Result (Enumerable)
result = client.query("SELECT * FROM products LIMIT 10")

# Iterate the result
result.each do |row|
  puts "#{row['id']}: #{row['name']} - Rp #{row['price']}"
end

# Convert to an Array of Hashes
products = client.query("SELECT id, name, price FROM products WHERE active = 1").to_a
puts products.first.inspect
# => {"id"=>1, "name"=>"Laptop", "price"=>15000000}

# The symbolize_keys option — keys as Symbols
result = client.query("SELECT * FROM products", symbolize_keys: true)
result.each { |r| puts r[:name] }

# The as: :array option — each row as an Array (faster)
result = client.query("SELECT id, name FROM products", as: :array)
result.each { |r| puts r.inspect }  # => [1, "Laptop"]

# Column metadata
puts result.fields.inspect   # => ["id", "name"]

# A single row
one = client.query("SELECT * FROM products WHERE id = 1").first
puts one["name"]   # => "Laptop"

# ANTI-PATTERN: string interpolation — vulnerable to SQL injection!
name = params[:name]
client.query("SELECT * FROM products WHERE name = '#{name}'")
# If name = "'; DROP TABLE products; --" → DISASTER!

# CORRECT: prepared statements with placeholders
stmt = client.prepare("SELECT * FROM products WHERE name = ? AND price < ?")
result = stmt.execute("Laptop", 20_000_000)
result.each { |r| puts r.inspect }
stmt.close

# Prepared statement for INSERT
stmt = client.prepare(
  "INSERT INTO products (name, price, stock, active) VALUES (?, ?, ?, ?)"
)
stmt.execute("Monitor", 3_500_000, 15, true)
new_id = client.last_id
puts "New product ID: #{new_id}"
stmt.close

# Prepared statement for UPDATE
stmt = client.prepare("UPDATE products SET price = ?, stock = ? WHERE id = ?")
rows_affected = stmt.execute(3_200_000, 20, new_id)
puts "Rows updated: #{client.affected_rows}"
stmt.close

# Prepared statement for DELETE
stmt = client.prepare("DELETE FROM products WHERE id = ? AND stock = 0")
stmt.execute(new_id)
stmt.close

class ProductRepository
  def initialize(client)
    @client = client
  end

  # CREATE
  def create(name:, price:, stock:, category_id:)
    stmt = @client.prepare(
      "INSERT INTO products (name, price, stock, category_id, active, created_at)
       VALUES (?, ?, ?, ?, TRUE, NOW())"
    )
    stmt.execute(name, price, stock, category_id)
    new_id = @client.last_id
    stmt.close
    find(new_id)
  end

  # READ
  def find(id)
    stmt = @client.prepare("SELECT * FROM products WHERE id = ?")
    result = stmt.execute(id)
    row = result.first
    stmt.close
    row
  end

  def find_all(active: true, limit: 50, offset: 0)
    stmt = @client.prepare(
      "SELECT p.*, c.name AS category
       FROM products p
       LEFT JOIN categories c ON p.category_id = c.id
       WHERE p.active = ?
       ORDER BY p.created_at DESC
       LIMIT ? OFFSET ?"
    )
    result = stmt.execute(active, limit, offset).to_a
    stmt.close
    result
  end

  def find_by_price(max:, min: 0)
    stmt = @client.prepare(
      "SELECT * FROM products
       WHERE price BETWEEN ? AND ? AND active = TRUE
       ORDER BY price ASC"
    )
    result = stmt.execute(min, max).to_a
    stmt.close
    result
  end

  # UPDATE
  def update(id, attributes = {})
    return false if attributes.empty?

    columns = attributes.keys.map { |k| "#{k} = ?" }.join(", ")
    values = attributes.values + [id]

    stmt = @client.prepare(
      "UPDATE products SET #{columns}, updated_at = NOW() WHERE id = ?"
    )
    stmt.execute(*values)
    rows = @client.affected_rows
    stmt.close
    rows > 0
  end

  # DELETE (soft delete — doesn't actually delete)
  def deactivate(id)
    stmt = @client.prepare(
      "UPDATE products SET active = FALSE, updated_at = NOW() WHERE id = ?"
    )
    stmt.execute(id)
    rows = @client.affected_rows
    stmt.close
    rows > 0
  end

  # Hard delete if truly necessary
  def delete!(id)
    stmt = @client.prepare("DELETE FROM products WHERE id = ?")
    stmt.execute(id)
    rows = @client.affected_rows
    stmt.close
    rows > 0
  end
end

def transfer_stock(from_id, to_id, amount)
  client.query("START TRANSACTION")

  begin
    # Check the source has enough stock
    stmt = client.prepare("SELECT stock FROM products WHERE id = ? FOR UPDATE")
    source = stmt.execute(from_id).first
    stmt.close

    raise "Insufficient stock" if source["stock"] < amount

    # Reduce the source stock
    stmt = client.prepare("UPDATE products SET stock = stock - ? WHERE id = ?")
    stmt.execute(amount, from_id)
    stmt.close

    # Increase the destination stock
    stmt = client.prepare("UPDATE products SET stock = stock + ? WHERE id = ?")
    stmt.execute(amount, to_id)
    stmt.close

    client.query("COMMIT")
    true

  rescue => e
    client.query("ROLLBACK")
    puts "Transaction rolled back: #{e.message}"
    false
  end
end

# A cleaner transaction helper
def with_transaction(client)
  client.query("START TRANSACTION")
  begin
    result = yield
    client.query("COMMIT")
    result
  rescue => e
    client.query("ROLLBACK")
    raise e
  end
end

with_transaction(client) do
  # all operations here
  client.query("UPDATE accounts SET balance = balance - 500000 WHERE id = 1")
  client.query("UPDATE accounts SET balance = balance + 500000 WHERE id = 2")
end

require 'sequel'

# Sequel connection to MySQL
DB = Sequel.connect(
  adapter:  "mysql2",
  host:     "localhost",
  user:     "root",
  password: ENV["MYSQL_PASSWORD"],
  database: "store_db",
  encoding: "utf8mb4",
  max_connections: 10
)

# Dataset — lazy, doesn't execute the query yet
products = DB[:products]

# SELECT
products.all                                    # SELECT * FROM products
products.where(active: true).all                # WHERE active = 1
products.where(price: 100_000..5_000_000).all   # WHERE price BETWEEN ...
products.where { price > 1_000_000 }.all        # WHERE price > 1000000
products.order(:price).limit(10).all            # ORDER BY price LIMIT 10
products.select(:id, :name, :price).all         # SELECT id, name, price

# JOIN
DB[:products]
  .join(:categories, id: :category_id)
  .select(Sequel[:products][:name], Sequel[:categories][:name].as(:category))
  .where(Sequel[:products][:active] => true)
  .all

# Aggregation
products.count                                  # SELECT COUNT(*) FROM products
products.where(active: true).sum(:price)        # SUM(price)
products.group(:category_id).select(:category_id, Sequel.function(:count).as(:total)).all

# INSERT
DB[:products].insert(
  name:       "Monitor",
  price:      3_500_000,
  stock:      15,
  category_id: 2,
  active:     true,
  created_at: Time.now
)
new_id = DB.last_insert_id

# UPDATE
DB[:products].where(id: new_id).update(price: 3_200_000, updated_at: Time.now)

# DELETE
DB[:products].where(id: new_id).delete

# Transactions with Sequel
DB.transaction do
  DB[:accounts].where(id: 1).update(balance: Sequel[:balance] - 500_000)
  DB[:accounts].where(id: 2).update(balance: Sequel[:balance] + 500_000)
  # If an exception is raised → automatic ROLLBACK
end

# Raw SQL when needed
DB.fetch("SELECT * FROM products WHERE name LIKE ?", "%Laptop%").all
DB.run("ALTER TABLE products ADD INDEX idx_price (price)")

# config/database.yml
default: &default
  adapter: mysql2
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: <%= ENV["DB_USERNAME"] || "root" %>
  password: <%= ENV["DB_PASSWORD"] %>
  host:     <%= ENV["DB_HOST"] || "localhost" %>
  port:     <%= ENV["DB_PORT"] || 3306 %>
  socket:   /tmp/mysql.sock
  reconnect: true

development:
  <<: *default
  database: store_development

test:
  <<: *default
  database: store_test

production:
  <<: *default
  database: <%= ENV["DB_NAME"] %>
  host:     <%= ENV["DB_HOST"] %>

# app/models/product.rb
class Product < ApplicationRecord
  belongs_to :category
  has_many   :order_items
  has_many   :orders, through: :order_items

  # Validations
  validates :name,  presence: true, length: { minimum: 2, maximum: 200 }
  validates :price, numericality: { greater_than: 0 }
  validates :stock, numericality: { greater_than_or_equal_to: 0 }

  # Scopes
  scope :active,     -> { where(active: true) }
  scope :affordable, -> { where("price < ?", 1_000_000) }
  scope :newest,     -> { order(created_at: :desc) }
  scope :in_price_range, ->(min, max) { where(price: min..max) }

  # Callbacks
  before_save :normalize_name
  after_create :log_new_product

  def available?
    stock > 0 && active?
  end

  def formatted_price
    "Rp #{price.to_s.reverse.gsub(/(\d{3})(?=\d)/, '\\1.').reverse}"
  end

  private

  def normalize_name
    self.name = name.strip.squeeze(" ")
  end

  def log_new_product
    Rails.logger.info "New product created: #{name} (ID: #{id})"
  end
end

# Queries with ActiveRecord
Product.all                                     # SELECT * FROM products
Product.active                                   # WHERE active = 1
Product.active.newest.limit(10)                  # WHERE active=1 ORDER BY created_at DESC LIMIT 10
Product.active.in_price_range(100_000, 5_000_000)  # WHERE active=1 AND price BETWEEN ...
Product.where(category_id: [1, 2, 3])            # WHERE category_id IN (1,2,3)
Product.where("name LIKE ?", "%#{keyword}%")     # LIKE with parameterized query
Product.includes(:category).active               # LEFT OUTER JOIN — eager loading
Product.joins(:category).where(categories: { name: "Electronics" })  # INNER JOIN

# Aggregation
Product.active.count              # SELECT COUNT(*) ...
Product.active.sum(:price)       # SELECT SUM(price) ...
Product.active.average(:price)   # SELECT AVG(price) ...
Product.active.maximum(:price)   # SELECT MAX(price) ...
Product.active.minimum(:price)   # SELECT MIN(price) ...
Product.group(:category_id).count  # GROUP BY with count

# Simple pagination
page = 1
per_page = 20
Product.active.limit(per_page).offset((page - 1) * per_page)

# db/migrate/20240815000000_create_products_table.rb
class CreateProductsTable < ActiveRecord::Migration[7.1]
  def change
    create_table :products do |t|
      t.string     :name,        null: false, limit: 200
      t.text       :description
      t.decimal    :price,       null: false, precision: 15, scale: 2
      t.integer    :stock,       null: false, default: 0
      t.boolean    :active,      null: false, default: true
      t.references :category,    null: false, foreign_key: true
      t.string     :sku,         limit: 50, unique: true
      t.string     :image_url

      t.timestamps   # created_at and updated_at automatically
    end

    # Indexes for frequently queried columns
    add_index :products, :active
    add_index :products, :price
    add_index :products, [:category_id, :active]   # composite index
    add_index :products, :sku, unique: true
    add_index :products, :name, type: :fulltext    # MySQL FULLTEXT index
  end
end

# Run migrations
# rails db:migrate
# rails db:rollback              # undo the last migration
# rails db:migrate:status        # check the status of all migrations

-- Without an index: full table scan
EXPLAIN SELECT * FROM products WHERE price < 1000000;
-- type: ALL → reads the entire table (slow!)

-- After add_index :products, :price
EXPLAIN SELECT * FROM products WHERE price < 1000000;
-- type: range → only reads relevant rows (fast!)

# Analyzing slow queries in Rails
# Enable query logging in development
# config/environments/development.rb:
# config.log_level = :debug

# Use EXPLAIN to analyze queries
ActiveRecord::Base.connection.execute(
  "EXPLAIN SELECT * FROM products WHERE category_id = 1 AND active = 1"
).each { |row| puts row.inspect }

# Or use the Bullet gem to detect N+1 queries
# gem 'bullet'
# Bullet will warn you about N+1 queries that could be avoided with includes

# Eager loading to avoid N+1
# ANTI-PATTERN: N+1 query
orders = Order.limit(10)
orders.each { |o| puts o.user.name }  # 1 query + 10 queries = 11 queries!

# CORRECT: eager loading
orders = Order.includes(:user).limit(10)
orders.each { |o| puts o.user.name }  # only 2 queries

# ANTI-PATTERN: loading all data into memory
Product.all.each { |p| process(p) }  # can crash with millions of rows!

# CORRECT: find_each — batch of 1000 rows
Product.active.find_each(batch_size: 1000) do |product|
  process(product)
end

# find_in_batches — get the batch as an array
Product.active.find_in_batches(batch_size: 500) do |batch|
  # batch is an Array of 500 Products
  # suitable for bulk insert or bulk update
  ids = batch.map(&:id)
  puts "Processing batch with #{batch.size} products, IDs #{ids.first}-#{ids.last}"
end

# in_batches — more flexible, returns a relation
Product.active.in_batches(of: 1000) do |batch_relation|
  batch_relation.update_all(batch_processed: true)  # one UPDATE per batch
end

# Raw SQL batch for maximum performance
offset = 0
batch_size = 1000
loop do
  rows = client.query(
    "SELECT * FROM products WHERE active = 1 LIMIT #{batch_size} OFFSET #{offset}"
  ).to_a
  break if rows.empty?
  process_batch(rows)
  offset += batch_size
  puts "Processed: #{offset} rows"
end

# SQL INJECTION — attack example
# User input: "'; DROP TABLE products; --"

# VULNERABLE — DON'T DO THIS
name_input = params[:name]
client.query("SELECT * FROM products WHERE name = '#{name_input}'")
# The query becomes: SELECT * FROM products WHERE name = ''; DROP TABLE products; --'

# SAFE 1: Prepared statements (mysql2)
stmt = client.prepare("SELECT * FROM products WHERE name = ?")
stmt.execute(name_input)

# SAFE 2: Manual escaping (less recommended)
safe_name = client.escape(name_input)
client.query("SELECT * FROM products WHERE name = '#{safe_name}'")

# SAFE 3: ActiveRecord parameterized queries
Product.where("name = ?", name_input)
Product.where(name: name_input)

# SAFE 4: Sequel parameterized
DB[:products].where(name: name_input)
DB[:products].where(Sequel.lit("name = ?", name_input))

# ANTI-PATTERN in ActiveRecord — still vulnerable!
Product.where("name = '#{params[:name]}'")  # DON'T!
Product.order(params[:column])               # DON'T! Can inject ORDER BY