qc-mysql v1.0.0

qc-mysql

Wrapper based on mysql middleware

Install

npm install --save qc-mysql

Usage

const mysql = require('qc-mysql')

/*
    The second parameter is the table prefix.(Default: '')
 */

let Db = new mysql({
    host     : '127.0.0.1',          // The hostname of the database you are connecting to.
    database : 'database',           // Name of the database to use for this connection.
    user     : 'root',               // The MySQL user to authenticate as.
    password : 'root',               // The password of that MySQL user.
    port     : 3306,                 // The port number to connect to.
    connectionLimit : 10             // The maximum number of connections to create at once. 
},'fix_')


async function select() {
    let data = {
        'field' : 'id,title',
        'order' : 'id desc'
    }

    // let results = await Db.find('table Name', data)  // Return 1 record.

    let results = await Db.select('table Name', data)  // Return multiple records.

    return results
}

console.log(select())

The second argument of find and select

Explained ①

//JOIN usage

let data = {
    'field' : 'c.id,c.name,k.title',
    'limit' : 10,
    'alias' : 'c',
    'join'  : [
        ['kinder k', 'c.yid=k.id'] // Default: INNER JOIN
        //....
    ]
 }

 await Db.select('childs', data)

 // console.log(Db.sql)  // SELECT c.id,c.name,k.title FROM fix_childs AS c INNER JOIN fix_kinder k ON c.yid=k.id LIMIT 10

 data = {
    'field' : 'c.id,c.name,k.title',
    'limit' : 10,
    'alias' : 'c',
    'join'  : [
        ['kinder k', 'c.yid=k.id', 'LEFT JOIN']
    ]
 }

 await Db.select('childs', data)

 // console.log(Db.sql)  // SELECT c.id,c.name,k.title FROM fix_child AS c LEFT JOIN fix_kinder k ON c.yid=k.id LIMIT 10

Explained ②

    
    //WHERE usage

    let data = {
        'where' : {
            'id'    : 5,                                    // id=5
            'ctime' : ['>', 100],                           // ctime > 100
            'ctimes': [ '>', 100, '<', 200 ],               // ctimes > 100 AND ctimes < 200
            'ids'   : [ '>', 100, '<', 200, 'or' ],         // ( ids > 100 OR ids < 200 )
            'name'  : ['like', 'hangyuedu%'],               // name like 'hangyuedu%'
            'uid'   : ['in', '(1,2,3)']                     // uid in (1,2,3)
        }
    }

INSERT

    
    let data = {
        'name' : 'qc',
        'status' : 1
    }

    let result = await this.Db.insert('msg', data)

UPDATE

    
    let whereData = {  // See below ② 
        'id' : 5
    }

    let updateData = { 
        'name' : 'qc',
        'status' : 1
    }

    let result = await this.Db.update('msg', whereData, updateData)

DELETE

    
    let map = {  // See below ② 
        'id' : 5
    }

    let result = await this.Db.delete('msg', map)

Return data see mysql

Transaction

    await Db.startTrans() // Open transaction
    try{
        await Db.delete('table', {'id':2})
        await Db.insert('table', { 'msgp' :'11', 'time' : '1572217706' })
        await Db.update('table', {'id':2}, { 'msgp' : '22' })
        if(await Db.commit()) { // Commit the transaction
            
        }
    } catch(err) {
        await Db.rollback()  // Transaction rollback
    }