Collection of administrative database operations for knex supported databases
npm install knex-db-manager-continued
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.
> ⚠️ This package is a fork of knex-db-manager and compatible with knex >1
- 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)~~
> ⚠️ This package is installable under knex-db-manager-continued.
You need to install knex, database driver and knex-db-manager
``
npm install knex-db-manager knex pg pg-escape
Database manager is initialized with normal knex configuration and with
superuser account which should be able to create / drop roles and databases.
> Initialization:
`js
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);
`
Creates the user, which is described in knex configuration. This user is used as
the database owner when creating new databases.
`js`
let promise = dbManager.createDbOwnerIfNotExist();
Creates database described in knex configuration or by given name. Owner of theconfig.knex.connection.user
created database is set to be the .
dbName is the name of the database to be created, if not given the name is readconfig.knex.connection.database
from .
> Read database from config.knex.connection.database:
`js`
let promise = dbManager.createDb();
> By given name:
`js`
let promise = dbManager.createDb('brave-new-db');
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 nameconfig.knex.connection.database
is read from .
> Drop database config.knex.connection.database:
`js`
let promise = dbManager.dropDb();
> By specific name:
`js`
let promise = dbManager.dropDb('brave-new-db');
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:
`js`
let promise = dbManager.copyDb('brave-new-db', 'brave-new-db-copy');
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:
`js`
let promise = dbManager.truncateDb();
> ignore certain tables:
`js`
let promise = dbManager.truncateDb(['migrations']);
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:
`js`
let promise = dbManager.updateIdSequences();
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 isconfig.dbManager.populatePathPattern
read from .
> Get database from config.knex.connection.database and patternconfig.dbManager.populatePathPattern
> from :
`js`
let promise = dbManager.populateDb();
> with pattern:
`js`
let promise = dbManager.populateDb(path.join(__dirname, 'seeds', 'test-*'));
Runs knex migrations configured in knex config.
> Get database from config.knex.connection.database:
`js`
let promise = dbManager.migrateDb();
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'. Otherwise20141024070315_test_schema.js
resolves to first numbers of latest migration file ran e.g. for version will be '20141024070315'.
> Get database from config.knex.connection.database:
`js`
let promise = dbManager.dbVersion();
Closes the single privileged connection and all normal knex connections.
> Kill database connection:
`js`
let promise = dbManager.close();
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
`js`
let promise = dbManager.closeKnex();
Returns knex query builder bound to configured database.
> Get database from config.knex.connection.database:
`js``
let knex = dbManager.knexInstance();
knex('table')
.where('id', 1)
.then((rows) => {
console.log('Query was ran with db owner privileges', rows);
});