sqldir

SQL Directory Reader

The SQL Directory Reader (sqldir) reads SQL scripts from a directory structure and populates the Scripts field of a Schema. It supports recursive directory scanning and extracts priority, sequence, and name information from filenames.

File Naming Convention

Scripts must follow this naming pattern (supports both underscores and hyphens as separators):

{priority}_{sequence}_{name}.{sql|pgsql}
{priority}-{sequence}-{name}.{sql|pgsql}

Components

• priority: Integer (0-9999) - Defines execution order (lower executes first)
• sequence: Integer (0-9999) - Defines order within the same priority level
• separator: Underscore _ or hyphen - (can be mixed)
• name: Descriptive name (alphanumeric, underscores, hyphens allowed)
• extension: .sql or .pgsql

Examples

migrations/
├── 1_001_create_schema.sql          # Priority 1, Sequence 1 (underscore format)
├── 1-002-create-users-table.sql     # Priority 1, Sequence 2 (hyphen format)
├── 1_003_create_posts_table.pgsql   # Priority 1, Sequence 3 (underscore format)
├── 2-001-add-indexes.sql            # Priority 2, Sequence 1 (hyphen format)
├── 2_002_add_constraints.sql        # Priority 2, Sequence 2 (underscore format)
├── 10-10-create-newid.pgsql         # Priority 10, Sequence 10 (hyphen format)
└── subdirectory/
    └── 3_001_seed_data.sql          # Priority 3, Sequence 1 (subdirs supported)

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

Both formats can be mixed in the same directory - the reader handles both seamlessly.

Invalid Filenames (Ignored)

• migration.sql - Missing priority/sequence
• 1_create_users.sql - Missing sequence
• create_users.sql - Missing priority/sequence
• 1_001_test.txt - Wrong extension
• readme.md - Not a SQL file

Usage

Basic Usage

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

reader := sqldir.NewReader(&readers.ReaderOptions{
    FilePath: "/path/to/migrations",
    Metadata: map[string]any{
        "schema_name":   "public",      // Optional, defaults to "public"
        "database_name": "myapp",       // Optional, defaults to "database"
    },
})

// Read all scripts
database, err := reader.ReadDatabase()
if err != nil {
    log.Fatal(err)
}

// Access scripts
for _, schema := range database.Schemas {
    for _, script := range schema.Scripts {
        fmt.Printf("Script: %s (P:%d S:%d)\n",
            script.Name, script.Priority, script.Sequence)
        fmt.Printf("SQL: %s\n", script.SQL)
    }
}

Read Schema Only

schema, err := reader.ReadSchema()
if err != nil {
    log.Fatal(err)
}

fmt.Printf("Found %d scripts\n", len(schema.Scripts))

Features

• Recursive Directory Scanning: Automatically scans all subdirectories
• Symlink Skipping: Symbolic links are automatically skipped (prevents loops and duplicates)
• Multiple Extensions: Supports both .sql and .pgsql files
• Flexible Naming: Extract metadata from filename patterns
• Error Handling: Validates directory existence and file accessibility
• Schema Integration: Scripts are added to the standard RelSpec Schema model

Script Model

Each script is stored as a models.Script:

type Script struct {
    Name        string   // Extracted from filename (e.g., "create_users")
    Description string   // Auto-generated description with file path
    SQL         string   // Complete SQL content from file
    Priority    int      // Execution priority from filename
    Sequence    uint     // Execution sequence from filename
    // ... other fields available but not populated by this reader
}

Integration with SQL Executor

The SQL Directory Reader is designed to work seamlessly with the SQL Executor Writer:

// 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)  // Executes in Priority→Sequence order

See pkg/writers/sqlexec/README.md for more details on script execution.

Error Handling

The reader will return errors for:

• Non-existent directory paths
• Inaccessible directories or files
• Invalid file permissions
• File read failures

Files that don't match the naming pattern are silently ignored (not treated as errors).

Testing

Run tests:

go test ./pkg/readers/sqldir/

Tests include:

• Valid file parsing (underscore and hyphen formats)
• Recursive directory scanning
• Symlink skipping
• Invalid filename handling
• Empty directory handling
• Error conditions