2
\$\begingroup\$

I used MySQL, but it was inconvenient so I made a wrapper. It works, but I'm unsure if there are any issues.

Used node module - mysql2, bluebird

sqlWarpper.js

'use strict'

import mysql from 'mysql2/promise'
import blueBird from 'bluebird'

function sqlWrapper() {
    this.pool = null
    this.connection = null
    this.options = null
    this.lquery = null
    this.queryHistory = []

    return this
}

/* Connection*/
sqlWrapper.prototype.open = function(msn, opt){
    var self = this
    self.options = {
        errorIgnore: false,
        noDataIsFields: false,
        supportBigNumbers: true,
        bigNumberStrings: true,
        debugMode: false
    }

    if (typeof opt == 'object' && Object.keys(opt).length > 0) {
        Object.keys(opt).forEach(function(k, v) {
            self.options[k] = opt[k]
        })
    }

    msn['Promise'] = blueBird

    if(self.options.debugMode == true){
        console.log(chalk.cyan('------------------- Debug Mode -------------------'))
    }

    self.pool = mysql.createPool(msn)

    return self
}

sqlWrapper.prototype.ready = async function(callback) {
    if(typeof callback != 'function'){
        return null
    }

    var self = this,
        error = null

    self.connect = await self.pool.getConnection((err, conn) => {
        if (err) {
            if (err.code === 'PROTOCOL_CONNECTION_LOST') {
                error = { code: err.code, msg: 'Database connection was closed.' }
            }
            if (err.code === 'ER_CON_COUNT_ERROR') {
                error = { code: err.code, msg: 'Database has too many connections.' }
            }
            if (err.code === 'ECONNREFUSED') {
                error = { code: err.code, msg: 'Database connection was refused.' }
            }
        }

        return conn
    }).catch((err) => {
        throw err
    })

    if(error != null){
        self.connect.release()
        throw error
    }

    const props = {
        lastQuery: function(){
            return self.lquery
        },
        queryList: function(){
            return self.queryHistory
        },
        query: function(sql, param) {
            if(typeof param == 'undefined'){
                param = []
            } else if(!Array.isArray(param)){
                self.connect.rollback()
                self.connect.release()
                throw new Error('Query Parameter Not Array')
            }

            self.lquery = sql
            self.queryHistory.push(sql)
            try {
                return self.connect.query(sql, param)
            } catch(e) {
                self.connect.rollback()
                self.connect.release()
                throw e
            }
        },
        insert: async function(sql, param){
            if(typeof param != 'undefined' && typeof param != 'object'){
                param = []
            }

            const [row, fields] = await this.query(sql, param)
            var val = row.insertId || 0

            return Promise.resolve(val)
        },
        getOne: async function(sql, param, field){
            if(typeof param != 'undefined' && typeof param != 'object'){
                field = param
                param = []
            }

            if(sql.indexOf(' limit ') == -1){
                sql += ' limit 1'
            }

            var val = '',
                key = null

            const [row, fields] = await this.query(sql, param)
            if(typeof field != 'undefined'){
                key = field
            } else {
                for(var i in row[0]){
                    if(key == null){
                        key = i
                        break
                    }
                }
            }

            if(typeof row[0] != 'undefined' && key != null){
                val = row[0][key] || ''
            }

            return Promise.resolve(val)
        },
        getRow: async function(sql, param){
            if(typeof param != 'undefined' && typeof param != 'object'){
                param = []
            }

            if(sql.indexOf(' limit ') == -1){
                sql += ' limit 1'
            }

            var val = []
            const [row, fields] = await this.query(sql, param)

            if(typeof row[0] != 'undefined'){
                val = row[0]
            }

            return Promise.resolve(val)

        },
        getAll: async function(sql, param, start = 0, limit = 2){
            if(typeof param != 'undefined' && typeof param != 'object'){
                limit = start
                start = param
                param = []
            }

            var val = []
            if(sql.indexOf(' limit ') == -1){
                sql += ' limit ' + start + ',' + limit
            }

            const [row, fields] = await this.query(sql, param)

            if(typeof row != 'undefined'){
                val = row
            }

            return Promise.resolve(val)
        },
        rollback: function(){
            self.connect.rollback()
        },
        commit: function(){
            self.connect.commit()
        },
        chainStart: function(){
            self.connect.beginTransaction()
        },
        chainEnd: async function(type){
            if(type == 'undefined' || type != 'commit'){
                await self.connect.rollback()
            } else {
                await self.connect.commit()
            }
            await self.connect.release()
        }
    }

    try {
        await callback(props)
    } catch(e){
        if(self.connect != null){
            self.connect.rollback()
            self.connect.release()
        }

        throw e
    }
}

var sql = new sqlWrapper()
export default sql

index.js

import sql from './sqlWrapper'

const db = sql.open({
  "connectionLimit": "10",
  "host":"localhost",
  "user": "test",
  "database": "test",
  "password": "1234"
}, { debugMode: true})

db.ready( async (conn) => {
    try {
        await conn.chainStart()
        var ssn = await conn.insert('insert test1 (sn) values(?)', [ sn ])

        var row = await conn.getOne('select * from test2 where sn = ?', [ ssn ], 'sn')
        console.log(row)
    } catch(e){
        console.log('1-------------------------------------------')
        console.log(e)
        conn.chainEnd('rollback')
        console.log('-------------------------------------------1')
        return
    }

    console.log('finish')
    conn.chainEnd('commit')
}).catch( (err) => {
    console.log('2-------------------------------------------')
    console.log(err)
})
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.