Qb-mysql is a query Builder for mysql, which assembs structured data into query objects for mysql through chain calls. Qb-mysql is not only very small in size, but also very powerful in function, with rich and diverse APIS, which can easily respond to enterprise-level application development

background

Daily server development, in the process of table relations slightly complex applications, or do query performance optimization, the use of ORM tool library processing associated table will be slightly inadequate, these times will find bare WRITE SQL more comfortable, more convenient. But when you’re lazy about writing naked SQL, you’re likely to open up injection holes.

Although mysql JS provides the escaping query-values and escaping query-Identifiers, But you might still use this select a.name, a.core, B. name from tableA a inner join TableB b on A.id = b.id where A.id =${aId} and B.id =${bId} lazy but quick way to join your query. Because the injection-proof version of Escape is written, you need to write more duplicate code, as shown below

const aId = 1
const bId = 1
const sql = 'select ?? .?? And?????? .?? And?????? .?? from ?? ?? inner join ?? ?? on ?? .?? =?? .?? where ?? .?? =? and ?? .?? =? '
const values = ['a'.'name'.'a'.'score'.'b'.'name'.'tableA'.'a'.'tableB'.'b'.'a'.'bId'.'b'.'id'.'a'.'id', aId, 'b'.'id', bId]
Copy the code

This can be very frustrating to write, especially in complex query scenarios where the SQL statement is very long and escape fields are multiple and repetitive.

In this case, it is easy to use QB-mysql

Installation and use

The installation

npm i -S qb-mysql
Copy the code

The introduction of

const Querybuilder = require('qb-mysql')
Copy the code

const aId = 1
const bId = 1

const qb = new Querybuild()

qb.select({ tableA: ['name'.'score'].tableB: ['name'] })
    .from('tableA')
    .innerJoin('tableB', { id: bId })
    .where({ tableA: { id: aId }, tableB: { id: bId } }) connection.query(... qb.toQuery())// The generated SQL statement is
// console.log(mysql.format(... qb.toQuery()))
// select `tableA`.`name`, `tableB`.`score`, `tableB`.`name` from `tableA`
// inner join `tableB` on `tableB`.`id` = `tableA`.`bId` where `tableA`.`id` = 1 and `tableB`.`id` = 1
Copy the code

API

select
count max min avg sum
from
join
where
order
page limit offset
group by
insert
update
delete
sub padEnd

select

The parameter types

type field = string | number | boolean
type cb = (qb: queryBuilder, tableName? : string) => queryBuilder | { sql: string, values: any[] } | void
type renane = string
type asArr = [field, rename]
type fieldArr = (field | asArr | cb)[]
type asObj = { [key: string]: rename }
type fieldObj = asObj | { [key: string]: cb }
type table = { tableName: valObj | valArr | cb }

function

The list of	instructions
select(params: field) => qb	A single field
select(params: fieldArr \| fieldObj) => qb	Multiple fields
select(params: table) => qb	Field with table name, support for multiple tables
select(params: cb) => qb	With the function, the argument is a new queryBuilder object and the return value is optional

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// A single field is considered a field if it is a string, and a number or Boolean is considered a value
qb.select('a') // => select `a`
qb.select(1) // => select 1
qb.select() // => select * // null, undefined returns *

// Multiple fields, can use objects, can also use arrays, nested arrays use as, objects use as
qb.select(['a'.'b']) // => select `a`, `b`
qb.select([['a'.'r']]) // => select `a` as `r`
qb.select({ a: 'ra'.b: 'rb' }) // => select `a` as `ra`, `b` as `rb`
qb.select({}) // => select * // empty object, empty array returns *

// the value type is the same as the value type
qb.select({ t: ['a']})// => select `t`.`a`
qb.select({ t1: { a: 'ra' }, t2: [['a'.'ra'].'b']})// => select `t1`.`a` as `ra`, `t2`.`a` as `ra`, `t2`.`b`
qb.select({ t: []})// => select 't'.* // value returns * if null

