Database Projects

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. Fabric SQL database source control is what produces it, and it does the conversion for you.

The starting point is a workspace connected to an Azure DevOps or GitHub repository. That connection applies to the whole workspace, and one selected branch is the one that directly affects it. Connect to the database from the Fabric SQL editor, SQL Server Management Studio, the MSSQL extension for Visual Studio Code, or another tool, and create the objects you need.

Then commit them. Open the Source control panel, select the checkbox next to the database, and choose Commit: the service reads the object definitions from the database and writes them to the remote repository.

What the team gets back is a project it can reason about.

As development continues — including edits to objects that already exist — the same commit step keeps the project in step with the database.

Read the SQL project file, do not write it

Alongside the object definitions the repository holds a SQL project file, which contains metadata about the database. That file is what lets the source control integration bring extra functionality into both Git and deployment pipelines.

Fabric generates and updates the project file automatically, so avoid manual edits: whatever you write there is overwritten by the integration on the next commit from Fabric. Durable changes belong in object files or in the supported scripts instead.

There is one sanctioned local addition. If you want to build the SQL project on your own machine with 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 for that purpose.

Fabric adds three metadata properties to the generated project.

The exclusion is the one to understand rather than memorize. Shared query scripts are versioned like every other file, but the database model is not validated against them — they are instructions to run, not objects to check. The Fabric query editor is where those pre-deployment and post-deployment scripts are created, under the Shared Queries folder.

Work code-first in Visual Studio Code

The project can also be driven from the other end — writing the code first and letting Fabric apply it. That path suits developers who prefer Visual Studio Code, who already have applications built on SQL projects, or who have more advanced release requirements.

Set it up once: install Visual Studio Code with the MSSQL and SQL projects extensions, create a new SQL database in the workspace and commit it with no objects, so the empty SQL project and the item metadata reach the repository, and then clone the repository to your machine. Open the cloned folder and switch to the branch associated with the workspace — it may not be the default one — and a folder named <yourdatabase>.SQLDatabase appears.

Each database object is then a .sql file inside that folder structure. A table, for example, is a file such as dbo/Tables/MyTable.sql holding its CREATE TABLE statement.

Before committing anything, build the project from the Database Projects view. Building validates the SQL syntax and produces a dacpac, so mistakes surface locally instead of failing later against the database. After a successful build, commit the files through the source control view or your preferred Git client and push to the remote repository, then check that the files appear in Azure DevOps or GitHub.

Back in Fabric, the Source control panel may already report pending changes from Git. Choose Update — or Update All — to apply the code from the SQL project to the database.

Follow the update into the database

Updating a Fabric SQL database from source control is two familiar operations joined together: 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 describing the database model the code adds up to. The publish operation then compares that dacpac with the target database and determines the changes necessary to make the database match it, so only the differences are applied rather than the whole model being recreated.

Because the Fabric interface streamlines the process, the publish runs with a fixed set of options, among them IncludeTransactionalScripts and GenerateSmartDefaults set to true and ScriptDatabaseOptions set to false.

Two consequences are worth holding on to.

First, validation happens before anything touches the database. The dacpac is only produced if the SQL files describe a coherent model, which is why building locally before committing catches problems early.

Second, the update is differential. An object that has not changed is left alone; the publish works out the delta between the model in the dacpac and the objects that exist. Once it completes, connecting to the database with any tool shows the objects the project added.

The same pairing is what a local build reproduces in miniature: build to validate, then let Fabric publish.

Know what a failed update requires

Because the build validates before the publish, a bad change fails loudly rather than half-applying.

If a change made to the local SQL project contains a syntax error or uses a feature that Fabric does not support, the database update fails. Fabric does not repair or reinterpret it: the change must be manually reverted in source control before work can continue. Until that happens, the repository holds a state that cannot be applied, and every attempt to update the workspace hits the same wall.

That is the practical argument for building the project locally before committing — a failed build on your machine costs a minute, while an unusable commit blocks the branch for everyone using it.

Know what the project does not carry

A database project describes objects, and there are two documented things it does not bring with it.

Database-level settings are the first. Collation and compatibility level are not included in the source control and deployment pipelines integration. For settings that can be applied with T-SQL after the database is created, the answer is to modify the database with scripts after deployment rather than expecting the project to carry them.

Table data is the second. When Fabric creates a database from committed definitions, the objects appear and the tables are new and empty: the model travels, the rows do not. Any data the new environment needs has to be ingested or, if it is a small controlled set, managed with a script.

Control static data with a post-deployment script

Pre-deployment and post-deployment scripts are the supported way to attach controlled operations to a database deployment, and the same capability applies to deployment pipelines — so one script covers both delivery paths.

The standard case is a lookup table whose values are known and change rarely, such as a dbo.Colors table an application depends on. In the Fabric SQL database query editor, create a query holding a MERGE statement that sets the contents of that table: matched rows are updated, rows missing from the target are inserted, and rows missing from the source are deleted. Written that way the statement is repeatable — running it twice leaves the same result.

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

From that point the query runs automatically as part of any update from source control and any deployment pipeline deployment, so the lookup rows are managed by source control like the rest of the project. Because pre-deployment and post-deployment scripts are included in the SQL project, the query can be edited either in the Fabric query editor or locally in Visual Studio Code and other SQL project tools, then committed like any other change.

The boundary is deliberate: controlled static rows belong in the project, transactional table data does not.

Branch, review, merge, then update

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

Branch out to new workspace creates a repository branch populated with the committed contents of the current branch, plus a Fabric workspace to go with it. The new database contains the objects checked into source control, and — as before — its tables are new and empty. Change the objects there, commit them from the Source control panel, and open a pull request from the secondary branch to the primary one, where the difference in database code between the two workspaces is visible for review.

Completing the pull request updates source control. The database in the primary workspace is not changed by it: applying the merged definitions still requires updating the primary workspace from source control with the Update button. That extra step is what lets a release manager decide when reviewed code becomes the primary database.

Key takeaways

• The project is generated for you: committing converts live object definitions into repository code, and Fabric generates and updates the SQL project file, so manual edits to it are overwritten on the next commit.
• Know the three metadata properties: .sharedQueries excluded from the build but tracked in source control, the pre-deployment and post-deployment scripts, and the automatic master.dacpac package reference.
• Build, then publish: an update builds the SQL project to validate syntax and create a dacpac, then SqlPackage determines and applies the differences. A syntax error or an unsupported feature fails the update and must be reverted manually in source control.
• Definitions travel, data and database settings do not: a branched database has the committed objects and empty tables, and collation or compatibility level need scripts after deployment.
• Use a post-deployment script for controlled static rows: a repeatable MERGE in Shared Queries, set as a post-deployment script, runs in both source-control updates and pipeline deployments.
• Merging is not publishing: the primary database changes only when the primary workspace is updated from source control.