Want to enhance the feature available for Postgres (customSchemaName for migration).

[arbazkhaan]

Add on to #265 #635

What you are doing?

I'm trying to run migration scripts with less privilege user which doesn't have permission to create or drop schema

        if (helpers.version.getDialectName() === 'pg') {
          const customSchemaName = helpers.umzug.getSchema('migration');
          //below condition is run for every customSchemaName when it's not 'public'
          if (customSchemaName && customSchemaName !== 'public' ) {
            return sequelize.createSchema(customSchemaName);
          }
        }

What do you expect to happen?

I wanted to avoid running "CREATE SCHEMA IF NOT EXISTS customSchemaName" query if provided customSchemaName exists already. This is because the user used to run migration may not have the permission to create any schema under the database blabla (like in my case)

What is actually happening?

ERROR: permission denied for database blabla

My Suggestion

I suggest below change for which i can create a new PR.

Dialect: postgres Sequelize CLI version: 5.5.1 Sequelize version: 5.21.7

[kirill578]

keywords: ERROR: permission denied for database

[mike-usa]

I replaced lines 66-77 in node_modules/sequelize-cli/lib/core/migrator.js (Sequelize 6) with the following code and it seems to work:

  return sequelize.authenticate().then(async () => {
    // Check if this is a PostgreSQL run and if there is a custom schema specified, and if there is, check if it's
    // been created. If not, attempt to create it.
    if (_index.default.version.getDialectName() === 'pg') {
      const customSchemaName = _index.default.umzug.getSchema('migration');
      const schemas = await sequelize.showAllSchemas()

      if (customSchemaName && customSchemaName !== 'public' && !schemas.includes(customSchemaName)) {
        return sequelize.createSchema(customSchemaName);
      }
    }
  }).then(() => migrator).catch(e => _index.default.view.error(e));

[arbazkhaan]

@mike-usa I can't see the changes in src/lib/core/migrator.js master branch.

https://github.com/sequelize/cli/issues/894#issuecomment-1114131590