@Unique and @Column decorators unique constraint name on postgresql/sqlite

[ngruychev]

Issue

Using the @Unique constraint with postgresql/sqlite doesn't set the desired constraint name.

Versions

• sequelize: 6.35.2
• sequelize-typescript: 2.1.6
• typescript: 5.3.3

Issue type

• bug report
• feature request

Actual behavior

Indexes follow a default naming convention - e.g. TableName_fieldName_key (TableName_fieldName1_fieldName2_key in case of composite indexes) in the case of PostgreSQL

Expected behavior

Model fields annotated with @Unique("IndexName"), @Unique({ name: "IndexName", msg: "msg" }), and @Column({ type: DataType.WHATEVER, unique: "IndexName" }) should result in unique indexes with the appropriate names in PostgreSQL and sqlite.

Steps to reproduce

Run the code below with npx ts-node index.ts. PostgreSQL and sqlite (didn't test other engines) follow Actual Behavior, while MySQL follows Expected behavior for all 4 columns, confirming what the behavior should be.

Related code

Code:

import { Model, Table, Column, DataType, Unique, PrimaryKey, AutoIncrement, Sequelize, Index } from 'sequelize-typescript';

@Table({ tableName: 'Item' })
class Item extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.INTEGER)
  id: number = 0;

  @Unique("uniqueThingIndex")
  @Column(DataType.STRING)
  thing: string = '';

  @Column({ type: DataType.STRING, unique: 'uniqueThingIndexTwo' })
  thingTwo: string = '';

  @Column(DataType.STRING)
  @Index({ name: 'indexThingThree', unique: true })
  thingThree: string = '';
}

const dialects = [
  { dialect: 'sqlite', constraintsQuery: "PRAGMA index_list('Item')", options: { storage: ':memory:' } },
  {
    dialect: 'postgres',
    constraintsQuery: "SELECT indexname FROM pg_indexes WHERE tablename = 'Item'",
    options: {
      host: 'localhost',
      port: 5432,
      username: 'test',
      password: 'test',
      database: 'test',
    },
  },
  {
    dialect: 'mysql',
    constraintsQuery: "SELECT INDEX_NAME FROM information_schema.statistics WHERE table_name = 'Item'",
    options: {
      host: 'localhost',
      port: 3306,
      username: 'test',
      password: 'test',
      database: 'test',
    },
  },
] as const;


(async () => {
  for (const { dialect, constraintsQuery, options } of dialects) {
    console.log('---', dialect, '---');
    const db = new Sequelize({
      dialect,
      storage: 'database.sqlite',
      models: [Item],
      ...options,
    });
    await db.sync();

    // get constraints via sql query
    const result = await db.query(constraintsQuery);
    console.log(result[0]);
    await db.close();
  }
})();

Output:

--- sqlite ---
Executing (default): SELECT name FROM sqlite_master WHERE type='table' AND name='Item';
Executing (default): CREATE TABLE IF NOT EXISTS `Item` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `thing` VARCHAR(255), `thingTwo` VARCHAR(255), `thingThree` VARCHAR(255), `thingFour` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, UNIQUE (`thing`), UNIQUE (`thingTwo`), UNIQUE (`thingThree`));
Executing (default): PRAGMA INDEX_LIST(`Item`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_Item_1`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_Item_2`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_Item_3`)
Executing (default): CREATE UNIQUE INDEX `indexThingFour` ON `Item` (`thingFour`)
Executing (default): PRAGMA index_list('Item')
[
  {
    seq: 0,
    name: 'indexThingFour',
    unique: 1,
    origin: 'c',
    partial: 0
  },
  {
    seq: 1,
    name: 'sqlite_autoindex_Item_3',
    unique: 1,
    origin: 'u',
    partial: 0
  },
  {
    seq: 2,
    name: 'sqlite_autoindex_Item_2',
    unique: 1,
    origin: 'u',
    partial: 0
  },
  {
    seq: 3,
    name: 'sqlite_autoindex_Item_1',
    unique: 1,
    origin: 'u',
    partial: 0
  }
]
--- postgres ---
Executing (default): SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'Item'
Executing (default): CREATE TABLE IF NOT EXISTS "Item" ("id"  SERIAL , "thing" VARCHAR(255), "thingTwo" VARCHAR(255), "thingThree" VARCHAR(255), "thingFour" VARCHAR(255), "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, UNIQUE ("thing"), UNIQUE ("thingTwo"), UNIQUE ("thingThree"), PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Item' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE UNIQUE INDEX "indexThingFour" ON "Item" ("thingFour")
Executing (default): SELECT indexname FROM pg_indexes WHERE tablename = 'Item'
[
  { indexname: 'Item_pkey' },
  { indexname: 'Item_thing_key' },
  { indexname: 'Item_thingTwo_key' },
  { indexname: 'Item_thingThree_key' },
  { indexname: 'indexThingFour' }
]
--- mysql ---
Executing (default): SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'Item' AND TABLE_SCHEMA = 'test'
Executing (default): CREATE TABLE IF NOT EXISTS `Item` (`id` INTEGER auto_increment , `thing` VARCHAR(255), `thingTwo` VARCHAR(255), `thingThree` VARCHAR(255), `thingFour` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, UNIQUE `uniqueThingIndex` (`thing`), UNIQUE `uniqueThingIndexTwo` (`thingTwo`), UNIQUE `uniqueThingIndexThree` (`thingThree`), PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `Item` FROM `test`
Executing (default): ALTER TABLE `Item` ADD UNIQUE INDEX `indexThingFour` (`thingFour`)
Executing (default): SELECT INDEX_NAME FROM information_schema.statistics WHERE table_name = 'Item'
[
  { INDEX_NAME: 'indexThingFour' },
  { INDEX_NAME: 'PRIMARY' },
  { INDEX_NAME: 'uniqueThingIndex' },
  { INDEX_NAME: 'uniqueThingIndexThree' },
  { INDEX_NAME: 'uniqueThingIndexTwo' }
]

tsconfig:

{
  "compilerOptions": {
    "experimentalDecorators": true,
    "lib": ["esnext", "dom"],
    "module": "nodenext",
    "moduleDetection": "force",
    "strict": true,
    "target": "es2019"
  },
  "ts-node": {
    "experimentalResolver": true
  },
}