seedup

A CLI tool and Go library for managing PostgreSQL database migrations and seed data. Uses goose for migrations and provides utilities for creating and applying seed data.

Installation

CLI Tool

go install github.com/lucasefe/seedup/cmd/seedup@latest

Go Library

go get github.com/lucasefe/seedup

Requirements

System Requirements

• git - For the check command (CI validation)

Quick Start

CLI Usage

# Set your database URL
export DATABASE_URL="postgres://user:pass@localhost/mydb"

# Create your first migration
seedup migrate create create_users_table

# Edit the migration file, then run it
seedup migrate up

# Check migration status
seedup migrate status

Go Library Usage

package main

import (
    "context"
    "log"

    "github.com/lucasefe/seedup"
)

func main() {
    ctx := context.Background()
    dbURL := "postgres://user:pass@localhost/mydb"

    // Run migrations
    if err := seedup.MigrateUp(ctx, dbURL, "./migrations"); err != nil {
        log.Fatal(err)
    }

    // Generate DBML documentation
    dbml, err := seedup.GenerateDBML(ctx, dbURL, seedup.DBMLOptions{
        ExcludeTables: []string{"goose_db_version"},
    })
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(dbml)
}

Go Library API

The seedup package exposes a clean public API for programmatic use.

CLI Passthrough (Embedding in Other Binaries)

Use Run or RunArgs to execute seedup commands programmatically with the same interface as the CLI:

package main

import (
    "log"
    "os"

    "github.com/lucasefe/seedup"
)

func main() {
    // Forward CLI args: otherbinary seedup <args...>
    if len(os.Args) > 1 && os.Args[1] == "seedup" {
        if err := seedup.RunArgs(os.Args[2:]...); err != nil {
            log.Fatal(err)
        }
        return
    }

    // Or run commands directly
    if err := seedup.Run("migrate up -d postgres://localhost/mydb"); err != nil {
        log.Fatal(err)
    }
}

Environment variables (DATABASE_URL, MIGRATIONS_DIR, SEED_DIR) are read from the current process environment.

Migration Functions

// Run all pending migrations
seedup.MigrateUp(ctx, dbURL, migrationsDir)

// Run a single pending migration
seedup.MigrateUpByOne(ctx, dbURL, migrationsDir)

// Rollback the last migration
seedup.MigrateDown(ctx, dbURL, migrationsDir)

// Show migration status (prints to stdout)
seedup.MigrateStatus(ctx, dbURL, migrationsDir)

// Create a new migration file
path, err := seedup.MigrateCreate(migrationsDir, "add_users_table")

DBML Generation

// Generate DBML schema documentation
content, err := seedup.GenerateDBML(ctx, dbURL, seedup.DBMLOptions{
    Schemas:       []string{"public", "auth"},  // Specific schemas
    ExcludeTables: []string{"goose_db_version"}, // Tables to skip
    AllSchemas:    false,                        // Include all non-system schemas
})

Seed Functions

// Apply seed data to the database
// seedDir is the seed set directory containing load.sql (e.g., "./seed/dev")
seedup.SeedApply(ctx, dbURL, migrationsDir, seedDir)

// Create seed data from an existing database
// seedDir is the seed set directory (e.g., "./seed/dev")
// queryFile is the dump.sql file (e.g., "./seed/dev/dump.sql")
seedup.SeedCreate(ctx, dbURL, seedDir, queryFile, seedup.SeedCreateOptions{
    DryRun:     false,
    Schemas:    []string{"public", "custom"},  // Specific schemas (default: ["public"])
    AllSchemas: false,                          // Include all non-system schemas
})

Database Management

// Create the database
seedup.DBCreate(ctx, dbURL, seedup.DBOptions{AdminURL: ""})

// Drop the database
seedup.DBDrop(ctx, dbURL, seedup.DBOptions{AdminURL: ""})

// Database setup (drop, create user, create db, permissions)
// Does NOT run migrations or apply seeds
seedup.DBSetup(ctx, seedup.DBSetupOptions{
    DatabaseURL: dbURL,
})

// Then apply seeds and migrations separately:
seedup.SeedApply(ctx, dbURL, "./migrations", "./seed/dev")
seedup.MigrateUp(ctx, dbURL, "./migrations")

