1

I am fairly new to TDD and database development. If I wanted to use test driven development when I am trying to create a database schema how would I do that? What are the common tools and processes used in order to do so?

2 Answers 2

0

TDD is for testing application. DB schema is not an application and you cannot test it per se. You can test application which uses that schema.

Tests will help you determine if your application is still working correctly after refactoring, which may include any changes in the schema (or any other changes).

1

Despite the answer being accepted, I would disagree with the statement: "TDD is for testing application. DB schema is not an application and you cannot test it per se."

TDD is for all IT work. TDD and Unit Testing, in particular, are (or should be) very much a part of database construction.

We do database construction using TDD; a data warehouse in our case.

There are two main ways we apply to databases.

Firstly, this is particularly important since the OLTP system is controlled by another team; and it is changed sometimes without us being informed. We do have interface schemas with schema binding on to try and protect us, but things still happen.

So, we collect the INFORMATION_SCHEMA.COLUMNS information on each table of interest to us then write a hash across this information, which the checksum result is compared to a known checksum value.

If the checksum mismatches, then we know the schema has changed.

I would now go on to address the complication with schema checking, that in TDD, you should build the test first then you do the programming ...

We build a 'shell' hash test initially, knowing it will fail (since you can't know the hash value until you have the full table structure), then follow the process as you normally would with TDD, build, test, modify test if necessary, retest, modify the build, retest, etc ...

Secondly, we can build tests that will verify the logic applied to stored procedures, functions, triggers and calculated columns.

2
  • That's not TDD as much as using TDD tools to warn you if an external party has intruded changes to your system unannounced, right?
    – Jack Deeth
    Mar 4, 2021 at 13:09
  • 1
    my answer talks around schema changes, but TDD can also test logic that applies to calculated columns, database triggers and stored procedure / function calculations. I'll amend my answer to include these.
    – Marcus D
    Mar 8, 2021 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.