Cross-database unit testing

327 views
Skip to first unread message

Karri

unread,
May 23, 2021, 1:31:16 PM5/23/21
to tSQLt
Hi,

I have a question regarding the setup of cross-database functionality in tSQLt and Visual Studio projects.
I've set up a VS Solution with two databases:
  • MainDatabase
  • MainDatabase.UnitTest
Both databases have tSQLt installed on them, but the .UnitTest database has the actual test classes and test cases. In the project configuration, I have added 'master' as a reference database into the .UnitTest database, but as soon as I add the MainDatabase ( configured as 'same database'), I start getting build errors:

Severity Code Description Line Project File Suppression State
Error SQL71508: The model already has an element that has the same name test_Dim.tSQLt.TestClass. 6 MainDatabase.UnitTests C:\Project\MainDatabase.UnitTests\Security\test_Dim.sql
Error SQL71501: Extended Property: [test_Dim].[tSQLt.TestClass] has an unresolved reference to object [test_Dim]. 6 MainDatabase.UnitTests C:\Project\MainDatabase.UnitTests\Security\test_Dim.sql
Error SQL71501: Procedure: [test_Dim].[test my Dimension] has an unresolved reference to Schema [test_Dim]. 3 MainDatabase.UnitTests C:\Project\MainDatabase.UnitTests\test_Dim\Stored Procedures\test my Dimension.sql
Error SQL71508: The model already has an element that has the same name test_Dim. 1 MainDatabase.UnitTests C:\Project\MainDatabase.UnitTests\Security\test_Dim.sql

My test case uses the fully qualified path OtherDB.tSQLt.FakeTable and I am able to run the test case on the MainDatabase.UnitTests database without problems.

And if I remove the reference to the MainDatabase, the build succeeds. To my understanding (and according to all of the blogs/SO articles I've read), this is how cross-database unit testing is supposed to work with tSQLt.

Am I doing something wrong?

/Karri

Sebastian Meine

unread,
May 23, 2021, 3:30:23 PM5/23/21
to ts...@googlegroups.com
Hi Kari,

You do not need to add tSQLt to the main database when selecting "same database". But, the error messages look like your tests are also in both projects. Could you check on that?
 
Thanks,

Sebastian


--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/2351fba5-3a77-4773-b891-82f99d0124c2n%40googlegroups.com.

Alex Yates

unread,
May 23, 2021, 5:52:55 PM5/23/21
to ts...@googlegroups.com
+1 to what Sebastian said. You don't want tsqlt in the main proj because you probably want to use the main proj to deploy to prod, and you don't want tsqlt on prod.

Also, it looks like you a referencing a separate database (hence the 3-part naming convention). Have you included the dependent database in your solution?

Cross-db dependencies are a pain.

Alex

Karri

unread,
May 24, 2021, 2:08:40 AM5/24/21
to tSQLt
Hi,

I uninstalled tSQLt from the MainDatabase, checked that there were no old test classes and cases in it. Now the the UnitTest project has a reference to the MainDatabase as a 'same database'.
The solution builds successfully.

But what I don't quite now understand is, how should the test cases be created? If I create a new test case in SSMS, the intellisense doesn't work since there are no actual 'MainDatabase' objects or schemas in my UnitTest database.
Also, if I try to run the tests with SQLTest (or with RunAll), I get an error that there is an unresolved object name -- my Dimension table.

Just for clarity, here's my test case and the output of the execution:

USE [MainDatabase.UnitTests]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--  Comments here are associated with the test.
--  For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/
CREATE PROCEDURE [test_Dim].[test my Dimension]
AS
BEGIN
  --Assemble
    EXEC tSQLt.FakeTable @TableName = N'dbo.Dim';
INSERT INTO dbo.Dim ( UserName ) VALUES ('Karri' );

  --Act
  SELECT UserName INTO #Actual
  FROM dbo.Dim

  SELECT TOP(0) UserName INTO #Expected
  FROM #Actual

  INSERT INTO #Expected ( UserName ) VALUES ('Karri')

  --Assert
  EXEC tSQLt.AssertEqualsTable @Expected = N'#Expected', @Actual = N'#Actual'
  
END;

Here are the results of RunAll:

(1 row affected)
[test_Dim].[test my Dimension] failed: (Error) FakeTable could not resolve the object name, 'dbo.Dim'. (When calling tSQLt.FakeTable, avoid the use of the @SchemaName parameter, as it is deprecated.)[16,10]{tSQLt.Private_ValidateFakeTableParameters,12}
 
+----------------------+
|Test Execution Summary|
+----------------------+
 
|No|Test Case Name                                |Dur(ms)|Result|
+--+----------------------------------------------+-------+------+
|1 |[test_Dim].[test my Dimension]|     17|Error |
----------------------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Line 1
Test Case Summary: 1 test case(s) executed, 0 succeeded, 0 skipped, 0 failed, 1 errored.
----------------------------------------------------------------------------------------

Completion time: 2021-05-24T09:02:25.1004065+03:00

Does this then mean that because of the cross-database reference, the only place to really create and run these test cases is through Visual Studio?

I can see what you mean by cross-db references being a pain :)

