limitations.md

Limitations

What Schemorph does not do, and what to do instead. Everything here is measured — each entry is pinned by a test, so if an engine upgrade changes the behavior we find out rather than assume.

Expressions that do not round-trip converge the database but not the plan

A declarative tool's core promise is convergence: apply the desired state, diff again, and the plan is empty. Schemorph pins that invariant across the expression-bearing shapes — column defaults (literal and function), CHECK constraints, computed columns, filtered indexes, keys and references (ConvergenceTests).

One shape breaks it. SQL Server does not store a CHECK constraint's text; it stores a parsed expression and re-emits it in its own form. IN (...) comes back as an OR chain:

-- what you wrote
CHECK (Status IN ('A', 'B', 'C'))
-- what the database returns
CHECK ([Status]='C' OR [Status]='B' OR [Status]='A')

DacFx compares the two expressions as text, so the desired state and the database it just produced read as different — forever. The effects are all the ones that matter:

• diff never empties; status always reports drift
• apply succeeds, changes nothing, and leaves the same plan behind
• --expect-plan fingerprints churn, and a CI plan-comment gate never goes quiet

The database itself is correct — the constraint is there and enforced. Only the comparison disagrees.

How to spot it: the plan names the culprit. A change that will not converge carries the slice of SQL the engine keeps re-issuing, so the churning constraint is right there in changes[].sql:

{ "objectName": "dbo.Orders", "actions": ["alter"],
  "sql": "ALTER TABLE [dbo].[Orders] DROP CONSTRAINT [CK_Orders_Status];" }

If the same object and the same constraint come back after a successful apply, this is what you are looking at — not drift, and not a failed apply.

What to do: write the expression in the form the engine stores. The tool will tell you what that is — schemorph inspect renders the live database as desired-state SQL, so applying once and inspecting gives you the canonical text to paste back:

schemorph inspect --url "$SCHEMORPH_URL" --out ./inspected

This is not specific to IN; any expression the engine normalizes differently (redundant parentheses, != vs <>, implicit schema qualification) can behave the same way. inspect is the general remedy.

Why it is not just fixed: converging it would mean re-emitting arbitrary expressions exactly the way SQL Server persists them — reimplementing the engine's own normalizer, in the consumer of that engine. The cure is worse than the disease, and a blunt alternative (ignoring check constraints in the comparison) would hide real changes, which Design Principles §4 forbids.

Column order is not compared

Schemorph diffs state, not ordinal position. A column's physical position is neither declared in a model nor reachable by an ALTER: a column added to an existing table lands last, whatever order the desired state lists it in, and a generated file typically lists it ahead of trailing audit columns. Both providers therefore exclude ordinal position from the comparison — SQL Server through IgnoreColumnOrder, PostgreSQL by comparing columns by name.

The alternative is worse in a different way on each engine. On SQL Server, honoring the difference makes the engine rebuild the whole table (new table, copy rows, drop, rename) to re-seat a column, turning an additive change into full data motion. On PostgreSQL there is no rebuild path at all, so the difference becomes a change no statement can carry out: the plan never empties and the apply has nothing to run.

What this means for you: if column position is material to your application — a positional INSERT, a SELECT * whose column order is consumed — name the columns explicitly. Schemorph will not reorder a table to match a file, and does not report the difference as drift.

A rename is planned as a drop and a create

Objects are matched by name, so one that changed its name is not one object seen twice — it is one that stopped being declared and another that started. Both providers plan it that way: a column rename becomes an ADD COLUMN beside a DROP COLUMN, a table rename a CREATE TABLE beside a DROP TABLE. No rename statement is ever emitted — measured on both engines (RenameTests).

The shape arrives and the values do not. The row survives, the column has the name the files asked for, and it holds nothing. Every check short of reading a value agrees that the rename worked, which is why this is worth stating rather than leaving to the plan to imply.

What stands between that and a plain apply is the destructive gate, on both engines: the drop half is classified destructive, so the plan comes back with no actions, SCHEMORPH001 names what was held back, and the column is untouched. Renaming costs you a refusal you have to read — which is the intended price, since a rename is the easiest way to reach a column removal by accident. It does not look like removing anything.

