tSQLt in a shared development database architecture

[Karri]

Hi,

I've been doing a fair amount of googling on the subject and haven't really found any definitive answer, so this Google group seems like a good place to post this question :) 

I'm just getting started implementing tSQLt as part of our development process, and I'm wondering about some best practices / design approach ideas. I'm working with a team of developers who share a development database due to restrictions on where data can be stored (i.e. local replicated copies for development are not allowed). We use SSMS for development and Visual Studio to version control the changes, deployment happens with a CI/CD pipeline to test, QA and production servers.

My original idea was that each developer would create unit tests into the shared dev database, run them there, and then version control the tests into the VS database project. When changes are released to test, the deployment pipeline would then run the tSQLt unit tests against test (which has better quality data), and if everything passes, then move the changes to QA and later to production.

I'm slightly apprehensive about polluting the later environments (especially QA and production) with tSQLt contents, so I was thinking that some sort of post deployment script is probably necessary to drop the tSQLt framework in QA and production. Does that sound like a good idea?

I was originally thinking that creating a parallel "dev_unittest" database to house the tests would make the database cleaner, but the cross-database functionality of tSQLt is not officially supported (even though it worked with a few initial test cases I tired).

Is anyone working with shared development databases and how are you using tSQLt?

Thanks in advance,

Karri

[djla...@gmail.com]

Hi Karri -- we are using it in a test environment among a team of developers, and we control what gets pushed to qa (and therefore production) at the schema level. All objects that tsqlt builds tests for get a schema name based on the object, which makes it easy to filter. Hth david

--
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/6ccd3b7f-12d1-4390-9224-20a06829c17fn%40googlegroups.com.

[Larry Blake]

You shouldn't run tSQLt in a shared environment, period.  The problem isn't the few extra objects.  It's the fact that during tests, "FakeTable" will rename your objects.  Anyone expecting persistent data will get some unpleasant surprises.

We use a Jenkins pipeline to load a Docker container with an empty database and tSQLt objects, then each test creates its own data.  At the end of the pipeline, the container is destroyed.  I realize this seems like overkill, but the point is it ensures isolation.  That's good for test integrity, and other users. 

[Larry Blake]

I didn't realize that cross database tSQLt isn't officially supported.  We've been doing it and had no problems.

[Bryant McClellan]

Hi Karri

When you say you are using VS to handle source control I assume you mean using composite SSDT projects. Doing so you can choose which environments reference the unit test project. That keeps you from having to unload it from your QA and production environments because you'll never deploy it there.

When not developing locally our developers all share development databases. Obviously they can feature branch code to avoid stepping on toes but eventually the code has to converge in source control. Each database is independent in that different types of data have different home databases. We don't commingle data to a common database. But we do cross-database testing where code in database A needs something in database B and has to perform some level of testing.

On Tuesday, May 18, 2021 at 9:49:54 AM UTC-4 Karri wrote:

[Karri]

Hi,

First off, thanks for all the feedback.

The team I work with all uses a centralized database server (dev) and everyone develops SQL code there. The server stack includes multiple databases which make up our stack.

Each database is version controlled in Git as a separate VS database project, and the entire stack is under a single VS Solution. We indeed use SSDT's Schema Compare to update the project metadata with changes (each developer commits to a local feature branch before it ends up in our trunk, via a PR). We use an on-prem version of Azure DevOps Server, which is then able to deploy the VS Solution nicely. 

As I said we need to work like this, because for complicated reasons I won't get into, we can't replicate copies of the databases locally to the developers' workstations.

My initial idea was to have our test environment run all the actual unit tests when code was pushed there. But that would mean we would need to wait for code to be reviewed and merged into the trunk before triggering the release to test, and that feels like a slow way to work. So then my next thought was to have tSQLt on the this shared server so that unit tests could be created and run in the same place. But as Larry pointed out, the renaming is an issue and would potentially mess up development by someone else.

So would a better approach be to have a 'unit test' database which would house all of the tSQLt unit tests but reference the actual database the data is in? This would mean having tSQLt installed on both 'MyActualDB' and 'MyActualDB_UnitTest', where the latter would have the test classes and test cases.

And then the assertion of the test case would run the FakeTable like this:

