Creating Fake table from test db without tSQLt installation in target db

[Ruchi John]

I have intergarted tSQLt framework using below links:

https://www.sqlshack.com/tsqlt-framework-and-sql-server-database-project-integration/

Now i have TestDBProject_UnitTest DB which has tSQLt installed and i am writing test cases in this.

I am calling store procedure of other database to test in test cases and i need to create fake tables for those but I don't want to install tSQLt in that database. Also it's not mentioned in the above to have so in target database.

How could i achieve that.

Help Please

[Thomas Bjerknes]

This is how I do it, with a similar setup to what you have:

1) Two projects. One is the database I want to test, the other contains the unit tests.  Lets call them DB_Dev and DB_Dev_UnitTests

2) In  DB_Dev_UnitTests, install the tSQLt framework.  Right click references, add database, choose your method for importing the framework (I use a dacpac file). Under Database location, make sure you reference 'Same database'

3) In  DB_Dev_UnitTests , add a database reference to DB_Dev. Under Database location, make sure you reference 'Same database'.

4) In both DB_Dev and DB_Dev_UnitTests, you may need to add a reference to the master database. If that is the case, Right click references, add database, add Master database, Under Database location, make sure you reference 'Same server, different database'.

5) Right click on your solution => Set startup projects, choose multiple startup projects. Set action 'Start' on both of your projects.

6) Right click project DB_Dev_UnitTests, properties, debug. I suggest you check 'Always recreate database' while you write your tests. It saves you a lot of pain from debugging strange results. This is just an emtpy local database, so recreating it is no big deal. Make sure the target connection string is something like Data Source=(localdb)\ProjectsV13\...........................

See if this get's you any closer. It works for me. By the way, the unit testing test adapter mentioned in the article is incredibly unstable in Visual Studio 2019. I have stopped using it, and instead I run a post deployment script that executes tSQLt.RunAll. Much better in my experience.

[Ruchi John]

Hi Thomas,

thanks for quick response but i have done all this still my scenario is-

1. In DB_Test_UnitTest i m writing test case to test store procedure of DB_Dev which is inserting data to a table.

2. In test case i have to write

DB_Dev.tsqlt.FakeTable ‘tablename’

this line shows error in visual studio project because DB_Dev doesn’t have reference of tSQLt

I am writing the wrong syntax? 

Thank you 

--
You received this message because you are subscribed to a topic in the Google Groups "tSQLt" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/tsqlt/sWU63eaJHv0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/c40a5a99-06ae-438f-93a8-95e3070e6660n%40googlegroups.com.

--

Regards,

Ruchi Rani

[Thomas Bjerknes]

Hi 

The tSQLt framework is in your test project, not in DB_Dev. So writing  DB_Dev.tsqlt.FakeTable ‘tablename’ wont work

Just write EXEC tSQLt.FakeTable 'tablename';

Note that it is important that you added tSQLt as 'Same database' as I mentioned earlier. If you didn't, this syntax will not work.

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/CAC%3Dig9Que%2BvOGkHnH9-Dh9uJGpZTobaR4vi9wXui5%2B3-%2BJ2DqA%40mail.gmail.com.

[Ruchi John]

Hi Thomas,

I Used this link to install and integrate tSQLt- https://www.sqlshack.com/tsqlt-framework-and-sql-server-database-project-integration/

Also for fake table of my DB_Dev I could found only one solution at https://blog.sqlterritory.com/2018/03/13/quickquestion-mock-table-in-another-db-using-tsqlt/

My unit test project is like-

My test case -

