Support custom CREATE DATABASE statements

[FelixZY]

Feature description

I'm developing an application which is highly sweden-centric. For this reason, I use the following setup:

CREATE DATABASE
  dansdata
WITH
  ENCODING 'UTF8'
  LOCALE_PROVIDER icu
  ICU_LOCALE "sv-SE"
  TEMPLATE template0;

However, these settings are not respected when the database is recreated by graphile-migrate.

Motivating example

I think it is important that migrations etc. are tested as close to production settings as possible.

Supporting development

I [tick all that apply]:

• am interested in building this feature myself
• am interested in collaborating on building this feature
• am willing to help testing this feature before it's released
• am willing to write a test-driven test suite for this feature (before it exists)
• am a Graphile sponsor ❤️
• have an active support or consultancy contract with Graphile

[FelixZY]

Additional use case:

CREATE EXTENSION IF NOT EXISTS "postgis"

requires superuser, which my graphile user does not have. I can work around this by granting the graphile user superuser, but then what's the difference to ROOT_DATABASE_URL?. It would be better if I could define a custom init script to be run by the root user (ROOT_DATABASE_URL).

[benjie]

For the latter question, please see the root option in action configuration, you are effectively asking for root afterReset commands. This will also let you ALTER DATABASE which may address some of your CREATE DATABASE concerns too.

https://github.com/graphile/migrate?tab=readme-ov-file#sql-action-spec

It’s unlikely I will work on this feature this year, so if it’s one you need you will need to be the one implementing it.