sqlexec

SQL Executor Writer

The SQL Executor Writer (sqlexec) executes SQL scripts from models.Script objects against a PostgreSQL database. Scripts are executed in order based on Priority (ascending) and Sequence (ascending).

Features

• Ordered Execution: Scripts execute in Priority→Sequence→Name order
• PostgreSQL Support: Uses pgx/v5 driver for robust PostgreSQL connectivity
• Stop on Error: Execution halts immediately on first error (default behavior)
• Progress Reporting: Prints execution status to stdout
• Multiple Schemas: Can execute scripts from multiple schemas in a database

Usage

Basic Usage

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

writer := sqlexec.NewWriter(&writers.WriterOptions{
    Metadata: map[string]any{
        "connection_string": "postgres://user:password@localhost:5432/dbname?sslmode=disable",
    },
})

// Execute all scripts from database
err := writer.WriteDatabase(database)
if err != nil {
    log.Fatalf("Execution failed: %v", err)
}

Execute Single Schema

err := writer.WriteSchema(schema)
if err != nil {
    log.Fatalf("Schema execution failed: %v", err)
}

Complete Example with SQL Directory Reader

import (
    "log"
    "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"
)

func main() {
    // Read SQL scripts from directory
    reader := sqldir.NewReader(&readers.ReaderOptions{
        FilePath: "./migrations",
    })

    db, err := reader.ReadDatabase()
    if err != nil {
        log.Fatal(err)
    }

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

    if err := writer.WriteDatabase(db); err != nil {
        log.Fatal(err)
    }
}

Configuration

Required Metadata

• connection_string: PostgreSQL connection string (required)

Connection String Format

postgres://[user[:password]@][host][:port][/dbname][?param1=value1&...]

Examples:

postgres://localhost/mydb
postgres://user:pass@localhost:5432/mydb?sslmode=disable
postgres://user@localhost/mydb?sslmode=require
postgresql://user:pass@prod-db.example.com:5432/production

Execution Order

Scripts are sorted and executed based on:

1. Priority (ascending): Lower priority values execute first
2. Sequence (ascending): Within same priority, lower sequence values execute first
3. Name (ascending): Within same priority and sequence, alphabetical order by name

Example Execution Order

Given these scripts:

Script A: Priority=2, Sequence=1, Name="zebra"
Script B: Priority=1, Sequence=3, Name="script"
Script C: Priority=1, Sequence=1, Name="apple"
Script D: Priority=1, Sequence=1, Name="beta"
Script E: Priority=3, Sequence=1, Name="script"

Execution order: C (apple) → D (beta) → B → A → E

Directory-based Sorting Example

Given these files:

1_001_create_schema.sql
1_001_create_users.sql      ← Alphabetically before "drop_tables"
1_001_drop_tables.sql
1_002_add_indexes.sql
2_001_constraints.sql

Execution order (note alphabetical sorting at same priority/sequence):

1_001_create_schema.sql
1_001_create_users.sql
1_001_drop_tables.sql
1_002_add_indexes.sql
2_001_constraints.sql

Output

The writer prints progress to stdout:

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

Error Handling

Connection Errors

If the database connection fails, execution stops immediately:

Error: failed to connect to database: connection refused

Script Execution Errors

If a script fails, execution stops and returns the error with context:

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

Behavior: Stop on first error (scripts executed before the error remain committed)

Empty Script Handling

Scripts with empty SQL content are skipped silently.

Database Support

Currently supports:

• ✅ PostgreSQL (via pgx/v5)

Future support planned for:

• MySQL/MariaDB
• SQLite
• Generic SQL via database/sql

Transaction Behavior

Current: Each script executes in its own implicit transaction (PostgreSQL default behavior)

Future Enhancement: Option to wrap all scripts in a single transaction for atomic execution with rollback on error.

Performance Considerations

• Scripts execute sequentially (not in parallel)
• Each script creates a database round-trip
• For large migrations, consider:
  • Combining related statements into fewer scripts
  • Using PostgreSQL's COPY command for bulk data
  • Running during low-traffic periods

Testing

Run tests:

go test ./pkg/writers/sqlexec/

Current tests include:

• Validation and error handling
• Script sorting logic
• Configuration validation

Integration Tests

For integration testing with a real database:

# Start PostgreSQL (example with Docker)
docker run -d --name postgres-test \
  -e POSTGRES_PASSWORD=test \
  -e POSTGRES_DB=testdb \
  -p 5432:5432 \
  postgres:16

# Run your integration tests
go test -tags=integration ./pkg/writers/sqlexec/

# Cleanup
docker stop postgres-test
docker rm postgres-test

Limitations

• WriteTable() is not supported (returns error)
• Requires PostgreSQL connection (no offline mode)
• No built-in transaction wrapping (yet)
• No rollback script support (yet, though models.Script.Rollback field exists)

Related

• SQL Directory Reader: pkg/readers/sqldir/ - Read scripts from filesystem
• Script Model: pkg/models/models.go - Script structure definition
• pgx Documentation: https://github.com/jackc/pgx - PostgreSQL driver docs