So as per your suggestion if I use this below change it gives error [TestActionLogClass].[test sp_updateBulkActionLogsVerify] failed: (Error) FakeTable could not resolve the object name, 'dbo.ActionLogs'. (When calling tSQLt.FakeTable, avoid the use of the @SchemaName parameter, as it is deprecated." 

With this syntax, it's not able to find tables to be faked because these tables do not exist in unit test db.

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAGCGso_YO5A1weVKf7mPh%3DJZnKa8D%2BZu-JYT1XbqKyvRkRmEvw%40mail.gmail.com.

[Thomas Bjerknes]

Hi

I'm not in front of the computer now, so I will have to look at this some other day. In the meantime, right click on your test project and view debug database.  In the explorer you should see the database your project runs the tests in. If you have set up things correctly, you should have all the objects from your main database, and tSQLt objects together in the same database. 

The point with the setup, is that you copy the main database and the tsqlt stuff into your test project. This way you run all your tests against this debug database, without affecting anything else. Another quick thing: you don't have to drop the expected and actual tables. The framework takes care of this 

Thomas 

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC%3Dig9RdH4sXGunN3DBsUkW_9w4YB9U_Jg8A4uJKnQDmSiec7w%40mail.gmail.com.

[Bryant McClellan]

Ruchi,

We have been using tSQLt for at least 5 years. We have many cross-database projects that require cross-database testing. In order for the tests to work properly you have to install tSQLt in each of the databases. tSQLt cannot fake a remote table because of the way SQL Server functions. In order to rename the original table the command must run in the context of the remote database and that means the framework has to exist there. Unless there are changes in the new version, that is what I learned from the authors.

The fact that you are using a sqlcmd variable to define the table reference tells me you are working across 2 separate databases rather than 2 projects in a composite solution.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

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

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC%3Dig9RdH4sXGunN3DBsUkW_9w4YB9U_Jg8A4uJKnQDmSiec7w%40mail.gmail.com.

[Ruchi John]

Yes that’s why I am stucked that i will have to install tsqlt on the development database as well which cause extra effort.

Also even if i do so, in my solution in visual studio i will be having tsqlt reference in both unit test project as well as actual development db project. This causes issues for tsqlt adapter to find the test cases. I couldn’t find test cases in test explorer if i add tsqlt in both projects 

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAN3pJNWKvv9%3Dyk%2B10uvm4UgGScAQJhoJ45V8SuA-hL0i%3D0LFDg%40mail.gmail.com.

--

Regards,

Ruchi Rani

[Ed Elliott]

If you are using a shared database for development you will hit issues.

I typically have one database per developer (either localdb or sql developer edition but could even be sql express or sql in a container) and run tests there.

To run your unit tests you really don’t need much (any?!) data - each test should setup the data you need for that test.

Ed

---

From: ts...@googlegroups.com <ts...@googlegroups.com> on behalf of Ruchi John <bijnor...@gmail.com>
Sent: Thursday, October 22, 2020 4:59:13 PM
To: ts...@googlegroups.com <ts...@googlegroups.com>
Subject: Re: #tSQLt: Re: Creating Fake table from test db without tSQLt installation in target db

 

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC%3Dig9RqHoV_2PSvnFdv2-YMAtrxc8di93RDZP_sQ444Vgs6xw%40mail.gmail.com.

[Bryant McClellan]

We only have a reference to the tSQLt dacpac once in each individual database solution. A solution will generally have 5 projects for us: the root project, 4 environmental projects and a UnitTest project. We bundle tSQLt in a dacpac with our own extensions to make distribution easy. If databaseA needs to fake a table in databaseB it assumes that the databaseB project has deployed the tSQLt dacpac itself. If not, the databaseB project is updated accordingly and a deploy is made. Because database unit testing via tSQLt is our standard we can make those assumptions.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

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

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC%3Dig9RqHoV_2PSvnFdv2-YMAtrxc8di93RDZP_sQ444Vgs6xw%40mail.gmail.com.

[Ruchi John]

yes i m setting up data in each test but trying to fake tables for that causing issues as it’s the other database for which table i want to fake

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/AM6PR07MB4677FD71900CFD064B85985BA21D0%40AM6PR07MB4677.eurprd07.prod.outlook.com.

--

Regards,

Ruchi Rani

[Ruchi John]

I didn't get your point "copy the main database and the tsqlt stuff into your test project." How to copy main database into test project? I have added reference of DevDB project (SQLClientDBSchema) in my unit test project (SQLUnitTests).

Choosing View Debug Database i saw these:

Also the tutorial link is confusing as it's not properly explaining the linking between unit test and actual db project.  In sql query provided they used "TestDbProject"(Although no step mentioned to add reference of that project into unit test project) and in screenshot they have shown TestDbProject_UnitTest. I have highlighted the difference in below screenshot.

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAGCGso-avL7jx7Kb1czzoSo%2BHc3TSCmn0k2AEqqioxRcweQvag%40mail.gmail.com.

[Thomas Bjerknes]

The copying happens automatically when you reference the main project an tsqlt, and you set startup as multiple. What does your debug database look like?

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC%3Dig9SLzDc4NS0hHNa-sMAHDtYZ_5gAS2H5LcwYk9z0NjmOYw%40mail.gmail.com.

[Ruchi John]

I had option "Go To Debug Database" option and it list all DB projects in Object Explorer but no external tables available in any of those. Here is screenshot, have a look please i am totally stucked.

Regards,

Ruchi Rani

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAGCGso-2JKqdN1ewv_gHg%3DuAke7LPHaSMiXbNLDNOKxpbhXiZg%40mail.gmail.com.

[Thomas Bjerknes]

Hi

I know it is confusing! I spent a week to get this working just trying a host of different ways until It finally worked. In my opinion there are no good tutorials on how to integrate it in VisualStudio. At least I have not found one, you have to piece it together from different sources. Anyway, this is my understanding of how it works from a Visual Studio perspective. I will explain my take on it below, but note that this is not the only way to do it. It may not even be the best way, but it has been working great for me since I started with it. The way it works in Visual Studio is conceptually a bit different than if you install tSQLt on your test server, or in the production database (only lunatics do the latter). Here goes: 

You have the database you want to test in one project. This is not your real database with data and everything, but an import of all the objects from your real database. You add this to source control, you do your change management here (at least I do). And it works as a proxy of your real database you can test against. In your case that is SQLClientDBSchema. 

Then you have your Unit testing project. You install tSQLt in this project. This is the only place I have tSQLt installed. You add master, and a reference to the main project. I will explain the steps again below, but conceptually the link between the projects are this: When you hit F5, It creates a copy of the main database in your test project. It also creates all the tSQLt objects in your test project. You create all your tests in your test project (you see a pattern here? :) ) . When run hit F5, it says that it deploys the database, which may sound confusing, but that is what is happening: It deploys it to your localdb:

This database contains everything! It contains the main db, tSQLt, and your tests. Everything you do from a testing perspective is against this database. This is important to understand, so when you mock a table, you don't mock a remote table (like in the article you referred to). You mock the table in your test project.

Lets say I have a table Customers in my database. I want to test against it. This table will end up in my localdb server through the deploy process explained above. I mock this table which is now in the debug database in my test project. No need to refer to some other database, or some other server. I write the tests against this mocked table. I know this is a valid test, because this table is a copy of the table in the main project, which again is a copy of the real world database. I am therefore testing the functionality of the real table in a roundabout way, but in a safe environment where I cannot ruin anything real! The database you see in the picture above is a throwaway database. You can right click and delete it. Run F5 and it comes back again with all the objects

When you write $(SqlClientSchema), you are using a variable to refer to objects in another project. Like this

 Don't do that, at least that is not how I do it. I mentioned earlier that you have to add tSQLt and the main project as  'Same database'.

See now they end up in your test project, and you can refer to them directly. Does right click properties look like this in your project:

In startup projects, does it look like this? This is important!

My suggestion is that you delete your test project and start over (or create a second test project). Import tSQL as dacpac. I found this to be much easier than everything else

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC%3Dig9TZP-ekkY4%3DYXgbCuNeCgNMsTh4fiGrZF3NLSWO5iMTcA%40mail.gmail.com.

