CODEWITHSUNDEEP
node.jspostgresqlsequelize.jsauto-increment
Grelnog
Grelnog

Reputation: 111

Alter sequence in a postgreSQL DB using Sequelize

I have a postgresql database that I need to first seed and use predetermine id, then when I create new items to the DB I want the id to auto increment. This was trivial when I was using a SQLite database. When I tried the same code with an PostgreSQL DB I find that the sequelize created IDs have started from zero and have ignored the seedcreated indices, resulting in a unique constraint error.

I figure that this because postgresql uses a separate sequence to keep track of the increment. My approch from here was to use a raw query from sequelize to alter the sequence. But when I try to run an alter sequence query sequelize can't find sequence. I run:

db.query("ALTER SEQUENCE COMPOUND_CPCD_seq RESTART WITH 100;");

But a error is generated and in the error message it states "Unhandled rejection SequelizeDatabaseError: relation "compound_cpcd_seq" does not exist". Is this a problem with upper and lower case letters since COMPOUND_CPCD_seq is not equal to compound_cpcd.seq? And if that is the case how do you work around that (since I have no control over how COMPOUND_CPCD_seq is created)?

What have I missed?

The model:

const COMPOUND= db.define('COMPOUND', {
  CPCD: {
  type: Sequelize.INTEGER,
  primaryKey: true,
  autoIncrement: true,
  unique: true,
 },
  Name: {
  type: Sequelize.STRING,
 }
})

Upvotes: 4

Views: 4502

Answers (3)

xab
xab

Reputation: 1236

One way to change the sequence current value is by creating a migration. This way the alteration is executed only once.

const tableName = 'YourTable';
const sequenceColumn = 'id';

module.exports = {
    up: (queryInterface) => queryInterface.sequelize.transaction(async (transaction) => {
        // Get current highest value from the table
        const [[{ max }]] = await queryInterface.sequelize.query(`SELECT MAX("${sequenceColumn}") AS max FROM public."${tableName}";`, { transaction });
        // Set the autoincrement current value to highest value + 1
        await queryInterface.sequelize.query(`ALTER SEQUENCE public."${tableName}_${sequenceColumn}_seq" RESTART WITH ${max + 1};`, { transaction });
    }),
    down: () => Promise.resolve(),
};

Upvotes: 1

Grelnog
Grelnog

Reputation: 111

I was stupid; the sequence need to be double quoted like:

db.query("ALTER SEQUENCE "COMPOUND_CPCD_seq" RESTART WITH 100;");

Upvotes: 1

Lohit Gupta
Lohit Gupta

Reputation: 1081

First of all you have to check whether the sequence is created in the correct schema of the database where your table is.

If it is correctly created, and the issue is really due to Upper and Lowercase letters, you can put the sequence name in quotes '' to remedy that.

Upvotes: 1

Related Questions