Next-gen SQL projects with Microsoft.Build.Sql

Рет қаралды 14,869

dotnet

Күн бұрын

Пікірлер

@amitkumdixit Ай бұрын

What about PostgreSQL? Is it supported

@drewskwierskoballa Ай бұрын

No - postgres doesn't have a library for declarative development to provide the backend for SQL projects.

@GC-qe8vc Ай бұрын

What databases other than Microsoft SQL Server, if any, are supported?

@drewskwierskoballa Ай бұрын

@@GC-qe8vcjust the Microsoft SQL family. If anyone knows of another declarative/state-based library for the other databases they’re welcome to chime in, but I don’t believe any of them have invested the time in this functionality.

@jacobstamm Ай бұрын

@@drewskwierskoballaI hope once the tooling is stable, we see support for other databases. It’s not fun to have one db use state-based version control and another use migration-based, so if the tooling for state-based isn’t cross-vendor, then multi-vendor companies will reach for something like Flyway out of necessity even if they prefer the simpler tooling you guys have built.

@jeffbarnard348 Ай бұрын

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

@ErikEjlskovJensen Ай бұрын

You only need CREATE

@jeffbarnard348 Ай бұрын

@@ErikEjlskovJensenWhen working in SSMS you can script a proc as CREATE OR ALTER. But when you copy/paste it into Visual Studio it does not compile, the data tools do not support that syntax. We have to manually change it every time to just CREATE. Again, hoping git integration in SSMS will negate the need to copy it manually.

@jacobstamm Ай бұрын

_cries in SQL Server 2012_

@drewskwierskoballa Ай бұрын

@@jeffbarnard348which script capability are you using in SSMS that doesn’t have a create-only output option?

@jeffbarnard348 Ай бұрын

@@drewskwierskoballaThe typical workflow has always been to develop in SSMS or Data Studio, then copy the contents into VS for source integration. Is there an alternative?

@jeffbarnard348 Ай бұрын

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

@timur2887 Ай бұрын

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

@drewskwierskoballa Ай бұрын

I wish there was a good solution for circular references too. In a good way, it can keep you from accidentally ending up in that architecture because managing state for independent objects can be tricky - but for onboarding existing projects there's just pain. Do you have a preferred workaround - the static dacpacs or the extreme project breakout?

@timur2887 Ай бұрын

@drewskwierskoballa I don't really like any of these solutions, but last time I preferred dacpacs over splitting. There were several databases in one solution, and it was important not to convert all to a mess so that developers do not get confused with the choice working on them.

@raul834 Ай бұрын

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?

@nothingisreal6345 Ай бұрын

you can create a diff script. This is typically relatively smart. You can review and modify it. The same issue exists with Code first approach. That's why I would NEVER use code first as you have zero control what will be done to DB - which can have massive consequences.

@jem64130 Ай бұрын

There is no tool out of the box for that but you have the concept of Pre- and Post- deployment scripts that you can leverage for the data related scripts.

@raul834 Ай бұрын

@@jem64130 i imagine these scripts are for the whole creation / modification script and not at the command level. This will incredible complicated to create (if it is even possible). My issue with any automatic approach is that I never saw one that really worked for anything moderately complicated. I would love to have one that worked. At my company we have manually created scripts for updating our databases and they have many thousands lines of code. Just the case where you change a 1:n relationship to n:m is trivial, but no automatic tool gets it.

@jem64130 Ай бұрын

@@raul834 For your case, you could create a temp table to backup your data in the pre-deployment and then initialize the final data based on the temp table in post-deployment. The tool does this kind of things out of the box for things like column reordering (disable constraints / copy to temp table / drop initial table / rename temp table in place / enable constraints), but not for multi-table changes. The real force of this tool is not to handle automatically complex scenarios but to handle automatically unknown scenarios. Like if you need to bring up to date a number of databases that are not necessarily all consistents (I had a product that needed to be deployed to 50+ databases). The other force is that you do not need to keep a script/checklist updated all along the development phase for all the changes (new columns and tables, procedure code changes) : the tool will be able to figure it out once you have consolidated your version. It's not perfect, but I would take that over c#-dev-hand-written script any day.

@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?

@drewskwierskoballa Ай бұрын

Good question! By default, we won't just make up data to make that operation succeed so you can get an error. But - there's a publish property (SqlPackage /p:GenerateSmartDefaults) that would fill in a minimal value for columns if null during deployment (like an empty string).

@way_no6810 Ай бұрын

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.

@jeffbarnard348 Ай бұрын

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

@ErikEjlskovJensen Ай бұрын

It is supported

@ErikEjlskovJensen Ай бұрын

Desinger support is not there yet

@imaginative-monkey Ай бұрын

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

@imaginative-monkey Ай бұрын

I guess I need to install the 17.13 preview...

@drewskwierskoballa Ай бұрын

it's a bit tricky - it's in a standalone component because of conflicts with the original SSDT. You'll want to install a separate instance of VS (which can absolutely be 17.13 preview) and enable just the SDK-style SSDT component. More info at aka.ms/ssdt-sdk-preview

@YuriCarranza 23 күн бұрын

There is a option to activate it in visual studio installer - individual components

@imaginative-monkey 23 күн бұрын

@@YuriCarranza Thanks, it seems it's still a preview feature. 👍

@GC-qe8vc Ай бұрын

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

@drewskwierskoballa Ай бұрын

Yes and no - they all can deploy changes to a database. The first key difference is Flyway and Liquibase require you to manually design your database changes ("changesets") instead of defining a single source of truth for your database schema. Flyway does have some support for state-based deployments for a few databases. You don't end up with code that you can quickly read to understand the object definitions. The second key difference is additional tracking tables and information have to be stored on each database such that they can apply the right remaining scripts to run the deployment, where a SQL project (dacpac) deployment dynamically determines the differences. There's nothing wrong with Flyway or Liquibase, but lots of orgs prefer SQL projects because of how well it integrates with the entire dev process and lights up CI/CD capabilities for 1 to 1,000s of databases. And yes, SQL projects are only for the Microsoft SQL family of databases. This includes SQL Server running in a VM or container anywhere, Azure SQL Managed Instance, Azure SQL Database, SQL database in Fabric, or Fabric Data warehouse.