MySQL Create Database in Node.js

Automation: You can automate the creation of databases for dynamic applications.
Simplifies Deployment: When setting up projects in new environments, programmatically creating databases saves time.
Control: You can manage database setup directly from your Node.js code.

Prerequisites

Install MySQL: Ensure MySQL is installed and running on your system.

Install Node.js: Node.js must be installed.

MySQL Module: Use the MySQL package to interact with the database. Install it using the command:

npm install mysql

Setting Up the Node.js Project

Initialize the Project:

npm init -y

Install the Required Package:

npm install mysql

Create a File: Create a file named app.js where you’ll write the code.

Connecting to MySQL

Before creating a database, you need to connect to the MySQL server.

Example: Connect to MySQL

const mysql = require('mysql');

// Create a connection to the MySQL server
const connection = mysql.createConnection({
    host: 'localhost', // MySQL server hostname
    user: 'root', // MySQL username
    password: '', // MySQL password
});

// Connect to the server
connection.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL:', err);
        return;
    }
    console.log('Connected to MySQL successfully!');
});

Explanation:

host: The hostname of the MySQL server (default is localhost).
user: Your MySQL username.
password: Your MySQL password.

Creating a Database in Node.js

You can create a MySQL database using the CREATE DATABASE SQL statement.

Example: Create a New Database

const mysql = require('mysql');

// Create a connection to MySQL server
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
});

// Connect to the MySQL server
connection.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL:', err);
        return;
    }
    console.log('Connected to MySQL successfully!');

    // SQL query to create a new database
    const createDbQuery = 'CREATE DATABASE nodejs_demo';

    // Execute the query
    connection.query(createDbQuery, (err, result) => {
        if (err) {
            console.error('Error creating database:', err);
            return;
        }
        console.log('Database created successfully!');
    });

    // Close the connection
    connection.end();
});

Output:

Connected to MySQL successfully!  
Database created successfully!

Check If a Database Already Exists

To avoid errors, you can check if a database exists before creating it.

Example: Create Database If It Doesn’t Exist

const mysql = require('mysql');

// Create a connection to MySQL server
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
});

// Connect to the MySQL server
connection.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL:', err);
        return;
    }
    console.log('Connected to MySQL successfully!');

    // SQL query to check and create database
    const createDbQuery = 'CREATE DATABASE IF NOT EXISTS nodejs_demo';

    // Execute the query
    connection.query(createDbQuery, (err, result) => {
        if (err) {
            console.error('Error creating database:', err);
            return;
        }
        console.log('Database is ready!');
    });

    // Close the connection
    connection.end();
});

Creating a Database with Character Set

Sometimes, you may need to specify the character set and collation for your database.

Example: Create a Database with UTF-8 Encoding

const createDbQuery = `
    CREATE DATABASE IF NOT EXISTS nodejs_demo
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci
`;

connection.query(createDbQuery, (err, result) => {
    if (err) {
        console.error('Error creating database:', err);
        return;
    }
    console.log('Database created with UTF-8 encoding!');
});

Explanation:

CHARACTER SET utf8mb4: Ensures support for all Unicode characters, including emojis.
COLLATE utf8mb4_general_ci: Specifies how text is compared and sorted.

Using Environment Variables for Security

It’s a good practice to avoid hardcoding sensitive information like database credentials in your code. Use environment variables instead.

Install the dotenv package:

npm install dotenv

Create a .env file in your project directory:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=

Load the environment variables in your script:

require('dotenv').config();

const connection = mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
});

Real-World Use Case

Imagine building a SaaS application where each new client gets their own database. Automating database creation ensures consistency and saves time during deployment.

Example: Dynamic Database Creation

const createDatabaseForClient = (clientName) => {
    const dbName = `client_${clientName}`;

    connection.query(`CREATE DATABASE IF NOT EXISTS ${dbName}`, (err, result) => {
        if (err) {
            console.error('Error creating database for client:', err);
            return;
        }
        console.log(`Database created for client: ${clientName}`);
    });
};

// Example usage
createDatabaseForClient('john');

MySQL Create Database

Why Create a MySQL Database in Node.js?