steven-ferguson / knex-cleaner

Database cleaner for Knex.js

knex-cleaner

Helper library to clean a PostgreSQL, MySQL or SQLite3 database tables using Knex. Great for integration tests.

Installation

npm install knex-cleaner

Usage

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

var knex = require('knex')({
  client: 'mysql',
  connection: {
    host     : '127.0.0.1',
    user     : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});

knexCleaner.clean(knex).then(function() {
  // your database is now clean
});

// You can also use this in BookshelfJS
var bookshelf = require('bookshelf')(knex);

knexCleaner.clean(bookshelf.knex).then(function() {

});

// You can also pass if it deletes the tables with delete instead of truncate
// as well as a list of tables to ignore.

var options = {
  mode: 'delete', // Valid options 'truncate', 'delete'
  restartIdentity: true, // Used to tell PostgresSQL to reset the ID counter
  ignoreTables: ['Dont_Del_1', 'Dont_Del_2']
}

knexCleaner.clean(knex, options).then(function() {
  // your database is now clean
});

The example above used MySQL but it has been tested on PostgreSQL and SQLite3.

knex-cleaner's Issues

Does not support different schemas in postgres

Currently, the library is limited to only working on the 'public' schema. Is this by choice or would a pull request help?

https://github.com/steven-ferguson/knex-cleaner/blob/master/lib/knex_tables.js#L22

Failure on cleaning DBs

Thank you for the package!!

I used to use
knexCleaner.clean(knex)
but that screwed my migrations. Now I use
knexCleaner.clean(knex, {mode: 'delete', ignoreTables: ['knex_migrations', 'knex_migrations_lock']})
which allows my knex migrations to still function as expected.

However, now knex-cleaner only works every other time, and I get the error update or delete on table "vendors" violates foreign key constraint "checkins_vendor_id_foreign" on table "checkins". Which I thought is exactly the kind of problem knex-cleaner should be able to resolve for me?

Restart identity not working for me

Hi, thanks for this project!

It might be my mistake, but when I use knex-cleaner before each unit test, the tables are getting deleted but the id does not seem to reset. I seed one user in my table (expect id=1) but when I select it from the db in a test, it is 10 or 11. Presumably the other tests are incrementing it.

const knexCleanConfig = {
	mode: 'delete',
	restartIdentity: true,
	ignoreTables: ['knex_migrations', 'knex_migrations_lock']
}

then beforeEach test:

await knexCleaner.clean(knex, knexCleanConfig)

await knex('users').insert([
  {
    username: 'test',
    email: '[email protected]',
    name: 'Test Name'
  }
])

the code to create the column:

table.specificType(
  'id',
  'integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'
)

Release new version

The current version in npm uses an outdated version of lodash which results in security warnings when using this package in a project.

Are you planning on releasing a version where this is fixed?

Support for MariaDB

Would be great if there was support for mariasql as the knex client.

The order of cleaning violate constraints sometimes.

Sometimes, it tries to clean a table before the one it references causing constraints violation.

ER_ROW_IS_REFERENCED_2: Cannot delete or update a parent row: a foreign key constraint fails (`Company`.`data_datalogger`, CONSTRAINT `fk17` FOREIGN KEY (`id_brand_datalogger`) REFERENCES `brand_datalogger` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Is there a way to manage the order of cleaning?

Types

Hey there, nice project!

I am trying to use it in one TypeScript project and I noticed it's missing a types.d.ts file. It would be awesome to have there in your project since TS is pretty popular right now.

Issue using with Typescript

I'm currently dealing with the same issue, attempting to use Knex-cleaner to truncate before each test:

node: 12.13.1
knex: ^0.21.13
knex-cleaner: ^1.3.1

import knexCleaner from "knex-cleaner";
import knex from ".././config/db-config";



beforeEach(() => {
      const options = {
            mode: "truncate", // Valid options 'truncate', 'delete'
            restartIdentity: true // Used to tell PostgresSQL to reset the ID counter
            // ignoreTables: ["Dont_Del_1", "Dont_Del_2"]
      };
      return knexCleaner.clean(knex, options).then(function () {
            console.log("the database is now clean");
      });
});

Getting the error :

Argument of type 'Knex<any, unknown[]>' is not assignable to parameter of type 'Knex'.
  Property 'clone' is missing in type 'Knex<any, unknown[]>' but required in type 'Knex'.ts(2345)
knex.d.ts(216, 5): 'clone' is declared here.

Unable to re-run migrations after cleanup

Using the truncate or delete options, I am unable to re-run my knex migrations after cleanup.
An option to 'drop' tables is one way to accomplish this.
Ignoring the migrations table is another, which I have also added in #17.

I have introduced this option in #14

Requesting Drop option

check out this commit, literally just asking you to plop these three lines into ur code as it is in this diff.

yeya@fbaf1ac

Support for Oracle

At this point "Could not get the sql to select table names from client: oracle" is returned when cleaning for Oracle database is attempted.

Missing quotes around table names

Postgres converts table names to lower case if they aren't wrapped with quotes.

So, I'm receiving something like

Unhandled rejection error: TRUNCATE foo,bar,fooBar CASCADE - relation "foobar" does not exist

for tables named in camel case.

Simple fix of adding '\"' + ... + '\"' works fine for me in https://github.com/steven-ferguson/knex-cleaner/blob/master/lib/knex_tables.js#L81.

Clear single table

Is it there any way to clean just one table?

Add knex migration tables as ignored by default

Hi!

I tried to use knex-cleaner for my project, running it in the mocha test. When I use it as described in README, trying to clean out my database after each testcase (using after declaration), I get the error from knex Unhandled rejection MigrationLocked: Cannot read property 'is_locked' of undefined.

The error appears, because the table, where knex stores it's migration state become truncated after knex-cleaner does all it's things.

To prevent this, I use the following code in my after block:

describe('ObjectUnderTest', function() {
  describe('#objectMethods', function() {
    after(function(){
      return knexCleaner.clean(knex, {'mode':'delete', 'ignoreTables':['knex_migrations', 'knex_migrations_lock']});
    });
  });
});

PROPOSAL

1. knex-cleaner shouldn't kill database schema by default, so the mode: 'delete' should be the default option, instead of truncate.
2. knex-cleaner shouldn't kill tables, required by Knex, so all tables, that have name prepended with 'knex_' should be ignored by default.

Breaks if no tables already exist

This is with a postgres DB.

If I find time I will fix the issue (just need to do an empty array check)