Security News
The Unpaid Backbone of Open Source: Solo Maintainers Face Increasing Security Demands
Solo open source maintainers face burnout and security challenges, with 60% unpaid and 60% considering quitting.
mysql-qbuilder
Advanced tools
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.
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.
Before to start to used the mysql-qbuilder is need to set some options In this section is show What You need for to work with mysql-qbuilder
const qBuilder = require('mysql-qbuilder')
qBuilder.setOptions({
hostname: 'hostName',
username: 'userName',
password: 'passWord',
database: 'databaseName'
})
qBuilder.connectToDatabase()
qBuilder.makeQuery()
sql query clauses
SELECT
SELECT ADD
INSERT
DELETE
UPDATE
ORDER BY
GROUP BY
OFFSET
LIMIT
WHERE
WHERE AND
WHERE OR
WHERE AND OR
WHERE NOT
WHERE COLUMN
WHERE DATE
WHERE YEAR
WHERE MONTH
WHERE DAY
WHERE IN
WHERE NOT IN
WHERE BETWEEN
WHERE NOT BETWEEN
WHERE NULL
WHERE NOT NULL
JOIN
FROM
TABLE
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') // Oops I forgot the name column
.addSelect('name') // Alright I added now
.from('tableName') // set Database table
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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']) // set the column name on database
qBuilder.prepare()
.setParameters([5, 'SomeTitle', 'SomeName']) // set the values of new record
.execute() // Save the new record on database
from()
table()
The from and table method is just set the table of query
qBuilder.makeQuery()
.select('id, title, count') // Oops I forgot the name column
.from('tableName') // set Database table
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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()
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) // The last parameter is for where clause
.execute()
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') // set Database table
.join('anotherTable', 'tableName.anotherTableId = anotherTable.id', 'Inner')
.where('tableName.id' '>' 2)
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
orderBy()
Is A method which You can order by some column
qBuilder.makeQuery()
.select('title, count, name')
.from('tableName') // set Database table
.where('id' '>' 2)
.orderBy('name', true) // Then is to be sorted in DESC
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
groupBy()
Is A method which You can group by some column
qBuilder.makeQuery()
.select('title, count, name')
.from('tableName') // set Database table
.where('id' '>' 2)
.groupBy('name')
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
take()
Is A method which You can get only few records from database
qBuilder.makeQuery()
.select('title, count, name')
.from('tableName') // set Database table
.where('id' '>' 2)
.take(500) // Get first 500 results
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
skip()
Is A method which You can skip first few records from database
qBuilder.makeQuery()
.select('title, count, name')
.from('tableName') // set Database table
.where('id' '>' 2)
.take(500) // Get results from 201 to 701
.skip(200) // Skip the first 200 results
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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()
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() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
WhereNot()
Where clause is is check do condition is not true
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNot('id', '=', 10, 'OR') // Get all Elements where id is not equal to 10
.andWhere('name', '=', 'Simon')
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
whereNull()
Get all records which the column is null
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNull('name') // get all records which the column name is null
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
whereNotNull()
Get all records which the column is not null
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNotNull('name') // get all records which the column name is NOT null
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
whereBetween()
Get all records which the column is between two values
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereBetween('id', [22, 300], 'OR') // Get all records between 22 and 300
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
whereNotBetween()
Get all records which the column is not between two values
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereNotBetween('id', [22, 300], 'AND') // Get all records which is not between 22 and 300
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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') // Get all records with id 2, 3, 4, 6, 8
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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') // Get all records which the id is not 2, 3, 4, 6, 8
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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') // Get all records which the title and name is same
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
whereYear
Get all records where the column createTime is from 2010
qBuilder.makeQuery()
.select('*')
.from('tableName')
.whereYear('createTime', '=', '2010', 'OR')
qBuilder.prepare().getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
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() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.prepare().getResult((err, data) => { // Old version less of 1.5.1
// data is the array of objects or just single object
})
Is possible to used when You want to add new record on database
// Build the query from all simple parts
qBuilder.prepare()
// Set all parameters which You need to used on mysql query builder
.setParameters([param1, param2, param3])
// Just execute the query and don't return some result
.execute()
Is possible to used when You want to get some records from database
// Build the query from all simple parts
qBuilder.prepare()
// Set all parameters which You need to used on mysql query builder
.setParameters([param1, param2, param3])
// Get the result of executed query
qBuilder.getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.getResult((err, data) => { // Old version less of 1.5.1
if (err) {
console.log(err)
} else {
// make something with data which is result of mysql query execution
}
})
From v1.3.1 Is possible to used mysql-qbuilder much easier for some common cases like
For to Used Helper Queries is not need to start with makeQuery()
Is need to start with useScheme('tableName')
If You want to used same table from database then is not need anymore to set table only used useScheme()
Add new record on database
qBuilder.useScheme('tableName')
// First field is Object where the keys is column of database and values is the values of columns
.add({ 'username': 'administrator', 'email': 'administrator@admin.com' })
qBuilder.execute()
Get first record from table
qBuilder.useScheme() // used same table like before
// First field is String or Array with all columns which You want to get from database
.getFirst('email, username')
qBuilder.getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.getResult((err, data) => { // Old version less of 1.5.1
if (err) {
console.log(err)
} else {
// make something with data which is result of mysql query execution
}
})
Get last record from table
qBuilder.useScheme() // used same table like before
// First field is String or Array with all columns which You want to get from database
.getLast('email, username')
qBuilder.getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.getResult((err, data) => { // Old version less of 1.5.1
if (err) {
console.log(err)
} else {
// make something with data which is result of mysql query execution
}
})
Get all records from table
qBuilder.useScheme() // used same table like before
// First field is String or Array with all columns which You want to get from database
.getAll('email, username')
qBuilder.getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.getResult((err, data) => { // Old version less of 1.5.1
if (err) {
console.log(err)
} else {
// make something with data which is result of mysql query execution
}
})
Find record on database by entered id
qBuilder.useScheme('tableName')
// First field is id of the record
// Second is all columns which You want to get from database
.findById(2, 'email')
// OR
.findById([2, 3, 4], 'email') // Is find records with id 2, 3 or 4
qBuilder.getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.getResult((err, data) => { // Old version less of 1.5.1
if (err) {
console.log(err)
} else {
// make something with data which is result of mysql query execution
}
})
Find records on database by many search conditions or (only one)
qBuilder.useScheme('tableName')
// First field is Object where the keys is column of database and values is the values of columns
// Second is all columns which You want to get from database
// Third is do You want every condition to be true or only one
.findByFields({ 'id': 2, 'username': 'administrator' }, 'email', 'or')
// Or Get all records which is with id 2, 3 or 4 or have username administrator or userNaMe
.findByFields({ 'id': [2, 3, 4], 'username': ['administrator', 'userNaMe'] }, 'email', 'or')
qBuilder.getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
qBuilder.getResult((err, data) => { // Old version less of 1.5.1
if (err) {
console.log(err)
} else {
// make something with data which is result of mysql query execution
}
})
qBuilder.setCommand('SELECT * FROM Table WHERE id > ?')
// Build the query from all simple parts
.prepare()
// Set all parameters which You need to used on mysql query builder
.setParameters([param1])
// Get the result of executed query
.getResult() // New version bigger of 1.5.1
.then(result => {
})
.catch(err => {
})
.getResult((err, data) => { // Old version less of 1.5.1
if (err) {
console.log(err)
} else {
// make something with data which is result of mysql query execution
}
})
qBuilder.getMysql()
add
Add record on databasegetFirst
Get first element from tablegetLast
Get last element from tablegetAll
Get all elements from tablefindById
Find record from table by idfindByFields
Find records from table by selected few columnsWhereDay
WhereMonth
WhereYear
FAQs
Query Builder for MySQL
The npm package mysql-qbuilder receives a total of 0 weekly downloads. As such, mysql-qbuilder popularity was classified as not popular.
We found that mysql-qbuilder demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Solo open source maintainers face burnout and security challenges, with 60% unpaid and 60% considering quitting.
Security News
License exceptions modify the terms of open source licenses, impacting how software can be used, modified, and distributed. Developers should be aware of the legal implications of these exceptions.
Security News
A developer is accusing Tencent of violating the GPL by modifying a Python utility and changing its license to BSD, highlighting the importance of copyleft compliance.