Recently released: Updates to DacFx and Microsoft.Build.Sql
Published Feb 13 2023 05:08 PM 4,982 Views

Database development with SQL projects is available for Windows, macOS, and Linux through Visual Studio, VS Code, and Azure Data Studio. SQL projects are a .NET-based project type for SQL objects, compiling to a database artifact (.dacpac) for manual or continuous deployments. The primary library backing SQL projects is the Data-Tier Application Framework (DacFx).

 

In February 2023, a new release of DacFx and the SqlPackage CLI added support for serverless SQL pools and populating new columns from default constraints. The Microsoft.Build.Sql project SDK has also been updated, including the addition of database references to NuGet packages.

 

SQL projects

With the Microsoft.Build.Sql library for SQL projects, working with SQL projects and dacpacs in cross-platform and automated environments has never been easier. SDK-style SQL projects are supported with the SQL Database Projects extension in VS Code and Azure Data Studio.  Using SQL projects means that your database is code and is ready to be checked into source control and deployed via pipelines.

 

Microsoft.Build.Sql projects in Azure Data StudioMicrosoft.Build.Sql projects in Azure Data Studio

 

Getting started with templates

In addition to the graphical interfaces, SQL projects can be created from the command line with SQL projects templates.

Install the SQL projects templates with the dotnet new -i command:

 

 

dotnet new -i Microsoft.Build.Sql.Templates

 

 

Creating a new SQL project "MyAwesomeDatabase" targeting Azure SQL Database is now available from the command line as:

 

 

dotnet new sqlproj -n "MyAwesomeDatabase" -tp "SqlAzureV12"

 

 

sqlproj-template.png

 

Database package references and system databases

In the v0.1.9-preview release of the Microsoft.Build.Sql SDK-style SQL projects, we’ve introduced the ability to add references to database packages. With this capability, you can augment the contents of the project with additional objects including more objects for the same database or contents of another database.

 

One common scenario for database references is to reference a system database, such as master. To expedite adding references to system databases for use across local and pipeline environments, dacpacs for the system databases are now available as standalone packages on NuGet.org. The default database in Microsoft.SqlServer.Dacpacs is master and msdb can be referenced by specifying the dacpac from the package with the attribute DacpacName. The following command will add a reference to the 160 version of master database to the SQL project:

 

 

dotnet add package Microsoft.SqlServer.Dacpacs --version 160.0.0

 

 

Additional options can be set on the package references, DatabaseSqlCmdVariable will specify the variable used to reference that database from within the SQL project.  For example, this reference would associate [$(WWI)].dbo.Table1 to a package Contoso.WideWorldImporters:

 

 

<PackageReference Include=”Contoso.WideWorldImporters”>
    <DatabaseSqlCmdVariable>WWI</DatabaseSqlCmdVariable>
</PackageReference>

 

 

Data-tier application framework (DacFx) updates

 

SqlPackage dotnet tool

Deploying a dacpac can be achieved through the SqlPackage CLI, which is available to install as a dotnet tool for Windows, macOS, and Linux. Since the November 2022 release of SqlPackage (161), SqlPackage is available for installation as a .NET tool. As a .NET tool, SqlPackage can quickly be installed or upgraded with the following commands:

 

 

dotnet tool install -g microsoft.sqlpackage

dotnet tool update -g microsoft.sqlpackage

 

 

 

dotnettool-update.png

 

New functionality

In the latest release of DacFx and SqlPackage (161.8089.0), support was added for populating new columns from default constraints. Additionally, serverless SQL pools are now supported for extract and publish operations. Objects gaining support for serverless SQL pools include DELTA external file format and expanded capabilities of OPENROWSET. To learn more about the new features and fixes in the latest SqlPackage, release notes are available in the SqlPackage documentation.

 

Future releases

Work continues to expand support and capabilities for SDK-style SQL projects (Microsoft.Build.Sql) and preview support in Visual Studio (SSDT) will arrive in the upcoming months.  Planning for future releases of DacFx incorporates both new features and issues reported to the DacFx GitHub repository (https://github.com/microsoft/dacfx).

 

To continue to empower any developer to efficiently handle their database as code, future releases will bring additional advances towards the aim of increasing flexibility and performance of DacFx and related components.

1 Comment
Version history
Last update:
‎Feb 13 2023 05:08 PM
Updated by: