Source control and CI/CD for databases

A database project only helps if everyone works from the same copy of it. In the ninth session Hana moved the Finch Rewards project into a GitHub repository, hf-finchrewards, next to the API code, and Elena set up the pipeline that will build, test and deploy every change. Renata Kowal, the cloud lead, joined for the part about credentials, because the pipeline would need permission to change databases and she wanted to be sure it never held a password. By the end of the session, a change to a stored procedure went from Hana's laptop to the test database through a pull request, an automated build, a review and an automatic deployment, and the same path, with one more approval, led to production.

What goes in the repository

The database project lives in its own folder, src/FinchRewards.Database, with the .sqlproj, one file per object, the pre- and post-deployment scripts, the reference data, the publish profiles for each environment and the refactor log. Tests go in tests/FinchRewards.Database.Tests. The pipeline definitions go in .github/workflows. What stays out matters as much: no connection strings with passwords, no dacpacs (they are built by the pipeline from source), no data beyond reference data, and nothing generated by the build. A .gitignore file excludes the bin and obj folders.

hf-finchrewards/
  .github/
    workflows/database.yml
    CODEOWNERS
  src/
    FinchRewards.Api/              application code
    FinchRewards.Database/
      FinchRewards.Database.sqlproj
      FinchRewards.Database.refactorlog
      rewards/Tables/Member.sql
      rewards/Procedures/uspPostPurchase.sql
      Scripts/PostDeployment.sql
      ReferenceData/Tier.sql
      Profiles/test.publish.xml
      Profiles/prod.publish.xml
  tests/
    FinchRewards.Database.Tests/

Keeping the API and the database in one repository was a deliberate choice. Many changes touch both: a new column, the procedure that writes it and the API endpoint that exposes it. In one repository they go into one pull request, are reviewed together and deploy together. Teams whose database serves many applications often keep it in its own repository instead, with its own release cycle; StockLine, which four systems read, will be set up that way.

Branching and pull requests

The team uses short-lived feature branches from main. Hana creates a branch such as feature/offer-expiry, commits her changes, pushes the branch and opens a pull request. The pull request triggers the build and tests, and at least one other developer reviews it before it can merge. Main is always deployable: whatever is on main is either in test or on its way there. Branches that live for weeks drift away from main and produce painful merges, so Elena's rule is that a branch should merge within three working days, splitting a large change into several smaller pull requests if needed.

Merge conflicts in a database project behave like conflicts in any code, because each object is a text file. Two developers adding different columns to Member will conflict in Member.sql, and resolving it means keeping both columns in the CREATE TABLE statement. Because the file describes state, the resolution is easy to check: the merged file should describe the table everyone wants. The project file itself rarely conflicts with SDK-style projects, which include .sql files by default instead of listing each one. The refactor log can conflict when two people rename things at once; both entries must be kept, in order. After resolving a conflict, the build is the check: if the merged model does not validate, the pull request cannot merge.

Protecting main

Branch protection rules, or rulesets in GitHub, enforce the process rather than relying on good intentions. For main in hf-finchrewards: changes only through pull requests, at least one approving review, the database build and tests must pass, the branch must be up to date with main before merging, and nobody can push directly, including administrators. A CODEOWNERS file names the people who must review changes to particular paths. Elena is a code owner for the tables folder and the publish profiles, so every schema change and every change to deployment options needs her approval, while procedure changes need any developer.

# .github/CODEOWNERS
/src/FinchRewards.Database/rewards/Tables/   @hf-dba-team
/src/FinchRewards.Database/Profiles/         @hf-dba-team
/src/FinchRewards.Database/                  @hf-rewards-devs
/.github/workflows/                          @hf-platform-team

The pipeline

Continuous integration builds and tests every change; continuous delivery deploys every change that passes to an environment, with gates before production. The Finch Rewards workflow has four jobs. Build runs on every pull request and on every push to main: it builds the project with dotnet build, which also runs code analysis, and saves the dacpac as an artifact. Test deploys the dacpac to an empty database created for the run, loads test data and runs the unit tests, then deletes the database. Deploy-test runs only on main: it creates a deployment report and script, saves them as artifacts and publishes to the test database. Deploy-prod waits for a manual approval in the GitHub environment named production, then does the same against production.

name: database
on:
  pull_request:
    paths: ['src/FinchRewards.Database/**', 'tests/FinchRewards.Database.Tests/**']
  push:
    branches: [main]
    paths: ['src/FinchRewards.Database/**']