/Karri

Dmitrij Kultasev

unread,
May 24, 2021, 3:58:48 AM5/24/21
to ts...@googlegroups.com
There could be some confusion about projects/databases. Projects <> Dabases, so if you have 2 projects that doesn't mean that they would go to different databases. So, the suggestion is:
let's say that your database name is Students. You can create 2 projects:
* Students --> will have your real database objects. You'll deploy this project to prod.Students
* Students.Tests --> will have reference to Students project as "the same database". Additionally it will have all tSQLt framework objects (can be referenced via dacpac) + all your new tests + test classes. This project will be deployed to dev.Students

tSQLt framework and unit tests and real objects must reside on the same database. Moreover, due to the limitations of both SSDT and tSQLt, you can't have 3-4 part names in your code + you can't fake synonyms.

Karri

unread,
May 24, 2021, 4:15:55 AM5/24/21
to tSQLt
Right. That's how I have my Visual Studio solution set up:

MySolution (Visual Studio Solution)
  • MyDatabase (Visual Studio SQL Server Database project)
    • Includes all tables, views, stored procs etc...
  • MyDatabase.UnitTest (Visual Studio SQL Server Database project)
    • Includes only tSQLt, test classes and test cases
    • References MyDatabase, master
So you're saying that I need to publish my MyDatabase.UnitTests project into the MyDatabase in my dev server? This is something that never occurred to me and hasn't been stated.
I always assumed that I could publish MyDatabase and MyDatabase.UnitTest to my dev, and when I run the test cases on the latter, because of the reference to the actual database, it will run them.

To my understanding, you can have 3 part names in your code. Normally if everything is in the same database, you would run "EXEC tSQLt.FakeTable @TableName = N'dbo.MyTable'".
But if you store tests on a parallel database, and as long as you have tSQLt installed on both, you could run "EXEC OtherDB.tSQLt.FakeTable @TableName = N'dbo.MyTable'";
Or then all the SO articles / blogs about this are wrong, which wouldn't surprise me ... 

Thomas Bjerknes

unread,
May 24, 2021, 4:17:15 AM5/24/21
to tSQLt
Hi

What Dmitrij said. I have two more things you can check

1) In your VS solution, do you have multiple startup projects? Righ click solution, set startup projects, choose multiple startup projects, and set both you main project and test project to 'start'.
2) If you are not aware of this, you can right click your test project, select 'goto debug database', and view the content there. Your test database should contain all objects from the main db, all of tSQLt objects and all test objects. If you don't see all these things there, something is wrong

Out of curiosity, is MainDatabase.UnitTests a 'real' database in a shared environment, or is it a localdb ?

Dmitrij Kultasev

unread,
May 24, 2021, 4:29:07 AM5/24/21
to ts...@googlegroups.com
I'm not sure why you  want to deploy both projects to dev. When you deploy the UnitTest project, it will deploy both MyDatabase project + tSQLt part. If you would like to deploy just MyDatabase after that, it would drop all test related objects as this project doesn't have them.
For the 3 part names: yes, you can workaround by having tSQLt on both databases, but then you can't store this code in SSDT as it doesn't like 3 part names. This can be worked around as well, however there is no normal native solution.

Karri

unread,
May 24, 2021, 4:42:30 AM5/24/21
to tSQLt
Hi

I haven't touched the startup project settings. The settings are the default ones that come with settings up a Solution.
My UnitTest database only contains the tSQLt framework, and not the actual objects, because there's a reference to the real database.

