Recipe: Verifying PostgreSQL schema migrations with plain SQL
Recipes are the new series of short-form posts to JW Player engineering blog. Their objective is promoting healthy coding hygiene and sharing hard-won practical knowledge.
You can treat them just as cooking recipes, a step-by-step set of instructions. In recipes, we’ll capture day in and day out of our engineers: best security practices, ready-made solutions for specific problems, tips, tricks, and opinions. Recipes may not get deep into the weeds or provide comprehensive details, but they will always be practical and useful.
In JW Player we use Sqitch to run database migrations. We love Sqitch for its pure plain SQL approach which makes it suitable for use with any language or framework. Database schema changes (called
deploy step) and their counterpart rollbacks (called
revert step) are easily written in plain SQL, as they’re meant to be.
Having said that, there’s one more step called
verify and writing it in plain SQL isn’t as straightforward. This step is used to ensure instructions in
deploy step did precisely what you expect them to do.
Sqitch PostgreSQL tutorial provides a couple of examples of
In one example, they ensure schema
flipr exists. The offered approach is rather implicit: it uses division by zero error to check whether the query finds matching rows.
SELECT 1/COUNT(*) FROM information_schema.schemata WHERE schema_name = 'flipr';
No matched records results in
COUNT(*) = 0, which makes PostgreSQL run
1/0 and fail with
ERROR: division by zero.
Another example is dangerously misleading:
SELECT pg_catalog.has_schema_privilege('flipr', 'usage');
At first glance, this step ensures the current user has
usage privilege on schema
SELECT true should pass the verification and
SELECT false should fail, right? Well, that’s not what’s happening.
This verify step will not fail on
false! It will pass even when the user has no
What this step actually does is the same as the first example: verifying that schema
flipr exists. If it’s not,
has_schema_privilege() throws an error. It has nothing to do with schema privileges.
PostgreSQL has built-in procedural language
PL/pgSQL (yep, mouthful). Postgres v9.5+ brings anonymous functions and
RAISE statements into
PL/pgSQL. This makes it perfect for test assertions.
You can verify that the matching record is found:
ASSERT (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'flipr');
This works perfectly with any built-in function returning a boolean:
ASSERT (SELECT has_schema_privilege('flipr', 'usage'));
This time it does exactly what you’d expect: ensure the current user has
usage rights on
You can even use variables to perform more complex checks.
result := (SELECT name FROM flipr.pipelines WHERE id = 1);
ASSERT result = 'Example';
This example ensures the record with
pipelines table has
name field equal
- The integrity of your PostgreSQL schema migrations can easily be verified using built-in
PL/pgSQLprocedural language. You can learn about its features in PostgreSQL Documentation.
- Open source is ingrained in JW Player’s DNA. Open source contributions are highly encouraged. We’ve submitted our
PL/pgSQLexamples to Sqitch PostgreSQL tutorial: https://github.com/sqitchers/sqitch/pull/425