Rails Mastery: Automating Database Transformations

JSON to Rails Migration: Active Record, JSONB, and Production-Safe Schema Changes

Rails migrations are versioned Ruby files that use the Active Record DSL to evolve your database schema. They generate different SQL depending on your adapter (PostgreSQL, MySQL, SQLite) — the same t.decimal becomes NUMERIC on Postgres and DECIMAL on MySQL. Converting JSON to a Rails migration requires understanding which column types Rails maps to which SQL, how to use t.jsonb for queryable structured data on PostgreSQL, how to enforce NOT NULL with null: false, and when to use structure.sql instead of schema.rb for features that Rails' schema dumper can't represent.

Live Example: Complete E-commerce Migration

# Input JSON
{
  "sku": "WIDGET-PRO-L",
  "name": "Widget Pro (Large)",
  "price": 149.99,
  "stock_count": 250,
  "category": "hardware",
  "is_available": true,
  "attributes": { "color": "black", "weight_kg": 1.2 },
  "published_at": "2024-01-15T08:30:00Z"
}

# Generated migration — db/migrate/20240101000001_create_products.rb
class CreateProducts < ActiveRecord::Migration[7.1]
  def change
    create_table :products do |t|
      t.string  :sku,          null: false, limit: 64
      t.string  :name,         null: false, limit: 200
      t.decimal :price,        null: false, precision: 10, scale: 2
      t.integer :stock_count,  null: false, default: 0
      t.string  :category,     null: false, limit: 30
      t.boolean :is_available, null: false, default: true
      t.jsonb   :attributes,   default: {}   # PostgreSQL JSONB
      t.datetime :published_at
      t.references :brand, null: true, foreign_key: true  # adds brand_id + FK constraint

      t.timestamps  # adds created_at, updated_at NOT NULL
    end

    add_index :products, :sku, unique: true
    add_index :products, [:category, :is_available],
              name: 'idx_products_category_available'
    add_index :products, :published_at
    # GIN index for JSONB containment queries (@>)
    add_index :products, :attributes, using: :gin,
              name: 'idx_products_attributes_gin'
  end
end

Using null: false adds the NOT NULL constraint at the database level — not just application validation. The database rejects NULL inserts even if your Rails validation is bypassed. t.jsonb is PostgreSQL-specific and enables @> containment queries with the GIN index.

Active Record Model: Querying JSONB

# app/models/product.rb
class Product < ApplicationRecord
  belongs_to :brand, optional: true
  has_many   :order_lines
  has_many   :orders, through: :order_lines

  # Validations
  validates :sku,      presence: true, uniqueness: true, length: { maximum: 64 }
  validates :name,     presence: true
  validates :price,    presence: true, numericality: { greater_than: 0 }
  validates :category, inclusion: { in: %w[hardware software services accessory] }

  # Scopes
  scope :available,      -> { where(is_available: true) }
  scope :in_category,    ->(cat) { where(category: cat) }
  scope :published,      -> { where.not(published_at: nil).where('published_at <= ?', Time.current) }

  # JSONB querying (PostgreSQL) — works with GIN index
  scope :with_color, ->(color) {
    where("attributes @> ?", { color: color }.to_json)
  }

  # Access JSONB as a hash
  def primary_color
    attributes_json = read_attribute(:attributes)
    attributes_json&.dig('color') || 'unknown'
  end
end

# Query examples
Product.available.in_category('hardware').order(price: :asc)

# JSONB containment — uses GIN index
Product.where("attributes @> ?", { color: 'black' }.to_json)

# JSONB key access
Product.where("attributes ->> 'color' = ?", 'black')   # ->> returns text
Product.where("(attributes ->> 'weight_kg')::float > ?", 1.0)

# ANY in JSONB array
# attributes = { "tags": ["featured", "sale"] }
Product.where("attributes -> 'tags' ? ?", 'featured')  # key exists in array

Adding Columns to an Existing Table

# New migration — never edit existing migration files after they've run
# db/migrate/20240301000001_add_description_to_products.rb

class AddDescriptionToProducts < ActiveRecord::Migration[7.1]
  def change
    add_column :products, :description, :text
    add_column :products, :meta_title,  :string, limit: 100
    add_column :products, :tags,        :jsonb,  default: []

    # Add index to new column
    add_index :products, :tags, using: :gin

    # Safe: add NOT NULL with default first, then remove default if needed
    add_column :products, :view_count, :integer, null: false, default: 0
  end
end

Changing Columns: Safe vs Unsafe

# SAFE: adding a nullable column or a column with default
add_column :users, :avatar_url, :string

# SAFE: adding an index
add_index :products, :category

# UNSAFE on large tables (locks the table):
# - change_column (requires full rewrite on some DBs)
# - add NOT NULL column without default
# - add unique index without CONCURRENTLY

# Safe approach: add NOT NULL to large table
# 1. Add nullable first
add_column :products, :slug, :string

