How to Use MySQL Database with Ruby on Rails

When I built my first production Rails application, I defaulted to SQLite because that’s what the generators set up out of the box. It worked fine for development, but the moment I deployed to a real multi-user environment, I needed something built for concurrent writes and real production traffic — so I moved to MySQL. Since then, I’ve configured MySQL with Rails on more projects than I can count, and in this guide, I’ll walk through the full setup, from configuration to advanced ActiveRecord patterns.

Why Pair Rails with MySQL?

Rails is database-agnostic by design, thanks to ActiveRecord’s adapter architecture, but I choose MySQL specifically when a project needs:

• Mature replication support for read-scaling.
• Wide hosting support (nearly every managed database provider offers MySQL).
• A team that’s already comfortable with MySQL operationally.
• Strong performance for typical web-application read/write patterns with InnoDB’s row-level locking.

Architecture: How Rails Talks to MySQL

graph TD
    A[Rails Application] --> B[ActiveRecord ORM]
    B --> C[mysql2 Gem - C Extension Driver]
    C --> D[MySQL Client Library - libmysqlclient]
    D --> E[TCP/Socket Connection]
    E --> F[MySQL Server - mysqld]
    F --> G[InnoDB Storage Engine]
    G --> H[(Data Files)]

Rails never talks to MySQL directly — it goes through ActiveRecord, which delegates to a database adapter gem (mysql2 almost universally today), which in turn wraps the native MySQL client library. Understanding this chain matters when debugging connection issues, since a problem could originate at any layer: your Rails config, the gem, the underlying C library, or the network path to the actual server.

Installing the mysql2 Gem

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

bundle install

On Linux, I first make sure the MySQL client development headers are installed, since mysql2 compiles a native extension:

sudo apt-get install default-libmysqlclient-dev build-essential

On macOS:

brew install mysql
bundle install

Configuring database.yml

# config/database.yml
default: &default
  adapter: mysql2
  encoding: utf8mb4
  collation: utf8mb4_unicode_ci
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: rails_app
  password: <%= ENV["DATABASE_PASSWORD"] %>
  host: <%= ENV.fetch("DATABASE_HOST") { "localhost" } %>

development:
  <<: *default
  database: myapp_development

test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: myapp_production
  username: <%= ENV["PRODUCTION_DB_USER"] %>
  password: <%= ENV["PRODUCTION_DB_PASSWORD"] %>
  host: <%= ENV["PRODUCTION_DB_HOST"] %>

I always explicitly set encoding: utf8mb4 (not the older utf8, which in MySQL is limited to 3 bytes per character and can’t store full Unicode, including emoji) and a modern collation. Retrofitting this after a database already has data in the wrong charset is genuinely painful, so I get it right from day one.

Creating and Migrating the Database

rails db:create
rails db:migrate

A typical migration:

class CreateArticles < ActiveRecord::Migration[7.1]
  def change
    create_table :articles do |t|
      t.string :title, null: false
      t.text :body
      t.references :user, null: false, foreign_key: true
      t.timestamps
    end

    add_index :articles, :title
  end
end

Running it:

rails db:migrate

== 20260715120000 CreateArticles: migrating ==================================
-- create_table(:articles)
   -> 0.0234s
-- add_index(:articles, :title)
   -> 0.0089s
== 20260715120000 CreateArticles: migrated (0.0335s) ==========================

Working with ActiveRecord Against MySQL

Basic model:

class Article < ApplicationRecord
  belongs_to :user
  validates :title, presence: true
end

Querying:

Article.where(user_id: 5).order(created_at: :desc).limit(10)

Generated SQL (visible via .to_sql or the Rails log):

SELECT `articles`.* FROM `articles`
WHERE `articles`.`user_id` = 5
ORDER BY `articles`.`created_at` DESC
LIMIT 10;

I check .to_sql constantly during development to make sure ActiveRecord is generating the query I expect, especially for anything involving joins or subqueries:

Article.joins(:user).where(users: { active: true }).to_sql

Using MySQL-Specific Features from Rails

JSON Columns

class AddAttributesToProducts < ActiveRecord::Migration[7.1]
  def change
    add_column :products, :attributes, :json
  end
end

product = Product.create!(name: "Wireless Mouse", attributes: { color: "black", wireless: true })
product.attributes_will_change! if product.attributes["color"] # ActiveRecord treats it as a Hash