// Static types are not enough, you can also use functions,
// The first argument is a new queryBuilder object. Non-arrow functions can also use this without arguments, and in some scenarios have a second argument, tableName
// The return value can be an object of type {SQL: string, values: any[]} or none, if not, use a queryBuilder object
qb.select({ min: qb= > qb.min('a')})// => select min(`a`)
qb.select([() = > ({ sql: 'max(??) as ?? '.values: ['a'.'maxA']}),'b']) // => select max(`a`) as `maxA`, `b`
qb.select({ t: { avg(qb, tableName) { this.avg([tableName, 'a'].'a')},b: 'b'}})// => select avg(`t`.`a`) as `a`, `t`.`b` as `b`
Copy the code

count max min avg sum

The parameter types

type name = string
type tableName = string

function

Qb -mysql supports count, Max, min, avg, sum, and subfunctions of select

The list of	instructions
count(as? : name) => qb	There is no
max(field: name, as? : name) => qb	There is no
min(field: name, as? : name) => qb	There is no
avg(field: name, as? : name) => qb	There is no
sum(field: name, as? : name) => qb	There is no
max([tableName, name], as? : name) => qb	Contains the name of the table
min([tableName, name], as? : name) => qb	Contains the name of the table
avg([tableName, name], as? : name) => qb	Contains the name of the table
sum([tableName, name], as? : name) => qb	Contains the name of the table

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// A single field is considered a field if it is a string, and a number or Boolean is considered a value
qb.count('count') // => count(*) as count
qb.avg('a') // => avg(`a`)
qb.max('a'.'maxA') // => max(`a`) as `maxA`
qb.min(['t'.'a'].'min') // => min(`t`.`a`) as `min`
qb.select((sub) = > sub.sum('a'.'sum')) // => select sum(`a`) as `sum`
Copy the code

from

The parameter types

type name = string

function

The list of	instructions
from(tableName: name, rename? : name) => qb	There is no

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// A single field is considered a field if it is a string, and a number or Boolean is considered a value
qb.from('t') // => from `t`
qb.from('t'.'r') // => from `t` `r`
Copy the code

join

The parameter types

type name = string
type targetTableKey = string
type sourceTableKey = string
type mapping = { [key: targetTableKey]: sourceTableKey }

function

It contains join, innerJoin, leftJoin, rightJoin, crossJoin and outerJoin. When the join function is used, there is a last prefix parameter in the parameter list, which can be used to add modifiers before join to make up for the lack of built-in functions

The list of	instructions
from(sourcetable: name).join(targetTable: name, mapping: mapping) => qb	When used together with from, use the first two parameters. If you want to specify prefix, you need to complete four parameters
join(targetTable: name, mapping: mapping, sourceTabel: name, prefix? : string) => qb	Used independently, a third parameter is required
join(params: { target: name, mapping: mapping, source: name }, prefix? : string) => qb	Used independently, the first argument is of type object

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// use with from
qb.from('t1').join('t2', { t1_id: 'id' }) // => from `t1` join `t2` on `t2`.`t1_id` = `t1`.`id`

// Use it independently, usually with multiple tables
qb.from('t1').join('t2', { t1_id: 'id' }).join('t3', { t2_id: id }, 't2') 
// => from `t1` join `t2` on `t2`.`t1_id` = `t1`.`id` join `t3` on `t3`.`t2_id` = `t2`.`id`

// The first argument can be object
qb.join({ source: 't1'.target: 't2'.mapping: { t1_id: id } }) => // join `t2` on `t2`.`t1_id` = `t1`.`id`
Copy the code

where

The parameter types