EXEC MyActualDB.tSQLt.FakeTable @TableName = 'foo';

Our development practice might sound a little messy but as our development tasks are quite well documented and designed, we haven't really had issues with people fudging each others SQL code.

But as tSQLt programmatically renames tables, I understand that running it in the same database as the real data is problematic, when it's also the shared development environment.

The docker container approach doesn't sound bad at all, unfortunately the (on-prem) infrastructure I'm working with at the moment doesn't really allow me to do fancy things like that :)

My goal here is to (somehow) integrate tSQLt into the daily development process so that it feels natural and doesn't screw up / complicate development.

Thanks again.

/Karri

[Dmitrij Kultasev]

You can spin up empty db to run the tsqlt test on every push, you don't need to run it again on the database

--

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/e4f226f8-999d-4c75-b01c-5b49a0b7481en%40googlegroups.com.

[Dmitrij Kultasev]

Sorry, I sent previous message accidentally. You can run tsqlt tests on empty database, you don't need to run it on the db with the data.

For the source code we create following structure:

ProjectA --> is the project where all the database projects located

ProjectA.Tests --> is the project which is referencing ProjectA `As the same database`. This project will store all the tests + tsqlt sources (they can be referenced as dacpac)

So, you use ProjectA.Tests to publish on empty db on every push and ProjectA to the live databases

On Tue, May 18, 2021 at 6:50 PM Karri <karri.l...@gmail.com> wrote:

--

[Bryant McClellan]

What Dimitrij is describing is essentially what I was talking about. We distribute both the tSQLt framework and our own extensions as a .dacpac. You can create a UnitTest project in your solution that references the .dacpac. Then your development project references the UnitTest project as 'Same database'. Then when you deploy the development project (which would also reference the core database project) it carries the UnitTest project with it. We never deploy the UnitTest project on its own.

If your deploy places the tSQLt framework code in the same database as the rest of your code, you will be happier.

We do not use Schema Compare. Rather, we open the SSDT project in VS, develop there and check in. That also allows for automated build/deploy. It will exercise your pipeline on every check-in, which is desirable.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

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

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/SK0wiuEcvvE/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/CAJ1xV3i84tPAX9DUvaVtFE025ZM9_wVHSJnsBkra%2BsVdCQrXPQ%40mail.gmail.com.

[Dmitrij Kultasev]

I started to create "perfect" SSDT solution template some time ago, but never continued to bring everything together yet, however you can see the structure I described and some other tricks you can do with SSDT projects/unit tests in my repo: https://github.com/dkultasev/SSDTPowerTemplate

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

[Karri]

Hmm, so you share a development database with the actual data but instead of SSMS you write your queries in Visual Studio? That's neat.

I especially like the .sqlproj file reorder. We've had some problems too with the XML and how Visual Studio treats the merges, although I have to say that it's been less of a problem when working with a TBD approach.

I'm just wondering (out loud) how working through Visual Studio instead of SSMS using a shared database would work? Or would it essentially be the same, since you'd just run the queries in VS against the shared database? And in my case, would I then deploy into that shared development database or into the test environment to run my tests?

/Karri

[Bryant McClellan]

We develop in VS. We can deploy directly from VS or allow the pipeline to do so on check-in. It is convenient to develop in VS since all the tests are then in source control alongside the balance of the database objects. Plus you get the advantage of building the solution to validate it before you deploy.

Some of our databases are still in TFVC, some are in Git. VS doesn't care.

You can run the same queries in VS as in SSMS. You just don't have the same extensions. For example, SSMSBoost is SSMS only. We still make use of SSMS, especially for gathering test data and formatting it using SSMSBoost

In our environment we only run unit tests on local  databases or the development databases. Since you likely already have a deploy pipeline for your test environment, you can just as easily have the tSQLt deploy go to that environment.

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/79ebe9a1-01c5-49f0-9db6-99151ca46793n%40googlegroups.com.

[Dmitrij Kultasev]

We create objects in SSMS or ADS then save it to the SSDT project. You can open ...sql files in any tool and edit it in any editor (even in Visual Studio), you just need to change CREATE statement to ALTER and then you can execute it against your database.

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/79ebe9a1-01c5-49f0-9db6-99151ca46793n%40googlegroups.com.

