พื้นฐานการทดสอบ RESTful API (Express + MySQL) ด้วย Jest

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

mkdir api-testing-demo
cd api-testing-demo
npm init -y

# ติดตั้ง Production Dependencies
npm install express body-parser cors mysql2 dotenv

# ติดตั้ง Development Dependencies
npm install -D jest supertest

api-testing-demo/
├── src/
│   └── db.js
├── tests/
│   └── api.test.js
├── index.js
├── jest.setup.js
├── package.json
└── .env

DB_HOST=localhost
DB_PORT=3307
DB_USER=root
DB_PASSWORD=
DB_NAME=test_db

// Load environment variables for testing
require('dotenv').config();

// Close database connections after all tests complete
afterAll(async () => {
  const pool = require('./src/db');
  await pool.end();
});

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

// สร้าง MySQL connection pool
// การใช้ pool ช่วยให้จัดการ connection ได้อย่างมีประสิทธิภาพ
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3307,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'test_db',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

module.exports = pool;

// Load environment variables
require('dotenv').config();

const express = require('express');
const bodyParser = require('body-parser');
const cors = require('cors');
const pool = require('./src/db');

const app = express();
const port = 5000;

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

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

// Get all users
app.get('/users', async (req, res) => {
  try {
    const [rows] = await pool.execute('SELECT * FROM users');
    res.json(rows);
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

// Get user by ID
app.get('/users/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const [rows] = await pool.execute('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.message });
  }
});