One consequence is easy to miss: the gate is per object, so at table granularity the create is safe on its own and applies while the drop is withheld. What is left is an empty table beside the full one, and the next diff still reports the drop.

Why it is not just fixed: the two snapshots carry nothing that separates a rename from a removal plus an unrelated addition — the identity a rename asserts exists only in the author's head, and never reached the files. A tool could guess from shape (same type, one name in, one name out) and then execute the guess; the failure mode of a wrong guess is data landing in a column it does not belong to, which is worse than losing it visibly, because nothing downstream reports it. An out-of-band hint would instead move the assertion outside the desired state, where the plan can no longer check it against anything.

What this means for you: rename with the engine's own statement — it carries the identity the files cannot — and then update the desired state to match. The next diff is empty and the values are where you left them. Do the two in the other order and the plan will offer to drop the column instead.

Ordering across strategies is documented, not automatic

Schemorph runs the declarative publish, then re-definitions, then versioned migrations (ADR-0002). It does not interleave them, so a migration that must run between two structural changes has to be expressed as two applies. See ADR-0004 §6.

Rollback restores shape, not data

There is no down-migration verb. Rolling back a structural change means putting the SQL files back and applying them — the desired state is the rollback target (ADR-0005):

git revert <commit>            # or: git checkout <good-ref> -- schema/
schemorph diff  --url "$SCHEMORPH_URL" --schema ./schema   # read the plan first
schemorph apply --url "$SCHEMORPH_URL" --schema ./schema --expect-plan <planHash>

Two things this does not do. It cannot bring back data: a reverse structural diff restores the shape, not the rows a destructive change removed — recovery from that is a backup, not a plan. And it does not undo versioned migrations: those are events in the ledger, run once and never un-run. To reverse a data migration, write a new migration that does the reversing.

Reverting a structural change is usually destructive in the other direction (a column that was added is now dropped), so the plan comes back gated — expect to pass --allow-destructive once you have read it.

Security principals are outside the declarative model

Users, logins, roles, role membership, and permissions are excluded from the comparison (ADR-0006): a generated desired state never emits them, so treating their absence as "delete them" would destroy live principals. Manage them through a separate operational path.

A concurrent index build cannot join the declarative transaction

CREATE INDEX CONCURRENTLY exists to build an index without holding a write lock, and PostgreSQL charges a fixed price for that: the statement refuses to run inside a transaction. PostgreSQL's declarative stage here is one transaction the tool owns — every table, column, constraint and index in that stage lands together or not at all, regardless of what the provider's overall atomicity declares (see failure-semantics.md — atomicity describes the whole apply's three stages, not stage 1 alone; PostgreSQL declares transactional there).

The two cannot both hold, and which one to give up is a decision about your database, not about this tool. So a desired state containing CONCURRENTLY is refused (not_implemented) rather than quietly stripped of the keyword or quietly stripped of the guarantee. Declare the index without CONCURRENTLY to let Schemorph build it inside the apply, or build it by hand outside Schemorph and declare it plainly afterwards — the next diff will find it already there and plan nothing.

A PostgreSQL function/procedure/trigger that changes shape fails loudly, not gracefully

CREATE OR REPLACE FUNCTION/PROCEDURE/TRIGGER is PostgreSQL's own idempotent form, and Schemorph's redefinition of a programmable object is exactly that statement, re-run whenever the file's checksum changes (PgProgrammablesTests, PgProgrammablesLoopTests). The engine itself refuses OR REPLACE when the new definition is not shape-compatible with the old one — a function whose parameter list or return type changed, most commonly — and Schemorph does not catch that refusal and retry as a drop and a create (unlike views, see below). The apply stops where it is, and rolls back: the declarative stage that already succeeded and any redefinitions before the failing one in this run all roll back together with the failing statement — one provider-owned session (ADR-0004's 2026-08-20 addendum) covers the whole apply — and the engine's own error (its SQLSTATE and message) is what you see. What to do: change the file to something shape-compatible — adding a new function overload, for instance, rather than changing an existing one's signature — or, if the shape change is genuinely wanted, drop the object yourself first (DROP FUNCTION "f"(...);) so OR REPLACE lands as a fresh create next time. Either way, the fix is: change the file (and the live object, if you took the drop route) and re-run apply — the same recovery as any other rolled-back failure (failure-semantics.md). Nothing from the failed attempt is left partially committed to reconcile by hand.

