Resetting autoincrement and truncate with cascade

[richardsimko]

Does knex provide any abstracted way of resetting autoincrement for a table? From what I can tell it can only be done using knex.raw() which makes it dependent on the dialect used.

A .resetIncrements() call would be nice so that it can be chained with .del() as knex('table_name').del().resetIncrements() which effectively resets a table to the state it was in when migrations were run.

[absolux]

Simply truncate the table

[richardsimko]

Cheers!

[richardsimko]

Sadly that doesn't work for postgres with the error:

cannot truncate a table referenced in a foreign key constraint

Postgres apparently requires you to truncate all referenced tables in the same query:

HINT:  Truncate table "table2" at the same time, or use TRUNCATE ... CASCADE.

Perhaps CASCADE should be added to truncate() if Postgres is used?

[absolux]

I think that even MySQL will raise that error, because of foreign keys constraints.

Perhaps CASCADE should be added to truncate() if Postgres is used?

CASCADE is not the only option for ON DELETE, there are also NO ACTION, SET NULL and by default RESTRICT. You should manually set the ON DELETE option for your foreign keys, knex will not do it for you.

Here is an example using knex migrations

// alter the table containing the foreign key
table.foreign('fk').references('pk').inTable('table_name').onDelete('CASCADE')

[richardsimko]

That didn't help I'm afraid.

To reiterate, the problem is not with deleting from a table which has a FK, the problem is deleting from a table which is referenced by a foreign key.

So in my example table2 has a reference to table1.

knex.schema.createTable('table1', function(table) {
  table.increments();
}
knex.schema.createTable('table2', function(table) {
  table.increments();
  //onDelete makes no difference
  table.integer('fk').references('id').inTable('table1').onDelete('CASCADE');
}
knex('table2').truncate() // Throws error
knex('table1').truncate() // Works fine as long as table2 is empty

[wubzz]

Indeed it would make sense to use CASCADE. Are there any cases where one would truncate without wanting CASCADE? I can't think of any.

If there are none, then here is the place to append it. If there are any, I'd rather see a boolean flag to the function itself indicating to run with CASCADE.

I definitely don't think a query building library with a truncate function should force the users to call knex.raw() in order to run with cascade, that's just silly. PR label added.

[tgriesser]

Are there any cases where one would truncate without wanting CASCADE? I can't think of any.

Yeah, you'd want it because a potentially destructive operation shouldn't be the default behavior.

If there are any, I'd rather see a boolean flag to the function itself indicating to run with CASCADE

That sounds like it'd be fine.

[juliocanares]

I solved using knex.raw

knex.raw('ALTER TABLE ' + 'your_table' + ' AUTO_INCREMENT = 1');

[npoirey]

I had the same problem for my tests. I solved it by using knex.raw also, not in the seed files but in my helper functions for unit testing :

const bookshelf = require('../models/database')
const config = require('../knexfile').test

function buildTestDb() {
  return bookshelf.knex.migrate.rollback(config)
    .then(() => bookshelf.knex.migrate.latest(config))
}

function seedTestDb() {
  let resetSequenceCommand = ''
  if (config.client === 'sqlite3') {
    resetSequenceCommand = 'UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME=\'users\';'
  } else if (config.client === 'pg') {
    resetSequenceCommand = 'ALTER SEQUENCE users_id_seq RESTART WITH 1'
  }
  return bookshelf.knex.schema.raw(resetSequenceCommand)
    .then(() => bookshelf.knex.seed.run(config))
}

After all, I only need it for tests in order to have manageable ids to assert against.
Having a way to truncate with a sequence reset for tables referenced by a foreign key would be better, I struggled for a while before thinking of doing it this way.

[malimccalla]

I'm having an issue with this too. I'm using postgres for my database and to seed it I use:

return knex('users').truncate()
    .then(() => {
      return knex('users').insert({
        display_name: 'John Doe',
        username: 'username',
        email: 'example@example.com',
        active: true,
        created_at: faker.date.past(),
        last_login: faker.date.past(),
        password_hash: bcrypt.hashSync('password')
      });
    })
    .then(() => {
      // so on

however since adding another table that references the user table I've been getting the error

cannot truncate a table referenced in a foreign key constraint

The relevant part of my new table migration looks like this:

exports.up = (knex) => {
  return knex.schema.createTable('playlists', (t) => {
    t.increments('id').notNullable().primary();
    t.integer('user_id').unsigned();
    t.foreign('user_id').references('id').inTable('users').onDelete('CASCADE');
  });
};

So I changed to using .del() instead of .truncate() which solved my error. The problem now is that my primary keys do not reset that cause other things to break.

I reckon a resetIncrements() would be a great addition.

[ais-one]

I did it in the seed with this...

exports.seed = function(knex, Promise) {
	return knex('mytable').del().then(function () {
		return knex.raw('ALTER TABLE ' + 'mytable' + ' AUTO_INCREMENT = 1').then(function() {
			...do your insert here...
		})
	})
})

if you are on node 7 and above, use async and await

exports.seed = async function(knex, Promise) {
	await knex('mytable').del()
	await knex.raw('ALTER TABLE ' + 'mytable' + ' AUTO_INCREMENT = 1')
	...do your insert here...
})

[ultrox]

thanks @ais-one, you lead me to good path. I would just want to add, your sintax is database specific, and to another guy reading this, @ais-one solution might not work for you, dependint what db you are using.

To reset id in Postgres you need to specify <YOUTtableNAME_id_seq>
It looks like this, usually do it after I delete all the tables.

await knex.raw('ALTER SEQUENCE requests_id_seq RESTART WITH 1')

await knex.raw('ALTER SEQUENCE profiles_id_seq RESTART WITH 1')

await knex.raw('ALTER SEQUENCE accounts_id_seq RESTART WITH 1')

await knex.raw('ALTER SEQUENCE cookies_id_seq RESTART WITH 1')

await knex.raw('ALTER SEQUENCE emails_id_seq RESTART WITH 1')

[ais-one]

You are right, my solution in the comment is for mysql, others does not work as the reset is db specific

[psaung]

If you are using mysql, you can specify mysql not to check foreign key constraint before truncating.

const truncateKeyConstraint = (table, cb) => knex
  .raw('SET foreign_key_checks = 0')
  .raw('TRUNCATE table '+table)
  .raw('SET foreign_key_checks = 1')
  .then(() => cb())
  .catch(err => cb(err))

[bennycode]

I am using a SQLite 3 database and it seems like truncate() also resets the ID count:

const config = require(`${process.cwd()}/knexfile`)[process.env.NODE_ENV]
const knex = require('knex')(config)

const {MyPOJO} = require('../src/MyPOJO')
const {Model} = require('objection')

beforeAll(async done => {
  await knex.migrate.latest()
  await knex(MyPOJO.tableName).truncate()
  Model.knex(knex)
  done()
})

[elhigu]

@bennyn indeed it does. Currently this issue is about adding option for truncate with cascade.

[ianwalter]

For other Postgres users looking for a solution to this, I've found that this works well:

await knex.raw('TRUNCATE TABLE my_table RESTART IDENTITY CASCADE')

[kevinlaw91]

I found a knex helper lib that support PostgreSQL, MySQL and SQLite3: knex-cleaner

[advename]

Any update on this?

[kibertoad]

@advename No progress. PRs most welcome!

[jasonahern]

If using MSSQL (Microsoft SQL Server) the following works -

[julianwagle]

await knex.raw(`TRUNCATE TABLE ${tableName} RESTART IDENTITY CASCADE`);