1. Case Requirements

Build a API service that provides a user list based on MySQL database + Express. Technologies used:

• Third-party packages: express and mysql2
• ES6 modules
• Promise asynchronous programming
• async / await

2. Main Implementation Steps

1. Set up project structure
2. Create a basic server
3. Create a database operation module
4. Create a business module (user_ctrl)
5. Create a routing module (user_router)

3. Implementation

3.1 Set Up Project Structure

1. Initialize the project:

   • Run npm init -y in an empty project directory.

2. Enable ES6 module support:

   • Declare "type": "module" in package.json.

3. Install dependencies:

   • Run npm install express@4.17.1 mysql2@2.2.5.

3.2 Create a Basic Server

Create app.js in the root directory with the following code:

import express from 'express';

const app = express();

app.listen(8080, () => {
  console.log('Server running at http://127.0.0.1:8080');
});

Start the server: nodemon app.js.

3.3 Create Database Operation Module

Create a db folder in the root directory, then create index.js inside it:

import mysql from 'mysql2';

const pool = mysql.createPool({
  host: '127.0.0.1',
  port: 3306,
  database: 'my_db',
  user: 'root',
  password: 'admin',
});

export default pool.promise();

3.4 Create Business Module

Create a controller folder, then create user_ctrl.js inside it:

import db from '../db/index.js';

export async function getAllUser(req, res) {
  const [rows] = await db.query('SELECT id, username, nickname FROM ev');
  res.send({
    status: 0,
    message: 'User list retrieved successfully!',
    data: rows,
  });
}

3.5 Create Routing Module

Create a router folder, then create user_router.js inside it:

import express from 'express';
import { getAllUser } from '../controller/user_ctrl.js';

const router = new express.Router();

router.get('/user', getAllUser);

export default router;

3.6 Import and Mount Routing Module in app.js

import express from 'express';
import userRouter from './router/user_router.js';

const app = express();

app.use('/api', userRouter);

app.listen(8080, () => {
  console.log('Server running at http://127.0.0.1:8080');
});

3.7 Add Error Handling in user_ctrl.js

import db from '../db/index.js';

export async function getAllUser(req, res) {
  try {
    const [rows] = await db.query('SELECT id, username, nickname FROM ev_users');
    res.send({
      status: 0,
      message: 'User list retrieved successfully!',
      data: rows,
    });
  } catch (err) {
    res.send({
      status: 1,
      message: 'Failed to retrieve user list!',
      desc: err.message,
    });
  }
}

3.8 Test with Postman

Make a GET request to http://localhost:8080/api/user.

(Omitted for brevity.)