Integrating ReadyRoll and tSQLt – Part 1

by Greg M Lucas on 19 November 2017

Having used RedGate’s SQL Source Control for many years, I have recently started using their ReadyRoll product. I like the idea of separating the tSQLt framework and tests into a different project (entirely separate from the application objects) and I also like the hybrid approach of using proper migrations for tables whilst still treating procedures, views and functions etc. as code. I am a huge fan of TDD for databases and in this post I talk about one way of integrating ReadyRoll and tSQLt – specifically setting up your development projects.

I am a long-time user of RedGate’s SQL Source Control where unit tests and application code (tables, procedures, views, functions etc) all reside in the same SQL Compare “project”.  SQL Compare does a good job of separating tSQLt framework and tests from the application code at build/release time.  However, because of the sheer number of tests, when committing code and tests, I normally go through a manual process of moving tests out of the “Stored Procedures” folder of the RedGate project structure into one of a number of “Tests” folders (but still part of the same RedGate project). This is a pain point, especially when the number of tests runs in to the thousands. I have also had a few issues with the SSOC v5 implementation of migrations for which, when I last looked at it, RedGate seem unable to provide a solution or workaround. Hence my switch to ReadyRoll.

The first thing I found is that it is quite a different operational paradigm but more of that in a future post. There seems to be surprisingly little information out there about how to follow good test-first database development practices with tSQLt when working with ReadyRoll projects.  So since ReadyRoll is a RedGate product, my starting point was their own documentation. I found this tutorial to be a good starting point although the focus is on integrating the very useful tSQLt Adapter for Visual Studio from Ed Elliott (b | t).

As noted in the tutorial, that requires the use of Programmable Objects, so it will not work with the ReadyRoll Core edition which is included with VS2017. In other words you’ll need a licensed version of the full ReadyRoll tool. If you don’t have it, you can get a 28 day trial of ReadyRoll in the Visual Studio Marketplace.  NB: This may have changed since RedGate changed their software licensing so that ReadyRoll is only available as part of the full SQL ToolBelt.

For reference, my development environment is Visual Studio 2015, SQL Server 2016 and ReadyRoll v1.14.21.  So let’s get into the detail, starting with the set-up of the first ReadyRoll project in Visual Studio that will hold the objects, code and any static data that are required to make our application work.

Creating the AppDb Project

  1. In Visual Studio, create a new ReadyRoll project called AppDb.  This project will hold all of our application code but not the test framework or any tests.
  2. In AppDb project properties, under Programmable Objects, select “Import into separate files”.  This will allow us to treat procedures, views and functions as code which can be easily overwritten, as compared with tables which must be migrated to maintain state.
  3. In project properties, under Offline Schema Model, check “Sychronise schema objects to individual files during import”.  This will ensure that a standard “CREATE TABLE” statement is maintained for every table.  These are treated as read-only so cannot be used to modify the table structure but do serve as a useful mechanism for understanding a table at a glance and also how the structure may have changed over time (via version control).
  4. Finally, under Semantic Versioning, I usually enable “Apply semantic versioning to incremental migrations”.  This means that migration scripts will be grouped by version in the migrations folder which will contain sub-folders named “1.0.0”, “1.0.1”, “1.1.12” etc.  This can be particularly helpful as the number of migration scripts in a project grows over time.
  5. Our AppDb project now looks like this:

  1. Now we need to ensure that ReadyRoll doesn’t accidentally import any tSQLt framework or test objects into the application database. Right-click the AppDb project and select “Unload Project“, then right-click the project again and select edit AppDb.sqlproj. This opens the actual project file which is xml-formatted so relatively easy to edit – if you are very careful :-). Find the setting <SyncOptionIgnoretSQLt>True</SyncOptionIgnoretSQLt> and move it from the commented out section to the uncommented section, like this:

I think ReadyRoll ignores tSQLt by default anyway but I always like to have these settings explicity declared so that the intent is clear for any developers coming along behind us.

