使用Nodejs操作mysql的简单封装

1. 安装mysql包

npm i mysql -s

2. 创建一个 database.config.js 用于mysql配置

/**
 * Database config.
 * 
 */
module.exports = {
    host: 'localhost',
    port: '3306',
    user: 'root',
    password: 'xxxxxxx.',
    database: ''
    ...
}

详细配置请参考：https://www.npmjs.com/package/mysql

3.创建一个db.js 写封装的逻辑

const mysql = require('mysql'); //mysql包
const config = require('./config/database.config'); //数据库配置

class CustomDB {
    /**
     * config一些配置文件，例如主机、端口、用户、密码....
     */
    constructor(config={}) {
        this.config = config;
    }
    
    /**
     * 执行sql
     */
    query(sql, params) {
        let me = this;
        return new Promise((resolve, reject) => {
            const connection = mysql.createConnection(me.config);
            connection.connect(err => {
                if (err) reject('DATABASE ERROR：connection failed!')
            });

            //Execute SQL statement
            connection.query(sql, params, (err, results, fileds) => {
                if (err) reject(`DATABASE ERROR：${err.message}`);
                resolve(results, fileds);

                //Close the connection
                connection.end(err => {
                    reject('DATABASE ERROR：close connection failed!')
                });
            });           
        });   
    }

    /**
     * 插入数据，单行插入
     * @param table
     * @param datas
     */
    insert({table, datas}) {
        return new Promise(async(resolve, reject) => {
            try {
                let fields = ''; //字段名
                let values = []; //字段值
                for(let key in datas) {
                    fields += key + ','
                    values.push(datas[key]);
                }
                fields = fields.slice(0, -1);
                values = JSON.stringify(values).slice(1, -1);
                const insertSql = `INSERT INTO ${table} (${fields}) VALUES (${values})`;
                let insertResult = await this.query(insertSql); //写入结果

                //是否成功写入
                if (insertResult.affectedRows > 0) {
                    resolve(insertResult)
                } else {
                    reject(`INSERT ERROR: ${insertResult.message}`);
                }
            } catch (error) {
                reject(typeof error === 'object' ? `ABNORMAL PRGORAM：${error.message}` : error);
            }            
        });
    }

    /**
     * 更新
     */
    update({table, sets, where}) {
        return new Promise(async(resolve, reject) => {
            try {
                let setData = '';
                for (let key in sets) {
                    let value = typeof sets[key] === 'string' ? `"${sets[key]}"` : `${sets[key]}`;
                    setData += `${key}=${value}, `;
                }
                setData = setData.slice(0, -2);
                const updateSql = `UPDATE ${table} SET ${setData} WHERE ${where}`;
                console.log(updateSql);
                let updateResult = await this.query(updateSql);
                if (updateResult.changedRows === 0) {
                    reject(`UPDATE ERROR: ${updateResult.message}`, updateResult);
                } else {
                    resolve(updateResult);
                }
            } catch (error) {
                reject && reject(typeof error === 'object' ? `ABNORMAL PRGORAM：${error.message}` : error);
            }
        });
    }

    /**
     * 删除
     * @param table
     */
    delete({table, where}) {
        return new Promise(async (resolve, reject) => {
            try {
                const deleteSql = `DELETE FROM ${table} WHERE ${where}`;
                let deleteResult = await this.query(deleteSql);
                if (deleteResult.affectedRows === 0) {
                    reject(`DELETE ERROR: ${deleteResult.message}`, deleteResult);
                } else {
                    resolve(deleteResult);
                }
            } catch (error) {
                console.log(error);
                reject && reject(typeof error === 'object' ? `ABNORMAL PRGORAM：${error.message}` : error);
            }
        });       
    }
}

//导出模块
module.exports = new CustomDB(config); //初始化实例，传入配置文件

4. 外部调用

const db = require('./libs/db.js'); //引入封装的db模块

//新增一个用户
let userData = {
   table: 'sys_user',
   datas: {
        ID: 'b57ce5e8-a2ff-45f8-b92e-97e136d9187d',
        ACCOUNT: 'admin1',
        PASSWORD: '123456',
        NICK_NAME: '管理员1',
        SYS_ID: 'b67ce5e8-a2ff-45f8-b92e-97e136d9187d',
        STATUS: 1,
        CREATE_TIME: '2019-07-06 14:20:30'
    }
};

db.insert(userData).then(res => {
    console.log(res);
}).catch(err => {
    console.log(err);
});

OR

let addUser = async() => {
    try {
        let addResult = await db.insert(userData);
          
        //success do sth...
    } catch (error) {
        //error do sth...
    }
}

Tip: 由于是基于Promise封装，代码调用中会出现 .then().catch(), 作者建议使用 async,await 来处理回调问题哦~