Node.js MySQL Create Database

In Node.js, you can easily create a MySQL database using the mysql2 package. The process involves connecting to the MySQL server, running a query to create the database, and handling any potential errors.

 

Key Features of Node.js MySQL Database Creation

1. Create a Database: Use SQL queries to create a new database.
2. Handle Errors: Properly manage errors that may occur during the database creation process.
3. Asynchronous Queries: Utilize callback or promise-based APIs to handle asynchronous operations in Node.js.

 

Step 1 Prerequisites

Ensure MySQL is installed and running on your system. You also need to install the mysql2 package in your Node.js project.

npm install mysql2

Step 2 Create Database with Callback

To create a new database, you can execute an SQL CREATE DATABASE query after establishing a connection to the MySQL server.

Example Code

const mysql = require('mysql2');

// Create a connection to MySQL server (not specifying database)
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
});

// Connect to MySQL server
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }
  console.log('Connected to MySQL as id ' + connection.threadId);

  // Create a new database
  connection.query('CREATE DATABASE testdb', (err, results) => {
    if (err) {
      console.error('Error creating database:', err.stack);
      return;
    }
    console.log('Database created successfully:', results);
  });

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

Output:

Connected to MySQL as id 1
Database created successfully: 
{ fieldCount: 0, affectedRows: 1, insertId: 0, serverStatus: 2, warningCount: 0, message: '', protocol41: true, changedRows: 0 }

In this example, the CREATE DATABASE testdb command creates a new database named testdb on the MySQL server.

Step 3 Create Database with Promises

You can also use mysql2's promise-based API to create a database more easily using async/await.

Example Code with Promises

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

async function createDatabase() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',        // Replace with your MySQL username
    password: '',        // Replace with your MySQL password
  });

  try {
    // Create a new database
    const [results] = await connection.execute('CREATE DATABASE testdb');
    console.log('Database created successfully:', results);
  } catch (err) {
    console.error('Error creating database:', err);
  } finally {
    await connection.end();
  }
}

createDatabase().catch(console.error);

Output:

Database created successfully: 
{ fieldCount: 0, affectedRows: 1, insertId: 0, serverStatus: 2, warningCount: 0, message: '', protocol41: true, changedRows: 0 }

Step 4 Checking if the Database Exists

To prevent errors, you may want to check if the database already exists before creating it.

Example Code to Check Database Existence

const mysql = require('mysql2');

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

connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }
  console.log('Connected to MySQL as id ' + connection.threadId);

  // Check if the database exists
  connection.query('SHOW DATABASES LIKE "testdb"', (err, results) => {
    if (err) {
      console.error('Error checking database:', err.stack);
      return;
    }

    if (results.length === 0) {
      // Create the database if it doesn't exist
      connection.query('CREATE DATABASE testdb', (err, results) => {
        if (err) {
          console.error('Error creating database:', err.stack);
          return;
        }
        console.log('Database created successfully:', results);
      });
    } else {
      console.log('Database "testdb" already exists.');
    }
  });

  connection.end();
});

Step 5 Error Handling and Cleanup

Make sure to handle errors properly and close the database connection after the operation to avoid leaving open connections.

Example Code with Proper Error Handling

const mysql = require('mysql2');

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

connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  console.log('Connected to MySQL as id ' + connection.threadId);

  // Try to create a database and handle errors
  connection.query('CREATE DATABASE testdb', (err, results) => {
    if (err) {
      console.error('Error creating database:', err.stack);
    } else {
      console.log('Database created successfully:', results);
    }

    // Close the connection
    connection.end((err) => {
      if (err) {
        console.error('Error closing connection:', err.stack);
      } else {
        console.log('Connection closed');
      }
    });
  });
});

 

Summary

Creating a MySQL database in Node.js can be easily accomplished with the mysql2 package. Whether you prefer using callbacks or promises, you can execute SQL commands to create databases and handle any potential errors. Always remember to check for existing databases before creating one, and ensure proper cleanup by closing the connection when done.