# 2. Backfill in a separate migration or rake task
Product.find_each do |product|
  product.update_column(:slug, product.name.parameterize + "-" + product.sku.downcase)
end

# 3. Add NOT NULL in a third migration (after backfill confirms 0 nulls)
change_column_null :products, :slug, false
add_index :products, :slug, unique: true

# PostgreSQL: create index without table lock
# Add to your migration:
disable_ddl_transaction!
add_index :products, :email, algorithm: :concurrently

structure.sql vs schema.rb

# config/application.rb
config.active_record.schema_format = :sql  # use structure.sql instead of schema.rb

# When to use structure.sql:
# - You use PostgreSQL-specific features: JSONB, partial indexes, GIN indexes,
#   custom types, functions, triggers, extensions (uuid-ossp, pgcrypto)
# - You have CHECK constraints
# - You have custom sequences
#
# schema.rb only captures what Rails knows how to represent.
# structure.sql is a verbatim pg_dump — it captures everything.

# CHECK constraint (requires structure.sql to be preserved in dump)
class AddAgeConstraint < ActiveRecord::Migration[7.1]
  def up
    execute <<-SQL
      ALTER TABLE users
        ADD CONSTRAINT age_must_be_positive CHECK (age IS NULL OR age >= 0);
    SQL
  end

  def down
    execute "ALTER TABLE users DROP CONSTRAINT age_must_be_positive"
  end
end

Polymorphic Associations

# A Comment can belong to Post or Product
class CreateComments < ActiveRecord::Migration[7.1]
  def change
    create_table :comments do |t|
      t.references :commentable, polymorphic: true, null: false
      # ^ adds commentable_id :bigint, commentable_type :string
      # ^ adds composite index on [commentable_type, commentable_id]
      t.references :user, null: false, foreign_key: true
      t.text :body, null: false
      t.timestamps
    end
  end
end

# model
class Comment < ApplicationRecord
  belongs_to :commentable, polymorphic: true
  belongs_to :user
end

class Product < ApplicationRecord
  has_many :comments, as: :commentable
end

N+1 Prevention: includes vs joins

# N+1: 1 query + N queries for associations
products = Product.all
products.each { |p| puts p.brand.name }   # N queries to brands

# includes: separate queries + Ruby merge (best for deep nesting or rendering)
products = Product.includes(:brand, order_lines: :order)

# joins: SQL JOIN (best for filtering on associations)
# Only select products that HAVE an order
products = Product.joins(:order_lines)
                  .where(order_lines: { status: 'pending' })
                  .distinct

# eager_load: LEFT OUTER JOIN + merges in Ruby (like includes but single query)
products = Product.eager_load(:brand)
                  .where(brands: { country: 'US' })

# preload: always separate queries regardless of where()
Product.preload(:brand)

Best Practices for Production

• Always use null: false for required columns: Application-layer validations can be bypassed (by seeds, background jobs, console operations). Database-level NOT NULL is the real guarantee. Add null: false to every column that your business logic requires to exist.
• Use precision: 10, scale: 2 for money: t.float uses IEEE 754 floating point, which cannot exactly represent 0.10 or 0.20. t.decimal(precision: 10, scale: 2) maps to NUMERIC — exact arithmetic. For high-value financial data, use precision: 19, scale: 4.
• Use algorithm: :concurrently for production indexes: Regular add_index locks the table for the duration of index building. On large tables (>100k rows), use algorithm: :concurrently with disable_ddl_transaction! to allow reads and writes during index creation.
• Check migration status before deploying: Run bin/rails db:migrate:status to see which migrations are pending. A deployment that runs db:migrate will apply all pending migrations in order — verify this is what you want before deploying.

FAQ

Q: When should I use t.jsonb vs separate columns?
A: t.jsonb (PostgreSQL) is right for flexible or schema-less attributes — product metadata, event payloads, user preferences. Use separate typed columns when you need to sort, filter frequently, or enforce business constraints. A GIN index on JSONB enables fast containment queries, but it's slower than a B-tree index on a dedicated column for equality queries.

Q: What's the difference between db:schema:load and db:migrate?
A: db:migrate runs all pending migrations in order — safe for production. db:schema:load loads schema.rb (or structure.sql) directly, recreating the database from scratch without running individual migrations — much faster for setting up a new development or CI database, but never safe for production because it drops and recreates the database.

Q: How do I roll back a specific migration?
A: bin/rails db:migrate:down VERSION=20240101000001 rolls back a specific migration. bin/rails db:rollback STEP=3 rolls back the last 3 migrations. The down method in your migration handles the rollback — if you don't have one, use a reversible block in change, or separate up and down methods.

Q: Should I use UUID or integer primary keys?
A: Integer (bigint) primary keys are the Rails default — faster for joins and index scans, easy to sort. UUIDs (t.uuid :id, primary_key: true, default: 'gen_random_uuid()' on PostgreSQL) are better when IDs appear in URLs (non-enumerable, non-guessable) or when you need to generate IDs in the application before the DB insert (useful for distributed systems or offline-first apps).