# 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. ## Commands ### relspec scripts list List all SQL scripts in a directory and show their execution order. **Usage:** ```bash relspec scripts list --dir [flags] ``` **Flags:** - `--dir ` (required): Directory containing SQL scripts - `--schema `: Schema name (default: "public") - `--database `: Database name (default: "database") **Example:** ```bash 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:** ```bash relspec scripts execute --dir --conn [flags] ``` **Flags:** - `--dir ` (required): Directory containing SQL scripts - `--conn ` (required): PostgreSQL connection string - `--schema `: Schema name (default: "public") - `--database `: Database name (default: "database") **Connection String Formats:** ```bash # 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:** ```bash # 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: ```go 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