relspecgo/docs/SCRIPTS_COMMAND.md

RelSpec Scripts Command

The relspec scripts command provides tools for managing and executing SQL migration scripts from a directory structure.

Overview

The scripts command supports two main operations:

• list: List SQL scripts from a directory in execution order
• execute: Execute SQL scripts against a PostgreSQL database

Scripts are read from a directory (recursively) and executed in a deterministic order based on Priority (ascending) and Sequence (ascending).

File Naming Convention

SQL scripts must follow this naming pattern (both separators are supported):

{priority}_{sequence}_{name}.{sql|pgsql}  (underscore format)
{priority}-{sequence}-{name}.{sql|pgsql}  (hyphen format)

Components

• priority: Integer (0-9999) - Execution priority level (lower executes first)
• sequence: Integer (0-9999) - Order within priority level (lower executes first)
• separator: Underscore _ or hyphen - (both formats can be mixed)
• name: Descriptive name (alphanumeric, underscores, hyphens)
• extension: .sql or .pgsql

Valid Examples

Underscore format:

1_001_create_users.sql          # Priority 1, Sequence 1
1_002_create_posts.sql          # Priority 1, Sequence 2
1_003_create_comments.pgsql     # Priority 1, Sequence 3
2_001_add_indexes.sql           # Priority 2, Sequence 1
2_002_add_constraints.sql       # Priority 2, Sequence 2
3_001_seed_users.sql            # Priority 3, Sequence 1

Hyphen format:

1-001-create-users.sql          # Priority 1, Sequence 1
1-002-create-posts.sql          # Priority 1, Sequence 2
1-003-create-comments.pgsql     # Priority 1, Sequence 3
10-10-create-newid.pgsql        # Priority 10, Sequence 10

Mixed format (both in same directory):

1_001_create_users.sql          # Priority 1, Sequence 1 (underscore)
1-002-create-posts.sql          # Priority 1, Sequence 2 (hyphen)
2_001_add_indexes.sql           # Priority 2, Sequence 1 (underscore)

Execution Order: 1→2→3→4→5→6 (sorted by Priority, then Sequence)

Invalid Examples (Will be ignored)

migration.sql                   # Missing priority/sequence
create_users.sql                # Missing priority/sequence
1_create_users.sql              # Missing sequence
1_001_test.txt                  # Wrong extension
README.md                       # Not a SQL file

Directory Structure

Scripts can be organized in subdirectories. The scanner recursively finds all matching SQL files:

migrations/
├── 1_001_create_schema.sql
├── 1_002_create_users.sql
├── tables/
│   ├── 1_003_create_posts.sql
│   └── 1_004_create_comments.pgsql
├── indexes/
│   └── 2_001_add_indexes.sql
└── data/
    └── 3_001_seed_data.sql

All files will be found and executed in Priority→Sequence order regardless of directory structure.

External File Embedding

Script SQL can embed nearby text or binary files before execution using -- @embed directives:

-- @embed: path=assets/message.txt var=:message mode=text
-- @embed: path=assets/photo.bin var=:payload mode=base64
INSERT INTO assets (message, payload)
VALUES (:message, decode(:payload, 'base64')::bytea);

• path: File path resolved relative to the SQL file containing the directive
• var: Named placeholder to replace, such as :message
• mode: text embeds an escaped SQL string literal; base64 embeds a base64 string literal

The directive comment is removed from the SQL, and every matching placeholder is replaced before the script is listed or executed.

Commands

relspec scripts list

List all SQL scripts in a directory and show their execution order.

Usage:

relspec scripts list --dir <directory> [flags]

Flags:

• --dir <path> (required): Directory containing SQL scripts
• --schema <name>: Schema name (default: "public")
• --database <name>: Database name (default: "database")

Example:

relspec scripts list --dir ./migrations

Output:

=== SQL Scripts List ===
Directory: ./migrations

Found 5 script(s) in execution order:

No.  Priority   Sequence Name                           Lines
---- --------   -------- ------------------------------ -----
1    1          1        create_users                   7
2    1          2        create_posts                   8
3    2          1        add_indexes                    4
4    2          2        add_constraints                6
5    3          1        seed_data                      4

relspec scripts execute

Execute SQL scripts from a directory against a PostgreSQL database.

Usage:

relspec scripts execute --dir <directory> --conn <connection-string> [flags]

Flags:

• --dir <path> (required): Directory containing SQL scripts
• --conn <string> (required): PostgreSQL connection string
• --schema <name>: Schema name (default: "public")
• --database <name>: Database name (default: "database")

Connection String Formats:

# Standard PostgreSQL URLs
postgres://username:password@localhost:5432/database_name
postgres://username:password@localhost/database_name
postgresql://user:pass@host:5432/dbname?sslmode=disable
postgresql://user:pass@host/dbname?sslmode=require

# Key-value format
host=localhost port=5432 user=username password=pass dbname=mydb sslmode=disable

Examples:

# Execute migration scripts
relspec scripts execute \
  --dir ./migrations \
  --conn "postgres://user:pass@localhost:5432/mydb"

# Execute with custom schema
relspec scripts execute \
  --dir ./migrations \
  --conn "postgres://localhost/mydb" \
  --schema public

# Execute with SSL disabled
relspec scripts execute \
  --dir ./sql \
  --conn "postgres://user:pass@localhost/db?sslmode=disable"

