How do I create a mysql database for my nodejs project?

Question

This should be really basic and simple to do but I can seriously not find any understandable information on how to create a simple database for my nodejs typescript project.

I have installed the following packages with npm:

• mysql2
• sequelize
• sequelize-cli
• sequelize-typescript

I have attempted the following commands at the terminal

C:\repos\NodeNew>mysql2 -u root -p
'mysql2' is not recognized as an internal or external command,
operable program or batch file.

C:\repos\NodeNew>mysql -u root -p
'mysql' is not recognized as an internal or external command,
operable program or batch file.

C:\repos\NodeNew>node mysql2 -u root -p
module.js:538
    throw err;
    ^

Error: Cannot find module 'C:\repos\NodeNew\mysql2'
    at Function.Module._resolveFilename (module.js:536:15)
    at Function.Module._load (module.js:466:25)
    at Function.Module.runMain (module.js:676:10)
    at startup (bootstrap_node.js:187:16)
    at bootstrap_node.js:608:3

C:\repos\NodeNew>

So how do I CREATE my database so I can connect to it with sequelize etc?

Answer 1

It can be solved 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 2

You should have MySQL installed on your computer, to install mysql on Windows see the following page: MySql Once you have MySQL up and running on your computer. Open the Command Terminal and execute the following:

npm install mysql

Now you have downloaded and installed a mysql database driver. Node.js can use this module to manipulate the MySQL database:

var mysql = require('mysql');

To create a conecction create a file connection.js:

 var con = mysql.createConnection({
  host: "localhost",
  user: "yourusername",
  password: "yourpassword"
});

con.connect(function(err) {
 if (err) throw err;
  console.log("Connected!");
});

to test it save the file and run:

 node connection.js

Which will give you this result:

 Connected!

Answer 3

The tools you have installed are only for connecting a Node.js app to MySQL and do not include command-line tools to manage the MySQL server.

I will assume you have installed MySQL and it's running – you should then be able to find its mysql.exe command line client in the bin/ directory in the server's installation directory. If you haven't fiddled with authentication, just running it might work.

When you get to a MySQL prompt, you can follow any old instructions for creating a database; CREATE DATABASE foo; is the gist of it (authentication and permissions being a different story).

Since you're on Windows, you might want to look into HeidiSQL – it's been a while since I've used it, but it's a decent graphical MySQL management tool.

You can also use mysql2 to create the database – illustrated below – but I recommend getting a management tool.

const mysql = require('mysql2');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'mysql',
});
connection.query('CREATE DATABASE foo');