Putting the Contract Under CI/CD: Database Deployments You Can Trust
So far in this series the database has a published interface (the contract) and a read surface that you keep stable even when an ORM wants to bind straight to your tables. Both of those are design ideas. This post is about the machinery that makes them survive contact with a team shipping changes every week, because a contract you cannot deploy the same way twice is not a contract. It is a hope.
I have written before about getting database code into version control and a pipeline at all, in the AI-for-DBAs post on version control and CI/CD. That post makes the case that database code belongs in a repository and a build pipeline like any other code, and what that unlocks. Start there if your database is not in source control yet; everything below assumes it is. What I want to add here is the part that is specific to treating the schema as a contract: using the pipeline not just to deploy, but to catch the moment a change would break the promise.
Two Ways to Ship a Schema, and What Each Protects
There are two broad models for deploying database changes, and the difference between them is not a detail. It decides how much control you have over the one operation that can ruin your week: changing an existing object that data already lives in.
State-based (declarative). You keep the desired end state of the schema in source – the full CREATE definitions of every table, view, and procedure – and a tool computes the difference between that state and the target database at deploy time. In the SQL Server world this is SSDT projects and the DACPAC they produce, published with SqlPackage.[1] The strength of this model is the published interface from the first post: views and procedures have no data of their own, so regenerating them from their definitions is safe and the diff is exactly what you want. The weakness is that the tool decides how to get from the old shape to the new one, and for a destructive change – dropping a column, narrowing a type – its plan may not be the plan you would have chosen.
Migration-based (imperative). You keep an ordered sequence of change scripts, each one a small step, and the tool runs the ones a given database has not seen yet. Flyway,[2] Liquibase,[3] and DbUp[4] all work this way, the last being a lightweight .NET library that simply tracks which scripts have run. The strength is total control over how each change happens, in an order you reviewed, which is exactly what a data backfill or a careful type change needs. The weakness is that nobody computes the diff for you, so the discipline of small, reviewed, version-controlled steps is the whole game – which is precisely the practice that evolutionary database design has argued for all along.[5]
You do not have to pick one religion. A common and effective split is state-based for the parts with no data – the published views and procedures, where regenerating from source is safe – and migration-based for the tables underneath, where every change to existing data needs a hand-reviewed step. The point is to choose deliberately, because the choice is really about who decides how a breaking change executes: the tool, or you.
Automating Expand, Migrate, Contract
The first post described how to make a breaking change safely in three phases: expand the interface to support old and new at once, migrate consumers on their own schedule, then contract by removing the old shape once nothing uses it. A pipeline is what makes that practical, because the three phases are not one deployment. They are three deployments, often weeks apart, and the migration tool is what keeps track of where each environment is in the sequence.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
/* Migration V31__expand_email_address.sql Phase 1 (expand): add the new column, backfill it, keep the old one. Safe and additive - nothing that reads the old column breaks. */ ALTER TABLE [dbo].[customer] ADD [email_address] nvarchar(256) NULL; GO UPDATE [dbo].[customer] SET [email_address] = [email] WHERE [email_address] IS NULL; GO |
Weeks later, after the read surface has been pointed at the new column and every consumer has moved, a second migration contracts:
|
1 2 3 4 5 6 |
/* Migration V37__contract_drop_email.sql Phase 3 (contract): remove the old column. By now nothing reads it. This is the only breaking step, and it runs after the migration window. */ ALTER TABLE [dbo].[customer] DROP COLUMN [email]; GO |
The value of putting this in a migration tool is that V31 and V37 are recorded, ordered, and applied exactly once per environment. You can look at any database and know which phase it is in. Production does not get the contract step until it has had the expand step and the weeks in between, because the tool will not run them out of order.
Make the Pipeline Refuse Breaking Changes
The real prize is not automated deployment. It is automated refusal: a build that fails when a change would break the contract, before it reaches anyone who depends on it. There are three layers worth wiring in.
Block data loss at publish time. SqlPackage has a BlockOnPossibleDataLoss option, on by default, that stops a deployment when the computed plan would drop or truncate data.[1] Leave it on. When it fires, it is usually telling you that what you thought was an additive change is actually a contract break that needs the expand-migrate-contract treatment instead of a straight publish.
Detect drift before you deploy. Compare the target database against the schema in source as a pipeline step. If production has drifted – someone made an emergency change by hand and never put it in the repo – you want the build to surface that before your deployment quietly reverts it. Drift detection is one of the genuine bonuses of having the schema in source control, and the AI-for-DBAs CI/CD post goes into it in more depth.
Test the contract itself. This is the step most teams skip, and it is the one that most directly protects the promise. Add a smoke test that queries the published interface and asserts its shape – that the columns the contract promises still exist, with the types it promised:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
/* Contract test: fail the build if the published view loses a promised column or changes a promised type. Runs against the freshly built database in CI. */ DECLARE @violations int; SELECT @violations = COUNT(1) FROM ( VALUES (N'customer_id', N'int') , (N'customer_name', N'nvarchar') , (N'email_address', N'nvarchar') , (N'created_utc', N'datetime2') ) AS [promised] ([column_name], [type_name]) WHERE NOT EXISTS ( SELECT 1 FROM [sys].[columns] AS [c] INNER JOIN [sys].[types] AS [t] ON [t].[user_type_id] = [c].[user_type_id] WHERE [c].[object_id] = OBJECT_ID(N'[api].[customer_read]') AND [c].[name] = [promised].[column_name] AND [t].[name] = [promised].[type_name] ); IF @violations > 0 BEGIN THROW 50001, N'Published contract for [api].[customer_read] has been broken.', 1; END; |
That is a deliberately small example, and a real contract test would cover result-set shapes of procedures and key behaviors too. The principle is what matters: the promised interface is written down as an assertion that the build runs on every change, so the day someone renames a column out from under the view, the pipeline says no instead of a partner’s nightly job saying no three weeks later. This is the contract-testing idea that API teams have used for years, applied to the database’s published surface.
Version the Contract, Not Just the Code
If the schema is a published interface, it has a version, and the pipeline is where that version gets stamped. Tag the release, record which migration the contract reached, and keep a short changelog of what changed and in which category – additive, deprecating, or breaking. None of this requires special tooling; a tag and a text file go a long way. What it buys you is the ability to answer the question every consumer eventually asks, “what changed and when,” from a record instead of from memory, the same way a public API publishes a changelog so its callers are never surprised.
Where This Leaves Us
The pipeline turns the contract from a good intention into something enforced. State-based or migration-based, you choose who controls a breaking change. Expand-migrate-contract becomes a sequence the tool tracks across environments. And a handful of pipeline gates – block data loss, detect drift, test the contract – mean the build refuses the change that would break the promise, at the cheapest possible moment to find out.
There is one gate I waved at and did not open: testing. A contract smoke test proves the shape is intact, but it does not prove the behavior behind the shape is still correct. That is a bigger subject, and it is next: Proving the Contract Holds: Testing Database Code.
How does your team deploy database changes today, and does the pipeline ever tell you no? I would like to hear about it on Bluesky or LinkedIn.
References and Footnotes
- SqlPackage – Microsoft Learn. The command-line tool that publishes a DACPAC by computing the difference between the desired schema state and the target database, including the
BlockOnPossibleDataLosssafeguard. ↩ - Flyway documentation – Redgate. A migration-based deployment tool that applies an ordered sequence of versioned change scripts, with commands such as migrate, validate, and undo. ↩
- Liquibase – GitHub. An open-source, database-independent migration tool that tracks and applies ordered changesets across many database engines. ↩
- DbUp – Read the Docs. A lightweight .NET library that tracks which change scripts have run and applies the ones needed to bring a SQL Server database up to date. ↩
- Evolutionary Database Design – Pramod Sadalage and Martin Fowler. The practices behind small, ordered, version-controlled database migrations that make incremental schema change safe. ↩