MySQL: How do I test my database architecture (foreign key consistency, stored procedures, etc)

Question

I'm just about designing a larger database architecture. It will contain a set of tables, several views and quite some stored procedures. Since it's a database of the larger type and in the very early stage of development (actually it's still only in the early design stage) I feel the need of a test suite to verify integrity during refactoring.

I'm quite familiar with testing concepts as far as application logic is concerned, both on server side (mainly PHPUnit) and client side (Selenium and the Android test infrastructure).

But how do I test my database architecture?

• Is there some kind of similar testing strategies and tools for databases in general and MySQL in particular?

• How do I verify that my views, stored procedures, triggers and God-knows-what are still valid after I change an underlying table?

• Do I have to wrap the database with, say, a PHP layer to enable testing of database logic (stored procedures, triggers, etc)?

Answer 1

There are two sides of database testing.

• One is oriented to testing database from the business logic point of view and should not concern persisted data. At that level there is a well-known technique - ORM. Algorithm in this case is simple: describe a model and create a set of unique cases or criterias to test if all cascades actions perform as they should (I mean, if we create Product and link it to Category, than after saving a session we get all entities written in DB with all required relations between). More to say: some ORMs already provide a unit testing module (for example, NHibernate) and some of them even more cool tool: the easiest and the fastest way to create database schemes, models, test cases: for example, Fluent NHibernate.

• Second is oriented to testing database schema itself. For that purpose you can look at a good library DbUnit. Quote from official site:

DbUnit is a JUnit extension (also usable with Ant) targeted at database-driven projects that, among other things, puts your database into a known state between test runs. DbUnit has the ability to export and import your database data to and from XML datasets. Since version 2.0, DbUnit can also work with very large datasets when used in streaming mode. DbUnit can also help you to verify that your database data match an expected set of values.

At finally, I highly recommend you to read the article "Evolutionary Database Design" from Martin Fowler's site. It's a bit outdated (2003), but still worth to read indeed.

Answer 2

To test a database, some of the things you will need are:

1. A test database containing all your data test cases, initial data, and so on. This will enable you to test from a known start position each time.
2. A set of transactions (INSERT, DELETE, UPDATE) that move your database through the states you want to test. These can themselves be stored in the test database.
3. Your set of tests - expressed as queries on the database, that do the actual checking of the results of your actions. These results will be tested by your test suite.
4. Exceptions can be thrown by a database, but if you are getting exceptions, you are likely to have much more serious concerns in your database and data. You can test the action of the database in a similar fashion, but except for "corner cases" this should be less necessary, as modern database engines are pretty robust at their task of data serving.

You should not need to wrap your database with a PHP layer - if you follow the above structure it should be possible to have your complete test suite in the DML and DDL of your actual database combined with your normal test suite.