Utilities

// Flatten all migrations into a single initial migration
seedup.Flatten(ctx, dbURL, migrationsDir)

// Validate migration timestamps (for CI)
seedup.Check(ctx, migrationsDir, "main")

Integrating seedup into Your Project

1. Project Structure

Set up your project with the following structure:

your-project/
├── migrations/           # Migration files go here
│   └── 20240101120000_initial.sql
├── seed/                 # Seed data root directory
│   └── dev/              # Seed set directory for "dev"
│       ├── dump.sql      # SQL query to extract seed data (INPUT)
│       └── load.sql      # Generated INSERT statements (OUTPUT)
├── Makefile              # Optional: wrap seedup commands
└── ...

2. Environment Variables

Configure seedup using environment variables (12-factor style):

# Required
export DATABASE_URL="postgres://user:pass@localhost/mydb"

# Optional (with defaults)
export MIGRATIONS_DIR="./migrations"    # default: ./migrations
export SEED_DIR="./seed"                # default: ./seed

3. Makefile Integration

Add these targets to your Makefile:

# Database migrations
.PHONY: migrate migrate-down migrate-status migrate-create

migrate:
	seedup migrate up

migrate-down:
	seedup migrate down

migrate-status:
	seedup migrate status

migrate-create:
	@read -p "Migration name: " name; \
	seedup migrate create $$name

# Seed data (use "dev" as the seed set name)
.PHONY: seed seed-create

seed:
	seedup seed apply dev
	seedup migrate up

seed-create:
	seedup seed create dev -d "$$PROD_DATABASE_URL"

# Database setup
.PHONY: db-setup db-drop

db-setup:
	seedup db setup --force
	seedup seed apply dev
	seedup migrate up

db-drop:
	seedup db drop --force

# CI checks
.PHONY: check-migrations

check-migrations:
	seedup check --base-branch main

4. CI/CD Integration

Add migration validation to your CI pipeline:

# .github/workflows/ci.yml
jobs:
  check-migrations:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0  # Required for branch comparison

      - uses: actions/setup-go@v5
        with:
          go-version: '1.22'

      - name: Install seedup
        run: go install github.com/lucasefe/seedup/cmd/seedup@latest

      - name: Check migration timestamps
        run: seedup check --base-branch ${{ github.base_ref || 'main' }}

Commands

migrate

Run database migrations using goose.

# Run all pending migrations
seedup migrate up

# Run a single migration
seedup migrate up-by-one

# Rollback the last migration
seedup migrate down

# Show migration status
seedup migrate status

# Create a new migration file
seedup migrate create add_users_table
# Creates: migrations/20240101120000_add_users_table.sql

seed apply

Apply seed data to your local database. This is useful for setting up development environments.

# Apply the "dev" seed set
seedup seed apply dev

# Apply to a specific database
seedup seed apply dev -d "$DATABASE_URL"

# Then run remaining migrations separately
seedup migrate up

The apply process:

1. Runs the initial migration (first migration file)
2. Loads seed data from seed/<name>/load.sql

Note: seed apply does NOT run remaining migrations. Run migrate up separately after applying seeds.

seed create

Create seed data from a database (typically production). This extracts data based on your query file.

# Create "dev" seed set from production (public schema only, the default)
seedup seed create dev -d "$PROD_DATABASE_URL"

# Include specific schemas
seedup seed create dev -d "$PROD_DATABASE_URL" --schemas public,custom

# Include all non-system schemas
seedup seed create dev -d "$PROD_DATABASE_URL" --all-schemas

# Dry run (preview without modifying files)
seedup seed create dev -d "$PROD_DATABASE_URL" --dry-run

The create process:

1. Reads the query file at seed/<name>/dump.sql
2. Executes queries against the source database
3. Exports results to seed/<name>/load.sql as batched INSERT statements
4. Flattens all migrations into a single initial migration

flatten

Consolidate all migrations into a single initial migration. Useful for cleaning up migration history.

seedup flatten -d "$PROD_DATABASE_URL"

check

Validate that new migrations have the latest timestamps. This prevents merge conflicts when multiple developers add migrations.

seedup check --base-branch main

If validation fails, seedup provides fix commands:

Error: New migrations must have the latest timestamps