I don't want to deploy both -- I'm just looking to deploy a single database which would have both the objects to test and the test cases for them. This deployed database would in any case be ephemeral.
Ultimately I want to trigger a release of this database to dev so that it will have a buildid suffix, like MyDatabase.UnitTest-Build.1234 so that it can be traced back to a specific build, and then torn down.

Hope this makes sense.

Thomas Bjerknes

unread,
May 24, 2021, 4:53:40 AM5/24/21
to tSQLt
Ok. This is my understanding of how it works. Take it with a pinch of salt though as I have used tSQLt for a year only:

Your testproject needs to contain ALL objects needed for testing. That includes the tables/procs/views/functions from you production db. Your tests will populate theses tables with data, and you obviously cannot populate the actual prod tables.
When you add a reference to prod in your test project, it will copy over all the prod objects to your test project. To achieve this you set multiple start projects as I mentioned above. It will then build both projects, make sure everything is in sync, and the run tests.

Running against a localdb will save you a lot of headaches by the way. All developers will run tests independently of each other. If something gets messed up with your test database, you can just drop and recreate it. 

Karri

unread,
May 24, 2021, 5:46:24 AM5/24/21
to tSQLt
My UnitTest database is a 'real' database, not just a Visual Studio database project. My original idea was to have it purely as a VS project and then deploy it as needed but I didn't really understand how to set up the project without having it as a real database. Also, developing the test cases in a real database is a little more straightforward with the SSMS plugins we have.

Thomas Bjerknes

unread,
May 24, 2021, 5:51:25 AM5/24/21
to tSQLt
Got it. Maybe it was confusing when I said 'real' database, sorry about that. It is alwaways run against a real sql server database, but my distinction was between a database on a server, and localdb. We run against localdb, this means there is nothing to deploy anywhere. All tests are run on the developers computer.   

Bryant McClellan

unread,
May 24, 2021, 6:48:03 AM5/24/21
to ts...@googlegroups.com
Thomas,
Just curious...you don't RunAll or something like it when the code is deployed to the development database server?

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803




Thomas Bjerknes

unread,
May 24, 2021, 6:59:32 AM5/24/21
to tSQLt
Yes I have a RunAll in a PostDeployment script

Karri

unread,
May 24, 2021, 8:18:58 AM5/24/21
to tSQLt
I want to revisit my original question and ask what's the correct way to go about having the tSQLT framework and unit tests in a separate database, and be able to deploy the solution so that all the testable objects and the framework are deployed together? -- I know that 'the correct way' is very subjective :)
I'm also interested in how the creation of the test cases should go, assuming I have the two databases in a SSDT Visual Studio solution. Should they be created in SSMS and imported with Schema compare or are they created directly in VS?

Thanks,
Karri

Bryant McClellan

unread,
May 24, 2021, 8:25:34 AM5/24/21
to ts...@googlegroups.com
IMHO schema compare can be a dangerous tool because the default settings do not match those of a publish profile. Certainly you can create the proper schema compare and add it to the project so everyone uses it. And SSMS has tools/features that are really handy compared to VS. Our MO is to use VS. Then again I've seen people create tests in SSMS then just drop the test script into the UnitTest project in the VS database solution. We ship tests to the same database so my experience differs from what you are attempting.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803



Karri

unread,
May 24, 2021, 8:32:22 AM5/24/21
to tSQLt
Thanks Bryant for the info, really appreciate it.

We haven't been using the Publish profiles since the Azure Devops SQL deployment extensions include the necessary information for how to publish the dacpac. But reading feedback here sounds like I should be :) As you said, we have a schema compare file for each database project in the Solution to help the devs.

I'm just thinking that does it make sense to deploy the MyDatabase first and then deploy the MyDatabase.UnitTests into the same database (to get the framework there) and then run the tests? Or is that just crazy? :) Of course just having the MyDatabase project and then including the tSQLt framework there with all test cases would be simpler, I think it would just be cleaner to separate them. We already have quite a lot of objects in the a single database (because of some of the db-automation tools we use).

Bryant McClellan

unread,
May 24, 2021, 8:50:13 AM5/24/21
to ts...@googlegroups.com
Karri,
In our situation we have composite SSDT solutions. A solution contains 5 projects. The core database project (or MyDatabase) is the part common to every environment (tables, views, procedures, schemas, UDFs...). There are then 3 environment projects (MyDatabase.Dev, MyDatabase.Stage, MyDatabase.Prod). Each of these has a 'same database' reference to MyDatabase. Each has things peculiar to the particular environment, like permissions and role references.