Using ReadyRoll means, at least in theory, that you can choose to write code in Visual Studio itself or against a SQL Server instance. The latter option requires using ReadyRoll to import any schema and code changes into Visual Studio but is probably a more familiar development practice to most SQL developers.  To do this, we next need to deploy our empty AppDb to a sandbox so that we can start coding.  Alternatively, if the database already exists with some objects, then you can start by importing those objects. After deploying AppDb you should see output like this:

Generating "C:\_dev\AppSolution\AppDb\obj\Debug\AppDb_Package.sql"...
		Building patch for the [AppDb] database on [(local)]...

		No migrations pending deployment
		
**The [AppDb] database will be created when the generated script is executed**
		
Generating "C:\_dev\AppSolution\AppDb\obj\Debug\AppDb.sql"...
		Deploying database project...
			Deploying "obj\Debug\AppDb.sql" to the [AppDb] database on [(local)]
			----- executing pre-deployment script "Pre-Deployment\01_Create_Database.sql" -----
			Creating AppDb...
			# Beginning transaction
			Changed database context to 'AppDb'.
			Creating extended properties
			# Committing transaction
			----- executing post-deployment script "Post-Deployment\01_Finalize_Deployment.sql" -----
			Deployment completed successfully.
	AppDb -> C:\_dev\AppSolution\AppDb\bin\Debug\AppDb.dll
	AppDb -> C:\_dev\AppSolution\AppDb\bin\Debug\
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

 

Creating the AppDb.tests Project

The next thing we need to do is create a second ReadyRoll project to hold the tSQLt framework and any tests:

  1. In Visual Studio, create a new ReadyRoll project called AppDb.tests.  This project will hold the tSQLt test framework, all unit tests and test helpers but no application code.
  2. In AppDb.tests project properties, under Programmable Objects, select “Import into separate files”.  I would also enable “Group programmable object files by schema” – if your database projects are anything like mine you will quickly get to the point where you’ll have hundreds (or even thousands) of tests and you really want to organise them properly from the start.  I believe that separating them by schema (a.k.a test class) makes the most sense.
  3. In project properties, under Offline Schema Model, check “Sychronise schema objects to individual files during import”. Because there won’t be too many tables in the tests project I usually leave “Group programmable object files by schema” unchecked.
  4. Under Semantic Versioning, I usually leave “Apply semantic versioning to incremental migrations” disabled for test projects. Generally speaking, the tests project will consist largely of code and very few migration scripts so they don’t need to be separated out by version.
  5. Our AppDb.tests project now looks like this:

  1. Next we need to ensure that ReadyRoll will include all tSQLt framework and test objects.  Right-click the AppDb.tests project and select “Unload Project“, then right-click the project again and select edit AppDb.sqlproj. Find the setting SyncOptionIgnoretSQLt and move it from the commented out section to the uncommented section then change the value to False, like this:

  1. We don’t want to maintain two copies of the application code so we need to create a dependency between AppDb.tests and AppDb.  In Visual Studio, select the AppDb.tests project then under the Project menu, select Project Dependencies.  In the resulting dialog,  check the name of the application database (AppDb in this case) to indicate that AppDb.tests now has a dependency on AppDb.  This will ensure that the application project is re-built/deployed before the tests project.

  1. Finally, we need to ensure that both projects are pointing to the same sandbox database.  This will ensure that the project containing the the application code and the tests project are combined into a single target against which we can write code and create/run tests.  In AppDb.tests project properties, under Debug, edit the target connection string so that it points to the same target database as as the main application database – AppDb in my case.

 

Writing Code and Tests

