Skip to content
PtahPtah
Edit this pagePropose a change on GitHubView sourceOpen the source used for this buildReport a documentation issueOpen a prefilled GitHub issue

SQL Server

Ptah supports SQL Server and Azure SQL as a deliberately conservative portable subset under the canonical dialect name sqlserver (the aliases mssql, sql-server, sql_server, and tsql normalize to it). This page covers the connection URL, what the subset includes, and the two behaviors that most often surprise SQL Server users: collation-aware identifier comparison and filtered-index predicate spelling. Commands that introspect this behavior require a live SQL Server database URL.

Connecting

Section titled “Connecting”

Pass a canonical sqlserver:// URL; mssql:// input is accepted and normalized:

Terminal window
ptah db read --db-url "sqlserver://sa:$SA_PASSWORD@localhost:1433?database=app&encrypt=disable"

The Ptah-only schema query parameter selects the default schema for introspection, migration metadata, and write helpers; it is removed before the URL reaches the driver, and it defaults to dbo:

Terminal window
ptah db read --db-url "sqlserver://sa:$SA_PASSWORD@localhost:1433?database=app&schema=audit&encrypt=disable"

--migrations-schema still takes precedence for migration metadata placement.

Supported surface

Section titled “Supported surface”
  • T-SQL rendering with bracket-quoted identifiers and schema-qualified names such as dbo.users.
  • IDENTITY(start,increment) for auto-increment columns and NVARCHAR/NVARCHAR(MAX) string mapping.
  • Core table DDL: primary keys, unique constraints, foreign keys, CHECK constraints, and indexes with ordered ascending or descending key columns.
  • Filtered indexes (see below).
  • Views and triggers rendered from raw SQL definitions.
  • Synonyms: declared, rendered, introspected from sys.synonyms, and diffed, including targets in another database or behind a linked server (see below).
  • Extended properties at database, schema, table and column scope: declared, rendered, introspected from sys.extended_properties, and diffed (see below).
  • Schemas the declared objects live in, created before them (see below).
  • Live introspection from sys.tables, sys.columns, sys.indexes, and related catalog views.
  • Transactional migration apply for DDL SQL Server supports in transactions, and migration-run serialization through a session application lock.
  • Transactional dev-database cleanup across supported user schemas, with a preflight that rejects replication-enabled databases and replicated tables.
  • One-row upsert rendering to MERGE through the Go DML AST (an embedder surface, not a CLI command).

SQL Server has no native enum object, so enum annotations render as NVARCHAR(255) columns with a generated CHECK constraint:

[status] NVARCHAR(255) NOT NULL CHECK ([status] IN ('active', 'blocked'))

Synonyms

Section titled “Synonyms”

A synonym is an alias for another object, and it is the one schema object whose target may live somewhere Ptah does not manage:

//ptah:schema:synonym name="current_orders" schema="app" target="sales.orders"
type CurrentOrdersSynonym struct{}
CREATE SYNONYM [app].[current_orders] FOR [sales].[orders];

The target is written with as many parts as it needs. Two parts name an object in this database; three name another database; four name a linked server. Ptah records what it was given and never rewrites it, because the part count is what tells SQL Server where to resolve the name, and adding a qualifier would turn a remote reference into a local one.

Ptah manages the alias and never the target. A synonym pointing outside this database is a supported declaration rather than an error — SQL Server does not require the target to exist either — and dependency ordering treats it as having no local dependency, so nothing tries to create or drop the object it points at. A local target does participate in ordering: the alias is emitted after the table or view it names.

A changed target is planned as a drop and a create in that order, because T-SQL has no ALTER SYNONYM and CREATE SYNONYM refuses a name that already exists.

Targets are compared with the server’s own bracket quoting normalized away, so a declared dbo.orders and a stored [dbo].[orders] are the same target rather than a difference reported on every run.

Every other target names a declared synonym as skipped rather than rendering nothing: the object exists in the schema model for one engine, and a dialect that dropped it silently would lose a declaration without saying so.

Extended properties

Section titled “Extended properties”

An extended property is a named value attached to a schema, a table, or a column of one:

//ptah:schema:extendedproperty name="retention" schema="app" table="orders" value="90d"
type OrdersRetentionProperty struct{}


//ptah:schema:extendedproperty name="classification" schema="app" table="orders" column="email" value="pii"
type OrdersEmailClassification struct{}
EXEC sp_addextendedproperty @name = N'retention', @value = N'90d', @level0type = N'SCHEMA', @level0name = N'app', @level1type = N'TABLE', @level1name = N'orders';
EXEC sp_addextendedproperty @name = N'classification', @value = N'pii', @level0type = N'SCHEMA', @level0name = N'app', @level1type = N'TABLE', @level1name = N'orders', @level2type = N'COLUMN', @level2name = N'email';

The address is the identity. SQL Server stores a property under a class and up to two ids, so the same name on the database, on a schema, on a table of it, and on a column of that table is four different properties. Which levels a declaration sets decides its scope: no level at all is the database’s own property, schema alone is schema scope, adding table addresses the table, and adding column addresses a column of it. A level without the one above it is refused, because SQL Server has no level N without a level N-1.

//ptah:schema:extendedproperty name="deployment_tier" value="production"
type DeploymentTierProperty struct{}
EXEC sp_addextendedproperty @name = N'deployment_tier', @value = N'production';

A database-scoped property passes no level, and that is not an omission: an empty @level0name would be a property on a schema called "", which the procedure also accepts and which belongs to nothing. It is in no schema either, so --schema neither selects nor removes it — the same rule an extension follows, where placement is not ownership.

A changed value plans sp_updateextendedproperty rather than a drop and an add, so the property is never absent partway through a script. Properties are emitted after every object one can hang off, and dropped before the objects they belong to.

MS_Description is refused by name. Ptah already models it as the object’s comment — the reader turns it into one and the renderer writes it as one — and accepting it here as well would give one live value two owners, each planning against it without seeing the other.

A value Ptah cannot write back is reported and left alone. sp_addextendedproperty takes a sql_variant, so a property may hold an int or a date as well as a string, and Ptah writes values as N'' literals. Re-emitting a non-string value through one would change its stored type, and CONVERT(NVARCHAR, value) on a date answers a locale-dependent rendering rather than the value. Such a property appears in a read with its base type and no value, and a comparison plans nothing for it in either direction.

One kind of property is outside this scope and is not read at all: one on an object other than a table — a view, a procedure, an index — which takes a different @level1type than the one Ptah writes.

Realm cleanup still refuses a database whose database- or schema-scoped properties it would leave behind. That guard predates this support and is unchanged: adding cleanup for them is a destructive change with its own measurement.

Every other target names a declared extended property as skipped rather than rendering nothing, for the same reason a synonym is: the object exists in the schema model for one engine, and a dialect that dropped it silently would lose a declaration without saying so.

Extended properties travel through the schema model, the renderer, the reader and the comparison, and the HCL surface schema inspect writes carries them — as it does synonyms.

synonym "current_orders" {
  schema = schema.app
  target = "sales.orders"
}


extended_property "MS_Description" {
  schema = schema.app
  table  = "orders"
  column = "total"
  value  = "order total including tax"
}

The owner is written as plain strings rather than as table.orders and column.total references, because that is what the statement carries: @level1name = N'orders' is a name, and a reference would resolve to nothing once --include left the table out. A block with no schema is a database-scoped property, the address that passes no level at all.

What a document cannot name it does not drop. YAML has no key for either object, and a .sql document is written back with CREATE SYNONYM and sp_addextendedproperty but read as a schema holding neither, so a file in those formats could not have declared one — and reading its silence as intent makes this loop destructive:

Terminal window
ptah schema inspect --db-url "$SQLSERVER_URL" --format sql > out.sql
ptah schema apply --db-url "$SQLSERVER_URL" --to file://out.sql

Each loader records what its format cannot express, and the comparison withholds those removals. HCL and a Go schema — //ptah:schema:synonym and //ptah:schema:extendedproperty — can both declare the two objects, so silence in either is a request to remove.

Schemas

Section titled “Schemas”

A schema on SQL Server is an ordinary object inside the connected database, so a declaration that puts a table in one is asking for the schema too. Ptah creates it before the objects that need it:

