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

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 3

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