initialize() is one of the most used built-in functions in JavaScript. It is generally used to initialize modules or write code that we want to execute only once on production mode, like printing out a confirmation message or creating a database for apps.
This article intends to show an example of how you can use the initialize() function to create and connect a MySQL database in your node.js API and how to initialize the Sequelize models inside this function.
- Developing the Node.js MySQL API
- Using initialize() to Configure MySQL Database & Initializing Sequelize
- 1. Create our MySQL database inside the initialize() function
- 2. Connect MySQL database and Initializing Sequelize ORM
- 3. Initializing the Sequelize Model and Convert it to SQL Table
- Conclusion
- You might also like:
Developing the Node.js MySQL API
This tutorial presents an example of a Node.js and MySQL boilerplate API that uses Sequelize ORM to connect the database, define the database tables, and interact with it.
- So it’s required that you have already set up MySQL on your computer and added it to your path. If not, you can use the following links:
To develop our app project named “initialize-example”, we will write our code in JavaScript for Node.js and use Express as the webserver for the API.
We are starting with a boilerplate for node.js server server.js. If you want to know more details about the start-server we are using in this article, go ahead and check this article: Complete Guide to Build a RESTful API with Node.js and Express.
Make sure that you have all the following packages installed and imported into your server.js:
- express.
- body-parser.
- cors.
- dotenv.
To do that, run the following command after initializing the npm in your project folder:
npm install --save express body-parser cors dotenv server.js
require("dotenv").config();
const express =require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const app = express();
const PORT= process.env.APP_PORT;
app.use(bodyParser.json());
app.use(cors());
app.listen(PORT, ()=>{
console.log(`server is listening on ${PORT}`);
});
module.exports = app;
Please remember to save all the environment variables related to your API in your .env file, so you will be able to load and use them later.
.env
APP_PORT = 3000
package.json
//package.json
{
"name": "initialize-example",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"author": "",
"license": "ISC",
"dependencies": {
"body-parser": "^1.19.0",
"cors": "^2.8.5",
"dotenv": "^8.2.0",
"express": "^4.17.1",
"save": "^2.4.0"
}
}
Using initialize() to Configure MySQL Database & Initializing Sequelize
As we already said, the initialize() function is executed once on API startup to perform all the actions defined inside this function.
In our API, we will use the initialize() function to perform the following actions:
- Connect to MySQL server using mysql2 and execute a query to create the database if it doesn’t already exist.
- Connect to the database with the Sequelize ORM.
- Initialize the Employee model.
- Create the Employee table in MySQL database if it doesn’t t exist by calling await sequelize.sync().
1. Create our MySQL database inside the initialize() function
To use the initialize() function, we need to define it and define all the actions we want to perform inside it in a new file named db.js.
db.js
async function initialize(){
// add the actions here
}
initialize();
In the initialize() of our app, the first action we want to execute is to create the database named “initialize-db”. We write the query to create the database inside the initialize() because this function is executed only once on an API which means that the database is created only once on API startup.
But before writing the query, we need to install the mysql2 package then import the mysql2/promise into db.js in order to use the Promise Wrapper. Make sure it’s mysql2 and not mysql.
npm install --save mysql2db.js
const config = require('./config.js');
const mysql = require('mysql2/promise');
async function initialize(){
try{
// first action is to create a db named initialize-db
const { host, port, user, password, database } = config.database;
const pool = await mysql.createPool({ host, port, user, password });
await pool.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
}
} catch(e){
console.log(e);
}
initialize();
To save the configuration data and database connection options for the MySQL database, we create a separate file config.js file like this:
config.js
module.exports ={
"database":{
"host": "localhost",
"port": 3306,
"user": "root",
"password": "enter your db password here",
"database": "initialize-db"
}
};
2. Connect MySQL database and Initializing Sequelize ORM
To connect to the Mysql database using Sequelize and mysql2 client, we need first to install then import the sequelize package.
npm install --save sequelize
const Sequelize = require('sequelize');
Next, we create a new instance of this class to connect the database by passing specific parameters and options. To know more about these parameters, check HERE.
In order to use the sequelize instance in the other files of our project, we will attach it to the exported db object.
db.js
const config = require('./config.js');
const mysql = require('mysql2/promise');
const Sequelize = require('sequelize');
module.exports = db = {};
async function initialize(){
try{
// first action is to create a db named initialize-db
const { host, port, user, password, database } = config.database;
const pool = await mysql.createPool({ host, port, user, password });
await pool.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
// second action: connect to db using Sequelize
const sequelize = new Sequelize(database, user, password, { dialect: 'mysql',
pool: {
max: config.pool.max,
min: config.pool.min,
acquire: config.pool.acquire,
idle: config.pool.idle
} }
);
db.sequelize = sequelize ;
}catch(e){
console.log(e);
}
}
initialize();
config.js
module.exports ={
"database":{
"host": "localhost",
"port": 3306,
"user": "root",
"password": "enter your db password here",
"database": "initialize-db"
},
"pool": {
"max": 5,
"min": 0,
"acquire": 30000,
"idle": 10000
}
};
3. Initializing the Sequelize Model and Convert it to SQL Table
The third action is to initialize the Employee sequelize model then automatically create the corresponding table in the MySQL database by calling the sequelize.sync(). But before that, we need first to create our model.
In our database, we will have only one table Employee that means we will have one model Employee. So inside your project folder, please create a new folder and name it models. Then inside this folder, create a new file, employee.js, to define the Employee model.
To define a schema for the Employee table in the MySQL Database, we use the define() method on the sequelize instance created already. This will give us full access to perform CRUD (create, read, update, delete) operations on Employees in MySQL. To know more about the define() method and how to define a model for MySQL table, please go HERE.
employee.js
const { Sequelize, DataTypes } = require('sequelize');
const db = require('../db.js');
const sequelize = db.sequelize;
const Employee = sequelize.define("Employee", {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
allowNull: false,
primaryKey: true,
},
name: {
type: DataTypes.STRING,
allowNull: false,
},
position: {
type: DataTypes.STRING,
allowNull: false,
},
email: {
type: DataTypes.STRING,
allowNull: false,
},
wage: {
type: DataTypes.INTEGER,
allowNull: false,
},
});
module.exports = Employee;
Now it’s time to import our Employee model and then initialize and attach it to the db object.
db.js
const config = require('./config.js');
const mysql = require('mysql2/promise');
const Sequelize = require('sequelize');
module.exports = db = {};
async function initialize(){
try{
// first action is to create a db named initialize-db
const { host, port, user, password, database } = config.database;
const pool = await mysql.createPool({ host, port, user, password });
await pool.query(`CREATE DATABASE IF NOT EXISTS \`${database}\`;`);
// second action: connect to db using Sequelize
const sequelize = new Sequelize(database, user, password, { dialect: 'mysql',
pool: {
max: config.pool.max,
min: config.pool.min,
acquire: config.pool.acquire,
idle: config.pool.idle
} }
);
db.sequelize = sequelize ;
// third action: init the Employee model and add it to the exported db object
db.Employee = require('./models/employee');
// sync all models with database
await sequelize.sync();
} catch(e){
console.log(e);
}
}
initialize();
sever.js
require("dotenv").config();
const express =require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const db = require('./db');
const sequelize = db.sequelize;
const app = express();
const PORT= process.env.APP_PORT;
app.use(bodyParser.json());
app.use(cors());
app.listen(PORT, ()=>{
console.log(`server is listening on ${PORT}`);
});
module.exports = app;
Finally, all you have to do is start your sever and you will see this in your terminal:
You can use Workbench to confirm :
Conclusion
In a production mode, all the actions we have just defined in the initialize() function will be executed only once on API startup, which means that the database and the tables will be created only once. If you want to see the rest of the CRUD API and how to write the controller using Sequelize async/await, read this article: How to use Sequelize async/await to interact with MySQL database in Node.js.
You might also like:
How to interact with MySQL database using async/await promises in node.js ?
How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
How to use Sequelize async/await to interact with MySQL database in Node.js.
Why is Connection Pooling better than Single Connection?.
How to create MySQL database using node.js.
How to store Session in MySQL Database using express-mysql-session.
How to Manage Session in Nodejs using Redis Store.
Complete Guide to Build a RESTful API with Node.js and Express.
Node.js + Redis – Complete API for Authentication, Registration and User Management.