SQL Database Source Control

Turn a live database into project code

A database project is the database expressed as code: the definitions of its objects, kept in a repository so they can be reviewed, compared, and rebuilt. A SQL database in Fabric has Git integration that produces this project for you, so a team can track the definitions of its database objects over time.

The starting point is a workspace connected to an Azure DevOps or GitHub remote repository. The repository connection applies at the workspace level, and a single branch of the repository is associated with that workspace. How to connect a workspace and how Git integration works in general belong to the Git integration lesson. Here the subject is what happens to the database.

The integration works in two directions, and they do different things.

To commit, create or change objects with any tool (the Fabric SQL editor, SQL Server Management Studio, or the MSSQL extension for Visual Studio Code), open the Source control panel, select the checkbox next to the database, and select Commit.

In the repository, the database is a folder named <yourdatabase>.SQLDatabase. Inside it, each object is a .sql file. A table, for example, is a file such as dbo/Tables/MyTable.sql that holds its CREATE TABLE statement:

CREATE TABLE dbo.MyTable
(
    Id INT PRIMARY KEY,
    ExampleColumn NVARCHAR(50)
);

The file holds the definition of the table, not its rows. As development continues, including edits to objects that already exist, the same commit step keeps the code in step with the database, and the repository shows the history of every object.

Read the SQL project file, do not write it

Next to the object files, the repository holds a SQL project file that contains metadata about the database. That file is what lets Fabric bring more functionality into source control and deployment pipelines.

Fabric generates and updates the project file automatically. Avoid manual edits: whatever you write there is overwritten on the next commit from Fabric. Lasting changes belong in object files or in the supported scripts.

One addition has a specific purpose. To build the SQL project on your own machine with SQL tools such as SQL Server Management Studio or the SQL projects extension for Visual Studio Code, you can add a reference to the master.dacpac file in the project file.

Fabric adds three things to the generated project file.

The exclusion is the one to understand. The queries in the Shared Queries folder are versioned like every other file, but the build does not treat them as database objects. They are scripts to run, not part of the database model, so a query there cannot break model validation.

Work code-first in Visual Studio Code

The project can also be driven from the other end: write the code first and let Fabric apply it. This path suits developers who prefer Visual Studio Code, who already have applications that use SQL projects, or who have more advanced CI/CD requirements.

The setup has three steps:

1. Install Visual Studio Code with the MSSQL and SQL projects extensions.
2. Create a new SQL database in the workspace and commit it without adding any objects. This step adds the empty SQL project and the SQL database item metadata to the repository.
3. Clone the repository to your machine and open the folder. The branch associated with the workspace might not be the default one, so switch to it to see the <yourdatabase>.SQLDatabase folder.

Then add .sql files for the objects you want, inside the folder structure of the database.

Before you commit, build the project from the Database Projects view. The build validates the database model, so a syntax error shows up on your machine. After a successful build, commit the files with the source control view of Visual Studio Code or another Git client, push the commit to the remote repository, and check that the files appear in Azure DevOps or GitHub.

Back in Fabric, the Source control panel might already show that there are pending changes from Git. Select Update all to apply the code from the SQL project to the database.

The local build is optional in the sense that nothing forces it. Its value is timing: Fabric builds the project again during the update, so an error is always found, but a local build finds it before the commit reaches the branch that the whole team uses.

Follow the update into the database

Updating a SQL database in Fabric from source control combines two operations: a SQL project build and a SqlPackage publish.

The build comes first. It validates the syntax of the SQL files and generates a .dacpac file, a package that describes the database model the code adds up to. The publish operation then determines the changes necessary to update the database to match the .dacpac file.

Two consequences follow from that order.

First, validation happens before anything changes in the database. The .dacpac exists only if the SQL files describe a valid model.

Second, the update is differential. The publish works out the difference between the model in the .dacpac and the objects that exist in the database, and it changes only those. An object that did not change is left alone. Once the update completes, connect to the database with any tool and the objects you added to the SQL project are visible.

Read the publish options that Fabric applies

When you run SqlPackage yourself, you choose its options. In Fabric you do not: because the interface is streamlined, Fabric applies a fixed set of four options to the publish operation of every update.

/p:ScriptDatabaseOptions = false
/p:DoNotAlterReplicatedObjects = false
/p:IncludeTransactionalScripts = true
/p:GenerateSmartDefaults = true

Each line is a SqlPackage property, written with /p: followed by the property name and its value. Three of them explain behavior you can observe.

Here is how to use that table.

Suppose a table already holds rows and you add a column that does not allow null values. The existing rows have no value for it. With GenerateSmartDefaults set to true, the publish supplies a default value automatically, so the update can add the column.

ScriptDatabaseOptions set to false matches a rule you will meet in a moment: database-level settings are not carried by an update from source control.

These values are not the defaults of the tool. On its own, SqlPackage defaults GenerateSmartDefaults and IncludeTransactionalScripts to false, and ScriptDatabaseOptions and DoNotAlterReplicatedObjects to true. Fabric reverses all four.

Handle an update that fails or shows Uncommitted

Two things can surprise you right after you select Update all. They look similar and need opposite reactions.

