databaseerror 5bsequelizedatabaseerror 5d 3a type 22enum 22 already exists sync 28force 3afalse 29

1//Add below script before require('sequelize')
2//e.g.
3
4require('./pgEnum-fix')
5const sequelize = require('sequelize')
6
7
8// pgEnum-fix.js
9/* eslint-disable func-names */
10/* eslint-disable no-restricted-syntax */
11/* eslint-disable guard-for-in */
12const PostgresQueryGenerator = require('sequelize/lib/dialects/postgres/query-generator');
13
14PostgresQueryGenerator.prototype.pgEnum = function (tableName, attr, dataType, options) {
15  const enumName = this.pgEnumName(tableName, attr, options);
16  let values;
17
18  if (dataType.values) {
19    values = `ENUM(${dataType.values.map((value) => this.escape(value)).join(', ')})`;
20  } else {
21    // eslint-disable-next-line prefer-destructuring
22    values = dataType.toString().match(/^ENUM\(.+\)/)[0];
23  }
24
25  if (_.isObject(tableName)) {
26    normalizedTableName = `${tableName.schema}.${tableName.tableName}`;
27  }
28
29  let sql = `DO $$ BEGIN IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'enum_${normalizedTableName}_${attr}') THEN CREATE TYPE ${enumName} AS ${values}; END IF; END$$;`;
30  
31  if (!!options && options.force === true) {
32    sql = this.pgEnumDrop(tableName, attr) + sql;
33  }
34  return sql;
35};
36
37PostgresQueryGenerator.prototype.changeColumnQuery = function (tableName, attributes) {
38  const query = (subQuery) => `ALTER TABLE ${this.quoteTable(tableName)} ALTER COLUMN ${subQuery};`;
39  const sql = [];
40  for (const attributeName in attributes) {
41    let definition = this.dataTypeMapping(tableName, attributeName, attributes[attributeName]);
42    let attrSql = '';
43
44    if (definition.includes('NOT NULL')) {
45      attrSql += query(`${this.quoteIdentifier(attributeName)} SET NOT NULL`);
46
47      definition = definition.replace('NOT NULL', '').trim();
48    } else if (!definition.includes('REFERENCES')) {
49      attrSql += query(`${this.quoteIdentifier(attributeName)} DROP NOT NULL`);
50    }
51
52    if (definition.includes('DEFAULT')) {
53      attrSql += query(`${this.quoteIdentifier(attributeName)} SET DEFAULT ${definition.match(/DEFAULT ([^;]+)/)[1]}`);
54
55      definition = definition.replace(/(DEFAULT[^;]+)/, '').trim();
56    } else if (!definition.includes('REFERENCES')) {
57      attrSql += query(`${this.quoteIdentifier(attributeName)} DROP DEFAULT`);
58    }
59
60    if (attributes[attributeName].startsWith('ENUM(')) {
61      attrSql += this.pgEnum(tableName, attributeName, attributes[attributeName]);
62      definition = definition.replace(/^ENUM\(.+\)/, this.pgEnumName(tableName, attributeName, { schema: false }));
63      // definition += ` USING (${this.quoteIdentifier(attributeName)}::${this.pgEnumName(tableName, attributeName)})`;
64    }
65
66    if (definition.match(/UNIQUE;*$/)) {
67      definition = definition.replace(/UNIQUE;*$/, '');
68      attrSql += query(`ADD UNIQUE (${this.quoteIdentifier(attributeName)})`).replace('ALTER COLUMN', '');
69    }
70
71    if (definition.includes('REFERENCES')) {
72      definition = definition.replace(/.+?(?=REFERENCES)/, '');
73      attrSql += query(`ADD FOREIGN KEY (${this.quoteIdentifier(attributeName)}) ${definition}`).replace('ALTER COLUMN', '');
74    } else {
75      attrSql += query(`${this.quoteIdentifier(attributeName)} TYPE ${definition}`);
76    }
77
78    sql.push(attrSql);
79  }
80
81  return sql.join('');
82};
83
84module.exports = () => PostgresQueryGenerator;
85
86

1Sequelize.sync({ force: true })
2  .then(() => {
3    console.log(`Database & tables generated!`)
4});
5