Then there is the unit test project (MyDatabase.UnitTest). It has a reference to the tSQLt dacpac and contains all user developed test procedures, test schemas... MyDatabase.Dev refers to MyDatabase.UnitTest as a 'same database' reference. When we deploy MyDatabase.Dev, MyDatabase.UnitTest is deployed with it to the database server. Because of that we have no cross-database test dependency. Every database solution has an independent UnitTest project, but they all share a common tSQLt dacpac. We never deploy the UnitTest project directly, only in conjunction with the Dev project. The core project is deployed along with the 3 environment project, never by itself.

I say all this not to change your process but to temper my suggestions. What works great for us may not be workable for you. We also have 3 on premises SQL swimlanes and 20-30 Azure SQL databases. Because of our Azure choices we cannot do cross-database anyway so the strategy above works well for us.

Also we use the publish profiles as fodder for the DevOps pipeline. The profiles control how comparisons are made and how the deploy functions. Accordingly we enlist SqlPackage.exe in a pipeline to exercise the deploy by consuming the publish profile.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803



Karri

unread,
May 24, 2021, 9:03:03 AM5/24/21
to tSQLt
Thanks Bryant, that's really useful to hear.
Your approach sounds smart, I like the common object way-of-working. We don't store any permissions or such roles in any of our database projects, but that's mostly because our user rights model is very simple.

We also operate on-prem at the moment, and it's nice to hear about the constraints you have regarding Azure SQL and cross-database refs, since we're going to have the ability to deploy stuff to Azure soon(ish). I'll need to think about how that will work in the future together with our current solution.

About the tSQLt.dacpac, did you create that using a blank database or is there some other clever way?

Bryant McClellan

unread,
May 24, 2021, 9:33:33 AM5/24/21
to ts...@googlegroups.com
Karri,
I used to deploy the tSQLt project to a local database to build the dacpac. Since we have to support at least 3 SQL versions that gets cumbersome. Each dacpac needs a compatibility level setting matching that of the project using it.

As long as the database properties are correct in the project, you can harvest the dacpac that VS creates when you build the project. That saves me a ton of time and steps. I have an independent project for the tSQLt framework and another for our extensions. Paired together I get a single dacpac with everything included by changing a few options and hitting Build.

We then distribute the dacpac in source control in a fixed location. Other DB solutions then refer to that location. Whenever we update the dacpac contents, each solution gets new code and deploys it on the first publish. Of course if there is a bug in our extension everybody knows about it. 🙄

If you choose Managed Instance in Azure it is just like an on premises server, just hosted somewhere else. We did not choose to do that. Having independent databases also forces us to code with less cohesion. We also employ Kafka as a streamed messaging system rather than building a bunch of ADF pipelines.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

Do it Best Corp.
6502 Nelson Road | Fort Wayne, IN 46803



Karri

unread,
May 26, 2021, 1:43:43 AM5/26/21
to tSQLt
I've now been testing different approaches, and using the localdb to create the unit tests seems like the best approach. 
This is how I've got it set up:

My Visual Studio solution includes all the relevant databases which deploy to test and prod.
I have created a dacpac of the tSQLt framework which I import into any _UnitTests database projects I might have in the solution.
When I'm ready to create unit tests for the new objects in my 'real' database (MyDatabase), I will publish the MyDatabase_UnitTests (which has a 'same database' reference) to the localdb.
As I now have all needed objects (actual + tsqlt) in my MyDatabase_UnitTests, I can create the tests, run them locally and sync them up with the project and version control them.
This is all pretty clear to my now.

This is a little off topic, but what I'd like to understand is how do you deploy a database which was references to another? 
When I run the publish manually from VS, the MyDatabase_UnitTests gets created in localdb with both it's contents (the unit tests) and the objects from MyDatabase. When I run a dacpac deploy task in Azure Devops Server, I can only have 1 dacpac in the folder the task is pointing to. And as such, the deployment fails because none of the MyDatabase objects exist there and I'm unable to execute the test cases. 
To get the objects from MyDatabase, do you deploy them both into the same database? 


Alex Yates

