migrate

HCL migrations

migrate.Apply is the single filesystem-backed migration seam for PostgreSQL, SQLite and SQL Server. The connection string selects the target, while callers pass the same embedded Atlas HCL bundle and options. SQL Server bundles are SQL Server-shaped rather than portable; see SQL Server.

err := migrate.Apply(ctx, dsn, schemaFS,
    migrate.WithDir("migrations"),
    migrate.WithName("runtime"),
)

Target selection

PostgreSQL accepts postgres://, postgresql://, and keyword connection strings such as host=localhost dbname=runtime sslmode=disable.

SQLite accepts sqlite:// URLs and plain file paths ending in .db:

err := migrate.Apply(ctx, "sqlite://state/runtime.sqlite", schemaFS, migrate.WithDir("migrations"))
err := migrate.Apply(ctx, "/var/lib/example/runtime.db", schemaFS, migrate.WithDir("migrations"))

SQL Server accepts sqlserver:// URLs in the go-mssqldb form, e.g. sqlserver://user:password@host:1433?database=app. mssql:// is rejected so that one spelling reaches the migrator. ADO connection strings (;-separated pairs that name the server with server, data source, address, addr or network address, e.g. server=db;user id=app;password=secret;database=acme) and odbc: connection strings are SQL Server too, and are passed to go-mssqldb unchanged.

Existing connections

WithDB runs a SQL Server or SQLite migration on a *sql.DB the caller already holds, with the connection string left empty. The dialect comes from the handle's driver. Apply neither closes the handle nor changes its pool settings:

err := migrate.Apply(ctx, "", bundle,
	migrate.WithDB(writeDB),
	migrate.WithRoutines("routines"),
	migrate.WithName("acme/routines"))

Passing both a connection string and WithDB is an error. A PostgreSQL handle is refused, because PostgreSQL migrations scope search_path and lock_timeout on connections they open themselves.

Relative SQLite paths are resolved from the process working directory. SQLite is always file-backed; :memory: and mode=memory are rejected. The normalized connection enables foreign keys, a 5-second busy timeout, and WAL while preserving caller-supplied query parameters and unmanaged pragmas.

Unknown URI schemes written as scheme://... fail instead of being passed to PostgreSQL accidentally. Scheme-like strings without :// are treated as PostgreSQL DSNs unless they are SQL Server ADO/ODBC strings or their path ends in .db; use one of the explicit forms above rather than relying on that ambiguity. A PostgreSQL keyword DSN remains PostgreSQL even when its dbname ends in .db.

db.NewGorm uses the same DSN resolver, so migration and application connections cannot disagree about the target.

Portable canonical HCL

A cross-database bundle declares PostgreSQL-shaped Atlas HCL. PostgreSQL evaluates the declaration directly. SQLite evaluates the same declaration with the PostgreSQL evaluator, validates the resulting realm against the compatibility contract below, and then projects it to SQLite. Acceptance by the HCL parser alone does not mean an object is SQLite-compatible.

All .hcl files below WithDir are loaded recursively in lexical path order and evaluated as one realm. variable blocks are supported on both targets when callers provide values through WithVariables; the resulting schema must still fit the portable set.

Supported cross-database blocks

Index expressions, per-column index attributes such as sort direction or operator classes, foreign-key attributes, and table or check attributes outside the forms above are not in the portable set.

Supported portable column types

The following table lists the PostgreSQL type families accepted after HCL evaluation, not every type accepted by Atlas's PostgreSQL parser. Underscored names such as character_varying and double_precision are direct HCL aliases. Space-containing SQL spellings and the less common aliases noted below are accepted through sql("...") and normalize to the same family.

Use only type modifiers whose loss is acceptable to the application, or enforce the invariant with a portable named check. Evaluated type families outside this table fail projection; they are not coerced to text.

Portable example

schema "public" {}

table "projects" {
  schema = schema.public

  column "id" {
    type = uuid
    null = false
  }
  column "payload" {
    type    = jsonb
    null    = false
    default = sql("'{}'")
  }
  column "created_at" {
    type    = timestamptz
    null    = false
    default = sql("CURRENT_TIMESTAMP")
  }

  primary_key {
    columns = [column.id]
  }
}

table "events" {
  schema = schema.public

  column "id" {
    type = uuid
    null = false
  }
  column "project_id" {
    type = uuid
    null = false
  }
  column "event_key" {
    type = text
    null = false
  }
  column "active" {
    type    = bool
    null    = false
    default = false
  }

  primary_key {
    columns = [column.id]
  }
  foreign_key "events_project_id_fkey" {
    columns     = [column.project_id]
    ref_columns = [table.projects.column.id]
    on_update   = NO_ACTION
    on_delete   = CASCADE
  }
  index "events_active_key" {
    unique  = true
    columns = [column.project_id, column.event_key]
    where   = "active"
  }
  check "events_event_key_nonempty" {
    expr = "length(event_key) > 0"
  }
}

