Node express MySql & Swagger

使用 Express-Generator 來自動產生環境

//全域方法安裝express-generator
npm install express-generator -g

//项目初始化:專案名稱
express -e 專案名稱

//cd 專案名稱,到專案
cd 專案名稱

//啟動
npm start

到此網址觀看
http://localhost:3000

安裝mysql

1	npm install --save mysql

安裝dotenv環境變數

1	npm install --save dotenv

環境變數:新增.env

//.env
DB_HOST=資料庫主機
DB_USER=使用者
DB_PASSWORD=密碼
DB_NAME=資料庫名稱

加入DB主機,使用者名稱,帳密,資料表名稱

連接MySQL資料庫 Connecting to MySQL
Create a new folder named config, and add database.js in this folder.
新增 config資料夾內新增database.js

//database.js
const mysql = require('mysql');
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
});

connection.connect((err) => {
    if (err) throw err;
    console.log('連接MySQL資料庫');
});

module.exports = connection;

定義使用者
新增models/users.js

載入資料庫const db = require('../config/database');
獲取使用者陣列getAllUsers 函式 db.query('SELECT * FROM users', callback); SQL SELECT
獲取單一使用者getUserById 函式 db.query('SELECT * FROM users WHERE id = ?', [id], callback) SQL WHERE
建立單一使用者getUserById 函式 db.query('INSERT INTO users SET ?', newTodo, callback)SQL WHERE
修改單一使用者updateUser 函式 db.query('UPDATE users SET ? WHERE id = ?', [updatedTodo, id], callback); SQL UPDATE
刪除單一使用者deleteUser 函式 db.query('DELETE FROM users WHERE id = ?', [id], callback) SQL DELETE
輸出getAllUsers,getUserById,getUserById,createUser,updateUser,deleteUser

//users.js
const db = require('../config/database');
//獲取所有
exports.getAllUsers =  (callback) =>
{
    db.query('SELECT * FROM users', callback);
};
//獲取單一
exports.getUserById = (id, callback) =>{
    db.query('SELECT * FROM users WHERE id = ?', [id], callback);
};
//建立單一
exports.createUser = (newTodo, callback) =>{
    db.query('INSERT INTO users SET ?', newTodo, callback);
};
//修改單一
exports.updateUser = (id, updatedTodo, callback)=>{
    db.query('UPDATE users SET ? WHERE id = ?', [updatedTodo, id], callback);
};
//刪除單一
exports.deleteUser = (id, callback) =>{
    db.query('DELETE FROM users WHERE id = ?', [id], callback);
};

建立控制器
新增controllers/userController.js

引入models/users

加入swagger名稱與描述必須語法

獲取單一，修改單一，刪除單一都必須有唯一碼req.params.id

//controllers/userController.js
//引入models/users
const User = require('../models/users');

//獲取所有
exports.getAllUsers = (req, res) => {
    User.getAllUsers((err, Users) =>
    {
        /* 	#swagger.tags = ['獲取所有使用者']
            #swagger.description = '' */
       if (err) throw err;
        res.json(Users);
    });
};
//獲取單一
exports.getUserById = (req, res) =>
{
    /* 	#swagger.tags = ['獲取單一使用者']
            #swagger.description = '' */
    User.getUserById(req.params.id, (err, User) => {
        if (err) throw err;
        res.json(User);
    });
};
//建立單一
exports.createUser = (req, res) =>
{
     /* 	#swagger.tags = ['新增單一使用者']
            #swagger.description = '' */
    /*	#swagger.parameters['obj'] = {
            in: 'body',
            description: '',
            required: true,
            schema: { $ref: "#/definitions/AddUser" }
    } */
    const newUser = {
        name: req.body.name,
        address: req.body.address,
        country: req.body.country 
    };

    User.createUser(newUser, (err, result) => {
       if (err) throw err;
        res.json({ message: '成功建立使用者',newUser,result});
    });
};
//修改立單一
exports.updateUser = (req, res) =>
{
    /* 	#swagger.tags = ['修改單一使用者']
            #swagger.description = '' */
     /*	#swagger.parameters['obj'] = {
            in: 'body',
            description: '',
            required: true,
            schema: { $ref: "#/definitions/AddUser" }
    } */
    const updatedUser = {
        name: req.body.name,
        address: req.body.address,
        country: req.body.country 
    };

    User.updateUser(req.params.id, updatedUser, (err, result) => {
       if (err) throw err;
        res.json({ message: '成功更新使用者',updatedUser,result});
    });
};
//刪除單一
exports.deleteUser = (req, res) =>
{
     /* 	#swagger.tags = ['刪除單一使用者']
            #swagger.description = '' */
    User.deleteUser(req.params.id, (err, result) => {
        if (err) throw err;
        res.json({ message: '成功刪除使用者！',result });
    });
};

swagger.js
definitionss內的AddUser是預設參數

const swaggerAutogen = require('swagger-autogen')();
//內容
const doc = {
    info: {
        "version": "1.0.0", 
        "title": "Node Mysql Restful Api",   
        description: "larahuang2024/06/29"
    },
   
    host:['localhost:3000'], //"localhost:3000"
    basePath: "/",
    schemes: ['http'],//, 'https
    consumes: ['application/json'],
    produces: ['application/json'],
    //安全定義
    securityDefinitions: {
       
        apiKeyAuth:{
            type: "apiKey",
            in: "header",   
            name: "Authorization",  
            description: ""
        }
    },
    //定義
    definitions: {
        AddUser: {
            $name: "Lara",
            $address: '三重',
            $country: '新北市'  
        },
       
    }
}
const outputFile = './swagger.json'; 
const endpointsFiles = ['./index.js'];
swaggerAutogen(outputFile, endpointsFiles,doc);

參考資料
sql語法
SQL語法