MySQL Create Table

After creating a database in MySQL, the next step is to define tables to store your data. Tables organize data into rows and columns, making it easier to retrieve and manipulate information.

Why Create Tables in MySQL Using Node.js?

• Automation: You can programmatically set up tables as part of your application initialization.
• Efficiency: Managing tables directly from your Node.js code eliminates the need to switch between your application and a database management tool.
• Dynamic Table Creation: You can create tables based on user input or specific use cases.

Prerequisites

MySQL Installed: Ensure MySQL is installed and running on your system.

Node.js Installed: Install Node.js if you haven’t already.

NPM Package: Install the MySQL package using:

npm install mysql

Setting Up the Node.js Project

Initialize the Project:

npm init -y

Install the MySQL Package:

npm install mysql

Create a File: Create a file named app.js where you’ll write your code.

Connecting to the MySQL Database

Before creating tables, you need to connect to an existing MySQL database.

Example: Connect to MySQL

const mysql = require('mysql');

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

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

Creating a Table in MySQL

To create a table in MySQL, you use the CREATE TABLE SQL statement. In Node.js, you can execute this statement programmatically.

Example: Create a Table

const createTableQuery = `
    CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        age INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
`;

// Execute the query
connection.query(createTableQuery, (err, result) => {
    if (err) {
        console.error('Error creating table:', err);
        return;
    }
    console.log('Table created successfully!');
});

Explanation:

• id: An auto-incrementing primary key to uniquely identify each row.
• name: A column to store user names (cannot be null).
• email: A column to store user emails (must be unique and cannot be null).
• age: A column to store the user’s age.
• created_at: A timestamp column that automatically records the row’s creation time.

Checking If a Table Exists Before Creating It

To avoid errors, you can check if the table exists before creating it.

Example: Create Table If It Doesn’t Exist

const createTableQuery = `
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        age INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
`;

connection.query(createTableQuery, (err, result) => {
    if (err) {
        console.error('Error creating table:', err);
        return;
    }
    console.log('Table is ready!');
});

Creating Tables with Relationships

For advanced applications, you may need to define relationships between tables (e.g., foreign keys).

Example: Create Two Related Tables

// Create a "departments" table
const createDepartmentsTable = `
    CREATE TABLE IF NOT EXISTS departments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL
    )
`;

connection.query(createDepartmentsTable, (err, result) => {
    if (err) {
        console.error('Error creating departments table:', err);
        return;
    }
    console.log('Departments table created successfully!');
});

// Create an "employees" table with a foreign key to "departments"
const createEmployeesTable = `
    CREATE TABLE IF NOT EXISTS employees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        department_id INT,
        FOREIGN KEY (department_id) REFERENCES departments(id)
    )
`;

connection.query(createEmployeesTable, (err, result) => {
    if (err) {
        console.error('Error creating employees table:', err);
        return;
    }
    console.log('Employees table created successfully!');
});

Explanation:

• The departments table stores department information.
• The employees table includes a department_id column that references the id column in the departments table.

Using Environment Variables for Security

Store database credentials in environment variables to keep them secure.

Install the dotenv Package:

npm install dotenv

Create a .env File:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_NAME=nodejs_demo

Load Environment Variables in Your Script:

require('dotenv').config();

const connection = mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
});

Real-World Use Case

Imagine you’re building an e-commerce application. You could create tables like:

1. Products Table: Stores product details.
2. Users Table: Stores customer information.
3. Orders Table: Tracks customer orders.

Example: Create an Orders Table

const createOrdersTable = `
    CREATE TABLE IF NOT EXISTS orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        product_id INT NOT NULL,
        quantity INT DEFAULT 1,
        order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    )
`;

connection.query(createOrdersTable, (err, result) => {
    if (err) {
        console.error('Error creating orders table:', err);
        return;
    }
    console.log('Orders table created successfully!');
});