Knex truncate table with foreign key constraints

Question

Is it possible to force truncate a table with foreign key constraints so that all rows in other tables effected are also removed?

I cannot see in documentation for an option to pass to knex('tableName').truncate() method.

Answer 1

I haven't found a built in way to do it, so I just drop into raw mode:

 knex.raw('TRUNCATE TABLE users, products CASCADE')

You can also set this up to happen automatically in your migrations:

exports.up = function(knex) {
  return knex.schema.createTable('users_products', (t) => {
      t.uuid('id').primary().defaultTo(knex.raw('uuid_generate_v4()'));
      t.uuid('user_id').notNullable().references('id').inTable('users').onDelete('CASCADE');
      t.uuid('product_id').notNullable().references('id').inTable('products').onDelete('CASCADE');
  });
};

Answer 2

I couldn't get this to work with Knex for cleaning up after Jest integration tests, so I used Node PostGres (i.e. pg) directly, like this:

  const { Pool } = require('pg')
  const connectionString = 'postgresql://dbuser:secretpassword@database.server.com:3211/mydb';
  const pool = new Pool({
    connectionString,
  })
  pool.query('TRUNCATE myTable RESTART IDENTITY CASCADE;', (err, res) => {
    console.log(err, res)
    pool.end()
  })