Unique property causes issue when generating a migration on a schema scoped table

[mmcxii]

Describe the bug
When generating a migration for a table that is a part of a specific schema (a la @Entity({ tableName: "schemaName.tableName" })), the migrator will generate an invalid SQL statement.

This is where I decorate a column:

@Property({
  unique: true,
})
columnName: string;

I also tried it like this and it produced the same result:

@Property()
@Unique()
columnName: string;

This is what is generated:

alter table "schemaName"."tableName"
add constraint "schemaName"."tableName_columnName_unique" unique ("columnName");

However when I remove the "schemaName". prefix from the constraint like this, the migration successfully ran as expected.

alter table "schemaName"."tableName"
add constraint "tableName_columnName_unique" unique ("columnName");

Stack trace

SyntaxErrorException: alter table "schemaName"."tableName" add constraint "schemaName"."tableName_columnName_unique" unique ("columnName"); - syntax error at or near "."
    at PostgreSqlExceptionConverter.convertException (/.../node_modules/@mikro-orm/postgresql/PostgreSqlExceptionConverter.js:30:24)
    at PostgreSqlDriver.convertException (/.../node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:171:54)
    at /.../node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:175:24
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
    at Function.runSerial (/.../node_modules/@mikro-orm/core/utils/Utils.js:458:22)
    at /.../node_modules/@mikro-orm/migrations/MigrationRunner.js:23:17

previous error: alter table "schemaName"."tableName" add constraint "schemaName"."tableName_columnName_unique" unique ("columnName"); - syntax error at or near "."
    at Parser.parseErrorMessage (/.../node_modules/pg-protocol/src/parser.ts:357:11)
    at Parser.handlePacket (.../node_modules/pg-protocol/src/parser.ts:186:21)
    at Parser.parse (/.../node_modules/pg-protocol/src/parser.ts:101:30)
    at Socket.<anonymous> (/.../node_modules/pg-protocol/src/

To Reproduce
Steps to reproduce the behavior:

1. Create a table with a schema scoped table name and a unique constraint on a column.
2. Create a migration to apply the table to the database.
3. Inspect the resulting SQL command.

Expected behavior
The migrator was expected to produce a valid SQL statement.

** Additional Context **
Using with Nest.js, however I don't think that is impacting this behavior.

Versions

Dependency	Version
node	14.2.0
typescript	4.1.2
mikro-orm	4.3.4
@mikro-orm/postgresql	4.3.4
@mikro-orm/nestjs	4.2.0

[mmcxii]

A follow up on this, I was tinkering around with the migration generated some more and noticed that if I edit the constraint from ... add constraint "schemaName"."tableName_columnName_unique" unique ("columnName"); to add constraint "schemaName.tableName_columnName_unique" unique ("columnName"); (note that I combine the string wrapping the schema name and the string wrapping the constraint name) then it fixes the issue and the migration will run as expected.

[B4nan]

I don't think we need to prefix constraint names, they will exist inside the schema only. The fix should be just about not passing the schema name inside. Will need to check, I believe we are not explicitly setting the name anywhere and its knex doing it, but it is definitely possible to set it explicitly.

[B4nan]

Wait a minute, I got confused, thought its a FK constraint. You can set the unique constraint name manually already:

@Property({
  unique: 'column_name_uniq',
})
columnName: string;

or

@Property()
@Unique({ name: 'column_name_uniq' })
columnName: string;

[mmcxii]

Looks like explicitly providing the unique constraint by name works!

This is the resulting migration:

@Entity({
  tableName: "schemaName.tableName",
})
export class TableName {
  // ... other columns

  @Property({
    unique: "column_name_unique",
  })
  columnName: string;
}

alter table "schemaName"."tableName"
add constraint "column_name_unique" unique ("column_name");

[antl3x]

@B4nan this does not solve the error scenario for primary key constraints. It keeps adding the schema.

I'm on version 4.3.5-dev.28

Example:

    this.addSql('alter table "IAM"."Account" add constraint "IAM"."Account_pkey" primary key ("id");');

[antl3x]

@B4nan updates on this?

[B4nan]

currently in the middle of full rewrite for schema diffing, targetting v5 as it will probably require some breaking changes. so you will need to wait a bit more, if you will see the same with v5, open new issue with full reproduction.

edit: but open for PR if its not a big change