[Alex Yates]

What Larry said. 👍

--

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/1c50c225-3d33-46ff-a3ba-a3c00f643225n%40googlegroups.com.

[Karri]

Regarding the tools, we've bought Redgate's "Essential Toolkit" which includes SQLPrompt, and looks to have many of the same functionalities as SSMSBoost. The former works in VS, which is nice.

I totally understand Larry's point on not executing tSQLt in a shared environment. 

I'm just trying to wrap my head around how to set up the SSDT project(s).

So if I have a database and matching project (e.g DataMart), and then create a new referencing DataMart.UnitTests database project in the same solution to store all the tSQLt test cases, do I then also install tSQLt in the DataMart database or is it enough that the DataMart.UnitTests contains the framework since it's referencing the former?

In an optimal setting, I'd like to be able to run the unit tests manually from Visual Studio after I've created a table into my DataMart database, and then run them again programmatically as part of the CI/CD pipeline when the trunk branch is deployed into test (since the data there mirrors production more closely).

[Dmitrij Kultasev]

All the tested objects + tSQLt objects must be in the same database. Normally you can't unit test objects from other databases. The main thing is here that you just publish "tests" project to the database where unit test should be executed and "without-test" project when you go to prod or other env.

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/83e84e94-b887-4396-9920-a6994449aefbn%40googlegroups.com.

[Bryant McClellan]

Our composite solutions all contain 5 projects. One is the core which contains everything common to every environment. There are 3 environmental projects, Dev, Stage and Prod. Each of these contain those things peculiar to the specific environment, like permissions. Each has a database reference to the core project marked as 'Same database'

The 5th project is UnitTest. It has a database reference to the tSQLt .dacpac. The Dev project has a database reference to the UnitTest project, marked as 'Same database'. When the Dev project is deployed, it also deploys the 'Same database' references, meaning the core and UnitTest projects. When Dev is checked in (or published directly) it also carries the database projects it references as 'Same database', namely core and UnitTest. In this way the development database has all the core elements and the UnitTest elements.

Certainly you can use any tool to write SQL code. Notepad is more than sufficient. I like that VS manages references directly and can flag breaking changes long before you publish them. VS is cumbersome for ad-hoc work, sure. We have built extensions to VS that provide all our object standards as templated code, including tSQLt test procedure templates. It is a great way to get new employees up to speed quickly and simplifies the application of standards.

G Bryant McClellan
Senior Solutions Architect
260.748.5393 (direct)

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

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/SK0wiuEcvvE/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/83e84e94-b887-4396-9920-a6994449aefbn%40googlegroups.com.

[Taylor Funk]

I've actually come up with the solution to this. In the idea of automated tests and shared environments. I have a case where we have multiple databases that reference each other. If you are running visual studio ms sql db projects a solution to use tsqlt is to do a new db project that references the base project as same database. When you deploy this is has the tsqlt objects, but in higher environments like prod you wouldn't deploy these test projects. Also as part of a shared environment to not have blocking builds from pr I add the build id using a powershell script into the database names. e.g. vehicles database references a mapping database. in the automated test I deploy mapping_test_buildid1, then vehicles_test_buildid1 with references db variable mapped as  mapping_test_buildid1. When i run the tests I run for each database.  mapping_test_buildid1.tsqlt.runall,  vehicles_test_buildid1.tsqlt.runall. output the results (remove new lines, an issue when you have lots of tests) and publish those xml results. in tfs or w/e junit tool you have. See some automated examples from the sqlcover project  Actions · GoEddie/SQLCover (github.com)

[Karri]

That's an interesting approach.

Our solution includes 6 databases in total, but the one we're really interested in testing is the 'datamart' that includes a dimensional datamodel for end-user consumption. I'm trying to understand how the tSQLt framework and the unit tests written by individual developers fits into the daily development process.

I'd like for the tSQLt tests to run automatically but as we use a shared development database, running tests in that doesn't work because of what Larry pointed out with the table renaming.

Maybe I'm making this too difficult, but the issue I see of running tests in the test environment as part of the CI/CD pipeline is that the code is then already part of the trunk branch, which we try to keep clean -- i.e no broken code is merged via PRs there. Once the code is part of the trunk, then running the test cases in the test environment as part of the deployment is easy.

