Node.js MySQL: Create a Table

Welcome to The Coding College! In this tutorial, you’ll learn how to create a MySQL table using Node.js. Tables are the backbone of relational databases, organizing your data into structured rows and columns.

Prerequisites

Before you start, ensure you have the following:

  1. Node.js Installed: Download Node.js.
  2. MySQL Installed: Download MySQL.
  3. Database Ready: A MySQL database to work with. Follow our Node.js MySQL Create Database tutorial if needed.

Step 1: Install the MySQL Module

Install the official MySQL module for Node.js by running:

npm install mysql

This module allows your Node.js application to interact with a MySQL database.

Step 2: Connect to the MySQL Database

Create a file named createTable.js and set up a connection to your database:

const mysql = require('mysql');

// Create a connection to the MySQL database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your-password',
  database: 'mydatabase', // Replace with your database name
});

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

Step 3: Define the Table Creation Query

Write an SQL query to create a table. For example, let’s create a table named users with columns id, name, and email:

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
  )
`;

Step 4: Execute the Query

Use the query() method of the MySQL module to execute the table creation query:

connection.query(createTableQuery, (err, result) => {
  if (err) throw err;
  console.log('Table "users" created successfully or already exists.');
});

Step 5: Close the Connection

Close the database connection after the operation is complete:

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 to create a MySQL table:

const mysql = require('mysql');

// Create a connection to the MySQL database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your-password',
  database: 'mydatabase', // Replace with your database name
});

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

// Query to create a table
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
  )
`;

// Execute the query
connection.query(createTableQuery, (err, result) => {
  if (err) throw err;
  console.log('Table "users" created successfully 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 6: Run the Script

Run the script using Node.js:

node createTable.js

If successful, the output will indicate that the table was created or already exists:

Connected to MySQL database!  
Table "users" created successfully or already exists.  
Connection closed.

Customizing Your Table

You can modify the CREATE TABLE query to define tables tailored to your needs. For instance:

Example: Products Table

const createProductsTable = `
  CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
  )
`;

Best Practices

  1. Use Environment Variables: Secure database credentials using .env files.
  2. Error Handling: Always handle errors to prevent application crashes.
  3. Parameterize Queries: Use placeholders (?) to protect against SQL injection.

Conclusion

Creating a MySQL table using Node.js is a foundational skill for any backend developer. With the steps outlined in this tutorial, you can define and create tables programmatically to store your application’s data.

At The Coding College, we’re committed to providing top-notch tutorials to help you excel in coding. Stay tuned for more guides and expand your development expertise!

Leave a Comment