Defaults, checks, and partial-index predicates are SQL strings, not a portable expression language. commons-db does not translate them. Every expression in a cross-database bundle must be accepted with the intended meaning by both PostgreSQL and SQLite. CURRENT_TIMESTAMP and sql("'{}'") are portable examples; PostgreSQL-only casts such as ::jsonb, functions such as now(), operators, and JSON expressions must not be assumed portable.

SQLite rejection list

SQLite rejects the following before schema mutation:

These declarations are parser-valid PostgreSQL HCL but are not SQLite-compatible: bit strings, network and MAC types, spatial types, intervals, serial and identity types, XML, money, arrays, ranges and multiranges, full-text search types, enums, domains, and other user-defined types.

Accepted declarations with different SQLite semantics

Some declarations are accepted because their storage representation is useful on both targets, but SQLite cannot enforce every PostgreSQL semantic:

• UUIDs are stored as text without a canonical-format check.
• String length, numeric precision and scale, floating-point width, and time precision are normalized to SQLite affinities.
• json and jsonb both become validated JSON text; PostgreSQL JSONB indexing and operators are not provided.
• Date and time families become datetime; SQLite does not enforce PostgreSQL timezone or temporal-type distinctions.
• Integer widths collapse to SQLite integer affinity.
• Boolean declarations have NUMERIC affinity and do not restrict stored values to 0 or 1; add a portable check when that invariant matters.
• Defaults, checks, foreign-key actions, and partial-index predicates are carried as written. Syntax or semantics that differ between engines fail at apply time or behave according to the target engine.

SQLite migration-time limits

The SQLite apply runs in one transaction and is additive by default. A newly created table may contain every supported constraint above. For an existing table, only missing columns and indexes are applied unless the caller opts in with WithRebuilds().

WithRebuilds() allows Atlas to copy existing rows through a transactional SQLite table rebuild for added primary keys and foreign keys, added or changed named checks, and column default and nullability changes. Added indexes are also supported. It disables foreign-key enforcement before opening the transaction, verifies that no new foreign-key violations appear before commit, and restores enforcement afterward. An incompatible value or constraint rolls the transaction back. Drops, renames, index modifications, and column type changes remain refused; in particular, an existing unique index cannot be removed by opting into rebuilds. A table with an unmanaged trigger is refused rather than silently losing that trigger during rebuild. PostgreSQL accepts the option but already applies these schema changes through its ordinary migration path.

Without WithRebuilds(), adding or changing a check or adding a foreign key on an existing table is unsupported, and adding a JSON column may be refused because its generated json_valid check is a table constraint. An otherwise portable added column must also satisfy SQLite's native ALTER TABLE ADD COLUMN restrictions; for example, a required column generally needs a usable default when rows already exist. WithRebuilds() does not transform or backfill values. Tables and views outside the bundle are not managed; callers must assess views that depend on changed columns before applying a rebuild.

PostgreSQL schema-scoped bundles

WithSchema applies one bundle inside a normalized PostgreSQL schema and leaves the default behavior on public unchanged:

err := migrate.Apply(ctx, dsn, schemaFS,
    migrate.WithName("runtime"),
    migrate.WithSchema("agent_tenant_context"),
)

The schema name must be a lowercase PostgreSQL identifier no longer than 63 bytes. The migration creates the schema and scopes Atlas inspection, HCL objects, SQL migration metadata, dependent-view handling, and managed security to it. A reusable HCL bundle still declares only schema "public" {}; migration remaps that declaration to the selected schema.

SQL files run with the selected schema as search_path. Keep objects owned by the bundle unqualified so the same bundle can target different schemas. Explicitly qualified SQL remains explicit and is not rewritten.

PostgreSQL SQL phases and security

SQL files default to the post-Atlas phase. Directives are read from the leading comment header:

-- phase: pre
-- dependsOn: 001_extensions.sql
-- runs: always
-- transaction: false

• phase is pre or post; omitted means post.
• dependsOn uses paths relative to the migration root. Dependencies are topologically ordered and changes rerun all transitive dependents.
• Scripts are transactional by default; the SQL and its SHA-256 migration log commit atomically.
• runs: always re-runs a script on every apply. It is an escape hatch for reconciliation that truly must occur at every boot, not a replacement for dependency tracking.

Views are restored automatically when Atlas reshapes a base table they depend on. Prefer small hash-gated files, such as one file per view or shared dependency, so a table reshape re-creates only affected objects and steady-state startup remains DDL-free.

Atlas-style PostgreSQL role and permission blocks may live in the same HCL files as tables. The OSS migration layer supports roles and memberships plus schema, table, column, and sequence permissions. Managed grants are reconciled exactly within the bundle's metadata scope; unrelated roles and grants remain untouched. Different scopes must not manage the same role or grantee/object pair.

Reusable PostgreSQL test templates

migrate.NewProvisioner adapts an HCL and SQL bundle to dbtest.Options.Provisioner:

