Knex.js Database Cleaner

Common errors

• TypeError: knexCleaner is not a function

  cause Attempting to call `knexCleaner()` directly instead of `knexCleaner.clean()` or an incorrect import for ESM.
  fix Ensure you are calling the `clean` method: `knexCleaner.clean(knex, options)`. If using ESM, `import knexCleaner from 'knex-cleaner';` then `knexCleaner.clean(...)`.

• Error: insert into "TableName" ("id", ...) values (...) - SQLITE_CONSTRAINT: UNIQUE constraint failed: TableName.id

  cause When using SQLite or similar, if `mode: 'delete'` is used, auto-incrementing IDs are not reset, leading to primary key collisions if new rows are inserted with the same ID range.
  fix For SQLite, consider manually resetting the sequence or using a different cleaning strategy. For PostgreSQL, ensure `restartIdentity: true` is set in the options when `mode: 'delete'` is used. For databases where `TRUNCATE` resets IDs, use `mode: 'truncate'` if feasible.

• SQLITE_CONSTRAINT: FOREIGN KEY constraint failed

  cause Attempting to `TRUNCATE` tables that have foreign key constraints referencing other tables, without the database being configured to cascade truncation (which most do not by default).
  fix Switch the `mode` option to `'delete'`. While slower, `DELETE` statements typically handle foreign key constraints more gracefully. Alternatively, temporarily disable foreign key checks if your database supports it and you understand the implications (not recommended for production). Or, ensure your `ignoreTables` list includes tables with critical parent data.

Warnings

• gotcha When using PostgreSQL, `restartIdentity: true` should be enabled in the options to reset auto-incrementing primary key sequences. Failing to do so can lead to primary key constraint violations on subsequent inserts if the IDs are not manually managed.
  Fix: Pass `{ restartIdentity: true }` in the options object to `knexCleaner.clean(knex, options)`.
• gotcha The `mode` option ('truncate' vs 'delete') behaves differently. 'truncate' is generally faster and resets auto-incrementing IDs in most databases (except SQLite), but may not work if tables have foreign key constraints referencing other tables without `CASCADE` actions. 'delete' is slower but more robust against foreign key constraints.
  Fix: Choose the `mode` based on your database schema and performance needs. If you encounter foreign key errors with `truncate`, switch to `mode: 'delete'`. Be aware that `delete` will not reset identity counters on its own (use `restartIdentity: true` for Postgres).
• deprecated This library has not seen significant feature development or major version updates since 2020. While it remains functional for its core purpose, compatibility with very recent Knex.js versions or advanced database features might not be fully tested or supported.
  Fix: Ensure you are using `knex-cleaner` with a Knex.js version it was last actively maintained with, or thoroughly test its behavior with newer Knex versions. Consider alternative libraries if encountering compatibility issues.

Install

• npm install knex-cleaner npm
• yarn add knex-cleaner yarn
• pnpm add knex-cleaner pnpm

Imports

• clean
  wrong:

  import { clean } from 'knex-cleaner';

  correct:

  import knexCleaner from 'knex-cleaner';
  knexCleaner.clean(knex, options)

  knex-cleaner exports a default function which should be imported as a named variable (e.g., `knexCleaner`) then its `clean` method is called. CommonJS users should use `require('knex-cleaner')`.
• knexCleaner
  wrong:

  const { clean } = require('knex-cleaner');

  correct:

  const knexCleaner = require('knex-cleaner');

  For CommonJS environments, the package exports a default function. Assign it to a variable, then call its `clean` method directly (e.g., `knexCleaner.clean`).
• Knex.js instance
  wrong:

  knexCleaner.clean(config);

  correct:

  import knexCleaner from 'knex-cleaner';
  const knex = require('knex')(config);
  knexCleaner.clean(knex);

  The `clean` method expects an initialized Knex.js database instance, not raw connection configuration objects. Ensure Knex is properly set up and connected before passing its instance.

Quickstart

This quickstart demonstrates how to initialize Knex.js with an SQLite database, populate it with data, and then use `knex-cleaner` to clear all user-defined tables, ignoring Knex's internal migration tables. It shows both 'delete' mode and verification of empty tables.

import knexCleaner from 'knex-cleaner';
import knex from 'knex'; // Assume knex is installed

const dbConfig = {
  client: 'sqlite3',
  connection: {
    filename: './mydb_test.sqlite'
  },
  useNullAsDefault: true
};

const myKnexInstance = knex(dbConfig);

async function setupDatabase() {
  // For SQLite, create a dummy table to ensure the database file exists
  await myKnexInstance.schema.hasTable('users').then(exists => {
    if (!exists) {
      return myKnexInstance.schema.createTable('users', table => {
        table.increments('id');
        table.string('name');
        table.string('email');
      });
    }
  });
  await myKnexInstance.schema.hasTable('products').then(exists => {
    if (!exists) {
      return myKnexInstance.schema.createTable('products', table => {
        table.increments('id');
        table.string('name');
        table.decimal('price', 8, 2);
      });
    }
  });

  // Insert some data
  await myKnexInstance('users').insert([{ name: 'Alice', email: 'alice@example.com' }]);
  await myKnexInstance('products').insert([{ name: 'Widget', price: 19.99 }]);
  console.log('Database initialized and populated.');
}

async function cleanAndVerify() {
  await setupDatabase();

  console.log('Tables before clean:', await myKnexInstance.raw("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';"));

  const options = {
    mode: 'delete', // Or 'truncate' for faster clearing on supported DBs
    restartIdentity: true, // Useful for PostgreSQL
    ignoreTables: ['knex_migrations', 'knex_migrations_lock'] // Ignore Knex migration tables
  };

  try {
    await knexCleaner.clean(myKnexInstance, options);
    console.log('Database tables successfully cleaned!');
    console.log('Users table count after clean:', (await myKnexInstance('users').count('id as count'))[0].count);
    console.log('Products table count after clean:', (await myKnexInstance('products').count('id as count'))[0].count);
  } catch (error) {
    console.error('Error cleaning database:', error);
  } finally {
    await myKnexInstance.destroy();
  }
}

cleanAndVerify();