{"id":16407,"library":"knex-cleaner","title":"Knex.js Database Cleaner","description":"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.","status":"maintenance","version":"1.3.1","language":"javascript","source_language":"en","source_url":"git://github.com/steven-ferguson/knex-cleaner","tags":["javascript","knex","knexjs","bookshelf","bookselfjs"],"install":[{"cmd":"npm install knex-cleaner","lang":"bash","label":"npm"},{"cmd":"yarn add knex-cleaner","lang":"bash","label":"yarn"},{"cmd":"pnpm add knex-cleaner","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Provides the database connection and query builder instance that knex-cleaner operates on. This is a peer dependency or expected runtime dependency.","package":"knex","optional":false}],"imports":[{"note":"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')`.","wrong":"import { clean } from 'knex-cleaner';","symbol":"clean","correct":"import knexCleaner from 'knex-cleaner';\nknexCleaner.clean(knex, options)"},{"note":"For CommonJS environments, the package exports a default function. Assign it to a variable, then call its `clean` method directly (e.g., `knexCleaner.clean`).","wrong":"const { clean } = require('knex-cleaner');","symbol":"knexCleaner","correct":"const knexCleaner = require('knex-cleaner');"},{"note":"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.","wrong":"knexCleaner.clean(config);","symbol":"Knex.js instance","correct":"import knexCleaner from 'knex-cleaner';\nconst knex = require('knex')(config);\nknexCleaner.clean(knex);"}],"quickstart":{"code":"import knexCleaner from 'knex-cleaner';\nimport knex from 'knex'; // Assume knex is installed\n\nconst dbConfig = {\n  client: 'sqlite3',\n  connection: {\n    filename: './mydb_test.sqlite'\n  },\n  useNullAsDefault: true\n};\n\nconst myKnexInstance = knex(dbConfig);\n\nasync function setupDatabase() {\n  // For SQLite, create a dummy table to ensure the database file exists\n  await myKnexInstance.schema.hasTable('users').then(exists => {\n    if (!exists) {\n      return myKnexInstance.schema.createTable('users', table => {\n        table.increments('id');\n        table.string('name');\n        table.string('email');\n      });\n    }\n  });\n  await myKnexInstance.schema.hasTable('products').then(exists => {\n    if (!exists) {\n      return myKnexInstance.schema.createTable('products', table => {\n        table.increments('id');\n        table.string('name');\n        table.decimal('price', 8, 2);\n      });\n    }\n  });\n\n  // Insert some data\n  await myKnexInstance('users').insert([{ name: 'Alice', email: 'alice@example.com' }]);\n  await myKnexInstance('products').insert([{ name: 'Widget', price: 19.99 }]);\n  console.log('Database initialized and populated.');\n}\n\nasync function cleanAndVerify() {\n  await setupDatabase();\n\n  console.log('Tables before clean:', await myKnexInstance.raw(\"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';\"));\n\n  const options = {\n    mode: 'delete', // Or 'truncate' for faster clearing on supported DBs\n    restartIdentity: true, // Useful for PostgreSQL\n    ignoreTables: ['knex_migrations', 'knex_migrations_lock'] // Ignore Knex migration tables\n  };\n\n  try {\n    await knexCleaner.clean(myKnexInstance, options);\n    console.log('Database tables successfully cleaned!');\n    console.log('Users table count after clean:', (await myKnexInstance('users').count('id as count'))[0].count);\n    console.log('Products table count after clean:', (await myKnexInstance('products').count('id as count'))[0].count);\n  } catch (error) {\n    console.error('Error cleaning database:', error);\n  } finally {\n    await myKnexInstance.destroy();\n  }\n}\n\ncleanAndVerify();","lang":"javascript","description":"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."},"warnings":[{"fix":"Pass `{ restartIdentity: true }` in the options object to `knexCleaner.clean(knex, options)`.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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).","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"deprecated","affected_versions":"<1.3.1"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"fix":"Ensure you are calling the `clean` method: `knexCleaner.clean(knex, options)`. If using ESM, `import knexCleaner from 'knex-cleaner';` then `knexCleaner.clean(...)`.","cause":"Attempting to call `knexCleaner()` directly instead of `knexCleaner.clean()` or an incorrect import for ESM.","error":"TypeError: knexCleaner is not a function"},{"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.","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.","error":"Error: insert into \"TableName\" (\"id\", ...) values (...) - SQLITE_CONSTRAINT: UNIQUE constraint failed: TableName.id"},{"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.","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).","error":"SQLITE_CONSTRAINT: FOREIGN KEY constraint failed"}],"ecosystem":"npm"}