4: Setting up unit testing

I have devised a way that you can do Unit Tests for Azure SQL Database using tSQLt.

It does require a SQL Server instance somewhere though and changing your database project so that the target platform is SQL Server.

Basically, you have to first create a dacpac for SQL Server and then deploy it to a SQL Server instance to perform your unit tests on.

Afterwards, you can make a copy of the database project in your pipeline and then change the target platform setting in the copied sqlproj file by changing the DSP option to to Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider'. Which will make the target platform Azure SQL Database.

I go into more detail about how to do this in a post called Update both SQL Server 2019 and Azure SQL Database objects using one commit.

A more complex example can be seen in another post called Deploy from one source to multiple SQL Server database types using GitHub Actions.

In addition, I have an example on how to do this for an Azure Synapse dedicated SQL Pool in another post I wrote called Unit tests for an Azure Synapse Analytics dedicated SQL Pool. You can use the same logic for GitHub.