IF SCHEMA_ID('app') IS NULL
    EXEC('CREATE SCHEMA [app]');
CREATE TABLE [app].[widget] (
  [id] INT PRIMARY KEY
);

The guarded form is not a style choice. SQL Server has no CREATE SCHEMA IF NOT EXISTS, repeating the statement answers Msg 2714: There is already an object named 'app' in the database, and CREATE SCHEMA must be the first statement of its batch — which is what the EXEC is for.

The schemas come from the objects being added, so a migration that adds nothing emits no schema statement and a single-schema declaration emits none either. A schema a document declares and puts nothing in is not created.

MySQL, MariaDB, ClickHouse and Oracle do not do this, and that is a difference in what a schema IS rather than a gap. A schema there is a database — or, on Oracle, a user — so creating one is CREATE DATABASE or CREATE USER, an administrative act outside what a schema migration owns.

Identifier collation

Section titled “Identifier collation”

SQL Server compares object identifiers using catalog collation rules — case, accent, locale, kana, and width sensitivity all depend on the target catalog. Ptah does not reproduce those rules locally; the live catalog is the source of truth. When comparing against a live database, Ptah asks SQL Server to resolve candidate identifier equivalence under COLLATE CATALOG_DEFAULT and carries that resolved snapshot through comparison, planning, checkpoint generation, and shadow verification (shadow execution is rejected when the shadow catalog resolves names differently).

An offline, dialect-only comparison cannot know the target collation, so it confirms only exact-spelling identity and treats distinct unresolved names in one namespace as potential conflicts: planning rejects the ambiguity instead of guessing. This makes offline SQL Server planning intentionally stricter than live planning. Embedders choose between the live-aware and offline comparison APIs — see Public Go API.

Filtered indexes

Section titled “Filtered indexes”

Declare a filtered index with the index annotation’s condition (or where) attribute:

//ptah:schema:index name="idx_active_users" fields="status" condition="status = 1"

The predicate renders verbatim as CREATE INDEX ... WHERE status = 1. SQL Server cannot alter a predicate in place, so a changed predicate plans as DROP INDEX plus CREATE INDEX ... WHERE with the new predicate.

SQL Server stores predicates in a canonical spelling — status = 1 comes back as ([status]=(1)) — and Ptah normalizes bracket quoting, parenthesized numeric literals, case, and whitespace before deciding whether an index changed. Rewrites beyond that spelling (such as the N'...' prefix on Unicode string literals) are not reconstructed, so those predicates compare as changed on every run. If a filtered index keeps reporting drift after it was applied, read the stored spelling with ptah db read and use it in the annotation; the rendered SQL always preserves your annotation text verbatim.

Limitations

Section titled “Limitations”
  • No PostgreSQL-style extensions, row-level security, roles and grants, or materialized views.
  • Column drift planning emits direct ALTER COLUMN only for type and nullability changes; default, generated-expression, unique, and CHECK changes need a manual migration.
  • Automatic column removal is rejected, because dependent constraints, defaults, and indexes must be dropped in the correct order first.
  • Standalone sequence objects are outside the subset.
  • A synonym’s target is recorded and resolved by the server, not validated by Ptah: a synonym naming an object that does not exist is created successfully and fails when something uses it, which is SQL Server’s own behavior.
  • View and trigger introspection records the persisted definition text without normalizing it into drift-safe definitions.
  • Index planning preserves key order, direction, and filtered predicates, but not included columns.
  • Engine-specific options such as WITH (ONLINE = ON) are not planned.
  • Dev-database cleanup rejects database replication, replicated tables, and unsupported database-scoped artifacts before its first DDL statement.
  • A column declared both PRIMARY KEY and UNIQUE is refused rather than rendered. SQL Server rejects a INT PRIMARY KEY UNIQUE outright, where the other engines fold it, so there is no rendering of that column this server accepts and emitting the key alone would discard what the author wrote. Write the key alone, or write a INT UNIQUE, PRIMARY KEY (a), which is a different statement this server does accept and which Ptah renders as the primary key plus the unique constraint it produces.

Next steps

Section titled “Next steps”