Querying JSON fields (raw SQL, since ActiveRecord’s query interface doesn’t have full native JSON path support):

Product.where("attributes->>'$.color' = ?", "black")

Full-Text Search

ActiveRecord doesn’t have a first-class MATCH...AGAINST helper, so I drop to raw SQL fragments:

Article.where("MATCH(title, body) AGAINST(? IN BOOLEAN MODE)", "+rails +mysql")

I make sure the corresponding migration actually creates the FULLTEXT index:

add_index :articles, [:title, :body], type: :fulltext, name: 'ft_index_articles'

Connection Pooling

Rails’ connection pool size (pool: in database.yml) should generally match your web server’s concurrency model. For Puma with multiple threads per worker:

production:
  <<: *default
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

I always ensure RAILS_MAX_THREADS matches Puma’s thread count, since a mismatch (Puma configured for more threads than the DB pool allows) causes ActiveRecord::ConnectionTimeoutError under load — a mistake I’ve debugged in production more than once before making this check part of my deployment checklist.

Handling Migrations Safely in Production

Large table migrations are one of the riskiest parts of running Rails with MySQL at scale. A few practices I follow:

1. Avoid change_column on huge tables without strong_migrations or similar tooling — MySQL may need to rewrite the whole table, causing long locks.
2. Add columns as nullable first, backfill data in a background job, then add NOT NULL in a separate migration.
3. Use algorithm: :inplace hints via strong_migrations gem to catch unsafe migrations before they hit production.

class AddStatusToOrders < ActiveRecord::Migration[7.1]
  def change
    add_column :orders, :status, :string # nullable first
  end
end

# Separate migration, after backfill:
class AddNotNullToOrdersStatus < ActiveRecord::Migration[7.1]
  def change
    change_column_null :orders, :status, false
  end
end

Real-World Scenario: Read Replicas with Rails

For a high-traffic application, I configured Rails’ built-in multiple-database support to route reads to a MySQL replica:

production:
  primary:
    <<: *default
    database: myapp_production
    host: primary-db.internal

  primary_replica:
    <<: *default
    database: myapp_production
    host: replica-db.internal
    replica: true

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
  connects_to database: { writing: :primary, reading: :primary_replica }
end

Rails automatically routes reads within a ActiveRecord::Base.connected_to(role: :reading) block (or automatically after writes, using its built-in read/write splitting) to the replica, reducing load on the primary — something I’ve used to squeeze meaningfully more headroom out of an existing MySQL instance before resorting to sharding.

Testing Considerations

I use transactional fixtures/tests (Rails’ default) so that each test runs inside a transaction that’s rolled back afterward — this keeps the MySQL test database clean without needing to truncate tables between every test:

# test_helper.rb / rails_helper.rb
config.use_transactional_fixtures = true

For system tests using JavaScript drivers (Capybara + a real browser), transactional fixtures don’t work across separate connections/threads, so I use database_cleaner with the :truncation strategy specifically for those tests.

Performance Tips

1. Use .includes to avoid N+1 queries — a classic ActiveRecord pitfall that becomes an obvious MySQL performance problem under load:

# Bad: N+1 queries
Article.all.each { |a| puts a.user.name }

# Good: 2 queries total
Article.includes(:user).each { |a| puts a.user.name }

2. Add indexes for every foreign key and frequently filtered column — Rails’ t.references adds an index automatically, but manually added foreign key columns need explicit add_index calls.
3. Use .pluck instead of loading full ActiveRecord objects when you only need specific columns:

Article.where(user_id: 5).pluck(:id, :title)

4. Batch large updates with find_each / in_batches to avoid loading huge result sets into memory and generating oversized transactions:

Article.where(archived: false).in_batches(of: 1000) do |batch|
  batch.update_all(archived: true)
end

Security Considerations

• Always use parameterized queries (where("title = ?", value) or the hash form where(title: value)) — never string-interpolate user input directly into SQL fragments, which reopens SQL injection risk even inside an ORM.
• Store database credentials in Rails encrypted credentials (rails credentials:edit) or environment variables, never committed in database.yml directly.
• Use a MySQL user scoped to only the privileges the Rails app actually needs (typically SELECT, INSERT, UPDATE, DELETE — not DROP or GRANT), separate from the account used for running migrations in CI/CD.

