node-mysqls
v0.2.2
Published
快速生成mysql语句
Downloads
5
Readme
EN | CN
node-mysqls
It is written in JavaScript. crud for mysql. You can also use transactions very easily.
node-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/node-mysqls
install：
npm install node-mysqls --save
or
yarn add node-mysqlsnode-mysqls parameters
- init: sql Initialization
- initConn: Connection initialization or switching database
- exec: Executing SQL statements
- sql: Chain Call Generates SQL Statement
- transaction: transaction API
Use：
//import
import { init, exec, sql, initConn, transaction } from 'node-mysqls'
//require
let { init, exec, sql, initConn, transaction } = require('node-mysqls')Config initialization：
// You can initialize the configuration at project startup
init({
host: 'localhost',
user: 'root',
password:'123456',
database: 'test',
port: 3306,
})
// or
const db1 = init({
host: 'localhost',
user: 'root',
password:'123456',
database: 'test',
port: 3306,
}, false)
switchLink(db1)Switch database link
const db1 = init({
host: 'localhost',
user: 'root',
password:'123456',
database: 'test',
port: 3306,
}, false)
const db2 = init({
host: 'localhost',
user: 'root',
password:'123456',
database: 'test2',
port: 3306,
}, false)
switchLink(db2)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)
init bool
- isDef: Is default database. (default: true)
Only Generate SQL statements.
sql
.table('node_table')
.field('id,name')
.where({id:1})
.select()
// result
SELECT id,name FROM node_table WHERE id=1use 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=1insert
sql
.table('node_table')
.data({name:'zane',email:'[email protected]'})
.insert()
INSERT INTO node_table (name,email) VALUES (`zane`,`[email protected]`)batch insert
let data = [
{name:'zane',email:'[email protected]'},
{name:'zane_1',email:'[email protected]'},
{name:'zane_2',email:'[email protected]'},
]
sql
.table('node_table')
.data(data)
.insert()
INSERT INTO node_table (name,email) VALUES ('zane','[email protected]'),('zane_1','[email protected]'),('zane_2','[email protected]')update
sql
.table('node_table')
.data({name:'zane',email:'[email protected]'})
.where({id:1})
.update()
UPDATE node_table SET name=`zane`,email=`[email protected]`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)