// Create new user
app.post('/users', async (req, res) => {
  try {
    const { fname, lname, username, email, avatar } = req.body;
    const [result] = await pool.execute(
      'INSERT INTO users (fname, lname, username, email, avatar) VALUES (?, ?, ?, ?, ?)',
      [fname, lname, username, email, avatar]
    );
    const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [result.insertId]);
    res.status(201).json(rows[0]);
  } catch (error) {
    res.status(500).json({ message: 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;
    const [result] = await pool.execute(
      'UPDATE users SET fname = ?, lname = ?, username = ?, email = ?, avatar = ? WHERE id = ?',
      [fname, lname, username, email, avatar, id]
    );
    const [rows] = await pool.execute('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.message });
  }
});

// Delete user
app.delete('/users/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [id]);
    if (rows.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    await pool.execute('DELETE FROM users WHERE id = ?', [id]);
    res.json({ message: `User with ID ${id} deleted` });
  } catch (error) {
    res.status(500).json({ message: error.message });
  }
});

// Start server only when run directly (not in tests)
if (require.main === module) {
  app.listen(port, () => {
    console.log(`Server running at http://localhost:${port}`);
    console.log(`MySQL connected via CAMPP at localhost:${process.env.DB_PORT || 3307}`);
  });
}

// Export for testing
module.exports = app;

const request = require('supertest');
const app = require('../index');

describe('MySQL API Tests', () => {
  let createdUserId;

  // Test 1: Test Root Endpoint
  it('GET / should return API ready message', async () => {
    const res = await request(app).get('/');
    expect(res.statusCode).toBe(200);
    expect(res.text).toContain('RESTful API is ready');
  });

  // Test 2: Test creating a user
  it('POST /users should create a user', async () => {
    const res = await request(app)
      .post('/users')
      .send({
        fname: 'John',
        lname: 'Smith',
        username: `john_smith_${Date.now()}`, // unique username
        email: `john${Date.now()}@example.com`, // unique email
        avatar: 'https://example.com/avatar.jpg'
      });
    expect(res.statusCode).toBe(201);
    expect(res.body).toHaveProperty('id');
    createdUserId = res.body.id; // Save ID for later tests
  });

  // Test 3: Test retrieving the created user
  it('GET /users/:id should return the created user', async () => {
    const res = await request(app).get(`/users/${createdUserId}`);
    expect(res.statusCode).toBe(200);
    expect(res.body).toHaveProperty('fname', 'John');
    expect(res.body).toHaveProperty('lname', 'Smith');
  });

  // Test 4: Test updating the user
  it('PUT /users/:id should update the user', async () => {
    const res = await request(app)
      .put(`/users/${createdUserId}`)
      .send({
        fname: 'Jackson',
        lname: 'Mars',
        username: `jackson_mars_${Date.now()}`,
        email: `jackson${Date.now()}@example.com`,
        avatar: 'https://example.com/new-avatar.jpg'
      });
    expect(res.statusCode).toBe(200);
    expect(res.body).toHaveProperty('fname', 'Jackson');
    expect(res.body).toHaveProperty('lname', 'Mars');
  });

  // Test 5: Test deleting the user
  it('DELETE /users/:id should delete the user', async () => {
    const res = await request(app).delete(`/users/${createdUserId}`);
    expect(res.statusCode).toBe(200);
    expect(res.body.message).toContain(`User with ID ${createdUserId} deleted`);
  });

  // Test 6: Test retrieving a deleted user
  it('GET /users/:id should return 404 for deleted user', async () => {
    const res = await request(app).get(`/users/${createdUserId}`);
    expect(res.statusCode).toBe(404);
    expect(res.body).toHaveProperty('message', 'User not found');
  });
});

const request = require('supertest');
const app = require('../index');

describe('MySQL API Tests', () => {
  let createdUserId; // Variable to store the ID of the created user

  // Test cases will be here
});

it('GET / should return API ready message', async () => {
  const res = await request(app).get('/');
  expect(res.statusCode).toBe(200);
  expect(res.text).toContain('RESTful API is ready');
});

it('POST /users should create a user', async () => {
  const res = await request(app)
    .post('/users')
    .send({
      fname: 'John',
      lname: 'Smith',
      username: `john_smith_${Date.now()}`,
      email: `john${Date.now()}@example.com`,
      avatar: 'https://example.com/avatar.jpg'
    });
  expect(res.statusCode).toBe(201);
  expect(res.body).toHaveProperty('id');
  createdUserId = res.body.id;
});

{
  "scripts": {
    "test": "jest",
    "test:watch": "jest --watch",
    "test:coverage": "jest --coverage",
    "start": "node index.js"
  },
  "jest": {
    "setupFilesAfterEnv": [
      "./jest.setup.js"
    ],
    "testEnvironment": "node"
  }
}

# รันการทดสอบครั้งเดียว
npm test

 PASS  tests/api.test.js
  MySQL API Tests
    √ GET / should return API ready message (29 ms)
    √ POST /users should create a user (50 ms)
    √ GET /users/:id should return the created user (5 ms)
    √ PUT /users/:id should update the user (8 ms)
    √ DELETE /users/:id should delete the user (7 ms)
    √ GET /users/:id should return 404 for deleted user (4 ms)

Test Suites: 1 passed, 1 total
Tests:       6 passed, 6 total
Snapshots:   0 total
Time:        1.867 s
Ran all test suites.

describe('Error Handling Tests', () => {

  // Test creating a user with incomplete data
  it('POST /users should return 500 for invalid data', async () => {
    const res = await request(app)
      .post('/users')
      .send({
        fname: 'John'
        // Missing lname, username, email
      });
    expect(res.statusCode).toBe(500);
    expect(res.body).toHaveProperty('message');
  });

  // Test retrieving a user that does not exist
  it('GET /users/999999 should return 404', async () => {
    const res = await request(app).get('/users/999999');
    expect(res.statusCode).toBe(404);
    expect(res.body).toHaveProperty('message', 'User not found');
  });

  // Test creating a user with a duplicate email
  it('POST /users should return 500 for duplicate email', async () => {
    const userData = {
      fname: 'Jane',
      lname: 'Doe',
      username: 'jane_doe_test',
      email: 'duplicate@test.com',
      avatar: 'https://example.com/avatar.jpg'
    };

    // Create user the first time
    await request(app).post('/users').send(userData);

    // Attempt to create another user with the same email
    const res = await request(app).post('/users').send({
      ...userData,
      username: 'jane_doe_test2' // Change username but keep the same email
    });

    expect(res.statusCode).toBe(500);
    expect(res.body.message).toContain('email');
  });
});

 PASS  tests/api.test.js
  MySQL API Tests
    √ GET / should return API ready message (29 ms)
    √ POST /users should create a user (50 ms)
    √ GET /users/:id should return the created user (5 ms)
    √ PUT /users/:id should update the user (8 ms)
    √ DELETE /users/:id should delete the user (7 ms)
    √ GET /users/:id should return 404 for deleted user (4 ms)
  Error Handling Tests
    √ POST /users should return 500 for invalid data (3 ms)
    √ GET /users/999999 should return 404 (5 ms)
    √ POST /users should return 500 for duplicate email (10 ms)

Test Suites: 1 passed, 1 total
Tests:       9 passed, 9 total
Snapshots:   0 total
Time:        1.867 s
Ran all test suites.

npm run test:coverage

 PASS  tests/api.test.js
  MySQL API Tests
    √ GET / should return API ready message (29 ms)
    √ POST /users should create a user (50 ms)
    √ GET /users/:id should return the created user (5 ms)
    √ PUT /users/:id should update the user (8 ms)
    √ DELETE /users/:id should delete the user (7 ms)
    √ GET /users/:id should return 404 for deleted user (4 ms)
  Error Handling Tests
    √ POST /users should return 500 for invalid data (3 ms)
    √ GET /users/999999 should return 404 (5 ms)
    √ POST /users should return 500 for duplicate email (10 ms)

----------------------|---------|----------|---------|---------|----------------------------
File                  | % Stmts | % Branch | % Funcs | % Lines | Uncovered Line #s
----------------------|---------|----------|---------|---------|----------------------------
All files             |   78.94 |       55 |   71.42 |   78.94 |
  index.js            |   77.77 |       50 |   71.42 |   77.77 | 20-24,38,68,72,82,87,93-95
 api-testing-demo/src |     100 |       60 |     100 |     100 |
  db.js               |     100 |       60 |     100 |     100 | 6-8,10
----------------------|---------|----------|---------|---------|----------------------------
Test Suites: 1 passed, 1 total
Tests:       9 passed, 9 total
Snapshots:   0 total
Time:        1.867 s
Ran all test suites.