Schema in Source Control: Treating Your Database Like Software

Shannon Lowder

Shannon Lowder

— 2 min read

I've had this conversation more times than I can count: "We keep our schema in source control." "What does that mean exactly?" "We have a folder with all our CREATE scripts." And then I look at the folder, and it has one monolithic create_all_tables.sql from 2009, twelve ALTER scripts with no discernible order, and three stored procedures that exist in the database but not in the folder. That's not source control for the schema. That's a backup plan that happens to use Git.

Real schema source control means the repository is the authoritative description of the schema. If it's not in the repo, it's not supposed to exist in the database. Here's what that actually requires.

One Object, One File

The organizing principle for a SSDT project (or any reasonable schema source control approach) is one file per database object. Not one file for all tables, not one file per schema — one file per table, view, stored procedure, function, trigger, index.

sql/
  Tables/
    dbo.Customers.sql
    dbo.Orders.sql
    dbo.OrderItems.sql
  Views/
    dbo.CustomerOrderSummary.sql
  Procedures/
    dbo.usp_CreateOrder.sql
    dbo.usp_GetCustomerOrders.sql
  Functions/
    dbo.fn_CalculateOrderTotal.sql

Why does this matter? Because when a stored procedure changes, you want the diff to show exactly what changed in that procedure — not the procedure buried inside a 10,000-line file with 200 other objects. Meaningful diffs require meaningful file granularity.

The Repository as Ground Truth

Once you commit to the one-object-one-file model and start using SSDT's deployment model, a discipline follows: you stop making ad-hoc changes in SSMS on production. Changes to the schema go through the project, through source control, through the deployment pipeline. The "quick fix" stored procedure change that you applied directly to prod but forgot to commit is exactly what this process is designed to prevent.

Enforcement mechanism: run a schema comparison between the SSDT project and production on a regular schedule. Any differences represent out-of-band changes. In SSDT this is the Schema Compare tool — compare project to a live database, review the diff, either add the object to the project or script a removal from the database. Over time, the gaps close and the discipline self-reinforces.

What Branches Mean for Database Development

When the schema is in source control with the application code, feature branches can include both the application logic and the schema changes the feature needs. A feature that adds a new column to the Customers table has that ALTER TABLE reflected in the SSDT project on the feature branch. When the branch merges, the schema change merges with it. When the branch deploys, both the application code and the schema change deploy together.

This coordination was previously handled through release notes ("don't forget to run migration script #47 before deploying this build") and was wrong often enough to cause incidents. When schema and code move together through branches, the coordination is structural, not procedural.

The Schema History

A SSDT project in source control gives you something you probably didn't have before: a complete history of every schema change. git log on the Tables/ folder shows you when every column was added, who added it, and (if commit messages are written well) why.

This history answers questions that used to be unanswerable: "When was this column added?" "Who changed this stored procedure last?" "What did this table look like before the August release?" Previously those answers required digging through email threads or deployment logs. With schema in source control, they're a git log command. That's the difference between institutional memory as procedure and institutional memory as data.

The investment to get a legacy database into a SSDT project is real — you need to script out every existing object and get it into the project structure. It's a day or two of work for a medium-sized database. It's worth it. As always, I'm here to help.

Read more