Concepts¶
Package¶
A package is the unit that moves from development to production: a folder with the SQL scripts for one database and a manifest (sql2devops.yaml) that says in which order they run and how to undo each one. See Packages.
When a package is submitted, Sql2DevOps stores an immutable copy of it in the control repository, with a SHA-256 hash of its content and an internal id. Every environment runs that stored copy, never the working folder. To change a submitted package you submit a new version.
Control repository¶
A folder, normally a Git repository, that holds:
projects/<project>/project.yaml: the project's environments and rules;projects/<project>/packages/<package>/v<n>/: the submitted copies;projects/<project>/events/: the history, one JSON file per event;keys/: the public keys that sign the events.
When it is a Git repository, each change is committed and pushed, with retries when two pipelines push at the same time. Git runs through the git executable, so the machine's existing credentials work.
Events and the history¶
Every submission, approval, execution, emergency, manual run and review is an event. Each event carries the hash of the previous one (a hash chain) and, when a signing key is configured, an ed25519 signature. sql2devops verify detects edited, removed or reordered events, unknown keys and modified package copies.
The history table¶
Each target database gets a sql2devops_history table: an independent witness of what was applied to it. Before every deployment both records are compared:
- already applied → nothing runs;
- they disagree (a database restored from a backup, a package applied by another route) → the deployment stops and explains why;
--forceoverrides and is recorded as forced.
Projects, environments and targets¶
A project groups packages and lists its environments in promotion order (for example DEV → HML → PROD). Each environment has one or more targets: the databases a package can be deployed to, each with its engine and the environment variable that holds its connection string.
Promotion and approvals¶
A version reaches an environment only after the same version succeeded in the previous one, so production always runs exactly what was tested. Each environment chooses its rule:
| Approval | Meaning |
|---|---|
none |
deploys as soon as the previous environment succeeded |
automatic |
a pipeline approves, with evidence (for example the URL of the test run) |
human |
named people approve; by default the submitter cannot approve their own package |
Emergencies and follow-ups¶
When production is down, emergency deploys without the environment's rule, with a reason and, if required, a ticket. It is recorded like any other execution and leaves follow-ups: a review by someone else and the same version deployed to the environments it skipped. status shows them until they are done. See Emergencies.
Lock¶
One deployment at a time per database: sp_getapplock on SQL Server, an advisory lock on PostgreSQL. Others wait up to --lock-timeout.
Identities¶
In GitHub Actions the actor is github:<login> (from GITHUB_ACTOR); elsewhere it is the operating-system user. Approvers and reviewers are listed with the same form in project.yaml.