This post is about working with ReadyRoll rather than writing application code or tests so I am only providing high-level coverage of this part:

  • Download and install the tSQLt framework in our sandbox instance of AppDb
  • Write some unit tests using the tSQLt framework
  • Write just enough code to pass those tests
  • My example also includes some static data and covering tests which we are also going to manage using ReadyRoll and sp_generate_merge.

 Importing application code changes into Visual Studio

  1. As we are using semantic versioning, first create a new version folder called “1.0.0” under the Migrations folder in the AppDb project in Visual Studio.

  1. Then, in the ReadyRoll window, if AppDb in VisualStudio doesn’t contain any objects you will see an option to “Import Database”. Check that ReadyRoll is pointing to the sandbox instance of AppDb that contains your first set of database objects and click “Import Database”.  If the project already contains any code artefacts, you will instead need to click the “Refresh” or “View Changes” button to check for changes in the sandbox.

  1. In the resulting window, note the following:
    • The ReadyRoll window shows new or changed objects that have been identified including schemas, tables and stored procedures.  This window also details all the changes that have been identified including creates, permissions and extended properties etc.
    • We can see that ReadyRoll has imported three migrations scripts.  These are the “001_20171119-1257_gml.sql” migration script that we can see under /Migrations/1.0.0 and which contains the CREATE SCHEMA and CREATE TABLE statements (but not code objects such as stored procedures).
    • We can also see that the individual schema and table scripts have been imported for reference.
    • There is no mention of static data because we have not defined any tables that should be included as static data within ReadyRoll.
    • Note also that although we also added the tSQLt framework and some tests, these objects have not been picked up.  This is the expected behaviour because we configured AppDb to explicitly exclude such objects.
  2. Click “Refresh (Verify Scripts)”.  This validates the imported DDL against the SHADOW database.
  3. It is good practice to rename any migration scripts to something more descriptive (although retaining the leading number as this governs the run order) e.g. from “001_20171119-1257_gml.sql” to  “001_baseline_objects.sql”.

 

Importing static data into Visual Studio

There are two ways to manage static or reference data with ReadyRoll, either the ReadyRoll-native (aka “online”) approach which generates INSERT and UPDATE statements in migration scripts or using an open-source tool to generate idempotent MERGE scripts (aka “offline”). Both of these approaches are covered in detail in the ReadyRoll Documentation along with the advantages and disadvantages of each approach.

Although it is a little more work, I prefer to generate MERGE scripts using sp_generate_merge because, for smaller data sets, I like the flexibility I get from MERGE.  The resulting scripts are stored in a dedicated folder in the AppDb project and there is one script per table which makes it easier to understand the expected state of the data at any point in time (as opposed to trawling through multiple migration scripts or re-building the database at a particular version from source control).

  1. In Visual Studio, in AppDb, create a new project folder called “Static Data” then add the first script to this folder.

  1. Ensure that “Script (Not in build)” is selected and specify a name for the script.  The format I’ve used here is [schema_name].[table_name].data.sql.

  1. Then paste the MERGE statement output from sp_generate_merge in to the new static data script
  2. Finally, we need to tell ReadyRoll to include static data scripts in any build.  Open the Properties tab for the AppDb project and under Additional Scripts, check the Static Data item.  As the number of static data scripts increases, it may be come necessary to change the order in which static data scripts are run under Deployment Order.

Import tSQLt framework into Visual Studio

Assuming that all we have in our AppDb sandbox at this stage is some application objects plus the tSQLt framework, start by selecting the AppDb.tests project in the ReadyRoll window.  Check that the project is pointing to the sandbox copy of AppDb and then click “Import Database” (or “Refresh” if “Import Database” is not visible).  Unfortunately, without further customisation of AppDb.tests, ReadyRoll will also detect and import application-scoped changes as well as tSQLt objects.

The presence of application-scoped objects in the AppDb.tests project presents two issues: (i) there can be confusion over which objects to edit within Visual Studio and (ii) it can cause deployment errors because the AppDb.tests deployment tries to deploy application objects that already exist, for example:

This error occurs regardless of whether we try and deploy AppDb or AppDb.tests first.  The solution, is to configure AppDb.tests to exclude application scoped objects before we start importing anything in to the project.

Fortunately there is a section in the documenation showing how to Exclude Database Objects.  This means further editing of my sqlproj file but has the advantage of being able to exclude objects by type and schema name for example. This means that I only have to update this section if I add new schemas or new object types in the schemas I want to exclude.