unread,
May 26, 2021, 2:31:05 AM5/26/21
to ts...@googlegroups.com
Cross dB dependencies are a pain and it really is best to try and avoid them.

If you can't do that, here are a few techniques:

In Visual Studio:
Either put all the databases in the same solution, or create "stub" projects that contain the necessary dependencies. This is annoying because it potentially leads to bigger/more complicated solutions/git repos/branch schemes... but it works.

For build/deploy:
Maintain a persistent "build" environment and continuously deploy the latest source code to it. Now you have a SQL instance where all the dependencies live and you can use it for green builds and publishes. It's annoying as hell because it's another environment that needs to be maintained... but it works.

In a better world, this build env would be generated via infra as code for each build, but that increases build times, and if you have complicated circular dependencies you run into trouble building all the databases. A way to get around that is to generate a queue of build environments ahead of time, but now it's even more complicated.

Tools like docker, dbaclone, Redgate SQL Clone, and plain old backups can help, but it's all extra complexity and overhead. It's the hidden cost of cross-db dependencies that few people consider.

Alex

Karri

unread,
May 26, 2021, 3:02:19 AM5/26/21
to tSQLt
Thanks Alex for that.
We have a build pipeline that runs for all (feature/bugfix/hotfix) branches as well as the trunk every time something is pushed to remote.
We use JIRA for our backlog, so when someone starts a tasks, the feature branch gets created from JIRA and kicks of a build. Once I've made changes to feature branch locally, I push it back to remote, and the build runs again. This build pipeline is mainly used to validate the VS solutions so that everything builds, it doesn't create artifacts. We have a separate 'release build' pipeline, which does all the same things but then also creates the artifacts for release to the next environment.

In Visual Studio, we have all the databases in the same solution, they are just separated into their own SQL projects. These UnitTests projects is the only one that will contain references to other databases. The reasoning behind this is that the databases in the solution (the DW stack) are wildly different in terms of contents -- this is partly because we have a staging database, a datavault database and datamarts. Some of the objects are generated programmatically, others are manually developed.

I don't still quite understand why the 'Publish..' feature in Visual Studio deploys the database and its references to the same database, but when I run this through the Azure Devops release pipeline, the end result is different. Or maybe it's a configuration issue then in the pipeline.

Dmitrij Kultasev

unread,
May 26, 2021, 3:18:00 AM5/26/21
to ts...@googlegroups.com
There might be mixture of different issues. First of all about publish. 
Setup1: 
    main_project
    other_project --> this one has reference to main_project project as `the same database`
Setup2:
    main_project
    other_project --> this one has reference to main_project project as not `the same database`

So if you publish Setup1.other_project, it will publish both main_project and other_project. If you publish Setup2.other_project it will publish only changes from other_project.

There is another issue mentioned there --> external objects. That is a pain both in SSDT and tSQLt. My preferred way to solve that is to create synonyms for every external object in such case I make SSDT happy. However tSQLt natively doesn't support faking synonyms then for this I've created Pull Request that is not approved yet, but it allows to fake synonym tables and views.

Bhavin Katrodiya

unread,
Jul 22, 2021, 3:18:22 AM7/22/21
to tSQLt
Thanks, Dmitrij for having support synonyms for tSQLt natively PR. Do you have visibility of when this PR will be approved and part of the framework? However, is this new functionality have privileges to apply ApplyConstraint same as after fake table?
As for us, adopting tSQLt with this limitation of faking synonyms is kind of a show-stopper for us to move ahead. Looking for another workaround takes lots of time[consider maintenance as well once this is part of framwork] due to the considerable volume of synonyms consumes by SPs. 

Dmitrij Kultasev

unread,
Jul 22, 2021, 3:46:14 AM7/22/21
to ts...@googlegroups.com
Hi, I'll let Sebastian provide full answer, but in short we are in the middle of organizing PR review and some shared coding sessions, however it's tough as we have full time jobs and living in different timezones.
My proposal would be to take changed objects from PR and replace them in your database. Then you'll not need to change your coffee after the merge

чт, 22 июл. 2021 г., 10:18 Bhavin Katrodiya <bhavin.k...@gmail.com>:

Bhavin Katrodiya

unread,
Jul 22, 2021, 11:14:57 PM7/22/21
to tSQLt
Hi, thanks for your updates, and understood the challenge involved in rollout of this functionality. 
Reply all
Reply to author
Forward
0 new messages