mysqls

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

Mysqls iS a plug-in designed to generate SQL statements for Node.js, chain call, flexible use. Support to generate SQL syntax, also support to generate syntax after direct call, support things and other features.

The API refers to the popular ThinkPHP model API.

NPM address: www.npmjs.com/package/mys…

Making: github.com/wangweiange…


Mysqls is the author in the process of development in order to be more simple, more efficient development efficiency and encapsulation of a library, made at the beginning of the year, recently from the new small modification and strengthen. Share it with those who need it, and those who are interested can also read the author’s ideas. Personally, I have used ThinkPHP and found its wrapper calls to mysql to be very friendly and easy to use, so most of the APIS refer to it. If you have any other comments or suggestions, please share them with me.

Installation:

npm install mysqls --save-devCopy the code


Mysqls Parameter description

  • Init: SQL initialization API

  • Exec: Executes SQL statements

  • SQL: Generates SQL statements in chain calls. SQL statements can be directly executed after being generated

  • Transaction: Executes the transaction API

Project use:

Import {init,exec, sql, transaction } from 'mysqls'/ / the requirelet { init, exec, sql, transaction } = require('mysqls')Copy the code


Mysql > initialize mysql

// init({host:'localhost',
    user: 'root',
    password:'123456',
    database: 'test',
    port: 3306,
})Copy the code


Init Parameter Description

  • Ispool: Whether to initialize in connection pool mode (default:true)
  • Host: host address (default:’127.0.0.1′)
  • User: user name (default:’root’)
  • Password: database password (default:’root’)
  • Database: used database (default:’test’)
  • Port: the port (default: ‘3306’)
  • WaitConnection: Whether to wait for a connection (used for connection pooling) (default:true)
  • ConnectionLimit: Indicates the connection pool size (default:10).
  • QueueLimit: queueLimit (default:0)


Only SQL statements are generated

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

// result
SELECT id,name FROM node_table WHERE id=1Copy the code


Use the exec function to execute SQL statements

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

const result = await exec(sqlstr);Copy the code


Use sqL.prototype. exec chain calls

const result = sql
    .table('node_table')
    .field('id,name')
    .where({id:1})
    .select(true)
    .exec();Copy the code
  • The select method takes the argument true when executing SQL in chain calls
  • The same applies to update(true), INSERT (true),delet(true), and Query (true) methods


Use Promise

/ / useexecfunctionexec(sql.table('web_pages'). The where ({id: 147}). The select ()). Then (res = > {the console. The log (res)}). The catch (err = > {the console. The log (err)}) / / useexecMethods the SQL table ('web_pages').where({id:147}).select(true).exec()
    .then(res=>{
        console.log(res)
    }).catch(err=>{
        console.log(err)
    })Copy the code


Use the async/await

/ / useexecFunction const result = awaitexec(sql.table('web_pages'). The where ({id: 147}). / / use the select ())execMethod const result = await sql.table()'web_pages').where({id:147}).select(true).exec()Copy the code


Handle affairs

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


Generate simple usage of SQL statements

  • Note: The order of SQL calls has been sorted internally, so it can be written out of the strict ORDER of SQL statements

The query

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

SELECT id,name FROM node_table WHERE id=1Copy the code

insert

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

INSERT INTO node_table (name,email) VALUES (`zane`,`[email protected]`)Copy the code

update

sql
    .table('node_table')
    .data({name:'zane',email:'[email protected]'})
    .update()

UPDATE node_table SET name=`zane`,email=`[email protected]`Copy the code

delete

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

DELETE FROM node_table WHERE name=`zane`Copy the code


Generate advanced usage of SQL statements

SQL. Table ()'node_table')
    .where({id:1,name:'zane'}). Select () select * FROM node_table WHERE id=1 AND name= 'zanelet data=[
    {id:1,name:'zhangsan',_type:'or'},
    {sex:1,number:3}
]
sql.table('node_table'Select () select * FROM node_table where(id=1 OR name= 'zhangsan') AND (sex=1 AND number=3) //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=3let data={
    id:{eq:100,egt:10,_type:'or'},
    name:'zhangshan'
}
sql.table('node_table'Select () SELECT * FROM node_table WHERE ((id=100) OR (ID >=10)) AND name= 'zhangshan' //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'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 () get (select * FROM think_user_1) UNION ALL (select * FROM think_user_2) UNION ALL (select * FROM think_user_2 think_user_3) UNION (SELECT name FROM think_user_4) UNION (SELECT * FROM think_user_5)Copy the code


See the documentation for more usage:

Github.com/wangweiange…