# Execute using key-value connection string
relspec scripts execute \
  --dir ./migrations \
  --conn "host=localhost port=5432 user=admin password=secret dbname=prod"

Output:

=== SQL Scripts Execution ===
Started at: 2025-12-30 22:30:15
Directory: ./migrations
Database:  postgres://user:***@localhost:5432/mydb

[1/2] Reading SQL scripts...
      ✓ Found 4 script(s)

[2/2] Executing scripts in order (Priority → Sequence)...

Executing script: create_users (Priority=1, Sequence=1)
✓ Successfully executed: create_users
Executing script: create_posts (Priority=1, Sequence=2)
✓ Successfully executed: create_posts
Executing script: add_indexes (Priority=2, Sequence=1)
✓ Successfully executed: add_indexes
Executing script: seed_data (Priority=2, Sequence=2)
✓ Successfully executed: seed_data

=== Execution Complete ===
Completed at: 2025-12-30 22:30:16
Successfully executed 4 script(s)

Execution Behavior

Execution Order

Scripts are always executed in this order:

1. Sort by Priority (ascending)
2. Within same priority, sort by Sequence (ascending)

Example:

Priority 1, Sequence 1   → Executes 1st
Priority 1, Sequence 2   → Executes 2nd
Priority 1, Sequence 10  → Executes 3rd
Priority 2, Sequence 1   → Executes 4th
Priority 2, Sequence 5   → Executes 5th
Priority 10, Sequence 1  → Executes 6th

Error Handling

• Stop on First Error: Execution stops immediately when any script fails
• No Automatic Rollback: Scripts executed before the failure remain committed
• Error Details: Full error message with script name, priority, and sequence

Example error output:

Executing script: add_indexes (Priority=2, Sequence=1)
Error: execution failed: failed to execute script add_indexes (Priority=2, Sequence=1):
       ERROR: syntax error at or near "IDNEX" (SQLSTATE 42601)

Transaction Behavior

• Each script executes in its own implicit transaction (PostgreSQL default)
• No automatic transaction wrapping across multiple scripts
• For atomic migrations, manually wrap SQL in BEGIN/COMMIT blocks

Empty Scripts

Scripts with empty SQL content are silently skipped.

Use Cases

Development Migrations

Organize database changes by priority levels:

migrations/
├── 1_xxx_schema.sql         # Priority 1: Core schema
├── 1_xxx_tables.sql
├── 2_xxx_indexes.sql        # Priority 2: Performance
├── 2_xxx_constraints.sql
└── 3_xxx_seed.sql           # Priority 3: Data

Multi-Environment Deployments

Use priority levels for environment-specific scripts:

deploy/
├── 1_xxx_core_schema.sql    # Priority 1: All environments
├── 2_xxx_dev_data.sql       # Priority 2: Dev only
├── 2_xxx_staging_data.sql   # Priority 2: Staging only
└── 3_xxx_prod_data.sql      # Priority 3: Production only

Incremental Rollouts

Use sequence for ordered feature rollouts:

features/
├── 1_001_feature_a_schema.sql
├── 1_002_feature_a_data.sql
├── 1_003_feature_b_schema.sql
├── 1_004_feature_b_data.sql

Integration with RelSpec

The scripts command uses:

• Reader: pkg/readers/sqldir/ - Reads SQL files into models.Schema.Scripts
• Writer: pkg/writers/sqlexec/ - Executes scripts from models.Schema.Scripts

You can use these packages programmatically:

import (
    "git.warky.dev/wdevs/relspecgo/pkg/readers"
    "git.warky.dev/wdevs/relspecgo/pkg/readers/sqldir"
    "git.warky.dev/wdevs/relspecgo/pkg/writers"
    "git.warky.dev/wdevs/relspecgo/pkg/writers/sqlexec"
)

// Read scripts
reader := sqldir.NewReader(&readers.ReaderOptions{
    FilePath: "./migrations",
})
db, _ := reader.ReadDatabase()

// Execute scripts
writer := sqlexec.NewWriter(&writers.WriterOptions{
    Metadata: map[string]any{
        "connection_string": "postgres://localhost/mydb",
    },
})
writer.WriteDatabase(db)

Best Practices

Naming

• Use zero-padded sequences: 001, 002, 010 (not 1, 2, 10)
• Use descriptive names: create_users_table, not table1
• Group related changes: same priority for related DDL

Organization

• Keep scripts small and focused (one logical change per file)
• Use priority levels to organize phases (schema → indexes → data)
• Document complex migrations with SQL comments

Safety

• Always test migrations in development first
• Use scripts list to verify execution order before running
• Back up production databases before executing
• Consider using transactions for critical changes
• Review generated SQL before execution

Version Control

• Commit scripts to version control
• Never modify executed scripts (create new ones instead)
• Use meaningful commit messages
• Tag releases with migration checkpoints

Limitations

• PostgreSQL only (currently)
• No built-in rollback support
• No migration state tracking (no "already executed" detection)
• No dry-run mode
• Stops on first error (no partial execution tracking)

Future Enhancements

Potential future features:

• Migration state tracking (executed scripts table)
• Rollback script support (using models.Script.Rollback field)
• Dry-run mode (validate without executing)
• Transaction wrapping (all-or-nothing execution)
• Multi-database support (MySQL, SQLite, etc.)
• Parallel execution for independent scripts