Node.js MySQL: Create a Database

Welcome to The Coding College! In this tutorial, you’ll learn how to create a MySQL database using Node.js. This is a fundamental step when building dynamic web applications that rely on relational data storage.

Prerequisites

Before starting, ensure you have:

  1. Node.js Installed: Download Node.js.
  2. MySQL Installed: Download MySQL.
  3. Basic Knowledge of SQL Queries.

Step 1: Install the MySQL Node.js Module

First, you need to install the MySQL package for Node.js. Run the following command in your project directory:

npm install mysql

This module provides functions to connect, interact with, and manage MySQL databases from your Node.js application.

Step 2: Set Up the MySQL Connection

Create a file named createDatabase.js and initialize a MySQL connection:

const mysql = require('mysql');

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

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

Step 3: Write the Query to Create a Database

Use the CREATE DATABASE SQL statement to create a new database:

const createDatabaseQuery = 'CREATE DATABASE IF NOT EXISTS mydatabase';

// Execute the query
connection.query(createDatabaseQuery, (err, result) => {
  if (err) throw err;
  console.log('Database created or already exists.');
});

The IF NOT EXISTS clause ensures that the query doesn’t throw an error if the database already exists.

Step 4: Close the Connection

Once the operation is complete, close the connection to free up resources:

connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Full Code

Here’s the complete script for creating a MySQL database:

const mysql = require('mysql');

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

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

// Query to create a database
const createDatabaseQuery = 'CREATE DATABASE IF NOT EXISTS mydatabase';

// Execute the query
connection.query(createDatabaseQuery, (err, result) => {
  if (err) throw err;
  console.log('Database created or already exists.');
});

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

Step 5: Run the Script

Run the script using Node.js:

node createDatabase.js

If successful, the output will indicate that the database has been created or already exists:

Connected to MySQL server!  
Database created or already exists.  
Connection closed.

Troubleshooting

  1. Access Denied Error:
    • Verify your username and password in the connection settings.
    • Ensure your MySQL server is running.
  2. MySQL Server Not Found:
    • Confirm that the host parameter is set correctly (use 127.0.0.1 for localhost).
  3. Connection Timeout:
    • Check your MySQL server settings and ensure it allows connections from your Node.js app.

Enhancing the Script

  • Environment Variables: Use .env files to store credentials securely.
  • Connection Pools: For better performance in production, use connection pooling.

Conclusion

Creating a MySQL database with Node.js is straightforward and essential for database-driven applications. By following this guide, you’ve learned how to connect Node.js to MySQL and programmatically create a database.

At The Coding College, we provide practical coding tutorials to help you succeed in your development journey. Bookmark our website for more valuable content!

Leave a Comment