พื้นฐานการพัฒนา RESTful API ด้วย Node.js, Express และ MySQL

-- สร้างตาราง Users
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fname VARCHAR(100) NOT NULL,
  lname VARCHAR(100) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  avatar VARCHAR(255) NULL
);

-- สร้างตาราง Products
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price INT NOT NULL
);

-- สร้างตาราง Orders
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- สร้างตาราง Order_Product (ตารางเชื่อมระหว่าง Orders และ Products)
CREATE TABLE order_product (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE table_name (
  column_name data_type constraints
);

FOREIGN KEY (column_name) REFERENCES other_table(id)
ON DELETE CASCADE   -- ลบข้อมูลที่เกี่ยวข้องเมื่อข้อมูลหลักถูกลบ

-- ตาราง users เก็บข้อมูลผู้ใช้
id INT AUTO_INCREMENT PRIMARY KEY  -- รหัสผู้ใช้ (เพิ่มอัตโนมัติ)
username VARCHAR(50) NOT NULL UNIQUE  -- ชื่อผู้ใช้ (ไม่ซ้ำกัน)

-- ตาราง orders เก็บคำสั่งซื้อ
user_id INT NOT NULL
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
-- user_id เชื่อมโยงกับ id ในตาราง users
-- เมื่อลบ user ข้อมูลคำสั่งซื้อจะถูกลบไปด้วย

-- เพิ่มข้อมูล Users
INSERT INTO users (id, fname, lname, username, email, avatar) VALUES
(1, 'Karn', 'Yong', 'karn.yong', 'karn.yong@melivecode.com', 'https://www.melivecode.com/users/1.png'),
(2, 'Parkpoom', 'Chaisiriprasert', 'parkpoom', 'parkpoom@melivecode.com', 'https://www.melivecode.com/users/2.png');

-- เพิ่มข้อมูล Products
INSERT INTO products (id, name, price) VALUES
(1, 'Laptop', 39999),
(2, 'Smartphone', 19999),
(3, 'Monitor', 7999);

-- เพิ่มข้อมูล Orders
INSERT INTO orders (id, user_id) VALUES
(1, 1),
(2, 2),
(3, 1);

-- เพิ่มข้อมูล Order_Product (เชื่อมโยงคำสั่งซื้อกับสินค้า)
INSERT INTO order_product (order_id, product_id) VALUES
-- คำสั่งซื้อ #1 (Karn) มี Laptop และ Smartphone
(1, 1),
(1, 2),
-- คำสั่งซื้อ #2 (Parkpoom) มี Smartphone
(2, 2),
-- คำสั่งซื้อ #3 (Karn) มี Smartphone และ Monitor
(3, 2),
(3, 3);

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

INSERT INTO users (fname, lname, email) VALUES
('Karn', 'Yong', 'karn@example.com'),
('Parkpoom', 'Chaisiriprasert', 'parkpoom@example.com');

// src/db.js
const mysql = require('mysql2/promise');

// สร้าง connection pool
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'express_mysql_db',
  port: process.env.DB_PORT || 3306,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

module.exports = pool;

SELECT * FROM users;

SELECT * FROM users WHERE id = 1;

SELECT fname, lname, email FROM users;

INSERT INTO users (fname, lname, username, email) VALUES ('Anna', 'Dee', 'anna.dee', 'Anna@example.com');

const sql = 'INSERT INTO users (fname, lname, username, email) VALUES (?, ?, ?, ?)';
await pool.query(sql, ['Anna', 'Dee', 'anna.dee', 'Anna@example.com']);

UPDATE users SET fname = 'Jane', email = 'jane@example.com' WHERE id = 3;

const sql = 'UPDATE users SET fname = ?, email = ? WHERE id = ?';
await pool.query(sql, ['Jane', 'jane@example.com', 1]);

DELETE FROM users WHERE id = 3;

await pool.query('DELETE FROM users WHERE id = ?', [3]);

SELECT users.fname, orders.id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

SELECT o.id, u.fname, p.name, p.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_product op ON o.id = op.order_id
JOIN products p ON op.product_id = p.id;

const sql = `SELECT * FROM users WHERE id = ${userId}`;
// ถ้า userId = "1; DROP TABLE users;" จะเกิดอันตราย

const sql = 'SELECT * FROM users WHERE id = ?';
await pool.query(sql, [userId]);
// ตัว ? จะถูกแทนที่ด้วยค่าที่ปลอดภัย

const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const pool = require('./src/db'); // Import database connection
const app = express();
const port = 5000; // Define port

// Middleware
app.use(cors());
app.use(bodyParser.json());

// Test route
app.get('/', (req, res) => {
  res.send('Hello! RESTful API is ready to use with MySQL');
});

// Add API endpoints below this line

// Start server
app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});

