Database Migrations

This document describes the database migration patterns used in Orcha.

Overview

Orcha uses Migratus for database migrations. Migrations can be:

• SQL-based: Simple .sql files for schema changes
• Clojure-based: .edn + .clj files for complex operations requiring logic

Running Migrations

bb migrate init           # Initialize migrations table (run once)
bb migrate migrate        # Run all pending migrations
bb migrate rollback       # Rollback last migration
bb migrate up <id>        # Run specific migration
bb migrate down <id>      # Rollback specific migration
bb migrate pending-list   # List pending migrations
bb migrate create "name"  # Create new migration files

From REPL

;; In dev/user.clj
(migratus {:command :migrate})
(migratus {:command :rollback})
(migratus {:command :create :args ["my-migration"]})

;; Full database reset (WARNING: destroys all data)
(reset-db!)

Migration Types

SQL Migrations

For simple schema changes, create a .sql file:

resources/migrations/YYYYMMDDHHMMSS-name.up.sql
resources/migrations/YYYYMMDDHHMMSS-name.down.sql

Example:

-- 20251222120000-add-user-email.up.sql
ALTER TABLE "user" ADD COLUMN email TEXT;

-- 20251222120000-add-user-email.down.sql
ALTER TABLE "user" DROP COLUMN email;

Clojure Migrations

For complex operations requiring logic, use .edn + .clj files:

resources/migrations/YYYYMMDDHHMMSS-name.edn
src/com/getorcha/db/migrations/name.clj

Clojure Migration Pattern

EDN Configuration

;; resources/migrations/20251221233007-development-seed.edn
{:ns      com.getorcha.db.migrations.development-seed
 :up-fn   migrate-up
 :down-fn migrate-down}

Clojure Handler

;; src/com/getorcha/db/migrations/development_seed.clj
(ns com.getorcha.db.migrations.development-seed
  (:require [com.getorcha.db.sql :as db.sql]
            [next.jdbc.result-set :as jdbc.rs]))

(defn migrate-up
  [{conn  :db
    :keys [with-seed-data?]
    :as   _config}]
  (when with-seed-data?
    ;; Note: migrations receive raw JDBC connection, need explicit builder-fn
    (let [jdbc-opts  {:return-keys true
                      :builder-fn  jdbc.rs/as-kebab-maps}
          tenant     (db.sql/execute-one!
                       conn
                       {:insert-into :tenant
                        :values      [{:name "Orcha" :slug "orcha"}]}
                       jdbc-opts)
          doc-source (db.sql/execute-one!
                       conn
                       {:insert-into :doc-source
                        :values      [{:tenant-id (:tenant/id tenant)
                                       :type      (db.sql/->cast "webhook" :doc-source-type)}]}
                       jdbc-opts)]
      ;; ... additional inserts
      )))

(defn migrate-down
  [{conn  :db
    :keys [with-seed-data?]
    :as   _config}]
  (when with-seed-data?
    (db.sql/execute! conn
      {:delete-from :doc-source
       :where [:in :id {:select [:ds.id]
                        :from   [[:doc-source :ds]]
                        :join   [[:tenant :t] [:= :ds.tenant-id :t.id]]
                        :where  [:= :t.slug "orcha"]}]})
    (db.sql/execute! conn
      {:delete-from :tenant
       :where [:= :slug "orcha"]})))

Migration Config

Migratus configuration is nested under :migrations-config in the db pool config at resources/com/getorcha/config.edn:

:com.getorcha.db/pool
{:credentials-json #ref [:com.getorcha/db-credentials-json]
 :migrations-config
 {:store                :database
  :migration-dir        "migrations/"
  :init-script          "init.sql"
  :migration-table-name "schema_migrations"
  :db-credentials-json  #ref [:com.getorcha/db-credentials-json]
  :with-seed-data?      #profile {:local-dev true :test true :default false}}
 ;; ... pool settings
}

Config Keys Passed to Migrations

Init Script

The init.sql script runs once when bb migrate init is called. It creates:

• PostgreSQL extensions
• Enum types
• All tables
• Indexes
• Triggers

Located at: resources/migrations/init.sql

Best Practices

File Naming

• Use underscores in Clojure filenames: development_seed.clj (not development-seed.clj)
• Namespace uses hyphens: com.getorcha.db.migrations.development-seed

Conditional Logic

Use config flags like :with-seed-data? for environment-specific behavior:

(defn migrate-up [{conn :db :keys [with-seed-data?]}]
  (when with-seed-data?
    ;; Only runs in local-dev and test profiles
    (db.sql/execute-one! conn
      {:insert-into :tenant
       :values      [{:name "Test" :slug "test"}]})))

Transactions

Migratus wraps each migration in a transaction. If any statement fails, the entire migration is rolled back.

Enum Casting

Use db.sql/->cast helper which converts kebab-case to snake_case automatically:

(db.sql/->cast "webhook" :doc-source-type)
;; => [:cast "webhook" :doc_source_type]

Schema Version Check

The application checks for pending migrations on startup. If migrations are pending, it fails fast:

;; In com.getorcha.db/pool init-key
(let [pending (migratus/pending-list migrations-config)]
  (when (seq pending)
    (throw (ex-info "Database schema out of sync - pending migrations exist"
                    {:pending-count (count pending)
                     :pending-ids   (mapv :id pending)}))))

This ensures the application never runs against an outdated schema.