This tutorial helps you get started with running tSQLt tests in Visual Studio. These steps assume you have already set up a project with a development database.

Set up a test project

Create a new test project

Right-click the solution in the Solution Explorer and select Add > New Project. Create a new SQL Change Automation project with the naming convention of your choice (e.g. AdventureWorks.Database.Tests). In the wizard configure the development database of your test project to point to your main project's development database. The test project does not need a baseline so do not set a deployment target. Click Next and then finish setting up your test project.

Make sure your test project has the correct Target Platform set in the Project Properties under Project Settings.

Add a build dependency against your development project

Right-click the test project in the Solution Explorer. Open Project Dependencies using Build Dependencies > Project Dependencies... and select your main development project to add the dependency.

Right-click the References node in your test project and select Add Database Reference. Select the main project as the Database Reference. Select Same database as the Database location. Click OK to add the reference.

Within References node, edit the database reference properties to set Copy Local to False.

Configure your development project

In the Solution Explorer, right-click the development project and select Unload Project. Right-click the unloaded project and click Edit. In the sqlproj file, ensure the SyncOptionIgnoretSQLt property is set to True (you may need to add this XML node, ensure it isn't in a commented out block).

<PropertyGroup>
 <SyncOptionIgnoretSQLt>True</SyncOptionIgnoretSQLt>
 ...
</PropertyGroup>

Save the file and reload the project using Project > Reload Project.

Configure your test project

In the Solution Explorer, right-click the test project and select Unload Project. Right-click the unloaded project and click Edit. In the sqlproj file, ensure the SyncOptionIgnoretSQLt property is set to False (you may need to add this XML node, ensure it isn't in a commented out block).

<PropertyGroup>
 <SyncOptionIgnoretSQLt>False</SyncOptionIgnoretSQLt>
 ...
</PropertyGroup>

The test project will need to track what migrations have run independently of your development project. Add a custom migration log schema name that is different to the schema name used in your development project (the default is dbo):

<PropertyGroup>
 <MigrationLogSchemaName>test</MigrationLogSchemaName>
 ...
</PropertyGroup>

The test project should not have a baseline and is targeting your development database. In order to be able to deploy your tests to your development database you will need to enable the SkipBaselineCheck option (you may need to add this XML node).

<PropertyGroup>
 <SkipBaselineCheck>True</SkipBaselineCheck>
 ...
</PropertyGroup>

Save the file and reload the project using Project > Reload Project.

Set up filters for your development and test projects

To ensure that your development project contains only your development work, and your test project contains only your tests you will need to add filters to each project.

1. Create a filter file named 'Filter.scpf' in the root of your development project that will exclude all test objects
   
   • As an example, main-filter.scpf removes all objects that are in a schema with a name starting with "test", or have a name starting with "test".
2. Create a filter file named 'Filter.scpf' in the root of your test project that will exclude all test objects
   
   • As an example, test-filter.scpf includes all objects that in a schema with a name starting with "test", or have a name starting with "test".
     
3. Close and re-open the solution to ensure the filters are detected

Write a tSQLt test

Install the framework

In your test project, add a new script under the Migrations folder and rename it to 0001_Install_tSQLt.sql.

Download tSQLt from their website and copy the contents of their tSQLt.class.sql into your 0001_Install_tSQLt.sql, keeping the Migration ID on the first line.

In order to be able to deploy the migration you will need to modify the install script to use named primary key constraints with non-nullable columns, for example:

CREATE TABLE tSQLt.Private_Configurations (
  Name NVARCHAR(100) NOT NULL,
--                   ^^^^^^^^
  Value SQL_VARIANT
);
GO

ALTER TABLE tSQLt.Private_Configurations ADD CONSTRAINT [PK_Private_Configurations] PRIMARY KEY CLUSTERED  ([Name])
--                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

You will need to do this for tSQLt.Private_NewTestClassList, tSQLt.TestResult, tSQLt.Private_Configurations,  tSQLt.CaptureOutputLog.

tSQLt requires that CLRs are enabled on your development server. You can configure this by running the following (such as in a query window using SQL Server Management Studio):

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

In some cases your development database may need to be set to trustworthy In order for tSqlt to run against it. You can do this by putting the following into a Pre-Deployment script:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd='ALTER DATABASE ' + QUOTENAME(DB_NAME()) + ' SET TRUSTWORTHY ON;';
EXEC(@cmd);

Use tSQLt Visual Studio test adapter

Download and install the tSqlT Test Adapter for Visual Studio (download for 2019, 2017).

Create a [your project name].runsettings file (e.g. AdventureWorks.runsettings) in the solution root with the following content, replacing the TestDatabaseConnectionString with the connection string of your development database (Ensure that the value for includePath matches your test project):

<?xml version="1.0" encoding="utf-8"?>
<RunSettings>
  <TestRunParameters>
    <Parameter name="TestDatabaseConnectionString" value="Data Source=(localdb)\ProjectsV13;Initial Catalog=AdventureWorks.Database;Integrated Security=True;" />
    <Parameter name="IncludePath" value="Tests" />
  </TestRunParameters>
</RunSettings>

Select the file by going to Test > Configure Run Settings > Select solution wide runsettings file (This option is sometimes disabled. Open Test > Options, to enable it).

Write a sample test

Right-click the test project and select Add > Inline function... This will create a new function in the Programmable Objects folder, where you can write your test, for example,

-- <Migration ID="565a2147-9a0d-4e91-8494-1306ca209e6f" />
EXEC tSQLt.NewTestClass 'ufnGetAccountingEndDateTests';
GO

CREATE PROCEDURE ufnGetAccountingEndDateTests.[test that correct accounting end date is returned]
AS
BEGIN
    DECLARE @actual DATETIME;
    SELECT @actual = [dbo].ufnGetAccountingEndDate();

    DECLARE @expected DATETIME;
    SET @expected = '2004-06-30 23:59:59:998';
    EXEC tSQLt.AssertEquals @expected, @actual;
END;
GO

Deploy your changes

In the SQL Change Automation tool window click the Deploy Project button.

Run the tests

In the Test menu, select Run > All tests.

The test results are displayed:

Note you can also run your tests from SSMS after deploying changes by running the following SQL:

EXEC tSQLt.RunAll

Learn more

• Writing tSQLt tests