permissions:
  id-token: write        # allow the workflow to request an Entra token (OIDC)
  contents: read
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-dotnet@v4
        with: { dotnet-version: '8.0.x' }
      - run: dotnet build src/FinchRewards.Database/FinchRewards.Database.sqlproj -c Release
      - uses: actions/upload-artifact@v4
        with: { name: dacpac, path: src/FinchRewards.Database/bin/Release/*.dacpac }
  deploy-test:
    if: github.ref == 'refs/heads/main'
    needs: build
    runs-on: ubuntu-latest
    environment: test
    steps:
      - uses: actions/download-artifact@v4
        with: { name: dacpac }
      - uses: azure/login@v2
        with:
          client-id: ${{ vars.AZURE_CLIENT_ID }}
          tenant-id: ${{ vars.AZURE_TENANT_ID }}
          subscription-id: ${{ vars.AZURE_SUBSCRIPTION_ID }}
      - uses: azure/sql-action@v2
        with:
          connection-string: ${{ vars.SQL_CONNECTION_TEST }}
          path: FinchRewards.Database.dacpac
          action: publish

Secrets and credentials

The workflow never sees a password. Renata set up OpenID Connect federation: an Entra app registration, sp-finchrewards-deploy-test, trusts tokens that GitHub issues for this repository's test environment, and a second one trusts the production environment only. When the job runs, GitHub gives it a short-lived token, the azure/login action exchanges it for an Entra token, and the SQL connection uses that Entra identity, as lesson 1 of MSAZ-281 describes. Nothing long-lived is stored in GitHub. The connection strings in the workflow variables name the server and database and say Authentication=Active Directory Default, so they hold no secret.

DP-800 lists secrets management among the pipeline skills, and the rule is simple: no secret in the repository, ever, including in history. Where a secret is unavoidable, such as an API key for a third-party service called by the database in lesson 11, it lives in Azure Key Vault, and the pipeline or the database reads it at run time through a managed identity. GitHub's secret scanning and push protection, enabled on the organization, block a commit that contains something that looks like a key. If a secret is ever committed, it is rotated at once; deleting the commit is not enough, because clones and caches keep it.

Deploying safely

A database deployment is harder to undo than an application deployment, because data changes cannot simply be rolled back by redeploying the old version. Elena's practices follow from that. Every production deployment publishes the script and the report as artifacts, so the exact change is on record. BlockOnPossibleDataLoss stays on. Breaking changes are split into steps that each deploy safely: to replace a column, first add the new one and write to both, then move the readers, then stop writing the old one, and only in a later release drop it. This expand-and-contract pattern lets the API and the database deploy independently, because every intermediate state works with both the old and new API.

Before production deployments that change large tables, the script is reviewed for operations that rebuild or lock a table, such as changing a column's type, which can hold locks for minutes on PointsTransaction. Those changes are scheduled outside the busy evening hours, and some are done online, for example by creating indexes WITH (ONLINE = ON) where the platform supports it. MSAZ-282 covers the automation and recovery side of these operations from the DBA's point of view.

Where the tests run

The test job needs a real database engine for each run, and there are two practical choices. The first is a SQL Server container started on the hosted runner: the job runs the SQL Server 2025 container image, deploys the dacpac to it, loads the test data and runs the tests, all on the runner, with nothing to clean up afterwards. It is fast and costs nothing beyond the runner, but it is SQL Server, not Azure SQL Database, so a feature that exists only in the cloud cannot be tested this way. The second is a temporary Azure SQL database, created by the job in a test server, ideally on the serverless tier so it costs little while idle, and deleted at the end. Hana's pipeline uses the container for every pull request, because it finishes in about four minutes, and a temporary Azure SQL database for a nightly run of the full suite, which catches the rare differences between the two engines.

Test data is part of the repository too. The tests load a small, synthetic data set from scripts in the test project: 40 members, 6 stores, a few hundred transactions and the reference data, all invented, so no personal information ever leaves production. Each test arranges the rows it needs, runs the procedure and checks the result, and the whole database is thrown away at the end of the run, so tests never depend on the leftovers of other tests.

When a deployment goes wrong

A failed deployment usually fails safely: SqlPackage runs the generated script in a transaction where it can, and the BlockOnPossibleDataLoss check stops before any data is touched. The harder case is a deployment that succeeds but is wrong, for example a procedure with a bug that posts the wrong points. Redeploying the previous dacpac restores the old procedure, but it cannot undo data the bad version wrote. The team's rule is to fix forward: correct the code in a new pull request, deploy it through the pipeline, and repair the affected data with a reviewed script, using the points transactions and the ledger journal to find exactly what changed. For a mistake that damages data badly, the point-in-time restore from MSAZ-282 can recover a copy of the database as it was just before the deployment, from which the right values can be copied back. Every production deployment records its start time in the pipeline log, which tells the team exactly which point in time to restore to.