type name = string
type cb = (qb: queryBuilder) => queryBuilder | { sql: string, values: any[] } | void
type value = string | number | boolean | cb | null | undefined
type valueArr = value[]
type ops = ‘in’ | ‘not in’ | ‘like’ | ‘not like’ | ‘is’ | ‘is not’ | ‘! = ‘|’ > ‘|’ < ‘|’ > = ‘|’ < = ‘|’ = ‘|’ < > ‘|’ < = > ‘
type opVal = { operator: ops, value: value, prefix? : boolean, suffix? : boolean }
type opsInObj = ‘in’ | ‘not in’ | ‘like’ | ‘not like’ | ‘%like’ | ‘not %like’ | ‘like%’ | ‘not like%’ | ‘is’ | ‘is not’ | ‘! = ‘|’ > ‘|’ < ‘|’ > = ‘|’ < = ‘|’ = ‘|’ < > ‘|’ < = > ‘
type opObj = { [key: opsInObj]: val }
type conditionObj = { [key: name]: value | valueArr | opVal | opObj }
type conditionObjWithOr = { [anyKey: string]: conditionObj[] }
type withTable = { [table: name]: conditionObje | conditionObjWithOr }
type conditionArr = (conditionObj | conditionObjWithOr | withTable)[]

function

The list of	instructions
where(condition: conditionObj, tableName? : name) => qb	The outermost layer uses and and only AND connections
where(condition: conditionObjWithOr, tableName? : name) => qb	The outermost layer is connected by and, with or and clauses in the middle
where(condition: withTable) => qb	Multiple tables are filtered together
where(condition: conditionArr, tableName? : name) => qb	The most layer uses OR connection, including and, or clauses in the middle

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

/ * * * * * * * * * * * * * * * * * * * * * * and connection only * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
// Value is a simple type
qb.where({ a: 1.b: 'b'.c: true }) 
// => where `a` = 1 and `b` = 'b' and `c` = true

// Value is an array of simple types, converted to the in function, using the second argument, specifying the table name
qb.where({ a: [1.2 ,3].b: 'str' }, 't') 
// => where `t`.`a` in (1, 2, 3) and `t`.`b` = `str`

// value uses the sub function
qb.where({ a: sub= > sub.select('f1').from('t').where({ f2: 1 }), b: 2 })
// => where `a` = (select `f1` from `t` where `f2` = 1) and b = 2

// value uses {operator, value} objects
qb.where({ a: { operator: '>'.value: 1 }, b: {  operator: 'like'.value: 'str'.prefix: true}})// => where `a` > 1 and `b` like '%str'

// value uses the {operator, value} object, and the value of the object is a sub function
qb.where({ a: { operator: 'in'.value: sub= > sub.select('f1').from('t').where({ f2: 1})},b: 1 })
// => where `a` in (select `f1` from `t` where `f2` = 1) and b = 1

// value Specifies the object whose key is the fixed operator value
qb.where({ a: { in: [1.2.3]},b: { '>': 1.'<': 10}})// => where `a` in (1, 2, 3) and `b` > 1 and `b` < 10'

// value specifies an object whose key is a fixed operator value and whose value is a sub function
qb.where({ a: { in: sub= > sub.select('f1').from('t').where({ f2: 1})},b: 1 })
// => where `a` in (select `f1` from `t` where `f2` = 1) and b = 1

/ * * * * * * * * * * * * * * * * to the or conditions of use and connection, namely through promotion () or the priority of the * * * * * * * * * * * * * * * * * * * * * * * * /
// When the value of an object is an array of objects, the objects in the array are joined by or, and the priority is raised by (). Objects in the array containing multiple keys are still joined by and
// If the key of the object is repeated, it will be overwritten.
qb.where({
  a: { is: undefined },
  or1: [{b: { '<': 2.'>': 0}}, {b: [1.2]},],or2: [{c: 1 },
    { d: { 'like%': str } },
    { e: '3'.f: true.or: [{ g: { is: null}}, {g: { like: 'gg'}}}]],h: 'str',},'t')
