Creating Migration Scripts
A script is the atomic, versioned, content-hashed unit of change. This page covers authorship, the target-resolution model, type classification, versioning semantics, and dependency declaration.
Authorship Paths
Inline
Paste or write SQL / MongoDB shell into the editor. As you type, type classification runs live and constrains the target picker; a name and content are the only fields required to persist. Target instance and database are resolved through catalog introspection (below) and may be deferred.
Pull from provider
With a configured repository/storage source, scan the source's path for files matching its glob and import a subset; each imported file becomes a Draft script with the filename as its name. Providers: GitHub, GitLab, Bitbucket, Azure DevOps, S3, Azure Blob, GCS, Dropbox. See Environments & Repository Sources.
Type Classification & Target Filtering
Content is classified into SQL, MongoDB, or Unknown by pattern match, DDL/DML/dialect syntax (PL/pgSQL, T-SQL, PL/SQL) vs. shell operators (db.collection.*, aggregation pipeline stages, ObjectId). Classification is reactive: flipping content from SQL to Mongo clears an incompatible selected instance. The instance picker is filtered to compatible targets (SQL → PostgreSQL/MySQL/MSSQL/Oracle; Mongo → MongoDB); Unknown shows all.
Target Resolution via Catalog Introspection
The target-database dropdown is populated by querying the selected instance's system catalogs in real time, pg_database, information_schema.schemata, sys.databases, admin.listDatabases(), or v$pdbs by dialect, so you select from what actually exists on the instance, not a free-text guess.
Author-Set Properties
Only name and content are required. Target connection, target database, environment, and release membership may be left unset and bound later, a script can be drafted and even sent for approval before its execution target is decided; these fields are revalidated at execution time.
| Property | Required | Notes |
|---|---|---|
| Name | Yes | Stable, descriptive identifier, e.g. alter-orders-add-status-column. |
| Content | Yes | SQL (DDL/DML) or MongoDB shell operations. |
| Type | Yes | Release (promotes through environments in a release) or Ad-hoc (executes independently). |
| Database Type | Yes | Target engine; drives validation and execution behavior. |
| Target Instance | No | Deduplicated, type-filtered instance list (host:port). |
| Target Database | No | Catalog-introspected against the selected instance. |
| Tags | No | Free-form labels for filtering and grouping. |
| Dependencies | No | Predecessor scripts, resolved topologically at run time. |
System-Managed Properties
| Property | Semantics |
|---|---|
| Approval Status | Draft → Pending → Approved | Rejected → Executed. |
| Version | Integer, starts at 1, increments on every content edit. |
| Content Hash | SHA-256 of content; binds reviewed bytes to executed bytes and gates approval invalidation. |
| Created / Updated At, Created By | Provenance timestamps and author. |
Edit Semantics & Approval Invalidation
Editing content bumps the version and recomputes the hash. If the script was Approved, the hash change resets it to Draft, approval is pinned to exact bytes, so a re-review is mandatory before the mutated script can run. This is the mechanism that guarantees the executed version is the reviewed version, not a safety suggestion.
Dependencies
Declaring "B depends on A" constrains ordering: A must execute successfully before B is attempted. Dependencies are added via search and shown as removable badges. At execution, standalone or release-scoped, the engine builds a DAG, topologically sorts, executes in order, and halts the frontier on first failure (dependents unattempted).
Cycle Detection
A cycle (A→B→C→A) is detected and rejected at save; the graph must be acyclic to persist.
Canonical patterns
- Referential ordering:
create-users→create-orders(FK to users) →create-order-items. - Structure-before-data:
add-status-column→populate-default-statuses. - Reference-data layering:
insert-countries→insert-states→insert-cities.
Authoring Discipline
- One logical change per script, keep the reviewable and rollbackable unit atomic; split compound changes.
- Idempotency,
IF NOT EXISTS/IF EXISTSguards so re-execution is safe under retry. - Lock-awareness, flag long ALTERs on hot tables; prefer online/concurrent variants and the appropriate execution mode.
- Consistent tag taxonomy, agree a scheme (
schema,data,index,hotfix, project) and apply it uniformly for fleet-wide filtering. - Promote through lower environments before production; never edit between stages.
Lifecycle
- Create, author content + properties. Status: Draft.
- Request Approval, submit. Status: Pending.
- Review, reviewer decides. Status: Approved | Rejected.
- Resubmit (if rejected), edit, version bumps, back to Pending.
- Compose, add approved Release-type scripts to a release.
- Execute, run against resolved target. Status: Executed.