mysqls

EN | CN

mysqls

It is written in JavaScript. crud for mysql. You can also use transactions very easily.

mysqls：A plug-in that generates SQL statements for node.js. call chaining .simple to use. support transaction.

• npm address：https://www.npmjs.com/package/mysqls

install：

npm install mysqls --save
or
yarn add mysqls

mysqls parameters

• init: sql Initialization
• exec: Executing SQL statements
• sql: Chain Call Generates SQL Statement
• transaction: transaction API

Use：

//import
import { init, exec, sql, transaction } from 'mysqls'

//require
let { init, exec, sql, transaction } = require('mysqls')

Config initialization：

// You can initialize the configuration at project startup
init({
    host: 'localhost',
    user: 'root',
    password:'123456',
    database: 'test',
    port: 3306,
})

init configs

• ispool: Is it initialized as a connection pool. (default:true)
• host: host address. (default:'127.0.0.1')
• user: user. (default:'root')
• password: password. (default:'root')
• database: database. (default:'test')
• port: port. (default:'3306')
• waitConnection: wait for connections. (default:true)
• connectionLimit: connection limit. (default:10)
• queueLimit: queue limit. (default:0)

Only Generate SQL statements.

sql
    .table('node_table')
    .field('id,name')
    .where({id:1})
    .select()

// result
SELECT id,name FROM node_table WHERE id=1

use exec function

const sqlstr = sql
    .table('node_table')
    .field('id,name')
    .where({id:1})
    .select();

const result = await exec(sqlstr);

use sql.prototype.exec

const result = sql
    .table('node_table')
    .field('id,name')
    .where({id:1})
    .select(true)
    .exec();

• .select(true):true
• It same to use at update(true)、insert(true)、delet(true)、query(true) method.

use Promise

// use exec function
exec(sql.table('web_pages').where({id:147}).select())
    .then(res=>{
        console.log(res)
    }).catch(err=>{
        console.log(err)
    })

// use exec method
sql.table('web_pages').where({id:147}).select(true).exec()
    .then(res=>{
        console.log(res)
    }).catch(err=>{
        console.log(err)
    })

使用async/await

// use exec function
const result = await exec(sql.table('web_pages').where({id:147}).select())

// use exec method
const result = await sql.table('web_pages').where({id:147}).select(true).exec()

transaction

const tranSqlArr = [
    sql.table('table1').data({number:'number-5'}).update(true,true),
    sql.table('table2').data({number:'number+5'}).update(true,true)
]
const result = await transaction(tranSqlArr)

Simple usage of generating SQL statements.

select

sql
    .table('node_table')
    .field('id,name')
    .where({id:1})
    .select()

SELECT id,name FROM node_table WHERE id=1

insert

sql
    .table('node_table')
    .data({name:'zane',email:'[e-mail]'})
    .insert()

INSERT INTO node_table (name,email) VALUES (`zane`,`[e-mail]`)

batch insert

let data = [
    {name:'zane',email:'[e-mail]'},
    {name:'zane_1',email:'[e-mail]'},
    {name:'zane_2',email:'[e-mail]'},
]
sql
    .table('node_table')
    .data(data)
    .insert()

INSERT INTO node_table (name,email) VALUES ('zane','[e-mail]'),('zane_1','[e-mail]'),('zane_2','[e-mail]')

update

sql
    .table('node_table')
    .data({name:'zane',email:'[e-mail]'})
    .where({id:1})
    .update()

UPDATE node_table SET name=`zane`,email=`[e-mail]`

delet

sql .table('node_table')
    .where({name:'zane'})
    .delet();

DELETE FROM node_table WHERE name=`zane`

Advanced Usage.

// parameter json
sql
    .table('node_table')
    .where({id:1,name:'zane'})
    .select()

SELECT  * FROM node_table WHERE id=1 AND name=`zane`

// parameter array
let data=[
    {id:1,name:'zhangsan',_type:'or'},
    {sex:1,number:3}
]
sql.table('node_table').where(data).select()

SELECT * FROM node_table WHERE (id=1 OR name=`zhangsan` ) AND (sex=1 AND number=3 )

// multiple fields
let data=[
    {id:1,name:'zhangsan',_type:'or',_nexttype:'or'},
    {sex:1,number:3,_type:'and'}
]
sql.table('node_table').where(data).select()

SELECT * FROM node_table WHERE (id=1 OR name=`zhangsan`) OR (sex=1 AND number=3)

// Expression Query
let data={
    id:{eq:100,egt:10,_type:'or'},
    name:'zhangshan'
}
sql.table('node_table').where(data).select()

SELECT  * FROM node_table WHERE ((id=100) OR (id>=10)) AND name=`zhangshan`

// Multiple queries
let data=[{
    id:{eq:100,egt:10,_type:'or'},
    name:'zhangshan',
    _nexttype:'or'
},{
    status:1,
    name:{like:'%zane%'}
}]
sql.table('node_table').where(data).select()

SELECT * FROM node_table WHERE (((id=100) OR (id>=10)) AND name=`zhangshan`) OR (status=1 AND ((name LIKE `%zane%`))) 


//UNION ， UNION ALL 
sql
    .union('SELECT * FROM think_user_1',true)
    .union('SELECT * FROM think_user_2',true)
    .union(['SELECT * FROM think_user_3','SELECT name FROM think_user_4'])
    .union('SELECT * FROM think_user_5',true)
    .select()

result
(SELECT * FROM think_user_1) UNION ALL  
(SELECT * FROM think_user_2) UNION ALL 
(SELECT * FROM think_user_3) UNION 
(SELECT name FROM think_user_4)  UNION  
(SELECT * FROM think_user_5)

Directory

• 1.synopsis
  • 1.1.Parameter Description and Transaction
• 2.Chain operation
  • 2.1.WHERE
  • 2.2.TABLE
  • 2.3.ALIAS
  • 2.4.DATA
  • 2.5.FIELD
  • 2.6.ORDER
  • 2.7.LIMIT
  • 2.8.PAGE
  • 2.9.GROUP
  • 2.10.HAVING
  • 2.11.UNION
  • 2.12.DISTINCT
  • 2.13.COMMENT
• 3.CURD
  • 3.1.SELECT
  • 3.2.UPDATE
  • 3.3.INSERT
  • 3.4.DELETE
• 4.Query mode
  • 4.1.Basic query
  • 4.2.Expression Query
  • 4.3.Interval query
  • 4.4.Composite query
  • 4.5.Statistical query
  • 4.6.SQL query
  • 4.7.Subquery