TDD With Store Procedures

When I'm coding in .Net (c#), I use NU开发者_开发知识库nit and Rhino Mock to do testing. Haven't done much database coding (Store Procs). How can we do TDD with database coding (Store Procs)?

---

If you're using an SSDT database project you can use a rename trick in the SSDT Unit Tests to mock your code entry points as you do in regular C#. Not true IoC stuff but gets the job done until something better comes along.

I wrote a post about it:

https://tangodude.wordpress.com/2014/02/08/ssdt-database-projects-mocking-database-objects-for-isolated-unit-testing-kind-of/

You can also tSQLt for your development, which supports better DI than SSDT on its own.

http://tsqlt.org/user-guide/isolating-dependencies/

---

1) Have a framework which allows you to take a DB object, and make a copy of it. This workds well when you have your DB source code including table definitions stored on a filesystem.

• a copy of a table into a temp database. Including indexes, etc..

• a copy of stored proc or trigger which replace all the DB names prefixing tables/other called SPs with temp database

• Able to pre-populate the table with data either cloned from existing table (using some filter) or prepared data set stored, for example, as a bcp input file

2) Have a framework which loads specified test tables with specified test data, then clones all the other needed SP/trigger code

3) Have a framework which executes the cloned stored procs, and evaluates both the results returned from them AND the data left in the table in case they update the data.

Just to give it flavor, it took us a couple of man-months to develop and deploy a full TDD system which included all of the functionality listed here as a small sub-set.

---

There are also some commertial product available:

http://www.infoq.com/articles/tdd-dbpro-Foy

---

Also, there are producs for specific DB backends, not sure which one you use:

Postgres: http://en.dklab.ru/lib/dklab_pgunit/

---

Additional reading:

MSDN http://msdn.microsoft.com/en-us/magazine/cc164243.aspx

http://tddbootcamp.com/2010/01/27/writing-tests-for-stored-procedures/