Pretty useful when writing scripts to initialize database for fresh install or dropping / creating new database when running tests and for truncating database between tests.

Library uses knex connection for non administrative queries, but also creates priviliged connection directly with driver with superuser privileges for creating and dropping databases / roles.

Supported Databases

• PostgreSQL
• MySQL
• SQLite3 (partial support even though most of the functions won't make sense with this)
• ~~Oracle DB Express (TBD)~~
• ~~MSSQL (TBD if we can get integration tests to run automatically)~~

Install

You need to install

knex

knex-db-manager

npm install knex-db-manager knex pg pg-escape

API & Usage

Database manager is initialized with normal

knex

Initialization:

let config = {
  knex: {
    // just the usual knex configuration
    client: 'postgres',
    connection: {
      host: 'localhost',
      database: 'appdb',
      user: 'dbowneruser',
      password: 'dbownerpassword',
    },
    pool: {
      min: 0,
      max: 10,
    },
    migrations: {
      directory: __dirname + '/migrations',
    },
  },
  dbManager: {
    // db manager related configuration
    collate: ['fi_FI.UTF-8', 'Finnish_Finland.1252'],
    superUser: 'userwithrightstocreateusersanddatabases',
    superPassword: 'privilegeduserpassword',
    populatePathPattern: 'data/**/*.js', // glob format for searching seeds
  },
};

let dbManager = require('knex-db-manager').databaseManagerFactory(config);

createDbOwnerIfNotExist(): Promise

Creates the user, which is described in

knex

let promise = dbManager.createDbOwnerIfNotExist();

createDb(dbName?: string): Promise

Creates database described in

knex

config.knex.connection.user

dbName

config.knex.connection.database

Read database from

config.knex.connection.database

:

let promise = dbManager.createDb();

By given name:

let promise = dbManager.createDb('brave-new-db');

dropDb(dbName?: string): Promise

Drops database described in

knex

dbName

config.knex.connection.database

Drop database

config.knex.connection.database

:

let promise = dbManager.dropDb();

By specific name:

let promise = dbManager.dropDb('brave-new-db');

copyDb(fromDbName: string, toDbName: string): Promise

Clones database to another name remotely on db serverside (may be useful e.g. to make backup before running migrations).

New database

toDatabaseName

fromDbName

Note: This method is not supported with MySQL (yet).

Making copy of DB:

let promise = dbManager.copyDb('brave-new-db', 'brave-new-db-copy');

truncateDb(ignoreTables?: string[]): Promise

Truncate tables of the database and reset corresponding id sequences.

ignoreTables

Truncate database

config.knex.connection.database

:

let promise = dbManager.truncateDb();

ignore certain tables:

let promise = dbManager.truncateDb(['migrations']);

updateIdSequences(): Promise

Updates all primary key id sequences to be biggest id in table + 1. So after running this next

INSERT

This was motivated by some people who liked to create test data with hard coded ids, so this helps them to make app to work normally after adding rows to tables, which has not used id sequence to get ids.

The function assumes that the primary key for each table is called

id

Note: This method is not supported with MySQL (yet).

Reset sequence of database

config.knex.connection.database

:

let promise = dbManager.updateIdSequences();

populateDb(glob: string): Promise

Finds

knex

glob

config.dbManager.populatePathPattern

Get database from

config.knex.connection.database

and pattern from

config.dbManager.populatePathPattern

:

let promise = dbManager.populateDb();

with pattern:

let promise = dbManager.populateDb(path.join(__dirname, 'seeds', 'test-*'));

migrateDb(): Promise

Runs

knex

Get database from

config.knex.connection.database

:

let promise = dbManager.migrateDb();

dbVersion(): Promise

Checks which migrations has been ran to database.

Expects that migration name starts with timestamp.

If no migrations has been run, promise resolves to

'none'

20141024070315_test_schema.js

'20141024070315'

Get database from

config.knex.connection.database

:

let promise = dbManager.dbVersion();

close(): Promise

Closes the single privileged connection and all normal knex connections.

Kill database connection:

let promise = dbManager.close();

closeKnex(): Promise

Closes knex connection which is made to the database for unprivileged queries. Sometimes this is needed e.g. for being able to drop database.

Close knex connection

let promise = dbManager.closeKnex();

knexInstance(): QueryBuilder

Returns

knex

Get database from

config.knex.connection.database

:

let knex = dbManager.knexInstance();
knex('table')
  .where('id', 1)
  .then((rows) => {
    console.log('Query was ran with db owner privileges', rows);
  });