Node.js MySQL Create Table

In Node.js, you can create a table in a MySQL database using the mysql2 package. The process involves connecting to a MySQL database, running a CREATE TABLE SQL query, and managing errors.

 

Key Features of Node.js MySQL Table Creation

1. Create a Table: Use SQL queries to define and create a new table.
2. Define Columns and Data Types: Specify column names and data types such as INT, VARCHAR, DATE, etc.
3. Handle Errors: Properly manage errors during table creation.
4. Asynchronous Operations: Utilize callback or promise-based APIs to handle the asynchronous nature of Node.js operations.

 

Step 1 Prerequisites

Ensure MySQL is installed and running on your system, and that the mysql2 package is installed in your Node.js project.

npm install mysql2

Step 2 Create a Table with Callback

After establishing a connection to MySQL, you can use a CREATE TABLE query to define the structure of a new table.

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'     // Replace with your database name
});

// Connect to MySQL
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 table
  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.stack);
      return;
    }
    console.log('Table created successfully:', results);
  });

  // Close the connection after the operation
  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

You can also use the promise-based API of mysql2 to simplify handling asynchronous operations.

Example Code with Promises

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

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

  try {
    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
      )
    `;
    const [results] = await connection.execute(createTableQuery);
    console.log('Table created successfully:', results);
  } catch (err) {
    console.error('Error creating table:', err);
  } finally {
    await connection.end();
  }
}

createTable().catch(console.error);

Output:

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

Step 4 Modifying Table Structure

If you need to add columns or make other changes to an existing table, you can use the ALTER TABLE SQL 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) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  const alterTableQuery = 'ALTER TABLE users ADD COLUMN age INT';
  connection.query(alterTableQuery, (err, results) => {
    if (err) {
      console.error('Error altering table:', err.stack);
      return;
    }
    console.log('Column added successfully:', results);
  });

  connection.end();
});

Step 5 Error Handling and Cleanup

Proper error handling and connection cleanup are essential. Always ensure that the connection is closed after the operation.

Example Code with Proper Error Handling

const mysql = require('mysql2');

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

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

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

  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.stack);
    } else {
      console.log('Table created successfully:', results);
    }

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

 

Summary

Creating a table in a MySQL database with Node.js is simple and can be done using the mysql2 package. You can either use callbacks or promises to handle asynchronous operations. Always ensure to handle errors effectively and close the connection after completing the operation to maintain good practices.