// Add space and indentation that do not exist for easy reading
// => where `t`.`a` is NULL
// and ((`t`.`b` < 2 and `t`.`b` > 0) or `t`.`b` in (1, 2))
// and (`t`.`c` = 1
// or `t`.`d` like 'str%'
// or (`t`.`e` = '3' and `t`.`f` = true and (`t`.`g` is NULL or `t`.`g` like '%gg%'))
/ /)
// and `t`.`h` = 'str'

/ * * * * * * * * * * * * * * * * * * joint query multiple tables, multiple table fields and filter * * * * * * * * * * * * * * * * * * * * * * * * * /
// The table name is the key of the parameter object, and the value of the object is the conditional expression, using the same schema as the first parameter of the previous function
// Add a layer of tableName,
// The tableName value cannot be operator, value, or operator values (e.g., in, is).
// Because, once the key is set to, these values fall back to the operator and are no longer treated as tableName

// The second parameter does not need to be passed
qb.where({ t1: { a: { '=': 1}},t2: { b: { like: 'a'}}},'t')
// => where `t1`.`a` = 1 and `t2`.`b` like '%a%'

qb.where({
  t1: { a: { is: undefined}},or1: [{t1: { b: { '<': 2.'>': 0}}}, {t2: { b: [1.2]}},],or2: [{t3: { c: 1}}, {t3: { d: { '<': 10}}}, {t3: { e: '3' },
      t4: { f: true },
      or: [{t4: { g: { is: null}}}, {t4: { g: { like: 'gg'}}},],},t5: { h: 'str'}})// Add space and indentation that do not exist for easy reading
// => where `t1`.`a` is NULL
// and ((`t1`.`b` < 2 and `t1`.`b` > 0) or `t2`.`b` in (1, 2))
// and (`t3`.`c` = 1
// or `t3`.`d` < 10
// or (`t3`.`e` = '3' and `t4`.`f` = true and (`t4`.`g` is NULL or `t4`.`g` like '%gg%'))
/ /)
// and `t5`.`h` = 'str

/ * * * * * * * * * * * * * * * * * * use the outermost layers or connection statement * * * * * * * * * * * * * * * * * * * * * * * /
// When the argument is not an object, but an array of objects, the statements generated by each object use or join, and the join rules within the object are the same as the previous function
/ / without tableName
qb.where([{ a: { '>': 0.'<': 10}}, {a: 15 }])
// => where (`a` > 0 and `a` < 10) or `a` = 15'

// Take a single tableName
qb.where([{ a: { '>': 0.'<': 10}}, {a: 15}].'t')
// => where (`t`.`a` > 0 and `t`.`a` < 10) or `t`.`a` = 15'

// Multiple tableName, the second parameter does not need to pass, also invalid
qb.where([
  { t1: { a: { '>': 0.'<': 10}}}, {t1: { a: 15}}, {t2: { c: { is: undefined}},or1: [{t3: { b: { '<': 2.'>': 0}}}, {t3: { b: [1.2]}},],or2: [{t4: { d: 20}}, {t5: { d: { '<': 10}}}, {t6: { e: '3' },
        t7: { f: true },
        or: [{t7: { g: { is: null}}}, {t8: { g: { like: 'gg'}}},],},t9: { h: 'str'}},])// Add space and indentation that do not exist for easy reading
// => where (`t1`.`a` > 0 and `t1`.`a` < 10)
// or `t1`.`a` = 15
// or (`t2`.`c` is NULL
// and ((`t3`.`b` < 2 and `t3`.`b` > 0) or `t3`.`b` in (1, 2))
// and (`t4`.`d` = 20
// or `t5`.`d` < 10
// or (`t6`.`e` = '3'
// and `t7`.`f` = true
// and (`t7`.`g` is NULL or `t8`.`g` like '%gg%')
/ /)
/ /)
// and `t9`.`h` = 'str'
/ /)
Copy the code

order by

The parameter types

