1

Our team has a bunch of micro-apis, I'm currently setting up a C# specflow project to do unit testing on all our stored procedures. we have 3 options

  1. Create a testing database for each micro-api. At the beginning of each test we clear the tables and populated them with test data, and run the stored procedure against it.

  2. We won't create a test database, just wrap everything in a transaction. We still clear and populate test data and run stored procedure against it, but at the end of each test we rollback the transaction

  3. Use an in-memory database, however, from my research I don't think SQL Server supports that. I looked into EF and SQL lite. (please correct me if I'm wrong)

What are the advantages and disadvantages of each. I'm trying to weigh them and see the best practice and what to watch out for.

I think option(1) is better because with option(2) other people might be using the database while your test cases are running and that could mess it up.

However with option(2) we won't have to create and maintain the testing database

Dale K
  • 25,246
  • 15
  • 42
  • 71
user1294510
  • 419
  • 2
  • 4
  • 19
  • 2
    Have you read this https://stackoverflow.com/a/3772382/914284 – Hamit YILDIRIM May 26 '19 at 23:32
  • You can use a SQL Server in memory DB, however the in memory DB doesn't support stored procedures. For usual unit testing you can mock them, but it sounds like the whole point is that you want to test the actual stored procs. – Ben May 26 '19 at 23:37
  • 1
    One approach I've used for development and automation of unit tests the [tSQLt framework](https://tsqlt.org/) with database containers. Unit tests are automatically run in a transaction with tsqlt and containers provide complete isolation. – Dan Guzman May 27 '19 at 00:42
  • @DanGuzman Oh I never found that during my research. Thanks! Would this containers function u mention allow multiple user to work concurrently? E.g. If there is a NoLock used in one of the stored procedure, and I have multiple developers making changes to data in the tables. Also have you used tools like specflow with tSQLt? Thanks again – user1294510 May 27 '19 at 01:16
  • @marven thanks, I just read it. The only problem I cant solve is that "Allowing developers to work concurrently". Beside having each developer have their own testing database I cant think of another way to do it. Do you have any suggestion? I dont see how you can have two people running test cases at the same time when they are testing against the same tables. Thanks again – user1294510 May 27 '19 at 01:19
  • @user1294510, tsqlt does not require containers. It can be used against a shared development database if that is your preference. The advantage of SQL Server containers or isolated database instances is that development and testing can be done in isolation and then changes promoted after successful testing. – Dan Guzman May 27 '19 at 01:37

0 Answers0