[Alex Yates]

Add tsqlt to the unittests project. The unit test one references both the main project, and the master dB.

To the unit test project, add a post deploy to exec tsqlt.RunAll.

Any time you want to run the unit tests, publish the unit tests project. (Not against shared instances).

Any time you just want to deploy the database, publish your main project.

For more info, here's a talk I did about this a few years back, with demos:

https://youtu.be/ItPiG8NIEcM

Alex

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/83e84e94-b887-4396-9920-a6994449aefbn%40googlegroups.com.

[Karri]

Thanks Alex. I actually just recently watched your WinOps talk, while researching this topic. Excellent talk :)

After all this information, I'm pretty close to having a full picture of how to implement this, the only thing that I'm hung up on the 'not against shared instances'.

Since our dev server is shared by the team, publishing my 'datamart.UnitTests' database there would be fine if I was the only user of the database, right?

Before a feature branch is merged into our trunk, the branch goes through the Azure Devops build server, which would be an optimal place to execute the tSQLt unit tests. However, if we have multiple devs pushing code to their respective feature branches on the build server, wouldn't they have conflicting deployments? Or would a way to solve this be that we queue the builds one after the other, so it's a FIFO sort of deal, and the UnitTest -database is torn down after each deploy?

[Alex Yates]

Welcome to the core problem with shared instances. If you are all building different versions (branches), how can you all deploy them to the same instance and not expect conflicts.

Best to run tests on disposable empty DBs. Your build/test runs can build a database called [random_guid], deploy the schema, run the tests, and then tear it down.

If you have cross-db dependencies, ensure you do this on a Dev server where the head of the main (production) branch is continuously deployed. That should cover most of your x-db dependencies.

If you regularly hit the issue where both DBs are updated on the same branch, first of all, don't do that. Second of all, consider a tool like dbaclone or Redgate SQL Clone to clone all the dependent DBs during your builds. Then your build can deploy all the DBs in your branch.

After every build, tear it all down. 

Ultimately, getting good at spinning up and tearing down dev/test servers will fundamentally change the entire philosophy underpinning you dev process for the better. It should allow you to kill your shared servers in a compliant way.

Alex

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/aef59f7c-5366-48c6-a166-b251139244b0n%40googlegroups.com.

[Alex Yates]

Oh, and thank you for the kind words about my WinOps session. 😊

[Karri]

I guess the way we work is slightly weird in a sense that, for example, if someone is developing a view with business logic, they will create it into the share development database and after it's finished, version control the changes into the SSDT project, as opposed to creating the view in VS against a local database, running unit tests and then deploying the changes to the next environment.

Our release practices centre around a release-branch strategy where the 'version' of the release is the branch and is (semi) short-lived, until the next one comes along. 

If we were operating in the cloud, handling the infrastructure would be much easier, but alas no. Anyway, I like the idea of that random_guid database. Maybe that could solve the problem of running tests without local environments.

[Sebastian Meine]

Hey all,

This is a really great discussion and it comes at a good time. 

Liz and I have been thinking about how to better support tSQLt users that develop in Visual Studio/SSDT. We are just a few days away from publishing something.

The basic recommendation is going to be to have the code in one project and the tests in a separate project that depends on the first. 

This is in line with what many have mentioned here, and we're just working out the final kinks in setting up a template project.

So, stay tuned... :)

I hope a couple of you will have some time to test it out.

Thanks,

Sebastian

To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/1da39bb0-962f-4bd6-9b19-712820a7c105n%40googlegroups.com.

[Karri]

Sebastian, that sounds excellent. 

[Bryant McClellan]

Sebastian,

What you are describing sounds like exactly what we have been doing for about 5 years. It will be a great template for others to get adoption much more simply while taking advantage of VS features. I'm looking forward to it!

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/CAC7EguUFK_VU%3D9U4uF9MY9TTogME32qjzrUQi7v%2B50YTPCNbrg%40mail.gmail.com.

[Karri]

The main challenge I have is that there's really no templates or best practice guides for new adoption.

Of course everyone has their own way of doing things and restrictions, but having at least a starting point would be great.

And then the veterans who have been doing this secretly for years and years can give their insight too :)

Really cool stuff.