How to create mysql database with sequelize (nodejs)

Question

I am trying to automate the process of creating db and tables as much as possible. Is it possible to create database via sequelize? Can I make a connection string that connects just to server, not to db directly?

Answer 1

Short Answer: Sure you can!

Here's how I got it done:

//create the sequelize instance, omitting the database-name arg
const sequelize = new Sequelize("", "<db_user>", "<db_password>", {
  dialect: "<dialect>"
});

return sequelize.query("CREATE DATABASE `<database_name>`;").then(data 
=> {
  // code to run after successful creation.
});

P.S. Where to place code would depend on your need. Inside an initial migration file worked for me.

Answer 2

Despite this question been answered a while ago, please take a look at this other answer (mine) from a very similar question (perhaps a duplicate?) that definitely applies here: https://stackoverflow.com/a/62413891/1971120

The idea is to use Sequelize Migrations, where you let Sequelize handle the migrations using files with this format:

module.exports = {
  up: (queryInterface, Sequelize) => {
    // logic for transforming into the new state
  },
  down: (queryInterface, Sequelize) => {
    // logic for reverting the changes
  }

Yeah, it handles rollbacks too. Making this process very easily to automate.

Answer 3

None of the solutions worked for me for sequelize db and model sync. Taking inspiration from osifo's answer, I solved it using beforeConnect hook of sequelize as below:

const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../config/config.json')[env];

const { host, port, username, password } = config;

# create sequelize instance without providing db name in config
sequelize = new Sequelize('', username, password, config);

sequelize.beforeConnect(async (config) => {
     const connection = await mysql.createConnection({ host: host, port: port, user: username, password: password });
     await connection.query(`CREATE DATABASE IF NOT EXISTS \`${process.env.DB_NAME}\`;`);
     config.database = process.env.DB_NAME;
});

Config.json file contains configurations for different dbs like

{
  "development": {
    "username": "root",
    "password": "init@123",
    "host": "mysqldb",
    "dialect": "mysql"
  },
  "test": {
    "username": "root",
    "password": null,
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "production": {
    "username": "root",
    "password": null,
    "host": "127.0.0.1",
    "dialect": "mysql",
  }
}

Database name is provided after db connection and creation(if required) in sequelize beforeConnect hook

Answer 4

Here are main steps to populate mySql tables with sequelize and sequelize-fixtures modules:

step 1: creating model

module.exports = function(sequelize, Sequelize) {
// Sequelize user model is initialized earlier as User
const User = sequelize.define('user', {
    id          :       { type: Sequelize.INTEGER, autoIncrement: true, primaryKey: true },
    firstname   :       { type: Sequelize.STRING },
    lastname    :       { type: Sequelize.STRING },
    email       :       { type: Sequelize.STRING, validate: {isEmail:true} },
    password    :       { type: Sequelize.STRING }, 
});

// User.drop();
return User;
}

Step 2: creating a config file to store database configs

{
  "development": {

    "username": "root",
    "password": null,
    "database": "hotsausemedia",
    "host": "127.0.0.1",
    "dialect": "mysql"
  },
  "test": { 
    "username": "",
    "password": null,
    "database": "hotsausemedia",
    "host": "",
    "dialect": "mysql"
  },
  "production": {
    "username": "",
    "password": null,
    "database": "hotsausemedia",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

step 3: creating sequelize-fixture to populate tables. here is an example of a json file to use for populating data

[
    {
        "model": "product",
        "keys": ["id"],
        "data": {
            "id": 1,
            "name": "Product #1",
            "src": "./assets/img/products/01.jpg",
            "price": 9.99,
            "desc": "Product description..."
        }
    },
    {
        "model": "product",
        "keys": ["id"],
        "data": {
            "id": 2,
            "name": "Product #2",
            "src": "./assets/img/products/02.jpg",
            "price": 19.99,
            "desc": "Product description..."
        }
    },
    ...

]

step 4: connecting to database and populating tables

models.sequelize.sync().then(() => {
    console.log('You are connected to the database successfully.');
    sequelize_fixtures.loadFile('./fixtures/*.json', models).then(() =>{
        console.log("database is updated!");
   });
   }).catch((err) => {
       console.log(err,"Some problems with database connection!!!");
});