node index.js

// User Endpoints
// Read All Users
app.get('/users', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM users');
    res.json(rows);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching users', error: error.message });
  }
});

// Read One User
app.get('/users/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
    if (rows.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    res.json(rows[0]);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching user', error: error.message });
  }
});

// Create User
app.post('/users', async (req, res) => {
  try {
    const { fname, lname, username, email, avatar } = req.body;
    const sql = 'INSERT INTO users (fname, lname, username, email, avatar) VALUES (?, ?, ?, ?, ?)';
    const [result] = await pool.query(sql, [fname, lname, username, email, avatar || null]);

    const [newUser] = await pool.query('SELECT * FROM users WHERE id = ?', [result.insertId]);
    res.status(201).json(newUser[0]);
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ message: 'Username or email already exists' });
    }
    res.status(500).json({ message: 'Error creating user', error: error.message });
  }
});

// Update User
app.put('/users/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const { fname, lname, username, email, avatar } = req.body;

    // Check if user exists
    const [existing] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }

    const sql = 'UPDATE users SET fname = ?, lname = ?, username = ?, email = ?, avatar = ? WHERE id = ?';
    await pool.query(sql, [fname, lname, username, email, avatar || null, id]);

    const [updated] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
    res.status(200).json(updated[0]);
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ message: 'Username or email already exists' });
    }
    res.status(500).json({ message: 'Error updating user', error: error.message });
  }
});

// Delete User
app.delete('/users/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);

    // Check if user exists
    const [existing] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }

    await pool.query('DELETE FROM users WHERE id = ?', [id]);
    res.status(200).json({ message: `User with ID ${id} deleted successfully` });
  } catch (error) {
    res.status(500).json({ message: 'Error deleting user', error: error.message });
  }
});

// Product Endpoints
// Read All Products
app.get('/products', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM products');
    res.json(rows);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching products', error: error.message });
  }
});

// Read One Product
app.get('/products/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const [rows] = await pool.query('SELECT * FROM products WHERE id = ?', [id]);
    if (rows.length === 0) {
      return res.status(404).json({ message: 'Product not found' });
    }
    res.json(rows[0]);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching product', error: error.message });
  }
});

// Create Product
app.post('/products', async (req, res) => {
  try {
    const { name, price } = req.body;
    const sql = 'INSERT INTO products (name, price) VALUES (?, ?)';
    const [result] = await pool.query(sql, [name, price]);

    const [newProduct] = await pool.query('SELECT * FROM products WHERE id = ?', [result.insertId]);
    res.status(201).json(newProduct[0]);
  } catch (error) {
    res.status(500).json({ message: 'Error creating product', error: error.message });
  }
});

// Update Product
app.put('/products/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const { name, price } = req.body;

    // Check if product exists
    const [existing] = await pool.query('SELECT * FROM products WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ message: 'Product not found' });
    }

    const sql = 'UPDATE products SET name = ?, price = ? WHERE id = ?';
    await pool.query(sql, [name, price, id]);

    const [updated] = await pool.query('SELECT * FROM products WHERE id = ?', [id]);
    res.status(200).json(updated[0]);
  } catch (error) {
    res.status(500).json({ message: 'Error updating product', error: error.message });
  }
});

// Delete Product
app.delete('/products/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);

    // Check if product exists
    const [existing] = await pool.query('SELECT * FROM products WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ message: 'Product not found' });
    }

    await pool.query('DELETE FROM products WHERE id = ?', [id]);
    res.status(200).json({ message: `Product with ID ${id} deleted successfully` });
  } catch (error) {
    res.status(500).json({ message: 'Error deleting product', error: error.message });
  }
});

