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.

couldn't you use the database driver? For example, I'm using mssql, npm mssql package is a dependency anyway so just use that to create the database before connecting knex to it? — meelash, Oct 01 '19 at 20:10

score 41 · Accepted Answer · edited Mar 18 '21 at 14:30

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.

Seems like an interesting direction. Will check it for PostgreSQL. — ifeins, Dec 08 '14 at 19:53
For postgresql it will ask for a default database, you can just use postgres as default database. — Maxim, Sep 23 '15 at 12:45

score 8 · Answer 2 · edited Nov 28 '16 at 23:27

8

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

edited Nov 28 '16 at 23:27

Rob Porter

801
8
23

answered Jul 15 '15 at 10:45

siyang

665
6
12

score 2 · Answer 3 · answered Dec 04 '14 at 02:32

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

answered Dec 04 '14 at 02:32

Josh Lankford

468
2
5

1

This would work, but I'm looking for a solution without dropping to the shell. I guess it's probably possible to do using the "pg" npm package. Pity that knex doesn't support the CREATE/DROP DATABASE commands. – ifeins Dec 05 '14 at 11:09

score 0 · Answer 4 · answered Oct 23 '18 at 17:00

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.)

Lanti · Answer 5 · 2021-02-20T19:27:36.727

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

score 0 · Answer 6 · answered Mar 12 '21 at 03:56

0

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

answered Mar 12 '21 at 03:56

Chris Halcrow

28,994
18
176
206

create/drop database task for gulp/knex

6 Answers6

env/development

package.json