On SQL Server this is not a gap to compare against: CREATE OR ALTER runs through DacFx's own declarative model, which the SQL Server provider already uses for structural comparison, so a shape-incompatible redefinition there can be planned as a rebuild rather than simply failing. PostgreSQL's programmable objects are handled as idempotent text, not diffed structurally, so there is no equivalent rebuild path yet for functions/procedures/triggers — a real difference between the providers, not merely an unfinished mirror of one.

Views are the one exception: before planning a CREATE OR REPLACE VIEW, the provider creates the file's query under a throwaway name and compares its columns against the live definition. When they are not append-compatible (a rename, reorder, retype, or removal — the shape CREATE OR REPLACE VIEW cannot express, SQLSTATE 42P16), the plan becomes a DROP VIEW + CREATE VIEW instead — automatically, with no file change needed — provided nothing else references the view. When something does, diff/apply refuse up front (SCHEMORPH010, docs/errors.md) rather than attempt an automatic CASCADE, which is not implemented: drop the dependent objects yourself first, or restructure to avoid the incompatible change, then re-run.

A PostgreSQL programmable object is always redefined once on adoption

Bringing Schemorph to a database that already has a matching view or function always re-runs its CREATE OR REPLACE once, even when the live definition already agrees with the file. SQL Server's provider can skip this: it reads sys.sql_modules, which stores the deployed text verbatim, and matches it against the file before deciding to redefine. PostgreSQL has no such verbatim store — pg_get_viewdef and its siblings return the engine's re-rendered canonical form, so a textual comparison would almost never match even when the definitions genuinely agree, and a false match would be worse than a redundant redefinition: it would silently adopt a definition that actually differs. CREATE OR REPLACE is idempotent by construction, so the one-time redefinition changes nothing beyond what the file already says; the ledger records it and every apply after that is a real no-op, same as SQL Server.

A connection lost right at commit acknowledgement is not reproduced live

FailureStage.Commit covers the moment the provider's session has sent the commit and the connection drops before the acknowledgement returns — the apply cannot tell whether the database actually committed. This path is exercised at the unit level, where a fake session throws from CommitAsync, but there is no integration test that drops a real connection at that exact instant against a live database: reproducing it deterministically needs fault injection (holding a proxy connection open and severing it right after the commit statement is sent), and a test built on that kind of timing is prone to flake in ways that would cost more reliability than the coverage buys back. The stage is documented, and the outcome it reports — failure-semantics.md — is what a caller should act on; whether it is exercised live is a coverage gap, not a behavior gap.

Two database engines, at equal capability range

SQL Server is complete. PostgreSQL now declares the same capability range — tables, columns, constraints, indexes, the target schema, views, functions, procedures, triggers, and versioned migrations (ADR-0003, ADR-0007). What remains outside the declaration — non-transactional DDL such as CREATE INDEX CONCURRENTLY, which cannot join the one transaction the declarative stage owns — is refused with an error naming what the provider does support, never half-planned.

The refusal is the contract, not a bug: a plan that cannot see a difference must not claim a sync. Ask the provider what it covers with schemorph schema — the capability list is part of the machine-readable manifest.

Parity means identical contract and equal capability range — deliberately not identical limitations. The expression-comparison defect above is SQL-Server-specific: the PostgreSQL comparison normalizes both sides through the engine's own renderings, so enum-style CHECK constraints converge there.

The destructive gate is not one of the places they differ. Removing a column the desired state no longer declares is classified destructive on both, each provider proving it from its own comparison rather than from the generated text — the same criterion, twice, because whether rows survive is a fact about the change and not about how a generator worded it. A column dropped and re-added under the same name is excluded on both for the same reason: its values are the new definition's output, so they are replaced rather than lost.

The rule underneath is worth stating, because it is what keeps parity meaningful while the providers grow at different rates: a provider that cannot prove a distinction reports nothing and keeps the coarser object-level classification. It under-claims rather than guessing. So an engine gaining a signal narrows what gets refused by making the refusal more accurate — never by asking you to trust a judgment the tool could not make.