automatic database fill up in sql script tests

Question

The problem

I am working on an application with ~ 1000 tables in a sql server database. I am having a recurring issue with a sql script.

The script is used by the consulting team to "clean" some part of the database and sometimes it will fail because a new table with a foreign key constraint was added a few month ago and until this moment no one ran the script with any data in this new table. A foreign key violation is raised and everyone yells at the devs for their crappy tools.

There is a CI test on this script, but no ones updates the data provided in the test. So the test never fails because the new tables are left empty, but the script still fails in real environments.

The possible solution

I would like to make sure that every table in the database contains at least a few rows when the script CI test is run. I could try to maintain a manual script to fill up the data and add a test that checks that every table contains some rows but I fear that it will be a maintenance nightmare.

Another solution is to have another powershell/sql that script analyses the schema and creates rows according to the column types with constant/random values in all tables ordered using a topological ordering based on the foreign keys relation graph. The script would first fill table without relations, then tables with relations to the already filled tables, etc

I have two questions:

• does this solution seems reasonable?
• am I overcomplicating things? is there simpler way to make sure a sql script won't fail because of foreign key constraint violations?

I checked existing tools but the static analysis tools don't seem to have the required features and the data generation tools are used to specify "realistic" data which I don't care about.

Answer 1

You need to throw this back on the developers who are breaking the SQL. They are in the best position to update the SQL as they make changes which are fresh in their minds.

The simplest way to accomplish this is to use continuous integration.

Set up a test DB. Periodically, a CI build server will look at the source code repository for new check-ins. If it finds one, it runs the build.

Aside from compilation, the build should also deploy your application and test stuff like does it produce a valid executable file, do the SQL scripts run against a test DB, etc.

During the build, the DB should be rebuilt (backup, created from script, whatever is appropriate). Some process such as a stored procedure built for this purpose should ensure that data exists in all tables: that is, no table is empty.

Whenever a developer checks in a change that breaks your SQL script, it will break the build because a table is empty (no test data) or the clean procedure was not updated, or both. Eventually, developers will have to update the SQL when checking in their changes because without a functioning build, it cannot be deployed or tested and they are paid to produce a functional piece of software.

Answer 2

It sounds to me like you've got at least 3 environments; dev, ci, and production, and that the ci environment's database isn't being kept in a realistic condition.

Given that, I would say that the first option you offer, "I could try to maintain a manual script to fill up the data" is non-optimal - a CI server should be as fully-automated as possible, no "manual" scripts.

That said, we do use a process that creates data at the beginning of every CI run. We also use it in our dev and test environments and it's the responsibility of the development team (which in our case includes both "development" and "testing" tasks) to keep it up-to-date. As developers, we often (sometimes multiple times/day) wipe our local db and use this process to ensure we have a valid environment.

note: we consider that process to be just as important as test code, which is just as important as "real" code - so it's maintained and under source control.

Answer 3

I will propose an entirely different way of doing things:

You should have one "database-creation" script which creates the database from scratch, starting on a completely empty schema/catalog and creating all the tables, with all necessary relations, and all necessary immutable initial data. Immutable initial data are, for example, the days of the week (Sun,Mon,Tue, etc.) in the days_of_week table.

This script must always be committed and checked out together with the rest of the code, (assuming you are also using some other language besides SQL in the shop,) because the code relies on this script. This way, developers and testers can forget about keeping old databases around and amending them with changes. The database should be a throw-away entity, always created from scratch, and populated with data as needed. The state of your system is described entirely by what is committed in the version control system, and you avoid the constant headache of whether the version of the database is the same as the version of the database-creation script and the version of the code. Also, this way, each developer can have a local instance of the database for development and testing purposes, always being thrown away and recreated with every iteration of the checkout-develop-test-commit cycle.

So, my recommendation is to not have any script which creates test data. Each test should temporarily create the data that it needs in order to run, possibly in a transaction which gets rolled back instead of being committed, so as to leave the database empty, or each test should create a temporary schema/catalog, create the database in it, fill it with data, perform the test, and then drop the entire schema/catalog. If the test cannot create the data due to some constraint violation, then someone made a change to the database-creation script and committed it without first bothering to run the tests. It is their fault.

If you really insist on having a test-data-creation script, then it, too, should be committed and checked out together with the database-creation script and the rest of the code. That will also work.

Operations should maintain whatever older-version databases they need, and it is their problem to migrate the contents of old databases to new databases released by the developers.