mysql-qbuilder
SQL Query builder working with NodeJS
npm install mysql-qbuilder --save --save-exat
To add this npm package to your local machine, type the above into your command line. You will notice a node_modules directory appear in your root where the package is now installed.
Introduction
mysql-qbuilder is NodeJS module provides a convenient, fluent interface to creating and running database queries.
It can be used to perform most database operations in your application.
mysql-qbuilder query builder uses mysql module to protect your application against SQL injection attacks.
There is no need to clean strings being passed as bindings.
What You get from that module
All common cases to use the sql query clauses
SELECT
-
INSERT
DELETE
UPDATE
ORDER BY
GROUP BY
OFFSET
LIMIT
WHERE
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
JOIN
FROM
TABLE
How to use
Require the module
const qBuilder = require('mysql-qbuilder')
Set the parameters for mysql connection
qBuilder.setOptions({
hostname: 'hostName',
username: 'userName',
password: 'passWord',
database: 'databaseName'
})
Then connecto to database
qBuilder.connectToDatabase()
Start to make query
qBuilder.makeQuery()
Select select()
addSelect()
Some times You don't want to select all columns from database
Then You need to enter just the columns.
If You want just write *
qBuilder.makeQuery().select('id, title, count')
qBuilder.makeQuery().select(['id', 'title', 'count'])
qBuilder.makeQuery().select('*')
If You forget some column then You can add that columns with addSelect function
If You forget some column then You can add that columns with addSelect function
qBuilder.makeQuery()
.select('id, title, count')
.addSelect('name')
.from('tableName')
qBuilder.prepare()
.getResult((err, data) => {
})
Insert add()
Many times You don't want to get the data from database. Just want to add new record
qBuilder.makeQuery()
.table('tableName')
.add(['count', 'title', 'name'])
qBuilder.prepare()
.setParameters([5, 'SomeTitle', 'SomeName'])
.execute()
From, Table from()
table()
The from and table method is just set the table of query
qBuilder.makeQuery()
.select('id, title, count')
.from('tableName')
qBuilder.prepare()
.getResult((err, data) => {
})
DELETE delete()
The query builder may also be used to delete records from the table used delete function
qBuilder.makeQuery()
.table('tableName')
.delete()
.where('id', '=', 10)
qBuilder.prepare().execute()
UPDATE update()
Some times You don't want to select or add new columns or delete
Some times is need just update the existing record in database. This is possible with update function
qBuilder.makeQuery()
.table('tableName')
.update(['title', 'count', 'name'])
.where('id', '=')
qBuilder.prepare()
.setParameters(['newTitle', 25, 'newName'], 10)
.execute()
JOIN join()
With Join is possible to get from database record from two tables with one query.
qBuilder.makeQuery()
.select('tableName.title, anotherTable.name')
.from('tableName')
.join('anotherTable', 'tableName.anotherTableId = anotherTable.id', 'Inner')
.where('tableName.id' '>' 2)
qBuilder.prepare()
.getResult((err, data) => {
})
ORDER BY orderBy()
Is A method which You can order by some column
qBuilder.makeQuery()
.select('title, count, name')
.from('tableName')
.where('id' '>' 2)
.orderBy('name', true)
qBuilder.prepare()
.getResult((err, data) => {
})
GROUP BY groupBy()
Is A method which You can group by some column
qBuilder.makeQuery()
.select('title, count, name')
.from('tableName')
.where('id' '>' 2)
.groupBy('name')
qBuilder.prepare()
.getResult((err, data) => {
})
LIMIT take()
Is A method which You can get only few records from database
qBuilder.makeQuery()
.select('title, count, name')
.from('tableName')
.where('id' '>' 2)
.take(500)
qBuilder.prepare()
.getResult((err, data) => {
})
OFFSET skip()
Is A method which You can skip first few records from database
qBuilder.makeQuery()
.select('title, count, name')
.from('tableName')
.where('id' '>' 2)
.take(500)
.skip(200)
qBuilder.prepare()
.getResult((err, data) => {
})
NOTICE From Previous version and that version the some secondary where clauses have one more extra (optional) parameter (andOr), where can set AND or OR. On previous version was AND and now default value is AND
WHERE where()
You may use the where method on a query builder instance to add where clauses to the query
The first argument is column name
The second argument is operator which You used (by default is =)
The third argument is the value of column name on database
qBuilder.makeQuery()
.select('*')
.from('tableName')
.where('id', '=', 10)
qBuilder.prepare().getResult((err, data) => {
})
WhereNot()
Where clause is is check do condition is not true
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNot('id', '=', 10, 'OR')
.andWhere('name', '=', 'Simon')
qBuilder.prepare().getResult((err, data) => {
})
andWhere()
Add another state and tell of the query to be both equal to true
qBuilder.makeQuery()
.select('*')
.from('tableName')
.where('id', '=', 10)
.andWhere('name', '=', 'Simon')
qBuilder.prepare().getResult((err, data) => {
})
orWhere()
Add another state and tell of the query to be first or second or both equal to true
qBuilder.makeQuery()
.select('*')
.from('tableName')
.where('id', '=', 10)
.orWhere('name', '=', 'Simon')
qBuilder.prepare().getResult((err, data) => {
})
andOrWhere()
The andOrWhere function is can used if You want first where which You call to be true
and add another where which tell or to be first or second operator
Like example is tell give me every record with name Simon and Id to be > or < of 35
qBuilder.makeQuery()
.select('*')
.from('tableName')
.where('name', '=', 'Simon')
.andOrWhere('id', '>', '<' 35)
qBuilder.prepare().getResult((err, data) => {
})
whereNull()
Get all records which the column is null
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNull('name')
qBuilder.prepare().getResult((err, data) => {
})
whereNotNull()
Get all records which the column is not null
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNotNull('name')
qBuilder.prepare().getResult((err, data) => {
})
whereBetween()
Get all records which the column is between two values
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereBetween('id', [22, 300], 'OR')
qBuilder.prepare().getResult((err, data) => {
})
whereNotBetween()
Get all records which the column is not between two values
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNotBetween('id', [22, 300], 'AND')
qBuilder.prepare().getResult((err, data) => {
})
whereIn
Get all records which the column value is equal to some parameters which is set
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereIn('id', [2, 4, 6, 3, 8], 'AND')
qBuilder.prepare().getResult((err, data) => {
})
whereNotIn
Get all records which the column value is not equal to some parameters which is set
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNotIn('id', [2, 4, 6, 3, 8], 'OR')
qBuilder.prepare().getResult((err, data) => {
})
whereColumn
Get all records which the first column and second column has same values on database
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereColumn('title', 'name', '=', 'OR')
qBuilder.prepare().getResult((err, data) => {
})
whereDate
Get all records where the column createTime is equal to 2010-04-01 Date Time
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereDate('createTime', '=', '2010-04-01')
qBuilder.prepare().getResult((err, data) => {
})
whereYear
Get all records where the column createTime is from 2010
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereYear('createTime', '=', '2010', 'OR')
qBuilder.prepare().getResult((err, data) => {
})
whereMonth
Get all records where the column createTime is with month equal to 10
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereMonth('createTime', '=', '10', 'OR')
qBuilder.prepare().getResult((err, data) => {
})
whereDay
Get all records where the column createTime is with day equal to 22
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereDay('createTime', '=', '22', 'OR')
qBuilder.prepare().getResult((err, data) => {
})
Used one of that functions for create sql query
.select('id, title, someDiff')
.addSelect(['count', 'name'])
.add(['title', 'count', 'name'])
.from('tableName')
.table('tableName')
.delete()
.update(['title', 'count', 'name'])
.join('roles', 'roles.id = users.roleId' 'INNER')
.take(500)
.skip(500)
.orderBy('count', true)
.groupBy('count', false)
.where('id', '>', 5)
.whereNot('id', '=', 5)
.andWhere('id')
.orWhere('count', '=', 10)
.andOrWhere('count', '>', '<', [10, 30])
.whereNull('title', 'OR')
.whreNotNull('title', 'AND')
.whereBetween('count', [10, 30])
.whereNotBetween('count', [10, 30], 'OR')
.whereIn('count', [5, 10, 15, 20, 25], 'AND')
.whereNotIn('title', ['first', 'second', 'third', 'fourth'], 'AND')
.whereDate('createTime', '>', '2010-04-01', 'OR')
.whereDay('createTime', '=', 22, 'AND')
.whereMonth('createTime', '=', 10, 'OR')
.whereYear('createTime', '=', 2010, 'OR')
.whereColumn('title', 'name', '=', 'OR')
After we finish with the build the query is need to prepare and execute
qBuilder.prepare()
.setParameters([param1, param2, param3])
.execute()
OR
qBuilder.prepare()
.setParameters([param1, param2, param3])
.getResult((err, data) => {
if (err) console.log(err)
console.log(data)
})
If You don't trust of the developer then You have option to write Your own query
qBuilder.setCommand('SELECT * FROM Table WHERE id > ?')
.prepare()
.setParameters([param1])
.getResult((err, data) => {
if (err) console.log(err)
console.log(data)
})
For more mysql advanced functions You can used that which return the mysql module
qBuilder.getMysql()
Change log
- v1.2.1
-
- Add
WhereDay
WhereMonth
WhereYear
-
- Add One more (Optional) Parameter on Where methods which is possible choose between AND or OR (Default = AND)