handle := dbtest.ForT(t, dbtest.Options{
    Name:    "query_sessions",
    LogName: "query-sessions-test",
    Provisioner: migrate.NewProvisioner(
        querySchema,
        migrate.WithDir("migrations"),
        migrate.WithName("query"),
    ),
})

The provisioner fingerprints the migration name, directory, Atlas options, variables, and every HCL and SQL file. dbtest prepares one sealed PostgreSQL template for that fingerprint, clones an isolated database for each call, and keeps the template for later tests. Normal hash-gated SQL is already present in the clone; runs: always scripts and security reconciliation still run for every instance.

This option requires database creation. dbtest fails before invoking the provisioner when COMMONS_DB_CREATE=false because that mode targets the configured database directly.

Programmatic SQLite declarations

The migrate/sqlite subpackage remains the lower-level seam for callers that already own a *sql.DB or *sql.Tx and construct Atlas *schema.Table declarations in Go. Its ReconcileTables function uses the same additive-only policy but does not load or project HCL.

declared, err := sqlitetable.Table{Name: "events", Columns: columns, StoredAs: stored, PrimaryKey: []string{"id"}}.Declare()
if err != nil {
    return err
}
if err := sqlitemigrate.ReconcileTables(ctx, tx, sqlitemigrate.ReconcileOptions{}, declared); err != nil {
    return err
}

The record store uses this lower-level API to add newly declared columns to tables that may already contain rows. New filesystem-backed migrations should call the target-aware parent migrate.Apply instead.

SQL Server

sqlserver:// targets reconcile through the Atlas SQL Server driver in atlas/sqlserver, which commons-db ships because Atlas OSS has none. A bundle declares tables in SQL Server-shaped HCL and stored procedures and functions as plain .sql files:

//go:embed db
var bundle embed.FS

err := migrate.Apply(ctx, "sqlserver://app:secret@db:1433?database=acme", bundle,
    migrate.WithDir("db"),
    migrate.WithRoutines("db/routines"),
    migrate.WithName("acme/orders"),
)

Routines come from .sql files

WithRoutines(dir) declares one routine per .sql file under dir. Each file holds exactly one CREATE [OR ALTER] PROCEDURE|FUNCTION batch, optionally followed by GO, and is named after the routine it creates (acme_count_orders.sql creates acme_count_orders). The file is the routine's only source: there are no procedure or function HCL blocks. It runs as written in SSMS or sqlcmd, and migrate runs the same text, with CREATE rewritten to CREATE OR ALTER and every other byte kept, as its own batch.

A routine is compared with the live sys.sql_modules definition through sqlglot's canonical T-SQL: comments, whitespace, keyword case, [brackets] and OR ALTER do not count as changes, while a literal or any other token does and produces one CREATE OR ALTER. A definition, declared or live, that sqlglot cannot parse is an error naming the routine, never a guessed change. Routine files are never hash-gated SQL scripts.

A routine that is removed from the bundle is left in the database; migrate never drops procedures or functions.

Tables from HCL

.hcl files under WithDir are optional when the bundle has routines. They declare exactly schema "dbo" {} and its tables:

Types: bit, tinyint, smallint, int, bigint, decimal(p,s), numeric(p,s), money, smallmoney, float(n) (stored as real up to 24 bits), real, char(n), varchar(n), nchar(n), nvarchar(n), text, ntext, binary(n), varbinary(n), image, date, time(p), datetime, datetime2(p), smalldatetime, datetimeoffset(p), uniqueidentifier, xml, sql_variant and rowversion (timestamp). A (max) size is written with sql(): type = sql("nvarchar(max)"). Defaults are named DF_<table>_<column>; changing one drops the live default constraint, whatever its name, and adds the new one. String defaults on nchar/nvarchar/ntext columns are written as N'...'.

Scoped inspection

An OIPA-sized database holds thousands of objects, so migrate inspects only the tables and routines the bundle names. Undeclared objects are never read, and so never changed. Inside declared tables, columns, keys, foreign keys and checks are reconciled to the declaration, but indexes the bundle does not declare are kept, so tuning indexes added by a DBA survive. An apply that finds nothing to change runs no DDL. Every apply holds a session sp_getapplock named after WithName (one-minute wait), so concurrent applies of one bundle serialize.

Compatibility level 100

Generated DDL and the catalog queries avoid syntax newer than compatibility level 100 (no STRING_AGG, OPENJSON or DROP ... IF EXISTS). CREATE OR ALTER depends on the engine version (2016 SP1+), not the database's compatibility level.

Refused on SQL Server

These fail with an error instead of being ignored:

• hash-gated pre/post .sql scripts outside the routine directory;
• WithSchema other than dbo, WithDrops and WithRebuilds;
• role and permission blocks;
• view, trigger, sequence, function and procedure HCL blocks, computed columns, user-defined and alias types (including sysname), and any unknown column, index or table attribute;
• declared tables that the database has partitioned or carries triggers on, and IDENTITY changes on an existing column.