Recipe: Verifying PostgreSQL schema migrations with plain SQL

Sqitch verify made simple

Sergii Tkachenko
Jan 24, 2019 · 3 min read
Image for post
Image for post

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

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

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

  • 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

JW Player Engineering

A technical blog about delivering online video at scale.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store