The update fails. If a change to the SQL project has a syntax error or uses a feature that Fabric does not support, the database update fails. Fabric does not repair the code. You must manually revert the change in source control before you can continue. Until then, the branch holds code that cannot be applied, and that affects everyone who updates from it.

The update succeeds, but the database shows Uncommitted. This can happen immediately after the update, even though nobody changed the database.

The reason is how Git integration decides the status. It does a direct comparison of all the file content generated for an item definition, and some unintentional differences are possible. One example is inline attributes on columns: the file you wrote and the file Fabric generates for the same table can differ in form.

Nothing is wrong with the database. The fix is to commit back to source control from the Fabric web interface, which syncs the definition in the repository with what a commit operation generates.

Know what the project does not carry

A database project describes objects. Two things are outside it.

Database-level settings are the first. Settings such as collation and compatibility level are not included in the source control and deployment pipelines integration. For database settings that you can set with T-SQL after database creation, you can modify the database with scripts after deployment.

Table data is the second. When Fabric creates a database from the code in the repository, for example in a workspace created with Branch out to new workspace, the database contains the objects that were checked into source control, and its tables are new and empty. The definitions travel and the rows do not. Any data the new environment needs has to be loaded, or, if it is a small controlled set, managed with a script as the next section shows.

Control static data with a post-deployment script

Pre-deployment and post-deployment scripts attach your own T-SQL to a deployment. The same capability applies to deployment pipelines, so one script covers both delivery paths: an update from source control and a deployment pipeline deployment.

The standard case is a lookup table with a known set of values that do not change often, such as a dbo.Colors table that an application uses. Create a new query in the SQL database editor in Fabric and add a MERGE statement that manages the contents of the table:

MERGE dbo.Colors AS target
USING (VALUES (1, 'Red'), (2, 'Green'), (3, 'Blue')) AS source (Id, Name)
ON target.Id = source.Id
WHEN MATCHED THEN
    UPDATE SET Name = source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, Name) VALUES (source.Id, source.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Read it clause by clause. The list of values is the wanted state. A row that exists in both places gets its name updated. A row missing from the table is inserted. A row in the table that is not in the list is deleted. Running the statement twice gives the same result, which is what a script that runs on every deployment needs.

Registering the query takes two steps. Rename it to something recognizable, such as Post-Deployment-StaticData.sql, and move it to Shared Queries. Then open the ... menu of the query and select Set as Post-deployment Script.

From that point, the query runs automatically as part of any update from source control or deployment pipeline deployment. Nobody has to remember to run it.

Because pre-deployment and post-deployment scripts are included in the SQL project, there are two ways to change the rows later: edit the query in the Fabric query editor and commit, or edit the file locally with Visual Studio Code or other SQL project tools and commit there. Either way, the change to the data is a change to code that the team can review.

Branch, review, merge, then update

The project makes team development possible, and the sequence has one step that people forget.

Branch out to new workspace, on the Branches tab of the Source control menu, asks for the names of a branch and a workspace. It creates the branch in the repository, populated with the committed contents of the branch you are branching from, and it creates the workspace in Fabric. When the database creation completes, the new database contains the objects from the repository, with new and empty tables.

A developer changes objects in that workspace and commits them with the Commit button. In Azure DevOps or GitHub, a pull request from the secondary branch to the primary branch shows the changes in the database code for review.

Completing the pull request updates source control. The database in the primary workspace is not changed.

To change the primary database, update the primary workspace from source control with the Update button on the Source control panel. That separate step is useful: someone decides when reviewed code becomes the primary database.

Publish a dacpac with SqlPackage yourself

Fabric runs SqlPackage for you during an update, but SqlPackage is also a command-line tool, and the same vocabulary appears in both places.

The SqlPackage publish operation incrementally updates the schema of a target database to match the structure of a source. With a .dacpac file as the source, the command names the action, the source file, and the target:

SqlPackage /Action:Publish /SourceFile:"C:\AdventureWorksLT.dacpac" /TargetConnectionString:"<connection string of the target database>"

For an existing database, the publish action requires the db_owner permission.

This is also why the local build matters in the code-first flow: the build is what produces the .dacpac file that a publish needs, whether Fabric runs the publish or you do.

Key takeaways

• The project file belongs to Fabric: Fabric generates and updates the SQL project file, and a manual edit is overwritten on the next commit from Fabric.
• Shared Queries are tracked, not built: the .sharedQueries folder stays in source control and is excluded from the project build.
• Build locally before you push: a local build finds a syntax error before the commit reaches the shared branch. A failed update must be reverted manually in source control.
• Uncommitted after a good update is normal: commit back from Fabric to sync the generated files.
• GenerateSmartDefaults is on: the publish provides a default value when a column that does not allow null values is added to a table that contains data.
• Definitions travel, data and database settings do not: a branched database has empty tables, and collation or compatibility level need scripts after deployment.
• Static rows go in a post-deployment script: a MERGE query in Shared Queries, set as a post-deployment script, runs on every update from source control and every deployment pipeline deployment.
• Merging is not publishing: the primary database changes only when the primary workspace is updated from source control.