Yeah I have the same awkward process for provisioning databases. Another difficulty is configuration of passwords/auth which varies by server typically. Generally speaking I don’t want my migration process to have the ability to assume the super user role.
The variance in the script prevents using the typical migration frameworks built into the app, and it’s the wrong responsibility - the app migrations have a contract with whatever database is being connected to about which users and schemas exist.
I’m still not sure a declarative mechanism is desirable though because the database “setup” is still subject to change after initial provisioning: new schemas/roles/etc will be required and we need an imperative API to execute those changes transactionally with precision.
The variance in the script prevents using the typical migration frameworks built into the app, and it’s the wrong responsibility - the app migrations have a contract with whatever database is being connected to about which users and schemas exist.
I’m still not sure a declarative mechanism is desirable though because the database “setup” is still subject to change after initial provisioning: new schemas/roles/etc will be required and we need an imperative API to execute those changes transactionally with precision.