Edit

Share via

SQL database source control integration in Microsoft Fabric

  • Article

Applies to: ✅ SQL database in Microsoft Fabric

In this tutorial, you learn how to work with your SQL database in Fabric with Fabric git integration source control.

A SQL database in Microsoft Fabric has source control integration, or "git integration", allowing SQL users to track the definitions of their database objects over time. This integration enables a team to:

  • Commit the database to source control, which automatically converts the live database into code in the configured source control repository (such as Azure DevOps).
  • Update database objects from the contents of source control, which validates the code in the source control repository before applying a differential change to the database.

Diagram of the simple commit and update cycle between the live database and source control.

If you're unfamiliar with git, here are a few recommended resources:

This article presents a series of useful scenarios that can be used individually or in combination to manage your development process with SQL database in Fabric:

Prerequisites

Setup

This repository connection applies at the workspace level, such that a single branch in the repository is associated with that workspace. The repository can have multiple branches, but only the code in the branch selected in workspace settings will directly impact the workspace.

For steps to connect your workspace to a source control repository, see Get started with Git integration. Your workspace can be connected to an Azure DevOps or GitHub remote repository.

Add the Fabric SQL database to source control

In this scenario, you'll commit database objects to source control. You might be developing an application where you're creating objects directly in a test database and track that database in source control just like your application code. As a result, you have access to the history of the definitions of your database objects and can use Git concepts like branching and merging to further customize your development process.

  1. Connect to your SQL database in the Fabric SQL editor, SQL Server Management Studio, the mssql extension with Visual Studio Code, or other external tools.
  2. Create a new table, stored procedure, or other object in the database.
  3. Select the ... menu for the database, select Refresh Git sync status.
  4. Select the Source control button to open the source control panel.
  5. Select the checkbox next to the desired database. Select Commit. The Fabric service reads object definitions from the database and writes them to the remote repository.
  6. You can now view the history of database objects in code repository source view.

As you continue to edit the database, including editing existing objects, you can commit those changes to source control by following the preceding steps.

Update the Fabric SQL database from source control

In this scenario, you'll be creating database objects as code in the SQL projects extension in VS Code, then committing the files to source control before updating the Fabric SQL database from the source control integration. This scenario is targeted towards developers who prefer to work in VS Code, have existing applications using SQL projects, or have more advanced CI/CD pipeline requirements.

  1. Make sure you have installed the latest release of VS Code and the mssql and SQL projects extensions for VS Code.
  2. Create a new SQL database in your workspace and commit it to source control without adding any objects. This step adds the empty SQL project and SQL database item metadata to the repository.
  3. Clone the source control repository to your local machine.
    • If you're using Azure DevOps, select the ... context menu for the source control project. Select Clone to copy your Azure DevOps repository to your local machine. If you're new to Azure DevOps, see the Code with git guide for Azure DevOps.
    • If you're using GitHub, select the Code button in the repository and copy the URL to clone the repository to your local machine. If you're new to GitHub, see the cloning a repository guide.
  4. Open the cloned folder in Visual Studio Code. The branch associated with your workspace might not be the default. You should see a folder named <yourdatabase>.SQLDatabase in VS Code after switching the branch.
  5. Create a .sql file for at least one table you would like to create in the database within the folder structure for your database. The file should contain the CREATE TABLE statement for the table. For example, create a file named MyTable.sql in the folder dbo/Tables with the following content: 
    CREATE TABLE dbo.MyTable
(
    Id INT PRIMARY KEY,
    ExampleColumn NVARCHAR(50)
);
  6. To ensure the syntax is valid, we can validate the database model with the SQL project. After adding the files, use the Database Projects view in VS Code to Build the project.
  7. After a successful build, Commit the files to source control with the source control view in VS Code or your preferred local git interface.
  8. Push/sync your commit to the remote repository. Check that your new files have appeared in Azure DevOps or GitHub.
  9. Return to the Fabric web interface and open the Source control panel in the workspace. You might already have an alert that "you have pending changes from git". Select the Update (Update All) button to apply the code from your SQL project to the database.
    • You might see the database immediately indicate it is "Uncommitted" after the update. This is because the Git Integration feature 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. In these cases, you'll need to commit back to source control in the Fabric web interface to sync the definition with what is generated as part of a commit operation.
  10. Once the update has completed, use a tool of your choice to connect to the database. The objects you added to the SQL project visible in the database.

Note

When making changes to the local SQL project, if there is a syntax error or use of unsupported features in Fabric, the database update will fail. You must manually revert the change in source control before you can continue.

Create a branch workspace

In this scenario, you'll set up a new development environment in Fabric by having Fabric create a duplicate set of resources based on the source control definition. The duplicate database will include the database objects that we have checked into source control. This scenario is targeted towards developers that are continuing their application development lifecycle in Fabric and are utilizing the source control integration from Fabric.

  1. Complete the scenario convert the Fabric SQL database into code in source control.
    • You should have a branch in a source control repository with both a SQL project and the Fabric object metadata.
  2. In the Fabric workspace, open the source control panel. From the Branches tab of the Source control menu, select Branch out to new workspace.
  3. Specify the names of the branch and workspace that will be created. The branch will be created in the source control repository and is populated with the committed contents of the branch associated with the workspace you are branching from. The workspace will be created in Fabric.
  4. Navigate to the newly created workspace in Fabric. When the database creation completes, the newly created database now contains the objects specified in your code repo. If you open the Fabric query editor and navigate in Object explorer, your database has new (empty) tables and other objects.

Merge changes from one branch into another

In this scenario, you'll use the source control repository to review database changes before they're available for deployment. This scenario is targeted towards developers that are working in a team environment and are using source control to manage their database changes.

Create two workspaces with associated branches in the same repository, as described in the previous scenario.

  1. With the database on the secondary branch, make changes to the database objects.
    • For example, modify an existing stored procedure or create a new table.
  2. Check in these changes to source control with the Commit button on the source control panel in Fabric.
  3. In Azure DevOps or GitHub, create a pull request from the secondary branch to the primary branch.
    • In the pull request, you can see the changes in the database code between the primary workspace and the secondary workspace.
  4. Once you complete the pull request, the source control is updated, but the database in Fabric on the primary workspace isn't changed. To change the primary database, update the primary workspace from source control using the Update button on the source control panel in Fabric.

Related content