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.