To fix:

  $ git mv migrations/{20240101120000,$(date -u +%Y%m%d%H%M%S)}_add_users.sql

db

Database lifecycle management commands for setting up and tearing down databases.

# Setup: create user + drop + create db + permissions (no migrations, no seeds)
seedup db setup

# Skip confirmation prompt (for CI/automation)
seedup db setup --force

# Drop the database
seedup db drop

# Drop without confirmation
seedup db drop --force

# Create the database (if it doesn't exist)
seedup db create

The db setup command performs:

1. Creates the database user (extracted from DATABASE_URL) if it doesn't exist
2. Drops the database if it exists
3. Creates the database
4. Sets up permissions (grants all privileges, sets owner)

Note: db setup does NOT run migrations or apply seeds. Use seed apply and migrate up separately.

The database name, user, and password are all extracted from the DATABASE_URL.

dbml

Generate DBML (Database Markup Language) documentation from your database schema.

# Generate DBML to stdout
seedup dbml

# Generate to file
seedup dbml -o schema.dbml

# Include all schemas (not just public)
seedup dbml --all-schemas -o schema.dbml

# Exclude specific tables
seedup dbml --exclude-tables goose_db_version

DBML files can be used with dbdiagram.io to visualize your database schema.

Writing Migrations

Migration files use the standard goose format:

-- +goose Up
-- +goose StatementBegin
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE users;
-- +goose StatementEnd

Writing Seed Query Files

The seed query file (e.g., seed/dev/dump.sql) defines which data to extract from your source database. When you run seedup seed create dev, it:

1. Creates temporary tables for each table in the database
2. Runs your query file to populate those temp tables
3. Exports the temp tables to seed/dev/load.sql as batched INSERT statements

The query file should contain INSERT statements that select data FROM your real tables INTO the corresponding temp tables. Each temp table is named pg_temp."seed.<schema>.<table>".

Example seed/dev/dump.sql:

-- Select recent users for development
INSERT INTO pg_temp."seed.public.users" (id, name, email, created_at)
SELECT id, name, email, created_at
FROM public.users
WHERE created_at > NOW() - INTERVAL '30 days'
LIMIT 100;

-- Select accounts for those users
INSERT INTO pg_temp."seed.public.accounts" (id, user_id, name, balance)
SELECT a.id, a.user_id, a.name, a.balance
FROM public.accounts a
WHERE a.user_id IN (SELECT id FROM pg_temp."seed.public.users");

-- Select related data
INSERT INTO pg_temp."seed.public.transactions" (id, account_id, amount, created_at)
SELECT t.id, t.account_id, t.amount, t.created_at
FROM public.transactions t
WHERE t.account_id IN (SELECT id FROM pg_temp."seed.public.accounts")
LIMIT 1000;

CLI Reference

Global Flags

-d, --database-url string     Database URL (overrides DATABASE_URL env)
-m, --migrations-dir string   Migrations directory (overrides MIGRATIONS_DIR env)
-v, --verbose                 Verbose output

Environment Variables

Variable	Description	Default
DATABASE_URL	PostgreSQL connection URL	required
MIGRATIONS_DIR	Path to migrations directory	./migrations
SEED_DIR	Path to seed data root directory	./seed

Examples

Full Development Setup

# Clone your project
git clone https://github.com/yourorg/yourproject
cd yourproject

# Configure environment
export DATABASE_URL="postgres://user:pass@localhost/myproject_dev"

# Step 1: Create database infrastructure
seedup db setup

# Step 2: Apply seed data (runs initial migration + loads seed)
seedup seed apply dev

# Step 3: Run remaining migrations
seedup migrate up

# Your database is now ready for development!

Creating New Seed Data

# Connect to production (read-only)
export PROD_DATABASE_URL="postgres://readonly:pass@prod-host/myproject"

# Create "dev" seed set from production
seedup seed create dev -d "$PROD_DATABASE_URL"

# Review and commit the changes
git add migrations/ seed/
git commit -m "Update seed data"

Adding a New Migration

# Create migration
seedup migrate create add_orders_table

# Edit the file
vim migrations/20240101120000_add_orders_table.sql

# Run it
seedup migrate up

# Commit
git add migrations/
git commit -m "Add orders table"

License

MIT