Node.js MySQL Create Table

In Node.js, creating a database table is the fundamental step in defining your application's data structure. Think of a table as a blueprint that dictates how your data—like users, products, or orders—will be stored and organized. Using the mysql2 package, you can send SQL commands from your JavaScript code directly to your MySQL server. This allows you to automate database setup and manage your schema programmatically.

Key Features of Node.js MySQL Table Creation

1. Schema Definition: Use standard SQL CREATE TABLE syntax to define the architecture of your data storage.
2. Data Constraints: Precisely define column types (like INT for numbers or VARCHAR for text) and constraints like NOT NULL or UNIQUE to ensure data integrity.
3. Primary Keys: Automatically generate unique identifiers for every row using AUTO_INCREMENT, which is essential for relational database mapping.
4. Flexible Control Flow: Choose between traditional callbacks for simpler scripts or modern async/await syntax for cleaner, more readable application code.

Step 1 Prerequisites

Before writing your script, ensure you have a MySQL server running and a database already created (e.g., testdb). You will also need to initialize your Node.js project and install the driver:

npm install mysql2

Step 2 Create a Table with Callback

The callback pattern is the classic way of handling asynchronous operations in Node.js. It’s useful for quick scripts, though it can become difficult to manage in larger applications (a situation often called "callback hell"). In this example, we define a users table structure and execute it through the query() method.

Example Code

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // The database must already exist
});

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

  // Define the SQL query
  // We use AUTO_INCREMENT for the ID so MySQL handles the numbering for us
  const createTableQuery = `
    CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `;

  connection.query(createTableQuery, (err, results) => {
    if (err) {
      console.error('Error creating table:', err.message);
      return;
    }
    console.log('Table created successfully:', results);
  });

  // Always close the connection to prevent memory leaks
  connection.end();
});

Output:

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

Step 3 Create a Table with Promises

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

Example Code with Promises

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

async function createTable() {
  // Using the promise-based connection
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'testdb'
  });

  try {
    // Adding 'IF NOT EXISTS' is a best practice for setup scripts
    const createTableQuery = `
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `;
    
    const [results] = await connection.execute(createTableQuery);
    console.log('Table check/creation completed:', results);
  } catch (err) {
    console.error('Database Operation Failed:', err.message);
  } finally {
    // The finally block ensures the connection closes even if an error occurs
    await connection.end();
    console.log('Connection closed.');
  }
}

createTable();

Output:

Table check/creation completed: 
{ fieldCount: 0, affectedRows: 0, insertId: 0, serverStatus: 2, warningCount: 0, message: '', protocol41: true, changedRows: 0 }
Connection closed.

Step 4 Modifying Table Structure

Real-world applications evolve. You might start with a users table and later realize you need to store the user's age or a profile picture URL. Instead of deleting the table and losing your data, you use the ALTER TABLE command.

Example Code to Add a Column

const mysql = require('mysql2');

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

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

  // Real-world scenario: Adding a column for user age
  const alterTableQuery = 'ALTER TABLE users ADD COLUMN IF NOT EXISTS age INT';
  
  connection.query(alterTableQuery, (err, results) => {
    if (err) {
      console.error('Error altering table:', err.message);
    } else {
      console.log('Table updated successfully.');
    }
    connection.end();
  });
});

Step 5 Error Handling and Cleanup

Robust error handling is what separates a demo script from production-ready code. You must account for connection timeouts, incorrect credentials, and SQL syntax errors. Furthermore, managing the connection lifecycle is critical; leaving connections open can eventually crash your database server by exhausting the "max connections" limit.

Example Code with Proper Error Handling

const mysql = require('mysql2');

const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'testdb'
};

const connection = mysql.createConnection(dbConfig);

connection.connect((err) => {
  if (err) {
    return console.error('FAILED TO CONNECT:', err.message);
  }

  const sql = `CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    amount DECIMAL(10, 2)
  )`;

  connection.query(sql, (err, results) => {
    if (err) {
      console.error('QUERY ERROR:', err.message);
    } else {
      console.log('Orders table is ready.');
    }

    // Always attempt to close the connection, regardless of query success
    connection.end((closeErr) => {
      if (closeErr) console.error('Error while closing connection:', closeErr.message);
    });
  });
});

Summary

Creating and managing MySQL tables in Node.js is a straightforward process once you understand the relationship between the JavaScript environment and SQL queries. By using the mysql2 package, you gain the flexibility of both callbacks and promises. Remember to always sanitize your data, handle errors gracefully using try/catch or callback checks, and use IF NOT EXISTS to keep your setup scripts reliable across different environments.