In Visual Studio, right-click AppDb.tests and select Unload project, then right-click the project again and select Edit AppDb.tests.sqlproj to add a section like this (I normally leave the existing commented out <ExcludeObjectsFromImport> section in place for reference):

The above section will exclude all roles, the schemas: log4Private, log4 and log4Utils plus all log4Private tables and functions and procedures in any of those schemas will also be excluded.

Now when I click “Refresh” or “View changes” in the ReadyRoll window, only the tSQLt framework (and any tests) will be imported.  This also now means that which ever project ReadyRoll deploys first, there will be no errors generated from pre-existing objects.  In fact, because of the dependency between AppDb.tests and AppDb added earlier, if we only deploy AppDb.tests, we still get all the application code too.

Import Application Code and Unit Tests into Visual Studio

Finally, let’s briefly look at normal development lifecycle where we have added some application objects and associated tests in our sandbox database and we need to get those into Visual Studio so we can commit our changes to source control.

  1. Before adding our changes to Visual Studio, we naturally check that all unit tests are passing:
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                                                                          |Dur(ms)|Result |
+--+--------------------------------------------------------------------------------------------------------+-------+-------+
|1 |[CommonModuleTests].[test log.ExceptionHandler allows custom error procedure name]                      |     77|Success|
|2 |[CommonModuleTests].[test log.ExceptionHandler changes null @ErrorContext to empty string]              |     30|Success|
|3 |[CommonModuleTests].[test log.ExceptionHandler identifies current database]                             |     30|Success|
|4 |[CommonModuleTests].[test log.ExceptionHandler inserts columns in correct order on internal error]      |    150|Success|
|5 |[CommonModuleTests].[test log.ExceptionHandler inserts columns in correct order]                        |     93|Success|
...
|50|[CommonSchemaTests].[test log4Private.SqlException.Id primary key is unique]                            |     30|Success|
|51|[CommonSchemaTests].[test log4Private.SqlException.SystemDate defaults to local system insert date-time]|     33|Success|
|52|[CommonSchemaTests].[test log4Private.SqlException.UtcDate defaults to UTC insert date-time]            |     34|Success|
|53|[StaticDataTests].[test log4Private.JournalControl static data]                                         |     30|Success|
|54|[StaticDataTests].[test log4Private.Severity static data]                                               |     13|Success|
-------------------------------------------------------------------------------
Test Case Summary: 54 test case(s) executed, 54 succeeded, 0 failed, 0 errored.
-------------------------------------------------------------------------------

 

  1. In the ReadyRoll window, select the AppDb project and click “Refresh” or “View changes”.  In the results we can see a number of changes including the identification of a DROP COLUMN that could result in data loss. In our hypothetical use case we no longer need the column in question so can ignore that warning and go ahead and import all these changes.

  1. In the ReadyRoll window, select the AppDb.tests project and click “Refresh” or “View changes”.  In the results we can see that only unit tests and test classes have been identified.  No application-scoped objects will be imported.  This is true whether we run a ReadyRoll refresh for AppDb.tests first or second.

 

Conclusion

That’s it!  We now have a fully integrated database solution which will meet the following needs:

  • Will deploy to a sandbox without error regardless of which project we deploy first
  • Clearly delineates application objects from test framework and tests
  • Will maintain the separation between application-scoped and test objects (subject to any additional schema/object type filters being added to the <ExcludeObjectsFromImport> section in AppDb.tests.sqlproj)
  • Automatically deploy or upgrade either project, with or without tests (CI build or Production) as needed

The only challenge I still have to solve is to be able to add a call to tSQLt.RunAll; to run all unit tests in the post-deployment script of the AppDb.tests project so that I can ensure all tests are run automatically as part of any tests project deployment.  However, that is a post for another day.

In the next post in this mini-series I will work through how to integrate ReadyRoll and tSQLt using dacpacs which has both advantages and disadvantages over the above method.

 

 

Leave a Comment

Previous post: