Skip to content

Packages

Manifest

# sql2devops.yaml  (sql2devops.json is also accepted)
package: CHG-1001        # your identifier, e.g. the ticket number
version: 1               # optional; set by submit --auto-version
project: finance
target: finance-db       # one package = one database
description: Interest rates table and calculation
steps:
  - file: 01-rates-table.sql
    type: ddl            # ddl, data, function, procedure, view, trigger, other
    rollback: rollback/01-rates-table.sql
  - file: 02-interest-function.sql
    type: function
    rollback: rollback/02-interest-function.sql
  - file: 03-initial-rates.sql
    type: data
    timeout: 5m
    rollback: none       # explicit: this step cannot be undone
on_error: rollback       # or: stop
variables: [api_password]
Field Required Meaning
package yes Identifier, unique in the project
version no Version number; a submitted version never changes
project yes Project in the control repository
target yes Name of the database target in project.yaml
steps yes Scripts in execution order
steps[].file yes Script path inside the package
steps[].type no Kind of change, for reports
steps[].rollback yes Script that undoes the step, or none
steps[].timeout no Limit for the step (30s, 5m, 1h)
on_error no rollback (default) or stop
variables no Variables the scripts use (see Variables)

A folder with a single .sql file and no manifest is a package too: --project and --target fill the generated manifest, and a rollback/<same name> file becomes its rollback.

Checking

sql2devops package check -d postgres ./CHG-1001

It checks the structure, that every file exists, the file encodings (UTF-8, UTF-8 with BOM, UTF-16 and Windows-1252 are detected), how each script splits into batches, and warns about variables used but not declared.

Submitting

sql2devops package submit ./CHG-1001

The package is copied to the control repository with its hash and an internal id, and a submitted event is recorded. Pipelines that regenerate a package on every run (for example an idempotent schema script) use --auto-version: identical content reuses the latest version; any change becomes the next one.

Running without the control repository

sql2devops package run -d postgres ./CHG-1001

Useful in a throwaway test database (for example in CI). Nothing is recorded.

Writing good rollbacks

When a step fails, its own rollback runs first, then those of the completed steps, newest first. Rollbacks run even after a timeout or Ctrl+C. Scripts and rollbacks are the author's responsibility: write rollbacks that are safe to run when their step was only partly applied (DROP ... IF EXISTS, checks before deletes).

When nothing of a failed step took effect (the connection failed, or the first statement failed on PostgreSQL or SQLite, where each batch is one statement), its rollback is skipped and the report says that the database is unchanged.