DDL language recognition to allow idempotent type creation

[dbhoot]

What do you want to change?

I use sqlc with a separate migration tool. While my migration tool doesn't require it, I generally like my migrations to be idempotent. In order to do that with custom types, you typically have to declare them in a block like

DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'status') THEN CREATE TYPE status AS ENUM ('pending', 'cancelled', 'completed', 'failed', 'reversed'); END IF; END $$;

However, when sqlc goes to generate code for tables that use the declared type, it doesn't recognize the type since the DDL in the $$ block is not understood. As a result, the referenced columns always show up as the empty interface.

https://play.sqlc.dev/p/e538df46e32e4eb22815262d88416d92a29fb9198ca5302257b42019833b4b46

My work around is to not have the type declaration be idempotent :(.

What database engines need to be changed?

PostgreSQL, MySQL, SQLite

What programming language backends need to be changed?

Go

[kyleconroy]

There are a few ways to workaround this issue. Understand and interpreting DO functions is going to be pretty far off. Instead of having sqlc rely on your migrations directory, you could output a dump of your SQL database after your migrations have been run.

The other option is to create another, sqlc-specific, migrations directory where you have the CREATE TYPE outside the DO function.

I understand both of these options aren't ideal, but I'd like to get you unblocked.

[dbhoot]

These are great alternatives. Thank you, I was about to unblock myself.

If understanding the DO block is out of band, would a structured comment be more achievable ?

[andrewmbenton]

If understanding the DO block is out of band, would a structured comment be more achievable ?

We have a few "structured comment" proposals in the works. Are you suggesting something like:

-- @sqlc-ddl "CREATE TYPE status AS ENUM ('pending', 'cancelled', 'completed', 'failed', 'reversed');"
DO $$
  BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'status') THEN
      CREATE TYPE status AS ENUM ('pending', 'cancelled', 'completed', 'failed', 'reversed');
    END IF;
  END
$$;

[dbhoot]

Yes, something along those lines.