Next-gen SQL projects with Microsoft.Build.Sql

Your database objects are stored as code for development in Visual Studio or VS Code with Microsoft.Build.Sql SDK-style SQL projects, but the advantages don’t stop there. SQL code analysis and other build-time tests can validate database code quality and correctness during continuous integration of code changes. With SQL projects delivering database object updates is easier and more reliable whether you're managing one database or a fleet of databases because the deployment plan is dynamically calculated through the SqlPackage CLI. In this session we’ll learn how to develop and deploy your database alongside your app components with SQL projects from the IDE to CI/CD.

Connect with .NET:

#dotnet

[jeffbarnard]

This is great news for database first teams who have a lot of branching and active db development

[amitkumdixit]

What about PostgreSQL? Is it supported

[timur]

The biggest drawback of project database reference is the lack of circular reference support inside SSDT solution. If you got two databases that references each other you have to either split your two projects in several with linear references or pregenerate DACPACs from these databases with no reference integrity checks and reference them in your solution instead of projects

[jeffbarnard]

CREATE OR ALTER support? Maybe Git in SSMS negates the need, but this has always been a pain point when editing and copying procs.

[raul]

The approach of comparing the model to the actual database and creating the modification scripts is nice, but it will just work for very simple update scenarios. Usually you modify a table and need to initialize fields with complex logic, like using a temporary table to calculate values from other tables in the system. Or you modify a 1:n relationship to be n:m. How would you do that with this system?

[John.Oliver]

How does the publishing of the dacpac handle the addition of a NOT NULL column to a table? When writing a script, I would create the column with a default constraint then remove the default constraint in the next statement to execute. Is this possible?

[jeffbarnard]

What about database unit test support? Does the designer work with SDK style projects?

[imaginative-monkey]

Is the new project template already available in Visual Studio 2022, ver. 17.12? I can just see the old template...

[way_no]

I am still using SQL2016 SSMS for all my coding and Scripts. Then I create a package for the latest versions. I tried the VS2022 SSDT and it was a pain. The separation of the debugger was not nice.

[GC-qevc]

So this is similar to Flyway or Liquidbase but only for Microsoft SQL Server...

[nothingisreal]

Next step: support Aspire. Currently Aspire only supports code first.