Recipe: Verifying PostgreSQL schema migrations with plain SQL

Sqitch verify made simple

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.

Problem

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 verify step.

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 inCOUNT(*) = 0, which makes PostgreSQL run1/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 flipr. 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 pg_catalog.has_schema_privilege() returning false! It will pass even when the user has no usage rights.

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.

Solution

PostgreSQL has built-in procedural language PL/pgSQL (yep, mouthful). Postgres v9.5+ brings anonymous functions and ASSERT / RAISE statements into PL/pgSQL. This makes it perfect for test assertions.

You can verify that the matching record is found:

DO $$
BEGIN
   ASSERT (SELECT 1 FROM information_schema.schemata WHERE schema_name = 'flipr');
END $$;

This works perfectly with any built-in function returning a boolean:

DO $$
BEGIN
   ASSERT (SELECT has_schema_privilege('flipr', 'usage'));
END $$;

This time it does exactly what you’d expect: ensure the current user has usage rights on flipr schema.

You can even use variables to perform more complex checks.

DO $$
DECLARE
    result varchar;
BEGIN
   result := (SELECT name FROM flipr.pipelines WHERE id = 1);
   ASSERT result = 'Example';
END $$;

This example ensures the record with id=1 in pipelines table has name field equal Example.

Remarks

• The integrity of your PostgreSQL schema migrations can easily be verified using built-in PL/pgSQL procedural 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/pgSQL examples to Sqitch PostgreSQL tutorial: https://github.com/sqitchers/sqitch/pull/425