relspecgo/pkg/writers/sqlexec/README.md

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

```go
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

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

### Complete Example with SQL Directory Reader

```go
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

### 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:
```bash
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:

```bash
# 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