Need help with knex-db-manager?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

Vincit
132 Stars 22 Forks ISC License 124 Commits 8 Opened issues

Description

Utility for create, drop, truncate etc. administrative database operations.

Services available

!
?

Need anything else?

Contributors list

Build Status Coverage Status Greenkeeper badge

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
, database driver and
knex-db-manager
npm install knex-db-manager knex pg pg-escape

API & Usage

Database manager is initialized with normal

knex
configuration and with superuser account which should be able to create / drop roles and databases.

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
configuration. This user is used as the database owner when creating new databases.
let promise = dbManager.createDbOwnerIfNotExist();

createDb(dbName?: string): Promise

Creates database described in

knex
configuration or by given name. Owner of the created database is set to be the
config.knex.connection.user
.

dbName
is the name of the database to be created, if not given the name is read from
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
configuration or by given name. Note that if there are any active connections to the database that is being dropped, the drop command might fail.

dbName
is the name of the database to be dropped, if not given the name is read from
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
will be created containing a copy of
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
list of tables names which should not be truncated.

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
to table will get valid id for the row from the sequence.

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
seed files by pattern and populate database with them.

glob
is a pattern to match files to be ran, if not given the name is read from
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
migrations configured in knex config.

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'
. Otherwise resolves to first numbers of latest migration file ran e.g. for
20141024070315_test_schema.js
version will be
'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
query builder bound to configured database.

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

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.