Common Mistakes I See with Rails + MySQL

A handful of issues I’ve run into repeatedly across projects:

1. Leaving database.yml on utf8 instead of utf8mb4. This is almost always fine until someone tries to save an emoji or certain international characters, at which point it throws an Incorrect string value error in production, sometimes months after launch.
2. Under-provisioning the connection pool. A mismatch between RAILS_MAX_THREADS and the pool: setting in database.yml causes intermittent ActiveRecord::ConnectionTimeoutError under load that can be maddening to reproduce locally with a single-threaded development server.
3. Running large, locking migrations during peak traffic. Adding an index or changing a column type on a multi-million-row table can lock it for longer than expected; I schedule these during low-traffic windows or use online schema-change tooling.
4. Relying entirely on ActiveRecord validations for data integrity. Validations run in Ruby and can be bypassed by direct SQL, bulk imports, or race conditions between concurrent requests. I still add real database-level constraints (NOT NULL, unique indexes, foreign keys) as the actual source of truth.
5. Not eager loading associations in serializers or views. N+1 queries are one of the most common Rails performance issues, and they’re often invisible in development with a small dataset, only surfacing once a production table grows large enough to make each individual query noticeably slower.

Troubleshooting Common Issues

Interview Questions on Rails + MySQL

1. What gem does Rails typically use to connect to MySQL, and what does it wrap? The mysql2 gem, which wraps the native libmysqlclient C library for performance.
2. Why is utf8mb4 preferred over utf8 in MySQL for a Rails app? MySQL’s utf8 charset only supports up to 3 bytes per character and cannot store full Unicode (including emoji); utf8mb4 supports the full 4-byte UTF-8 range.
3. How does Rails avoid N+1 queries? Through eager loading via .includes, .preload, or .eager_load, which batch-load associated records in fewer queries.
4. How would you safely add a NOT NULL column to a large, actively-used table? Add the column as nullable first, backfill data in batches via a background job, then add the NOT NULL constraint in a subsequent migration.
5. How does Rails route reads to a MySQL replica? Via connects_to database: { writing: :primary, reading: :primary_replica } combined with connected_to(role: :reading) or Rails’ automatic read/write splitting.

Frequently Asked Questions

Q: Can I switch an existing Rails app from SQLite or PostgreSQL to MySQL? A: Yes, though you’ll need to review any database-specific SQL (like PostgreSQL-specific array types or full-text search syntax) and adjust database.yml and the adapter gem accordingly; a full data migration tool is usually needed for existing data.

Q: Does Rails support MySQL-specific column types like JSON or ENUM? A: Yes — t.json maps to MySQL’s native JSON type, and raw SQL migrations can define ENUM columns, though ActiveRecord’s enum feature is more commonly implemented at the application layer with an integer column instead.

Q: Is mysql2 the only adapter option? A: It’s the dominant choice today; there are also alternatives like trilogy (a newer pure-Ruby-friendly client gaining adoption), which I’ve started testing on some projects for simpler deployment without native compilation.

Q: How do I run raw SQL directly from Rails when ActiveRecord’s query interface isn’t enough? A: ActiveRecord::Base.connection.execute("SELECT ...") or ApplicationRecord.connection.select_all(sql), though I reserve this for cases the query interface genuinely can’t express cleanly.

Summary and Key Takeaways

Rails and MySQL are a proven, production-tested combination, and ActiveRecord does a good job abstracting most day-to-day database work — but understanding what’s happening underneath (charset choices, connection pooling, N+1 queries, migration locking behavior) is what separates an app that merely works from one that holds up under real production load.

Key takeaways:

• Always configure utf8mb4 from the start to avoid painful charset migrations later.
• Match your connection pool size to your actual web server concurrency.
• Use .includes diligently to avoid N+1 query patterns that MySQL will happily let you create.
• Handle large-table migrations carefully — nullable-first, backfill, then constrain.
• Consider read replicas via Rails’ multiple-database support before reaching for more complex scaling solutions.

References

• Ruby on Rails Guides: Configuring Rails Applications (Database)
• Ruby on Rails Guides: Active Record Multiple Databases
• MySQL Reference Manual: Charset utf8mb4
• mysql2 Gem Documentation