Auto generated index/foreign keys are sometimes too long

[epechuzal]

Describe the bug

When specifying related objects with @OneToMany or @manytoone, mikro-orm will automatically create relevant index and foreign key constraints with generated names based on entity names.

In some cases this means that we go past the 64 character limit for index/foreign key identifiers.

To Reproduce

Try an example like this

@Entity({ tableName: 'sponsored_page_templates' })
export class SponsoredPageTemplate {
  @PrimaryKey()
  id!: number;

  @OneToMany({ entity: () => SponsoredPageTemplateCustomAttribute, mappedBy: 'sponsoredPageTemplate' })
  sponsoredPageTemplateCustomAttributes = new Collection<SponsoredPageTemplateCustomAttribute>(this);
}

@Entity({ tableName: 'sponsored_page_template_custom_attributes' })
export class SponsoredPageTemplateCustomAttribute {
  @PrimaryKey()
  id!: number;

  @ManyToOne({ entity: () => SponsoredPageTemplate })
  sponsoredPageTemplate!: SponsoredPageTemplate;
}

const orm = await MikroOrm.init(....)
const generator = orm.getSchemaGenerator();
await generator.dropSchema();
await generator.createSchema();

You will get the error below:

Stack trace

DriverException: alter table `sponsored_page_template_custom_attributes` add index `sponsored_page_template_custom_attributes_sponsored_page_template_id_index`(`sponsored_page_template_id`); - Identifier name 'sponsored_page_template_custom_attributes_sponsored_page_template_id_index' is too long
    at MySqlExceptionConverter.convertException (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/@mikro-orm/core/platforms/ExceptionConverter.js:8:16)
    at MySqlExceptionConverter.convertException (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/@mikro-orm/mysql-base/MySqlExceptionConverter.js:78:22)
    at MySqlDriver.convertException (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:171:54)
    at /Users/eddypechuzal/Workspace/sfp-entities/node_modules/@mikro-orm/core/drivers/DatabaseDriver.js:175:24
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
    at async SchemaGenerator.execute (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:119:13)
    at async SchemaGenerator.createSchema (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/@mikro-orm/knex/schema/SchemaGenerator.js:27:9)
    at async exports.MysqlTestOrm.resetSchema (/Users/eddypechuzal/Workspace/sfp-entities/dist/index.js:1:161400)
    at async exports.MysqlTestOrm.init (/Users/eddypechuzal/Workspace/sfp-entities/dist/index.js:1:161280)
    at async createOrm (/Users/eddypechuzal/Workspace/publisher-managers/inventory-api/dist/setup-test-env.js:13:5)

previous Error: alter table `sponsored_page_template_custom_attributes` add index `sponsored_page_template_custom_attributes_sponsored_page_template_id_index`(`sponsored_page_template_id`); - Identifier name 'sponsored_page_template_custom_attributes_sponsored_page_template_id_index' is too long
    at Packet.asError (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/mysql2/lib/packets/packet.js:712:17)
    at Query.execute (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/mysql2/lib/commands/command.js:28:26)
    at Connection.handlePacket (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/mysql2/lib/connection.js:425:32)
    at PacketParser.onPacket (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/mysql2/lib/connection.js:75:12)
    at PacketParser.executeStart (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/Users/eddypechuzal/Workspace/sfp-entities/node_modules/mysql2/lib/connection.js:82:25)
    at Socket.emit (events.js:311:20)
    at Socket.EventEmitter.emit (domain.js:482:12)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11) {
  code: 'ER_TOO_LONG_IDENT',
  errno: 1059,
  sqlState: '42000',
  sqlMessage: "Identifier name 'sponsored_page_template_custom_attributes_sponsored_page_template_id_index' is too long",
  name: 'DriverException'
}

I am unfortunately unable to rename any of the entities or tables, so I am looking for a way to alter mikro-orm's behavior here.

Attempted fixes

I tried explicitly adding an index to the child entity like this

  @Index({ name: 'short_name' })
  @ManyToOne({ entity: () => SponsoredPageTemplate })
  sponsoredPageTemplate!: SponsoredPageTemplate;

However then I hit the same issue for the automatically generated foreign key constraint:

Error: alter table `sponsored_page_template_custom_attributes` add constraint `sponsored_page_template_custom_attributes_sponsored_page_template_id_foreign` foreign key (`sponsored_page_template_id`) references `sponsored_page_templates` (`id`) on update cascade; - Identifier name 'sponsored_page_template_custom_attributes_sponsored_page_template_id_foreign' is too long

Expected behavior
Mikro-orm should know when an identifier will violate the 64 character limit and come up with an alternative name, maybe something like this
<first few chars of entity A>_<first few chars of entity B>_<some random string to avoid duplication>

Versions

Dependency	Version
node	12.x
typescript	4.x
mikro-orm	4.x

[B4nan]

You can specify the index name manually:

@ManyToOne({ entity: () => SponsoredPageTemplate, index: 'custom_idx_name' })

But yes, there is currently no way to adjust the FK constraint name.

[epechuzal]

Is it possible to update mikro-orm such that it generates identifiers that are always <64 chars?

[B4nan]

Sure we can make it work that way (i guess only in mysql, or is this a common limitation?).

[epechuzal]

I got the Mysql limitations from their v5.7 docs, but I am not familiar with any other DB drivers so I am not sure how they handle this

[epechuzal]

@B4nan I tried a quick fix locally which seems to work and put it in a PR - let me know what you think

[amaranth]

This is a problem in postgres too, https://www.postgresql.org/docs/12/limits.html says the identifier limit is 63 bytes. Should I file a new issue?

On a related note, it seems sqlite has no identifier limit. I have no idea how things work with mongodb but it seems to have a limit of 120 bytes maybe?

[B4nan]

This is already fixed for postgres, see #1915 (probably in v5 only - no plans to backport anything schema related to v4).