Database Migrations

Database Migrations

Merq uses Goose for SQL-based database migrations. Goose provides versioned, reversible migrations that are safe to run in production.

Overview

Tool	Purpose
Goose (cmd/goose)	Primary migration tool — SQL files, versioned, production-safe
GORM AutoMigrate (cmd/migrator)	Legacy dev tool — kept for local schema sync only, never for production

Migration files live in merq-backend/migrations/ and are plain SQL with Goose markers.

Migration Files

merq-backend/
└── migrations/
    ├── 00001_baseline.sql   # Full schema baseline (65 tables)
    └── 000XX_<name>.sql     # Subsequent migrations

File Naming

Files follow the pattern: {version}_{description}.sql

• version — zero-padded integer (e.g. 00001, 00002)
• description — snake_case description of the change

File Structure

Each migration file has Up and Down sections:

-- +goose Up
-- SQL to apply the migration
CREATE TABLE IF NOT EXISTS example (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    workspace_id BIGINT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- +goose Down
-- SQL to reverse the migration
DROP TABLE IF EXISTS example;

Running Migrations

Via Scripts (recommended for local dev)

cd merq-backend

# Run all pending migrations
./scripts/migrate.sh up

# Check migration status
./scripts/migrate.sh status

# Rollback last migration
./scripts/migrate.sh down

# Rollback all migrations
./scripts/migrate.sh down-to 0

# Re-apply last migration
./scripts/migrate.sh redo

# Use a specific env file
./scripts/migrate.sh --env=.env.development up

Via Makefile

make goose-up          # Run pending migrations
make goose-down        # Rollback last migration
make goose-status      # Show migration status
make goose-reset       # Rollback ALL migrations
make goose-redo        # Rollback and re-apply last

Via Binary (production/Docker)

The main_goose binary is built into the Docker image and used by entrypoint.sh:

./main_goose -dir ./migrations up

Creating a New Migration

# Using the script
./scripts/migrate.sh create add_column_to_outlets

# Using make
make goose-create name=add_column_to_outlets

This creates migrations/000XX_add_column_to_outlets.sql with empty Up/Down stubs.

Migration Guidelines

• Always use IF NOT EXISTS / IF EXISTS guards for DDL
• Always provide a Down section that fully reverses the Up
• For ALTER TABLE, the Down should restore the original state
• Never modify an already-applied migration — create a new one instead
• Keep each migration focused on one logical change

-- +goose Up
ALTER TABLE outlets ADD COLUMN IF NOT EXISTS category TEXT;
CREATE INDEX IF NOT EXISTS idx_outlets_category ON outlets(category);

-- +goose Down
DROP INDEX IF EXISTS idx_outlets_category;
ALTER TABLE outlets DROP COLUMN IF EXISTS category;

Production Deployment

Migrations are controlled by environment variables in Dokploy:

Variable	Description	Default
RUN_MIGRATION	Run goose up on container startup	false
RUN_AUTO_MIGRATION	Run GORM AutoMigrate (dev/emergency only)	false

Set RUN_MIGRATION=true when deploying a release that includes schema changes. Goose is idempotent — if there are no pending migrations, it exits cleanly without doing anything.

Startup Flow

Container starts
  └── RUN_MIGRATION=true?
        ├── Yes → ./main_goose -dir ./migrations up
        │           └── applies only new/pending migrations
        └── No  → skip migrations
  └── RUN_AUTO_MIGRATION=true?
        ├── Yes → ./main_migrator (GORM AutoMigrate — dev/emergency)
        └── No  → skip
  └── RUN_SEEDER=true?
        ├── Yes → ./main_seeder
        └── No  → skip
  └── exec ./main_server

Baseline Migration

00001_baseline.sql captures the full schema at the point goose was introduced (65 tables). It includes:

• All CREATE TABLE IF NOT EXISTS statements
• All sequences and indexes
• The seeder_history table (for seeder idempotency)
• A complete Down section with DROP TABLE IF EXISTS ... CASCADE for all tables

If you’re setting up a fresh database, running goose up will apply the baseline and create the full schema. If you’re running against an existing production database that already has the schema, you need to mark the baseline as applied without running it:

# Mark baseline as applied without running SQL
./scripts/migrate.sh --env=.env.production mark-applied 00001
# or
go run cmd/goose/main.go -dir migrations mark-applied 1

Related Documentation

• Dev Scripts — scripts/migrate.sh usage
• Environment Variables — RUN_MIGRATION, RUN_AUTO_MIGRATION
• Dokploy Deployment — production deployment flow
• Database Schema — table definitions