Alter sequence in a postgreSQL DB using Sequelize

Question

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,
 }
})

Lohit Gupta · Accepted Answer · 2017-08-10 13:04:36Z

1

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.

answered Aug 10, 2017 at 13:04

Lohit Gupta

1,0816 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grelnog · Accepted Answer · 2017-08-10 13:16:55Z

1

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

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

answered Aug 10, 2017 at 13:16

Grelnog

1113 silver badges11 bronze badges

Comments

xab · Accepted Answer · 2020-03-07 09:50:02Z

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(),
};

Collectives™ on Stack Overflow

Alter sequence in a postgreSQL DB using Sequelize

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related