CODEWITHSUNDEEP
c#sqlunit-testingintegration-testing
Samuel
Samuel

Reputation: 12341

Database integration tests structure

For the help, here is my environment:

I'm in the process of creating a lot of integration tests that will interact with a database. Each test must have a database in a very specific state. I don't want to insert data with an ORM or directly with Sql Management studio.

What I want is a couple of sql scripts with a structure same like VMware snapshots.

For example:

--> [Main script with system tables like tables mapped to enum].sql

-- --> [A couple of rows in some tables].sql

-- -- --> [A couple of other rows in other tables].sql

-- --> [A couple of rows for another test].sql

-- -- --> [A couple of other rows for handle others cases].sql

With this type of structure, I will be able to rollback to a specific version of snapshot to set the database in a specific state and than create another branch of snapshots in the structure.

Before each tests, I clean all tables in the database and then execute a specific sql script.

Is it a good way to do integration tests with database?

Does exist tool to do this type of sql script structure?

Do you a a list of bests practices?

If it's not a good idea what I want to do, what is the best architecture, methodology to do this type of tests?

Thank you very much.

Upvotes: 2

Views: 2132

Answers (2)

Stuart Whiteford
Stuart Whiteford

Reputation: 531

We use FitNesse coupled with dbFit for this kind of database testing, we're also using SQL 2008 R2. I'm not sure what the structure of VMWare snapshots look like but FinNesse allows you to build up a hierarchical set of tests (with a wiki like structure and syntax).

The main benefits from my own point of view are:

  • dbFit will implicity run in a transaction and automatically rollback at the end of each test.
  • The hierarchical structure enables you to add setup and teardown scripts that can apply across multiple tests.
  • You can create a suite of individual tests that execute all the tests sequentially.
  • Integration (to some degree) with our continuous integration server, TeamCity.

Upvotes: 1

Torbjörn Kalin
Torbjörn Kalin

Reputation: 1986

You say that you don't want to use an ORM, but I choose to disregard that in my answer. Sorry :)

How to populate

I prefer inserting data into the database using the same operations that a user would. That is, if I need an order for a product in the database, I create the product using the admin interface, let a user create an account using the public interface and have that user place an order.

This doesn't have to be done by clicking buttons in the actual UI, but preferably using an underlying abstraction of the UI.

Benefits:

  • The database will have the same state as when executed for real, not the state you think it will have
  • If you need to make changes, e.g. to the schema, you only have to change the production code
  • More features are covered by the tests
  • The database is populated only with data needed for this perticular use case
  • The test are easier to read (it's not easy to read insert scripts)

Drawbacks:

  • It's slower
  • Does't work well if you need a lot of data (which is rare)

I've only needed a large amount of data for tests once. For that I created my own declarative domain specific language with which I could specify the necessary data in a (verbatim) string inside my test. Then I wrote a tool that parsed the data and inserted it into the database.

Clean the database

There are several ways to clean the database: re-create the database; delete all data; execute the test in a transaction and make a rollback... My faviourite, though, is not to clean the database. For every test I create a new user and a new product (using the example from above). I don't have to clean the database and still the tests won't interfere with each other. Also, I will have the data around after the test if I need to inspect it.

Hope it's somewhat helpful even though it's not really an answer to your question.

Upvotes: 3

Related Questions