sqlproj - SQL Server database project different configuration for Debug and Release

Question

I have a SQL Server database project (.sqlproj) which I am using as part of a CI/CD pipeline to deploy database changes. I would like to deploy the same code to two databases (Dev and Production) but each with a slightly different configuration:

In Dev, I have an Azure AD group Database-Dev-Developers:

CREATE USER [Database-Dev-Developers] FOR EXTERNAL PROVIDER;

In Production, I have an Azure AD group Database-Prod-Developers:

CREATE USER [Database-Prod-Developers] FOR EXTERNAL PROVIDER;

I can find no way to alter which scripts are build/published based on the configuration. Ideally I'd like to be able to specify the project configuration at build time (Debug/Release), which changes the output.

I have tried adding conditional expressions for the relevant files in the .sqlproj file, but this has no effect:

Condition=" '$(Configuration)' == 'Debug' "

question from:https://stackoverflow.com/questions/65869868/sql-server-database-project-different-configuration-for-debug-and-release

Answer 1

You should look into using a Token Replacement step in your pipeline. You can add different variable values for Dev vs Prod to replace the tokens with. Then you just need one tokenized configuration file that can be used for both Dev and Prod.

I'm not exactly sure how kosher it is to use tokens in a .sqlproj file, it depends on what configurations you're trying to replace. But I've seen it used very successfully on ...config.json files in modern .NET Core based projects.

Another thing you can look into is File Transformations. I don't have any experience using these though.