knex-cleaner

library1.3.1jsnpmunverified

knex-cleaner is a helper library designed to programmatically clear database tables for Knex.js-based applications, primarily used in testing environments. It provides functionalities to truncate or delete all tables (or a specified subset) within a given Knex database instance. It supports PostgreSQL, MySQL, and SQLite3 databases. The current stable version is 1.3.1, with its last release in 2020, primarily focusing on dependency updates and minor feature enhancements like handling schemas other than 'public' for PostgreSQL. Its key differentiation lies in its direct integration with Knex instances, offering granular control over the cleaning process, including the ability to ignore specific tables and reset identity counters for PostgreSQL. This makes it a suitable tool for ensuring a clean and consistent database state before or after running integration tests, abstracting away manual SQL commands for table clearing. While it can be used with Bookshelf.js, it operates directly on the underlying Knex instance.

database testing

npm install knex-cleaner

github ↗package ↗

INSTALL

IMPORT

SIG · KNEX-CLEANER

knex-cleaner

databasejavascriptv1.3.1

Install

—

Import

—

Disk

—

Pass rate

0/ 6

Env Coverage0 / 6

glibc

18–22

musl

18–22

Install & Compatibility

Where this runs

tested against v? · npm install

Install × environment matrix

Each cell = how many times install + import succeeded across repeated harness runs. Partial = flaky.
glibc = Debian/Ubuntu slim · musl = Alpine Linux

musl

node 18–226 runs

build_error

glibc

node 18–226 runs

build_error

Code

Verified usage

Verified import paths — ran on the pinned version, not inferred.

clean

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

✗ import { clean } from 'knex-cleaner';

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

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

✗ const { clean } = 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

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

✗ knexCleaner.clean(config);

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.

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

Debug

Known issues

gotchaWhen 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)`.

affects: >=1.0.0

gotchaThe `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).

affects: >=1.0.0

deprecatedThis 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.

affects: <1.3.1

Errors

Common errors & fixes

TypeError: knexCleaner is not a function

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

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

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.

Upgrade

Version history

1.3.1latest on npm

Audit

Dependencies

knexrequiredProvides the database connection and query builder instance that knex-cleaner operates on. This is a peer dependency or expected runtime dependency.

Agent activity

9 hits · last 30 days

node

Resources

githubhttps://github.com/steven-ferguson/knex-cleaner ↗

packagehttps://www.npmjs.com/package/knex-cleaner ↗