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 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:

Priority (ascending): Lower priority values execute first
Sequence (ascending): Within same priority, lower sequence values execute first

Example Execution Order

Given these scripts:

Script A: Priority=2, Sequence=1
Script B: Priority=1, Sequence=3
Script C: Priority=1, Sequence=1
Script D: Priority=1, Sequence=2
Script E: Priority=3, Sequence=1

Execution order: C → D → B → A → E

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)

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

README.md

SQL Executor Writer