CODEWITHSUNDEEP
sequelize.js
Dave Briand
Dave Briand

Reputation: 1744

Sequelize Create Database

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.

Upvotes: 44

Views: 68870

Answers (7)

Sam Aijax
Sam Aijax

Reputation: 1

You can use getQueryInterface function and createaDabase function to create database before model initialization:

import { Sequelize } from "sequelize";

let sequelizeOptions: any = {
    dialect: "mysql",
    host: process.env.DB_HOST || "localhost",
    port: process.env.DB_PORT || 12345,
    username: process.env.DB_USER || "root",
    password: process.env.DB_PASSWORD || "",
}


export const dataBase = async () => {
    // create db if it doesn't already exist
    const { host, port, user, password, database } = { host: process.env.DB_HOST, port: process.env.DB_PORT, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_NAME || "zconnectmarket" };

    // const connection = await mysql.createConnection(({ host, port, user, password } as any));
    // await connection.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);

    // connect to db
    const sequelizeCont = await new Sequelize((sequelizeOptions as any)).getQueryInterface().createDatabase(database);
    // sync all models with database

    return sequelizeCont;

}

export default new Sequelize(({ ...sequelizeOptions, database: process.env.DB_NAME || "DBNAME" } as any))

Upvotes: 0

Tony
Tony

Reputation: 51

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

Upvotes: 0

nikksan
nikksan

Reputation: 3481

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.

Upvotes: 10

Spencer
Spencer

Reputation: 1522

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

Upvotes: 25

elirandav
elirandav

Reputation: 2073

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"
  },

Upvotes: 7

Marco Barcellos
Marco Barcellos

Reputation: 192

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

Upvotes: 7

Grant
Grant

Reputation: 2014

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',
  ...
});

Upvotes: 7

Related Questions