[Ruchi John]

Hi Thomas,

Thank you so much, I really appreciate your efforts to help me. 

Yes selecting "Same Database" fixed the issue. Now i have copy of dev database into my test db :)

I would like to ask 3 things still :

1. How to add .dacpac for tsqlt. Will it be downloaded from somewhere? I didn't have any file of that type.

2. tSQLT test adapter takes too long to find and run test cases in test explorers. I had installed tsqladapter through nuget in some .net project. I know you had mentioned to not to use it, instead using postdeployment script. I am using that to publish my testdb project to sql server but i also want to see my test cases in test explorer.

3. I also selected "Älways re-create database". I noticed after publish in database there are store procedures those i actually had created earlier directly in test database. So should I select Drop object in target as well or not?

Thanks

Regards,

Ruchi Rani

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAGCGso8faNLeRqPbSeuYfY1ZrVXMUurCz6%2BrJaUNA9hf0SsTZw%40mail.gmail.com.

[Ed Elliott]

You can set a filter in your run settings file so the test adapter is faster, without the filter every sql file in the project will be searched for tests but if you put your tests in a folder called “tests” or something and add the filter to the run settings “*tests*” it will be much faster.

The filter is the includePath setting: https://the.agilesql.club/2016/08/tsqlt-visual-studio-test-adapter/

Ed

---

From: ts...@googlegroups.com <ts...@googlegroups.com> on behalf of Ruchi John <bijnor...@gmail.com>

Sent: Friday, October 23, 2020 7:57:22 AM

To: ts...@googlegroups.com <ts...@googlegroups.com>
Subject: Re: #tSQLt: Re: Creating Fake table from test db without tSQLt installation in target db

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC%3Dig9RiTKUHK%3DdjfnJY8Ri0D2_4OeOSDp%3D%3DC2xGPGKeroFkcA%40mail.gmail.com.

[Thomas Bjerknes]

Hi

If it works now, then there is no need to use the dacpac version, it is just another way of importing tSQLt. Just keep it the way it is. Don't check the 'DROP objects' box. Your test procedures are supposed to be in your debug database. That is the point of this setup. Everything ends up in the same database, the tsqlt framework, the main database, and the tests themselves. 

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAC%3Dig9RiTKUHK%3DdjfnJY8Ri0D2_4OeOSDp%3D%3DC2xGPGKeroFkcA%40mail.gmail.com.

[Ruchi John]

Thanks Thomas and Ed !!!!

You guys made my day :) otherwise i might be stuck on this for long...

Next step is to integrate it in CI pipeline... Will be back with queries soon :)

Regards,

Ruchi Rani

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/CAGCGso_HgPpp3eJVbiUa9cS-Wdo0cn7oGYvdD-hfmV-8%3DEvPng%40mail.gmail.com.

[Ruchi John]

Hi Eid,

A quick question if you can help out. Actually I created a folder to keep my test inside that for specific module testing and used that folder name to filer the path in runsetting file. Now i created another folder to write down test cases for another module. I want to include that one as well but if i add another parameter nameed ÏncludePath , it's not working.

Even I tried to create a folder and within that created multiple folders one for each module and written test cases within those. Used main folder name in runsetting file but it's also not working. :(

Regards,

Ruchi Rani

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/AM6PR07MB46771337612287ACE040EA87A21A0%40AM6PR07MB4677.eurprd07.prod.outlook.com.

[Ruchi John]

Hi Guys,

Please help me again, I am getting this error in Visual Studio test explorer for some of my test cases while those are passing with success msg in Sql server itself:

 failure.message: Expecting to get a data reader with the response to: "exec tSQLt.RunWithXmlResults

Regards,

Ruchi Rani