type field = string
type order = ‘desc’ | ‘asc’ | ‘DESC’ | ‘ASC’
type fieldOrder = string
type fieldsOrder = { [key: field]: order }

function

The list of	instructions
order(param: fieldOrder) => qb	To sort a field, use a space between the sorting method and the field
order(param: fieldsOrder) => qb	Sort multiple fields. Key is the field, value is the sort mode, and value is empty

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// Sort a field
qb.order('a') // => order by `a`
qb.order('a desc') // => order by `a` desc

// Sort multiple fields
qb.order({ a: ' '.b: 'desc' }) // => order by `a`, `b` desc
qb.order({ a: 'DESC'.b: 'ASC' }) // => order by `a` DESC, `b` ASC
Copy the code

page limit offset

The parameter types

type val = number

function

The list of	instructions
limit(size: val) => qb	Size must be greater than 0
offset(size: val) => qb	Size must be greater than 0
page(size: val, page? : val) => qb	Page is a combination of limit and offse

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

qb.limit(1) // => limit 1
qb.offset(1) // => offset 1
qb.page(10) // => limit 10
qb.page(10.1) // => limit 10
qb.page(10.2) // => limit 10 offset 10
Copy the code

group by

The parameter types

type name = string
type table = { [key: name]: name | name[] }

function

The list of	instructions
group(field: name, tableName? : name) => qb	Aggregate individual fields
group(fields: name[], tableName? : string) => qb	Aggregate multiple fields
group(fields: table) => qb	Field aggregation of multiple tables

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// Single field aggregation
qb.group('a') // => group by `a`
qb.group('a'.'t') // => group by `t`.`a`

// Aggregate multiple fields
qb.group(['a'.'b']) // => group by `a` `b`
qb.group(['a'.'b'].'t') // => group by `t`.`a` `t`.`b`

// Aggregate the fields of multiple tables
qb.group({ t: 'a' }) // => group by `t`.`a`
qb.group({ t1: ['a'.'b'].t2: 'c' }) // => group by `t1`.`a` `t1`.`b` `t2`.`c`
Copy the code

insert

The parameter types

type name = string
type data = { [key: string]: string | number | boolean | null | undefined }

function

The list of	instructions
insert(tableName: name, data: data) => qb	Insert a single row of data
insert(tableName: name, data: data[]) => qb	Insert multiple rows of data

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// Insert a single row of data using the set statement
qb.insert('t', { a: 1.b: '2'.c: true }) // => insert into `t` set `a` = 1, `b` = '2', `c` = true

// Insert multiple rows of data using values statement
qb.insert('t'[{a: 1.b: 'b' }, { a: 2.b: 'b' }]) // => insert into `t` (`a`, `b`) values (1, 'b'), (2, 'b')

Copy the code

update

The parameter types

type name = string
type data = { [key: string]: string | number | boolean | null | undefined }
Type the where = Array | Object / / reference the where part

function

The list of	instructions
update(tableName: name, data: data, where? : where) => qb	The third WHERE parameter is optional and has the same type as the WHERE function parameter

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

qb.update('t', { a: true.b: 'str'.c: nullD:1 }) 
// => update `t` set `a` = true, `b` = 'str', `c` = NULL, `d` = 1
qb.update('t', { a: true.b: 'str'.c: null[{},id: 1 }, { f: 2 }])
// => update `t` set `a` = true, `b` = 'str', `c` = NULL where `id` = 1 or `f` = 2
Copy the code

delete

The parameter types

type name = string
Type the where = Array | Object / / reference the where part

function

The list of	instructions
delete(tableName: name, where? : where) => qb	The second WHERE parameter is optional and has the same type as the WHERE function parameter

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// A single field is considered a field if it is a string, and a number or Boolean is considered a value
qb.delete('t') // => delete from `t`
qb.delete('t', { id: 1 }) // => delete from `t` where `id` = 1'
Copy the code

sub padEnd

