create/drop database task for gulp/knex

Question

I have an Express.js web applications which uses Knex.js as the SQL query builder and migrations engine. While Knex.js has methods for creating, dropping, and altering tables, it doesn't have methods for creating/dropping the database itself.

I was wondering if there is an extension for Knex.js or even a gulp task that allows you to create/drop a database. I couldn't find any. I'm using a PostgreSQL database.

Answer 1

I'm not sure about PostgreSQL, but I hit the same problem with MySQL. I discovered you can use knex to connect without selecting a database, create the database with raw SQL, then re-connect selecting the new database.

Here is a stand-alone script that creates a new database with a single-column table:

var conn = {
  host: '127.0.0.1',
  user: 'user',
  password: 'pass',
  charset: 'utf8',
};

// connect without database selected
var knex = require('knex')({ client: 'mysql', connection: conn });

knex.raw('CREATE DATABASE my_database').then(function () {
  knex.destroy();

  // connect with database selected
  conn.database = 'my_database';
  knex = require('knex')({ client: 'mysql', connection: conn });

  knex.schema
    .createTable('my_table', function (table) {
      table.string('my_field');
    })
    .then(function () {
      knex.destroy();
    });
});

This works (well enough for me for now) but I'm interested to hear of other solutions.

Answer 2

You can add https://www.npmjs.org/package/gulp-shell

This should work:

var gulp  = require('gulp')
var shell = require('gulp-shell')

gulp.task('example', function () {
  return gulp.src('*.js', {read: false})
    .pipe(shell([
      'psql DROP DATABASE dbname;',
      'psql CREATE DATABASE dbname;'
    ], {
      templateData: {
        f: function (s) {
          return s.replace(/$/, '.bak')
        }
      }
    }))
})

Answer 3

Loosely related (not a Gulp issue), but this is my solution to create the database in a Knex app. I simply ignore the error and NOT integrate with knexfile or the seed, so not getting called constantly, instead I call this from package.json. dropdb and createdb executables comes with Postgres:

// $ yarn node ./lib/createdb.mjs
import { exec } from 'child_process';

if (process.env.NODE_ENV === 'development') {
  exec(`createdb ${dbName} --template=template0`, (error, stdout, stderr) => {
    if (!error) return console.log(`"${dbName}" database created!`);
    return console.error('createdb stderr:', stderr);
  });
}

Answer 4

knex-db-manager is an add-in package that can do this.

Answer 5

var knex = require('knex')({
  client: 'pg',
  connection: {
    host: HOST,
    user: USERNAME,
    password: PASSWORD,
    database: 'postgres',
    charset: 'utf8'
  }
});

knex.raw('CREATE DATABASE DB_NAME;')
  .then(function() {
    return knex.raw('DROP DATABASE DB_NAME;')
  })
  .finally(function () {
    console.log("Done");
  });

Answer 6

Postgres will accept the application's connection string to create the application database.

env/development

DATABASE_URL=postgres://user:password@localhost:5432/sample_development

package.json

"scripts": {
  "db:create:development": "env-cmd env/development psql ${DATABASE_URL} -c 'CREATE DATABASE sample_test'",
  "db:create:test": "env-cmd env/test psql ${DATABASE_URL} -c 'CREATE DATABASE sample_test'"
}

Then run npm run-script db:create:development.

(This uses the env-cmd package but it's not required.)