Node.js MySQL Create Database

When building data-driven applications, the first step is often setting up your storage environment. In Node.js, you can programmatically create a MySQL database using the mysql2 package. This is particularly useful for automation scripts, installers, or multi-tenant applications where you need to spin up new isolated environments on the fly.

Key Features of Node.js MySQL Database Creation

1. Programmatic Creation: Use standard SQL CREATE DATABASE queries directly from your JavaScript code.
2. Robust Error Handling: Manage scenarios where the database already exists or where credentials fail.
3. Asynchronous Flow: Node.js handles database I/O without blocking the main thread, keeping your application responsive.

Step 1 Prerequisites

Before writing your script, ensure you have a MySQL server running locally or remotely. You will need to install the mysql2 driver in your project directory using npm:

npm install mysql2

Step 2 Create Database with Callback

The traditional way to interact with MySQL in Node.js is through the callback pattern. This involves connecting to the server instance—without specifying a database name—and then executing the creation query.

Example Code

const mysql = require('mysql2');

// Create a connection to the MySQL server
// Note: We don't specify a 'database' here because it hasn't been created yet!
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Use your MySQL username
  password: 'your_password', // Use your MySQL password
});

// Establish the connection
connection.connect((err) => {
  if (err) {
    console.error('Connection failed:', err.message);
    return;
  }
  console.log('Connected to MySQL server.');

  // The SQL command to create a new database
  const sql = 'CREATE DATABASE my_new_app_db';
  
  connection.query(sql, (err, results) => {
    if (err) {
      console.error('Error creating database:', err.message);
      return;
    }
    console.log('Database "my_new_app_db" created successfully!');
    console.log('Result metadata:', results);
  });

  // Always close the connection when finished
  connection.end();
});

Expected Output:

Connected to MySQL server.
Database "my_new_app_db" created successfully!
{ fieldCount: 0, affectedRows: 1, insertId: 0, serverStatus: 2, ... }

Step 3 Create Database with Promises

Modern Node.js development relies heavily on async/await. This makes your code look synchronous and much easier to read, especially when you need to perform multiple database operations in sequence.

Example Code with Promises

const mysql = require('mysql2/promise');

async function initializeProjectDatabase() {
  let connection;
  try {
    // Using the promise-based API
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
    });

    console.log('Connected to server...');

    // Execute the creation query
    const [results] = await connection.execute('CREATE DATABASE IF NOT EXISTS dev_db');
    
    console.log('Database status:', results.warningCount > 0 ? 'Already exists' : 'Created successfully');
  } catch (err) {
    console.error('An error occurred:', err.message);
  } finally {
    // The finally block ensures the connection closes even if an error occurs
    if (connection) await connection.end();
  }
}

initializeProjectDatabase();

Step 4 Checking if the Database Exists

Sometimes you need to perform logic based on whether a database exists (for example, if you need to migrate data only to new installations). You can query the information_schema or use SHOW DATABASES.

Example Code to Check Database Existence

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
});

connection.connect((err) => {
  if (err) throw err;

  const dbName = 'inventory_system';

  // Check the system schema for the database name
  connection.query(`SHOW DATABASES LIKE "${dbName}"`, (err, results) => {
    if (err) throw err;

    if (results.length === 0) {
      console.log(`${dbName} does not exist. Creating it now...`);
      connection.query(`CREATE DATABASE ${dbName}`, (err) => {
        if (err) throw err;
        console.log('Database created.');
      });
    } else {
      console.log(`Database "${dbName}" already exists. Skipping creation.`);
    }
    
    connection.end();
  });
});

Step 5 Error Handling and Cleanup

In a production environment, database operations are prone to network timeouts or permission issues. Proper cleanup ensures you don't leak memory or exhaust the MySQL connection pool.

Example Code with Proper Error Handling

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  connectTimeout: 10000 // 10 seconds timeout
});

connection.connect((err) => {
  if (err) {
    return console.error('Could not connect to MySQL. Is the server running?');
  }

  // Handle errors specifically for the query
  connection.query('CREATE DATABASE billing_logs', (err, results) => {
    if (err) {
      if (err.errno === 1007) {
        console.warn('Warning: Database already exists.');
      } else {
        console.error('Query Error:', err.code, err.message);
      }
    } else {
      console.log('Billing database initialized.');
    }

    // Always attempt to close the connection
    connection.end((closeErr) => {
      if (closeErr) console.error('Error while closing connection:', closeErr);
      else console.log('Session closed safely.');
    });
  });
});

Summary

Creating a MySQL database in Node.js is a straightforward process when using the mysql2 package. By understanding the difference between the callback and promise-based approaches, you can write code that fits your project's architecture. Remember to use CREATE DATABASE IF NOT EXISTS to avoid runtime errors and always close your connections to maintain a healthy, high-performance application.