Create database in Knex migration

Question

I thought it would be a good idea to start using migrations from the very first db interaction, so I'd like to create a migration for the database creation.

knexfile.js

'use strict';

require('dotenv').config({ path: 'process.env' });

const config =  {
  client: 'pg',
  connection: {
    host: process.env.DB_URL,
  },
};


module.exports = config;

migration file:

'use strict';

exports.up = function(knex, Promise) {
  return knex.raw('CREATE DATABASE asd');
};

exports.down = function(knex, Promise) {
  return knex.raw('DROP DATABASE asd');
};

exports.config = {
  transaction: false
};

It works properly up to this point, but when I add database to the knexfile configuration, it fails to migrate as Knex tries to connect to the non existing database.

I also tried to use a new Knex instance for this single migration like:

exports.up = function(_, Promise) {
  // Remove database from config so Knex won't try to connect
  // to a non existing database.
  const config = require(process.cwd() + '/knexfile');
  config.connection.database = null;
  const knex = require('knex')(config);

  return knex.raw('CREATE DATABASE asd');
};

But knex is already initialized before the migration, so it fails with the same error:

error: database "asd" does not exist

Any ideas about how to create the database from a Knex migration? I'm open to any best practices about database creation, that can handle db urls for different environments.

Answer 1

Because the database is not known before hand, the easiest way to do this would be to create your own script using the migration API. It should be possible to specify your database name dynamically

Save as migrate.js

const Knex = require('knex')

// You can dynamically pass the database name
// as a command-line argument, or obtain it from
// a .env file
const databaseName = 'database_name'

const connection = {
  host: 'localhost',
  user: 'root',
  password: 'password'
}

async function main() {
  let knex = Knex({
    client: 'mysql',
    connection
  })
  
  // Lets create our database if it does not exist
  await knex.raw('CREATE DATABASE IF NOT EXISTS ??', databaseName)
  

  // Now that our database is known, let's create another knex object
  // with database name specified so that we can run our migrations
  knex = Knex({
    client: 'mysql',
    connection: {
      ...connection,
      database: databaseName,
    }
  })

  // Now we can happily run our migrations
  await knex.migrate.latest()

  // Done!!
}

main().catch(console.log).then(process.exit)

You can now run your script

node migrate.js

You can use a .env file to store your config or pass them as command-line arguments

Answer 2

Knex does not really support creating databases easily.

First of all running migrations has to create table, which contains information of migrations that has been ran, so database must exist prior executing migrations.

The usually you would like to have different configuration file for creating databases, with user with enough privileges to create databases and which connects to e.g. database called postgres or template1.

Like that you should be able to do just by creating simple script, which makes sure that DB is created before running the migrations.

Also there is tool knex-db-manager (written mostly by me) which might help tasks of creating database owner users / databases.