V2.2: Migrations

Migrations are Ruby files that provide syntax for generating Data Definition Language for the ROM::SQL adapter. They are generated by the Hanami command line, and provided by the Sequel library.

Migration filenames are defined in snake case and prefaced by a numeric timestamp of when they were created. This allows for roughly sequential execution without the likelihood of collisions from sequential integers when multiple people are creating them at the same time.

They are located in the config/db/migrate directory.

$ tree config/db/migrate
config/db/migrate
├── 20240717170227_create_posts.rb
└── 20240717170318_add_published_at_to_posts.rb

Direction

Migration files are bi-directional, they define schema changes forward and backward, or up and down in Sequel’s syntax. This is important, in case your migration changes cause a problem you will want to roll them back as quickly as possible, and requiring a fresh migration to do this may take too much time.

ROM::SQL.migration do
  change do
    create_table :users do
      primary_key :id
      foreign_key :account_id, :accounts, on_delete: :cascade, null: false

      column :given_name, String, null: false
      column :family_name, String, null: false
      column :email, "citext", null: false
    end
  end
end

In the example above, we used the change method to define our migration. This is written in the style of an up migration, and the down version is inferred by Sequel.

Sometimes, this cannot be automatically inferred. You will need to provide explicit up and down definitions in this case.

ROM::SQL.migration do
  up do
    alter_table :users do
      add_unique_constraint [:email], name: :users_email_uniq
    end
  end

  down do
    alter_table :users do
      drop_constraint :users_email_uniq
    end
  end
end

Transactions

The majority of migrations are run within a transaction, so that DDL errors trigger a rollback and don’t leave you in a partial state. But certain operations don’t support running inside a transaction.

Within a migration block, no_transaction tells the migrator to run the migration without first starting a transaction.

ROM::SQL.migration do
  no_transaction

  up do
    alter_table :users do
      add_index :email, concurrently: true
    end
  end

  down do 
    alter_table :users do
      drop_index :email, concurrently: true
    end
  end
end

Syntax

Sequel migration syntax provides some flexibility in how you may choose to represent your table columns.

create_table :users do
  # column method, explicit SQL type
  column :email, "varchar(255)", null: false

  # column method, inferred SQL type: varchar(255)
  column :email, String, null: false

  # helper method, no inference, SQL type: text
  text :email, null: false

  # Ruby type method, inferred SQL type: varchar(255)
  String :email, null: false
end

Sequel also provides Ruby syntax for defining logical pieces, such as constraints

create_table :users do
  primary_key :id
  column :name, String, null: false
  constraint(:name_min_length) { char_length(name) > 2 }
end

Read more at Sequel: Schema modification methods

Ruby syntax is not a requirement, however, and sometimes what you are doing is not easy or possible to express in Sequel’s DSL. In those cases, execute acts as an escape-hatch into raw SQL. The drawback of execute is that it cannot infer how to reverse your changes, so you will have to provide explicit up and down migrations.

ROM::SQL.migration do
  up do
    execute <<~SQL
        CREATE TRIGGER posts_tsvector_update()
        BEFORE INSERT OR UPDATE ON public.posts
        FOR EACH ROW
        WHEN (
          OLD.title IS DISTINCT FROM NEW.title OR
          OLD.content IS DISTINCT FROM NEW.content
        )
        EXECUTE PROCEDURE tsvector_update_trigger(search_tsvector, 'public.english', title, content)
    SQL
  end

  down do
    execute "DROP TRIGGER posts_tsvector_update() ON public.posts"
  end
end

In this example, we’re storing vector information for PostgreSQL’s text search in a column, automatically built by a trigger.

Database Structure

When adding migrations to your project, it’s useful to maintain a record of what the current state of your database structure was at the time.

This serves three purposes:

1. Spot-check your schema changes to ensure you’re doing what you intended
2. Provide a simple way to connect the structure of your database to a specific code change
3. A blank-slate for setting up a development or test database without running all migrations sequentially

Hanami provides this in the form of config/db/structure.sql. The choice of using plain SQL to reflect your DB structure gives you maximum flexibility in using the most powerful features of your database.