// Order Endpoints
// Read All Orders
app.get('/orders', async (req, res) => {
  try {
    const [orders] = await pool.query(`
      SELECT
        o.id,
        o.user_id,
        o.created_at,
        u.id as user_id,
        u.fname,
        u.lname,
        u.username,
        u.email,
        u.avatar
      FROM orders o
      JOIN users u ON o.user_id = u.id
    `);

    // Get products for each order
    for (const order of orders) {
      const [products] = await pool.query(`
        SELECT p.*
        FROM products p
        JOIN order_product op ON p.id = op.product_id
        WHERE op.order_id = ?
      `, [order.id]);
      order.products = products;
      order.user = {
        id: order.user_id,
        fname: order.fname,
        lname: order.lname,
        username: order.username,
        email: order.email,
        avatar: order.avatar
      };
      delete order.fname;
      delete order.lname;
      delete order.username;
      delete order.email;
      delete order.avatar;
    }

    res.json(orders);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching orders', error: error.message });
  }
});

// Read One Order
app.get('/orders/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const [orders] = await pool.query(`
      SELECT
        o.id,
        o.user_id,
        o.created_at,
        u.fname,
        u.lname,
        u.username,
        u.email,
        u.avatar
      FROM orders o
      JOIN users u ON o.user_id = u.id
      WHERE o.id = ?
    `, [id]);

    if (orders.length === 0) {
      return res.status(404).json({ message: 'Order not found' });
    }

    const order = orders[0];

    // Get products for the order
    const [products] = await pool.query(`
      SELECT p.*
      FROM products p
      JOIN order_product op ON p.id = op.product_id
      WHERE op.order_id = ?
    `, [id]);

    order.products = products;
    order.user = {
      id: order.user_id,
      fname: order.fname,
      lname: order.lname,
      username: order.username,
      email: order.email,
      avatar: order.avatar
    };
    delete order.fname;
    delete order.lname;
    delete order.username;
    delete order.email;
    delete order.avatar;

    res.json(order);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching order', error: error.message });
  }
});

// Create Order
app.post('/orders', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    const { userId, productIds } = req.body;

    // Check if user exists
    const [users] = await connection.query('SELECT * FROM users WHERE id = ?', [userId]);
    if (users.length === 0) {
      await connection.rollback();
      return res.status(400).json({ message: 'Invalid user ID' });
    }

    // Create order
    const [orderResult] = await connection.query('INSERT INTO orders (user_id) VALUES (?)', [userId]);
    const orderId = orderResult.insertId;

    // Add products to order
    for (const productId of productIds) {
      await connection.query('INSERT INTO order_product (order_id, product_id) VALUES (?, ?)', [orderId, productId]);
    }

    await connection.commit();

    // Get complete order data
    const [orders] = await pool.query(`
      SELECT
        o.id,
        o.user_id,
        o.created_at,
        u.fname,
        u.lname,
        u.username,
        u.email,
        u.avatar
      FROM orders o
      JOIN users u ON o.user_id = u.id
      WHERE o.id = ?
    `, [orderId]);

    const [products] = await pool.query(`
      SELECT p.*
      FROM products p
      JOIN order_product op ON p.id = op.product_id
      WHERE op.order_id = ?
    `, [orderId]);

    const order = orders[0];
    order.products = products;
    order.user = {
      id: order.user_id,
      fname: order.fname,
      lname: order.lname,
      username: order.username,
      email: order.email,
      avatar: order.avatar
    };
    delete order.fname;
    delete order.lname;
    delete order.username;
    delete order.email;
    delete order.avatar;

    res.status(201).json(order);
  } catch (error) {
    await connection.rollback();
    if (error.code === 'ER_NO_REFERENCED_ROW_2') {
      return res.status(400).json({ message: 'Invalid user ID or product ID' });
    }
    res.status(500).json({ message: 'Error creating order', error: error.message });
  } finally {
    connection.release();
  }
});

// Update Order
app.put('/orders/:id', async (req, res) => {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    const id = parseInt(req.params.id);
    const { userId, productIds } = req.body;

    // Check if order exists
    const [existing] = await connection.query('SELECT * FROM orders WHERE id = ?', [id]);
    if (existing.length === 0) {
      await connection.rollback();
      return res.status(404).json({ message: 'Order not found' });
    }

    // Update order user
    await connection.query('UPDATE orders SET user_id = ? WHERE id = ?', [userId, id]);

    // Delete old products and add new ones
    await connection.query('DELETE FROM order_product WHERE order_id = ?', [id]);
    for (const productId of productIds) {
      await connection.query('INSERT INTO order_product (order_id, product_id) VALUES (?, ?)', [id, productId]);
    }

    await connection.commit();

    // Get complete order data
    const [orders] = await pool.query(`
      SELECT
        o.id,
        o.user_id,
        o.created_at,
        u.fname,
        u.lname,
        u.username,
        u.email,
        u.avatar
      FROM orders o
      JOIN users u ON o.user_id = u.id
      WHERE o.id = ?
    `, [id]);

    const [products] = await pool.query(`
      SELECT p.*
      FROM products p
      JOIN order_product op ON p.id = op.product_id
      WHERE op.order_id = ?
    `, [id]);

    const order = orders[0];
    order.products = products;
    order.user = {
      id: order.user_id,
      fname: order.fname,
      lname: order.lname,
      username: order.username,
      email: order.email,
      avatar: order.avatar
    };
    delete order.fname;
    delete order.lname;
    delete order.username;
    delete order.email;
    delete order.avatar;

    res.status(200).json(order);
  } catch (error) {
    await connection.rollback();
    if (error.code === 'ER_NO_REFERENCED_ROW_2') {
      return res.status(400).json({ message: 'Invalid user ID or product ID' });
    }
    res.status(500).json({ message: 'Error updating order', error: error.message });
  } finally {
    connection.release();
  }
});

// Delete Order
app.delete('/orders/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);

    // Check if order exists
    const [existing] = await pool.query('SELECT * FROM orders WHERE id = ?', [id]);
    if (existing.length === 0) {
      return res.status(404).json({ message: 'Order not found' });
    }

    // Delete related order_product records (will be auto-deleted by CASCADE)
    await pool.query('DELETE FROM orders WHERE id = ?', [id]);
    res.status(200).json({ message: `Order with ID ${id} deleted successfully` });
  } catch (error) {
    res.status(500).json({ message: 'Error deleting order', error: error.message });
  }
});

SELECT
  o.id, o.user_id, o.created_at,
  u.fname, u.lname, u.username, u.email, u.avatar
FROM orders o
JOIN users u ON o.user_id = u.id

SELECT p.*
FROM products p
JOIN order_product op ON p.id = op.product_id
WHERE op.order_id = ?

const connection = await pool.getConnection();
try {
  await connection.beginTransaction();
  // หลายคำสั่ง SQL
  await connection.commit();
} catch (error) {
  await connection.rollback();
}

INSERT INTO orders (user_id) VALUES (?);           -- สร้างคำสั่งซื้อ
INSERT INTO order_product (order_id, product_id)   -- เชื่อมสินค้า
VALUES (?, ?);

DELETE FROM orders WHERE id = ?
-- ข้อมูลใน order_product จะถูกลบอัตโนมัติ (CASCADE)

// Relationship Endpoints
// Get Orders by User ID
app.get('/users/:id/orders', async (req, res) => {
  const id = parseInt(req.params.id);
  try {
    // Check if user exists
    const [users] = await pool.query('SELECT * FROM users WHERE id = ?', [id]);
    if (users.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }

    const [orders] = await pool.query(`
      SELECT
        o.id,
        o.user_id,
        o.created_at,
        u.fname,
        u.lname,
        u.username,
        u.email,
        u.avatar
      FROM orders o
      JOIN users u ON o.user_id = u.id
      WHERE o.user_id = ?
    `, [id]);

    // Get products for each order
    for (const order of orders) {
      const [products] = await pool.query(`
        SELECT p.*
        FROM products p
        JOIN order_product op ON p.id = op.product_id
        WHERE op.order_id = ?
      `, [order.id]);
      order.products = products;
      order.user = {
        id: order.user_id,
        fname: order.fname,
        lname: order.lname,
        username: order.username,
        email: order.email,
        avatar: order.avatar
      };
      delete order.fname;
      delete order.lname;
      delete order.username;
      delete order.email;
      delete order.avatar;
    }

    res.json(orders);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching user orders', error: error.message });
  }
});

// Get Products by Order ID
app.get('/orders/:id/products', async (req, res) => {
  const id = parseInt(req.params.id);
  try {
    // Check if order exists
    const [orders] = await pool.query('SELECT * FROM orders WHERE id = ?', [id]);
    if (orders.length === 0) {
      return res.status(404).json({ message: 'Order not found' });
    }

    const [products] = await pool.query(`
      SELECT p.*
      FROM products p
      JOIN order_product op ON p.id = op.product_id
      WHERE op.order_id = ?
    `, [id]);

    res.json(products);
  } catch (error) {
    res.status(500).json({ message: 'Error fetching order products', error: error.message });
  }
});