When the built-in functions do not meet the requirements, you can customize SQL with the sub higher-order function, which accepts only one parameter of the type of callback function, which has one parameter, qb.

Qb is a brand new queryBuilder object, and this points to the queryBuilder object when the callback uses a non-arrow function, so using this is equivalent to using QB

The return value of the callback function is optional. If there is a return value, the return value must contain {SQL: string, values: []}, qb-mysql will use the values of SQL and values to generate the query object. If no value is returned, the value of queryBuilder will be used directly.

PadEnd is an auxiliary function used to append content to queryBuilder. SQL and queryBuilder.value. It is usually used in conjunction with the sub function.

The parameter types

type cb = (qb: queryBuilder) => queryBuilder | { sql: string, values: any[] } | void
type values = any[]
type val = string | values

function

The list of	instructions
sub(callback: cb) => qb	The cb function argument is a new queryBuilder object with an optional return value
padEnd(str: val, values? : values) => qb	Append string to queryBuilder.sql, and the second parameter appends value to queryBuilder.values

example

The following results are obtained by calling console.log(mysql.format(… Qb. ToQuery ()))

// Use subqueries
qb.select('f1')
  .from('t1')
  .padEnd(' where ?? in ('['f2'])
  .sub((builder) = > builder.select('f3').from('t2')
  .padEnd(') ')
// => 'select `f1` from `t1` where `f2` in (select `f3` from `t2`)
Copy the code

clear

Qb-mysql supports multiple queries. When using multiple statements (select, insert, update, delete), semicolons (;) are automatically inserted between each statement.

QueryBuilder = new queryBuilder () const queryBuilder = new queryBuilder () Querybuilder.clear () can also be called on an already instantiated queryBuilder object

The parameter types

There is no

function

The list of	instructions
clear() => qb	Typically used to clear the previously generated content when the query is generated by multiple calls to the function in the same scope

example

// 1. Create an instance
 const qb = new QueryBuilder()
 qb.select('a').from('t').select(qb= > qb.count()).form('t')
 // => select `a` from `t`; select count(*) from `t`
 
 // 2. If clear is called, proceed with the following statement in the same scope
 qb.clear().select(qb= > qb.count()).form('t')
 // => select count(*) from `t`
 
 // 2. If clear is not called, proceed with the following statement in the same scope
 qb.select(qb= > qb.count()).form('t')
 // => select `a` from `t`; select count(*) from `t`; select count(*) from `t`
Copy the code

Matters needing attention

To minimize the code volume, QB-mysql does not perform syntax analysis and detection. Therefore, query objects will be generated even if there are errors in the statement.
- For example, qb.from(‘t’).select(‘a’) is generatedfrom `t`select `a`
- Pay special attentionorder.limit.offsetIf the order is reversed, the SQL query will be abnormal

Qb-mysql supports multiple queries. When multiple statements are used, semicolons (;) are automatically inserted between each statement. , but note that this is not transaction-safe, for example

// Query the length of the table
qb.select('a').from('t').select(qb= > qb.count()).form('t')
// => select `a` from `t`; select count(*) from `t`

// Query data at the same time as updating data. Note that this is not transaction secure, because different engines may check the data before the update
qb.update('t', { a: 'a' }, { id: 1 } ).select().form('t').where({ id: 1 })
// => update `t` set `a` = 'a' where `id` = 1; select * from `t` where `id` = 1
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

A small and powerful artifact – QB-mysql

background

Installation and use

API

select

count max min avg sum

from

join

where

order by

page limit offset

group by

insert

update

delete

sub padEnd

clear

Matters needing attention

A small and powerful artifact – QB-mysql

background

Installation and use

API

select

count max min avg sum

from

join

where

order by

page limit offset

group by

insert

update

delete

sub padEnd

clear

Matters needing attention

Related Posts

Front-end performance optimization – Picture lazy loading (anti-shake, throttling)

Jest Test RECORDS ESM problems

How do junior engineers grow quickly and seek breakthroughs