Welcome to NodeQB
Query builder for node mysql. Inspired concept from laravel
Install
yarn add nodeqb
npm install nodeqb
Usage
const NodeQB = require("nodeqb");
const db = new NodeQB({
type: "mysql",
method: "pool",
defaults: {
orderColumn: "createdAt"
},
config: {
host: 'your-host',
port: 'your-port',
database: "your-database",
user: 'your-username',
password: 'your-password',
connectionLimit: 10,
}
})
Documentation
Maximum all support function from laravel query builder.
How to use
Replace the ->
and ::
with .
In php laravel
DB::table('tableName')->get()
In NodeQB
prefer use table()
call on first. It will reset the previous query value
db.table('tableName').get()
Await / callback
we are supporting both await and callback method
const result =await db.table('tableName').get();
console.log(result)
db.table('tableName').get((err,results,fields)=>{
if (err){
return
}
console.log(results)
})
Error Handling
You could handle the all error via catch function as well callback
checkout the Error response object
In error object. we have one custom key for detecting error type
err.errorType
- connection ->
connection failure error
- query ->
query related errors
db.table('tableName').get((err,results,fields)=>{
if (err){
console.log(err.errorType)
return
}
console.log(results)
})
const result =await db.table('tableName').get().catch(err=>{
if (err){
console.log(err.errorType)
}
})
console.log(result)
if (result){
}
Escape
Note:*
All these type of where
,having
inputs escaped with mysql escape string method. So no need escape string/object/callback
inside this method.
If you are using escape again in this method maybe you have query error
If you need escape method
db.escapeAll(passYourInput)
Methods
.getQuery()
Get the compiled final query output sql string
const query = db.table('tableName').select('colA','colB').getQuery()
console.log(query)
GetRows methods
MultiRow
.get()
-> await/callback
=>[]
returning multiple row array response
SingleRow
.first()
-> await/callback
=>{}
returning single row object response
db.table('tableName').first()
ForceMethod
.getForce
-> await/callback
=>[]
.getForceSingle
-> await/callback
=>{}
Quick way get the result. This will ignore the unnecessary columns from select. That means filter the table columns names with select columns
Purpose of creation GraphQL
, we could use this method instead of select *
on graphql resolver
On below method assume the mail
column not available on table.While running force method invalid column removed on execution
await db.table('tableName').select('user_email',"mail","user_mobile").getForceSingle()
Available: v3.1.0-Beta and above
.forceInsert
-> await/callback
=>[]
.forceUpdate
-> await/callback
=>{}
same like above method it will ignore unnecessary columns . For below example it will remove the invalid_col
await db.table('tableName').forceInsert({ 'user_email':'test@mail' ,"invalid_col":'test val'})
WHERE
.where()
, .orWhere()
, .having()
, .orHaving()
All below usages are supported above method
Params Methods
- Single Condition
(column,condition,value) or (column,value)
- Array
[Single condition]
- Object
{column:value} or {"columnWithCondition":value}
- Callback
(query)=> query.methods
condition supported ['>', '<', '>=', '<=', '!=', '=', 'like']
Single Condition
db.table('tableName').where('columName','condition','value').get()
db.table('tableName').where('someColum','>','someValue').get()
db.table('tableName').where(['someColum','>','someValue']).get()
condition is =
.No need to add condition just two params if enough
db.table('tableName').where('columName','value').get()
Multi Condition
You could add the condition on key. All are AND
db.table('tableName').where({
columName:"value",
"columnId>":10,
"columnName>=":"test"
}).get()
Callback
db.table("tableName").where("columName","value").where((q)=>{
return q.where("name","value").orWhere("name",100)
})
AND | OR
db.table("tableName").where('columnName',"value").orWhere("columName",'!=',"value").get()
.whereAnd()
, .whereOR
it just concatenates the string on the chain. very rar case you could use this instead writing raw query
SELECT
.select()
db.table('tableName').select("colA","colB","colC","colD").get()
.addSelect()
db.table('tableName').select("colA","colB","colC").addSelect("colD").get()
.distinct()
db.table('tableName').distinct("colD").get()
.min()
, .max()
, .sum()
, .avg()
-> await
const res = await db.table('tableName').max("colA");
console.log(res)
Raw Queries
.raw()
Better avoid direct raw
Support both formatter and plain string
db.raw('select * from tableName').get()
db.raw('select * from ??',['tableName']).get()
.selectRaw(), .whereRaw(), .havingRaw(), .orderByRaw(), .groupByRaw()
All below function same for all above methods
db.table('tableName').selectRaw("colA as a,ColB as b").get()
For Better we recommended to use query formatter instead of raw string. Escape string already included with this method.
You could match the string|number|{key:value}
Object not support multiple iteration only single key value pair is enough like below example
db.table('tableName').whereRaw(" `colA`=? AND ? ",["colValue",{"name":"value"}]).get()
Note * :formatter method in selectRaw
. Column wrapped with '
single quote. you have query error. At the time use ??
instead of the ?
//Error: You have an error in your SQL syntax; check
Solution
db.table('tableName').selectRaw("?? as name, ?? as email",['user_name','user_email']).get();
if you have any query error you could check and do same like this ??
JOINS
.join()
,.letfJoin()
,.rightJoin()
Below snippet functions are same for above methods
.join("joinTable","joinTable.columnName | function","condition","tableName.columnName")
db.table("tableName").join('secTable',"secTable._id","=","tableName.primaryId")
Callback method like where
db.table("tableName").join('secTb',(q)=>{
return q.onJoin('secTb.id','>',"user_id").andJoin("secTb.name","tableName.primaryId")
}).get()
.onJoin()
, .orJoin()
,.andJoin()
Purpose of the method used inside the callback of join methods
db.table("tableName").join('secTb',(q)=>{
return q.onJoin('secTb.id','>',"user_id").andJoin("secTb.name","tableName.primaryId")
}).get()
db.table("tableName").join('secTb',"tableName.id",'=','secTb.id').orJoin('tableName.name','secTb.name').get()
INSERT | UPDATE | DELETE
Result value explanation click here
results = {
fieldCount: number;
affectedRows: number;
insertId: number;
serverStatus?: number;
warningCount?: number;
message: string;
changedRows: number;
protocol41: boolean;
}
.insert()
-> await/callback
.insert(object,callback)
db.table('tableName').insert({colA:"ColB"},(err, results, fields)=>{
console.log(results.insertId)
})
const res =await db.table('tableName').insert({colA:"ColB"})
console.log(res.insertId)
.insertGetId()
-> await
you directly get last insert id
const res = await db.table('tableName').insertGetId({colA:"ColB"})
console.log(res.insertId)
Warning :* without .where
it will affect the entire table
.update()
-> await/callback
Same like insert
.update(object,callback)
db.table('tableName').where('colB','>',10).update({colA:"ColB"},(err, results, fields)=>{
console.log(results.affectedRows)
})
const res =await db.table('tableName').where('colB','>',10).update({colA:"ColB"})
console.log(res.affectedRows)
.delete()
-> await/callback
.delete(callback)
db.table('tableName').where("colA",100).delete((err, results, fields)=>{
console.log(results.affectedRows)
})
const res =await db.table('tableName').where("colA",100).delete()
console.log(res.affectedRows)
Careful Methods
.drop(), .truncate()
-> await/callback
- drop ->
remove the table/database
- truncate ->
empty the table/database
Below snippet functions are same for above methods
.drop(callback)
db.table('tableName').drop((err, results, fields)=>{
console.log(results)
})
const res =await db.table('tableName').drop()
console.log(res)
Other getMethods
.getColumns
-> await/Callback
=>[]
const res = db.table('tableName').getColumns()
We will update the other documentation soon...
Author
prasanth
Contribute
Got a missing feature you'd like to use? Found a bug? Go ahead and fork this repo, build the feature and issue a pull request.
Feel free raise a issue
Show your support
Give a ⭐️ if this project helped you!
This README was generated with ❤️ by readme-md-generator