In the April release of the SQL Database Projects extension for Azure Data Studio and VS Code (v0.16.1) we are introducing preview support for an SDK-style SQL project based on the package Microsoft.Build.Sql. The SDK-style SQL project introduces several advantages, especially for developers shipping their applications through CI/CD pipelines or working in cross-platform environments. SQL projects are foundational to declarative development for databases and expedite common development practices such as source control and code analysis.
Declarative development creates an environment where developers can focus on creating database objects while relying on the support of tooling locally and and in deployment pipelines to manage applying the differential changes calculated on the current state of the target database. Developers create objects such as tables or stored procedures by writing their definition with CREATE statements in scripts that live in source control just as if it is source code for any component of an application. Existing functionality for SQL projects in Visual Studio, Azure Data Studio, and VS Code provides developers with declarative development capabilities, however the existing SQL project file format has a few limitations. With Microsoft.Build.Sql and SDK-style SQL projects, we look forward to unlocking new scenarios for your development practices.
File Format Changes
Microsoft.Build.Sql introduces full “globbing” (wildcard) file selection by default where any SQL file contained in a path that includes the SQL project’s path is included without additional steps. In comparison to the existing SQL projects this will result in significantly fewer merge conflicts for teams adding multiple files in a single iteration. Additionally, project properties set in the .sqlproj file have default values built into Microsoft.Build.Sql.
Existing capabilities of SQL projects such as SQLCMD variables, pre-and post deployment scripts, and database references are also included in SDK-style SQL projects with Microsoft.Build.Sql. The format for these items remains unchanged at this time. Below is the comparison of the .sqlproj file for the same project in the standard SQL project file format and the SDK-style SQL project file format.
Command Line and Pipelines
When working from the command line or in a CI/CD pipeline the SDK-style project is advantageous with its compatibility to cross-platform dotnet build. For an SDK-style SQL project your SQL project can be built from Linux-based pipelines with no additional changes to your repository.
The GitHub action, sql-action, is bringing support for consolidated build and publish of SDK-style SQL projects in a future release. You can try this out today by using “sql-action@master” to get the very latest development version of the action for testing. Sql-action also supports passing arguments to the SqlPackage publish operation as well as the dotnet build operation.
# .github/workflows/sql-deploy.yml
on: [push]
jobs:
build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v1
- uses: azure/sql-action@vmaster
with:
connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
project-file: './Database.sqlproj'
arguments: '/p:DropObjectsNotInSource=true'
Creating SDK-style SQL projects
To create a new SDK-style SQL project in Azure Data Studio or VS Code, the latest release of the SQL database projects extension (v0.16.1) adds a checkbox option to create an SDK-style project during new project creation. Projects can also be manually created from the available sample on GitHub.
The Road Ahead
Existing SQL projects can be manually converted to SDK-style SQL projects with a handful of line changes and deletions. It is recommended to create a copy of the .sqlproj file before beginning modifications. We are working to provide a conversion tool for both existing SQL project format as well as for projects built on MSBuild.Sdk.SqlProj.
We are excited for what SDK-style SQL projects will bring to developers and will be continuing to add features to the SDK as well as support in the graphical tools. You can learn more about Microsoft.Build.Sql and planned improvements on the GitHub repository.
SQL projects can unlock development efficiencies for individuals as well as teams looking to up-level their database development practices. Give it a spin and let us know your thoughts!
Learn more: https://github.com/microsoft/DacFx/tree/main/src/Microsoft.Build.Sql
Posted at https://sl.advdat.com/3OlDrmKhttps://sl.advdat.com/3OlDrmK