【Node.js】封装原生Node连接MySQL数据库

【Node.js】封装原生Node连接MySQL数据库

utils/config.js

// configure database file information
let config = {
    host: "127.0.0.1",
    port: 3306,
    user: "root",
    password: "root",
    database: "test"
}
module.exports = config;

utils/db.js

const mysql = require("mysql");
const config = require("./config");

class DbConn {
    constructor() {
        // 建立连接
        this.connection = mysql.createConnection(config);

        this.connection.connect(err => {
            if (!err) {
                console.log('Connect to database...')
            } else {
                throw (err);
                console.error("Error connecting:" + err.stcak);
                return;
            }
        });
    }

    // 处理sql语句查询的方法
    async query(sql) {
        return await this.sqlPromise(sql);
    }

    // 异步放到Pormise里面去处理
    sqlPromise(sql) {
        return new Promise((resolve, reject) => {
            this.connection.query(sql, (err, results) => {
                if (!err) {
                    resolve([err, results])
                } else {
                    resolve([err, undefined])
                }
            })
        })
    }
}

let dbConn = new DbConn();
module.exports = dbConn;

或者

const mysql = require("mysql");
const config = require("./config");

class DbConn {
    constructor() {
        this.connection = null;
        this.connect();
    }

    // connection
    connect() {
        this.connection = mysql.createConnection(config);
        this.connection.connect(err => {
            if (!err) {
                console.log('Connected to database...')
            } else {
                throw (err);
                console.error('Error connecting: ' + err.stack);
                return;
            }
        })
    }

    // query
    async query(sql) {
        return await this.to(this.sqlPromise(sql));
    }

    // promise operation
    sqlPromise(sql) {
        let p = new Promise((resolve, reject) => {
            this.connection.query(sql, (err, results) => {
                if (!err) {
                    resolve(results);
                } else {
                    reject(err);
                }
            });
        });
        return p;
    }

    to(promise) {
        // close connection
        // this.close()
        return promise.then(res => {
            return [null, res];
        }).catch(err => {
            return err;
        });
    }
    // close
    close() {
        this.connection.end();
    }
}
let dbConn = new DbConn();
module.exports = dbConn;

app.js

const express = require('express');
let app = express();
const db = require("./utils/db");

app.get('/getStar', async (req, res) => {
    let sql = `SELECT * FROM star where starId = 2`;
    // getStar(sql, req, res);
    [err, results] = await db.query(sql);
    // console.log(err);
    // console.log(results);
    if(!err){
        res.send({
            msg:"success",
            status: 200,
            result: results
        })
    }else{
        res.send({
            msg:"fail",
            status: 404,
            err_data: err
        })
    }
})

app.listen(3000, () => {
    console.log(`Port 3000 is listening...`)
})