dimanche 10 juillet 2016

Create a unit test two check if two psql schema are the same

I have this project with an huge db schema, that sometimes is updated.

So I have a file, called schema.sql, which has the current schema, and a directory with all the patches I deploy over time (called like patches/20160710.sql, with a file with the first schema called origin.sql).

If I do a fresh installation, I use schema.sql, otherwise I apply the patch when I do an update.

I have test server where I do tests before updating schema in production and I've automated almost everything. So far works good.

Unfortunately once I wrote a different thing go schema.sql and in the patch. My unit test runs only against a fresh database, so tests didn't fail and I found the error only I tried on the test server the patch.

I would like to write an unit test which takes schema.sql, deploy it, and compare the schema with the schema generated by all patches applied on origin.sql.

I need also the check some tables have the same values.

Database is postgresql and software is in node.js.

Tests are run by Gitlab CI with a custom docker image, so I really can do the test in whatever language is better.

Both patches and schema.sql have, of course, the same name for database, so I thought I have to apply schema.sql, dump it in some way, then drop the database and recreate it from patches, dump it again and compare the results.

Does this approach make any sense or there is a better way to achieve what I'm doing?

If this makes sense, what's a good way to dump the two databases and compare them?

Aucun commentaire:

Enregistrer un commentaire