Sequelize Create Database

Question

Is there a way to have sequelize create the database I'm trying to connect to if it doesn't exist?

I have a raw MySQL instance and I get this error back:

ER_BAD_DB_ERROR: Unknown database 'mytest'

I'd like to handle that error, use the creds I provided (or a different set of creates with create permissions), and run CREATE DATABASE mytest.

Answer 1

I may have a reasonable solution. I am sure it could be written more cleanly though.

For this example I'm using Postgres, the answer would be slightly different for MySQL. I'm heavily borrowing from this answer: node-postgres create database

In database.js I have the following init function

var Sequelize = require('sequelize'),
    pg = require('pg');

module.exports.init = function(callback) {
    var dbName = 'db_name',
        username = 'postgres',
        password = 'password',
        host = 'localhost'

    var conStringPri = 'postgres://' + username + ':' + password + '@' + host + '/postgres';
    var conStringPost = 'postgres://' + username + ':' + password + '@' + host + '/' + dbName;

    // connect to postgres db
    pg.connect(conStringPri, function(err, client, done) { 
        // create the db and ignore any errors, for example if it already exists.
        client.query('CREATE DATABASE ' + dbName, function(err) {
            //db should exist now, initialize Sequelize
            var sequelize = new Sequelize(conStringPost);
            callback(sequelize);
            client.end(); // close the connection
        });
    });
};

The init function is creating the database before sequelize is called. It first opens a connection to postgres and creates the database. If the database already exists, an error will be thrown which we are ignoring. Once it is created we initialize sequelize and send it to the callback. Importantly, if the database already exists it will not be overwritten.

In app.js I receive the database instance and send it over to whichever module needs it, in this case it is passport.

require('./server/config/database.js').init(function(database) {
  require('./server/config/passport.js')(passport, database);
});

Answer 2

I ended up using the mysql2 package, here is what I did..

    const mysql = require('mysql2/promise');
    
    mysql.createConnection({
        user     : config.sequelize.username,
        password : config.sequelize.password
    }).then((connection) => {
        connection.query('CREATE DATABASE IF NOT EXISTS myRandomDb;').then(() => {
            // Safe to use sequelize now
        })
    })

After that I can connect to that database using sequelize.

Answer 3

In my case I was using sqlite, but the idea was the same. I needed to create the database first with sqlite3.

const sqlite = require('sqlite3');
const db = new sqlite.Database('/path/to/database.sqlite');
const sequelize = new Sequelize('database', '', '', {
  dialect: 'sqlite',
  storage: '/path/to/database.sqlite',
  ...
});

Answer 4

I didn't have this problem with Postgresql but I did have with mysql. Solution was creating a pre-migrate script and trigger it before the sequelize migration, here's part of my package.json :

"db:pre-migrate": "node scripts/createDB.js",
"db:migrate": "npm run db:pre-migrate && node_modules/.bin/sequelize db:migrate",

and similarly to what was proposed in some answer here, you could create this createDB.js using modules like node-postgres or mysql(or any other that connects to mysql with raw queries):

const mysql = require('mysql2/promise');

const dbName = process.env.DB_SCHEMAS || "YOUR_DB";

mysql.createConnection({
    host: process.env.DB_HOST || "127.0.0.1",
    port: process.env.DB_PORT || "3306",
    user     : process.env.DB_USER || "root",
    password : process.env.DB_PASSWORD || "root",
}).then( connection => {
    connection.query(`CREATE DATABASE IF NOT EXISTS ${dbName};`).then((res) => {
        console.info("Database create or successfully checked");
        process.exit(0);
    })
})

Answer 5

Shortly: In my case, I got this error when running tests for the first time. To resolve that, I just added the command npx sequelize-cli db:create before running jest.

---

After adding the command in my test script, the package.json looks like this:

  "scripts": {
    "test": "npx sequelize-cli db:create && jest"
  }

You should see this message at the beggingnig of nmp test exexution:

Loaded configuration file "config/config.json".
Using environment "development".
Database orders created.

Sequelize knows to create a database called "orders" because I mentioed it in "config/config.json".

{
  "development": {
    "username": "root",
    "password": "pswd",
    "database": "manager",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "port": "3307"
  },

Answer 6

You can try:

import { Sequelize } from 'sequelize';

// user must have permission to create databases.
const sequelize = new Sequelize('postgres://user:pass@localhost:5432/postgres');

async function creatDatabase(dbName: string) {
  let res;
  try {
    res = await sequelize.getQueryInterface().createDatabase(dbName);
  } catch(e) {
    console.log(e);
  }
  return res;
}

creatDatabase('secondDB');