This project forked from martintajur/node-mysql-activerecord
Node QueryBuilder Adapter for Node.js (tags: nodejs, node, mysql, active record, activerecord, querybuilder, query builder)
Select supports these 2 syntax:
qb.select('s.id, s.title, s.artist, s.genre')
and
qb.select(['s.id', 's.title', 's.artist', 's.genre'])
but I constantly find myself doing this:
qb.select('s.id', 's.title', 's.artist', 's.genre')
Can we also allow the vararg approach?
This check makes it impossible to pass Date objects as values.
if ((typeof v).match(/^(number|string|boolean)$/) === null && v !== null) {
throw new Error("set(): Invalid value provided! (provided: " + v + " (type: " + (typeof v) + ")");
}
While when bypassed, the query is executed fine, and the dates are inserted as expected.
Is this an acceptable solution?:
if ((typeof v).match(/^(number|string|boolean)$/) === null && v !== null && v.getFullYear === undefined) {
throw new Error("set(): Invalid value provided! (provided: " + v + " (type: " + (typeof v) + ")");
}
Hi,
Can you please know how to get 'is null' condition in where statement.
Select departmentName, departmentSales
From Department
Where
employeeLastName is null
I'm trying to Select {tableName}.* from {tableName} not working
when I join 2 tables
becase I just wanna return the values from first table
example
db.select('p.*').from('products p').join('brand b', 'b.id=p.brand_id')
The apostrophe is not escaped in this case:
let titleQuery = "it's"
qb.like('s.title', titleQuery, 'both')The produced SQL has invalid syntax
AND `s`.`title` LIKE '%it's%'The documentation states:
All fields are escaped automatically, no exceptions.
I have an express server, responding to API calls.
Before calling server.listen() I initialise the Query Builder, and leave it running in the background
It all works fine until after an arbitrary amount of time passes and I get this
events.js:167
throw er; // Unhandled 'error' event
^
Error: Connection lost: The server closed the connection.
at Protocol.end (/data/darends/artron/backend/node_modules/mysql/lib/protocol/Protocol.js:112:13)
at Socket.<anonymous> (/data/darends/artron/backend/node_modules/mysql/lib/Connection.js:97:28)
at Socket.<anonymous> (/data/darends/artron/backend/node_modules/mysql/lib/Connection.js:502:10)
at Socket.emit (events.js:187:15)
at endReadableNT (_stream_readable.js:1094:12)
at process._tickCallback (internal/process/next_tick.js:63:19)
Emitted 'error' event at:
at Connection._handleProtocolError (/data/darends/artron/backend/node_modules/mysql/lib/Connection.js:425:8)
at Protocol.emit (events.js:182:13)
at Protocol._delegateError (/data/darends/artron/backend/node_modules/mysql/lib/protocol/Protocol.js:390:10)
at Protocol.end (/data/darends/artron/backend/node_modules/mysql/lib/protocol/Protocol.js:116:8)
at Socket.<anonymous> (/data/darends/artron/backend/node_modules/mysql/lib/Connection.js:97:28)
[... lines matching original stack trace ...]
at process._tickCallback (internal/process/next_tick.js:63:19)
Note: This occurs when using single or pool (even with qb.release())
On researching how mysqljs/mysql handles its fatal errors,
If a fatal errors occurs and there are no pending callbacks, or a normal error occurs which has no callback belonging to it, the error is emitted as an 'error' event on the connection object. This is demonstrated in the example below:
Query Builder does not expose anything which would allow me to either catch or handle the error
mysql suggests this method.
connection.on('error', function(err) {
console.log(err.code); // 'ER_BAD_DB_ERROR'
});I was wondering if either
.on method is able to be exposedI need to use the mysql driver's typeCast to convert my bit(1) fields to boolean. (see https://www.bennadel.com/blog/3188-casting-bit-fields-to-booleans-using-the-node-js-mysql-driver.htm)
In adapter.js, the following line attempts to deep copy the original connection settings by using stringify/parse.
const settings = JSON.parse(JSON.stringify(this._connection_settings));
That approach does not work when a configuration type is function.
Workaround is too re-add any functions.
const fns = {}
Object.keys(this._connection_settings).forEach(it=>{
if (typeof this._connection_settings[it] === 'function'){
fns[it] = this._connection_settings[it]
}
})
const settings = JSON.parse(JSON.stringify(this._connection_settings));
Object.assign(settings, fns)
This is tested and works perfectly using this configuration:
module.exports = {
host: 'hello.world.com',
user: 'me',
password: 'secret',
database: 'items',
typeCast: function castField(field, next) {
if ((field.type === "BIT") && (field.length === 1)) {
const bytes = field.buffer();
return (bytes[0] === 1);
}
return next();
}
};
i'm use regular insert
db.get_connection(function (sql) {
sql.insert('table', {phone: '012345678', function (err, res) {
}
}By the way builder cut first zero out
when I check sql.last_query() it became number type 12345678
How to fix it
How would I define an insert_batch with the table, data, on_dupe, and a callback? Each time I try to do this, it doesn't seem it can support all those parameters together in which I get an error that the callback is not a function which I assume is because it expects a callback where the other parameters are.
I'm trying to use an insert_batch, but also want to skip duplicates using the on_dupe.
Thanks!
The docs state this value should be insert_id, but MySql driver returns insertId.
Since this is such a common item to retrieve, I think the drivers should all map to the same field name.
It directly executes statements without escaping values passed to like function
Hi
Thanks for the awesome repo.
Please check my code below and tell me what is the problem going on.
db.json file code :
{
"host": "localhost",
"user": "root",
"password": "root",
"database": "larastrip",
"pool_size": 100
}
This is the way i am importing below:
import Connection from '../lib/database/db.json';
const qb = require('node-querybuilder').QueryBuilder(Connection);
This is the error which i am getting:
throw new Error("No database driver specified!");
Here's the error:
Error: You have not specified any tables to select from!
I called qb.delete('theTable', ...) and then tried to call qb.from('theTable').select(...).get(...)
When I call the select before the delete, it works.
If I use a fresh connection, it also works.
It seems the delete call is not resetting the connection properly, according to the docs
At this point, the QueryBuilder will be reset and ready to build a new query.
Not sure if I should report bugs or issue pull requests...
It would be helpful if we could push and pop where statements.
qb.where({foo:'bar'}
qb.pushWhere()
qb.or_where({v1: 1})
qb.or_where({v2: 2})
qb.popWhere()
where foo='bar' and (v1=1 or v2=2)node version: v8.7.0
/Users/.../node_modules/mysql/lib/protocol/Parser.js:77
throw err; // Rethrow non-MySQL errors
^
Error: Cannot find module '../../node_modules/mysql/lib/protocol/SqlString.js'
at Function.Module._resolveFilename (module.js:527:15)
at Function.Module._load (module.js:476:23)
at Module.require (module.js:568:17)
at require (internal/module.js:11:18)
at qb_escape (/Users/kirrie/Documents/Github/signal/heart-api/node_modules/node-querybuilder/drivers/mysql/query_builder.js:281:27)
at Object._where (/Users/kirrie/Documents/Github/signal/heart-api/node_modules/node-querybuilder/drivers/mysql/query_builder.js:587:35)
at Object.where (/Users/kirrie/Documents/Github/signal/heart-api/node_modules/node-querybuilder/drivers/mysql/query_builder.js:507:25)
at pool.get_connection.connection (/Users/kirrie/Documents/Github/signal/heart-api/libraries/model.js:244:7)
at nqb.pool.getConnection (/Users/kirrie/Documents/Github/signal/heart-api/node_modules/node-querybuilder/drivers/mysql/adapters.js:172:25)
at Handshake.onConnect [as _callback] (/Users/kirrie/Documents/Github/signal/heart-api/node_modules/mysql/lib/Pool.js:59:7)
maybe path should be 'mysql/lib/protocol/SqlString.js' not start with '../../node_modules'.
Trying to run this query including case when clause
this.db.select("ar.id as resolution_id, ar.resolution_no, ar.resolution_heading, ar.resolution_text, ar.resolution_proposer, ar.resolution_seconder, " +
"case when (art.proxy_vote_allowed=1 and ar.proxy_allowed_to_vote=1) then 1 " +
"when (art.proxy_vote_allowed=1 and ar.`proxy_allowed_to_vote`=0) then 0 " +
"when (art.proxy_vote_allowed=0 and ar.`proxy_allowed_to_vote`=0) then 0 " +
"when (art.proxy_vote_allowed=0 and ar.`proxy_allowed_to_vote`=1) then 1 " +
"end as proxy_vote_allowed, ard.decision_made_by, ard.decision, art.resolution_type, art.resolution_category, art.objection_feasibility, art.pending_dues_vote_allowed, art.decision_calculation_type, art.decision_at_agm, art.decision_threshold_percentage", false)
.from('agm_resolutions as ar')
.join('agm_resolution_decisions as ard', 'ar.id = ard.resolution_id and ard.is_active=1', 'left')
.join('agm_resolution_types as art', 'art.id = ar.resolution_type_id and art.is_active=1')
.where('ar.agm_id', agmId)
.where('ar.is_active', 1);
try {
let result = await this.db.get();
this.db.disconnect();
return result;
} catch (e) {
console.log(this.db.last_query());
}
but the last_query function prints out something different:
SELECT ar.id as resolution_id, ar.resolution_no, ar.resolution_heading, ar.resolution_text, ar.resolution_proposer, ar.resolution_seconder, case when (art.proxy_vote_allowed=1 and ar.proxy_allowed_to_vote=1) then 1 when (art.proxy_vote_allowed=1 and ar.`proxy_allowed_to_vote`=0) then 0 when (art.proxy_vote_allowed=0 and ar.`proxy_allowed_to_vote`=0) then 0 when (art.proxy_vote_allowed=0 and ar.`proxy_allowed_to_vote`=1) AS `then 1 endproxy_vote_allowed, ard`.`decision_made_by, ard`.`decision, art`.`resolution_type, art`.`resolution_category, art`.`objection_feasibility, art`.`pending_dues_vote_allowed, art`.`decision_calculation_type, art`.`decision_at_agm, art`.`decision_threshold_percentage` FROM `agm_resolutions` as `ar` LEFT JOIN `agm_resolution_decisions` as `ard` ON `ar`.`id` = `ard`.`resolution_id` and `ard`.`is_active` = 1 JOIN `agm_resolution_types` as `art` ON `art`.`id` = `ar`.`resolution_type_id` and `art`.`is_active` = 1 WHERE `ar`.`agm_id` = 1 AND `ar`.`is_active` = 1
It prints out parsing error, obviously because it messed up the case when condition when (art.proxy_vote_allowed=0 and ar.proxy_allowed_to_vote=1) AS `then 1 endproxy_vote_allowed, here.
Issue: connection_settings for one pool are lost when setting up another pool
Snippet:
// Connects to database_a on localhost
const AsqlSettings = {
host: '127.0.0.1',
database: 'myuser',
user: 'mypassword',
password: 'database_a',
pool_size: 20
}
// Connects to database_b on localhost
const BsqlSettings = {
host: '127.0.0.1',
database: 'myuser',
user: 'mypassword',
password: 'database_b',
pool_size: 20
}
// Pool for database_a is declared
var APool = require('node-querybuilder').QueryBuilder(AsqlSettings, 'mysql', 'pool');
// pool for database_b is declared
var BPool = require('node-querybuilder').QueryBuilder(BsqlSettings, 'mysql', 'pool');
// database_a is queried, but returns error because "database_b.tableABC" does not exist
// and it shouldn't be suing database_b, it should be using database_a based on settings
APool.get_connection(function(qb){
qb.select(['field1','field2']).get('tableABC', (err,response) => {
// optionally do:
// console.log(qb.connection_settings());
// here, you will see its not using AsqlSettings, but rather BsqlSettings
// and connecting to database_b not database_a despite being APool
qb.release();
if(err) { console.error(err); return; }
for (const i in response) {
const row = response[i];
console.log(row['field1']);
console.log(row['field2']);
}
});
});
// has no issues, since this pool's connection settings were retained
BPool.get_connection(function(qb){
qb.select(['fieldX','fieldY']).get('table123', (err,response) => {
qb.release();
if(err){ console.error(err); return; }
for (const i in response) {
const row = response[i];
console.log(row['fieldX']);
console.log(row['fieldY']);
}
});
});`Effect of this bug:
Cannot use multiple databases in the same JS, such as one for authentication and another for data
whenever we pass where condition in .update_batch(table,dataset,index[,where],callback), it throws following error
[ { Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `id` = 30 AND `tripId` IN (30,30)' at line 1
at Query.Sequence._packetToError (/home/mohd/app/express-test/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14)
at Query.ErrorPacket (/home/mohd/app/express-test/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
at Protocol._parsePacket (/home/mohd/app/express-test/node_modules/mysql/lib/protocol/Protocol.js:279:23)
at Parser.write (/home/mohd/app/express-test/node_modules/mysql/lib/protocol/Parser.js:76:12)
at Protocol.write (/home/mohd/app/express-test/node_modules/mysql/lib/protocol/Protocol.js:39:16)
at Socket.<anonymous> (/home/mohd/app/express-test/node_modules/mysql/lib/Connection.js:103:28)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
--------------------
at Protocol._enqueue (/home/mohd/app/express-test/node_modules/mysql/lib/protocol/Protocol.js:145:48)
at Connection.query (/home/mohd/app/express-test/node_modules/mysql/lib/Connection.js:208:25)
at exec (/home/mohd/app/express-test/node_modules/node-querybuilder/drivers/mysql/query_exec.js:11:18)
at next_batch (/home/mohd/app/express-test/node_modules/node-querybuilder/drivers/mysql/query_exec.js:151:17)
at Object.update_batch (/home/mohd/app/express-test/node_modules/node-querybuilder/drivers/mysql/query_exec.js:169:15)
at Object.<anonymous> (/home/mohd/app/express-test/b.js:26:17)
at Module._compile (module.js:652:30)
at Object.Module._extensions..js (module.js:663:10)
at Module.load (module.js:565:32)
at tryModuleLoad (module.js:505:12)
code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'WHERE `id` = 30 AND `tripId` IN (30,30)\' at line 1',
sqlState: '42000',
index: 0,
sql: 'UPDATE (`trip_students_booking`) SET `studentId` = CASE WHEN `tripId` = 30 THEN 43 WHEN `tripId` = 30 THEN 42 ELSE `studentId` END WHERE WHERE `id` = 30 AND `tripId` IN (30,30)' } ]
We can see that 2 times added where clause
How to generate the query clause with nested and/or, example:
Select departmentName, departmentSales
From Department
Where
EmployeeCount > 1000
AND ( DepartmentRevenue > 100000 OR DepartmentCost > 10000 )
db table is empty just has the "column_1" (works the same if table has contents)
response from the first url is normal
but from the second after delete, app fails (records get deleted, just the select afterwards fails)
I need to select data where ``last_update >= now() - INTERVAL 1 MINUTE
Example of what I'm currently trying to use:
qb.select('*').where('last_update >= NOW() - INTERVAL 1 MINUTE', null, false).get('account', (err, rows) => {
qb.release();
if (err) throw err;
return res.json(rows)
})EDIT: It looks like the issue seems to be occurring due to it placing NOW() inside single quotes when executing the query causing mysql to treat it as a field rather than a function. Any idea how to get around this?
EDIT 2: I realized you can pass false for escape strings which is what I want for executing the NOW() function, but for some reason, it keeps appending the AS keyword after the function? Any idea why this is?
I'm using the query above, but it seems to return this with the AS keyword which is clearly not right?:
ComQueryPacket {
command: 3,
sql:
'SELECT * FROM `account` WHERE last_update >= NOW() AS `- INTERVAL 1 MINUTE`' }
queryBuilder.join('tbl_user as u','CASE WHEN m.iMsgTo ='+params.iUserID+' THEN m.iMsgFrom = u.user_id WHEN m.iMsgFrom ='+params.iUserID+' THEN m.iMsgTo = u.user_id END','INNER');
This is a subpart of query. When I am executing this query query builder is adding CASE WHEN .Please help me with this. i don't want to add this ` sign with join on query.
Need help ASAP.
driver: mysql
const members = [] // some function that may return an empty array
qb.insert_batch('group_user', members, (err, res) => {})Expected behavior would be to simply invoke the callback. If the server response with 0 affected rows is needed, then to actually call the DB with no rows to insert.
TypeError: cb is not a function
at Query._connection.query (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/node-querybuilder/drivers/mysql/query_exec.js:33:17)
at Query.<anonymous> (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/Connection.js:502:10)
at Query._callback (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/Connection.js:468:16)
at Query.Sequence.end (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
at Query.ErrorPacket (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/protocol/sequences/Query.js:90:8)
at Protocol._parsePacket (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/protocol/Protocol.js:278:23)
at Parser.write (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/protocol/Parser.js:76:12)
at Protocol.write (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/Connection.js:91:28)
at Socket.<anonymous> (/Users/steven/dev/projects/other/quickchords-server/src/main/javascript/node_modules/mysql/lib/Connection.js:502:10)
Hi,
I have a problem with"node-querybuilder ", ie, select is not a function, bellow giving my code,kindly request you to check and I hope you will give a solution for this issue ASAP.
var qb = require('node-querybuilder').QueryBuilder({
connectionLimit : 100,
host : 'localhost',
user : 'root',
password : '',
database : 'address_book',
debug : false,
pool_size: 50
}, 'mysql', 'pool');
qb.select('*').get('users', function(err,response) {
if (err) return console.error("Uh oh! Couldn't get results: " + err.msg);
console.log("Query Ran: " + qb.last_query());
return response;
});
TypeError: qb.select is not a function
at Object.db_connect (E:\ExperionProjects\trials\myapp\config\connect_db.js:17:9)
at E:\ExperionProjects\trials\myapp\routes\test.js:29:28
at Layer.handle [as handle_request] (E:\ExperionProjects\trials\myapp\node_modules\express\lib\router\layer.js:95:5)
at next (E:\ExperionProjects\trials\myapp\node_modules\express\lib\router\route.js:131:13)
at Route.dispatch (E:\ExperionProjects\trials\myapp\node_modules\express\lib\router\route.js:112:3)
at Layer.handle [as handle_request] (E:\ExperionProjects\trials\myapp\node_modules\express\lib\router\layer.js:95:5)
at E:\ExperionProjects\trials\myapp\node_modules\express\lib\router\index.js:277:22
at Function.process_params (E:\ExperionProjects\trials\myapp\node_modules\express\lib\router\index.js:330:12)
at next (E:\ExperionProjects\trials\myapp\node_modules\express\lib\router\index.js:271:10)
at Function.handle (E:\ExperionProjects\trials\myapp\node_modules\express\lib\router\index.js:176:3)
FYI I couldn't use this in my Strongloop (express js) project without adding a shim for Object.assign in the mysql/adapters.js
The error wasn't very helpful either.
{"error":{"name":"Error","status":500,"message":"Couldn't load the Connection library for mysql({}): TypeError: undefined is not a function","stack":"Error: Couldn't load the Connection library for mysql({}): TypeError: undefined is not a function\n at get_adapter (/Users/xxx/Sites/xxx/node_modules/node-querybuilder/index.js:124:10)\n ...
I needed to add var assign = Object.assign || require('object.assign'); and change instances of Object.assign to assign in mysql/adapters.js.
Hello, I am working on some framework and wan to create tables dynamically and i just want to know that is it possible to create table using this query builder?
I was using batch_update but facing this error. The query I am getting in next_batch is working fine still I am getting this.
batch_data
[
{ column1: 'test', coolumn2: '2.0.2444', id: 12 },
{ column1: 'test', coolumn2: '2.0.24444', id: 13 }
]
qb.update_batch(table_name, batch_data, key, where_object, (err, res) => {
if (err) {
reject(err);
}
resolve(res);
});
TypeError: Cannot read property 'reset_query' of undefined
at next_batch (C:\Project\node_modules\node-querybuilder\drivers\mysql\query_exec.js:178:22)
at handler (C:\Project\node_modules\node-querybuilder\drivers\mysql\query_exec.js:205:15)
at new Promise (<anonymous>)
at Single.update_batch (C:\Project\node_modules\node-querybuilder\drivers\mysql\query_exec.js:209:20)
at C:\Project\dao\mysql_helper.js:125:8
at C:\Project\node_modules\node-querybuilder\drivers\mysql\adapters\pool.js:54:65
at DatastoreShim.applySegment (C:\Project\node_modules\newrelic\lib\shim\shim.js:1430:20)
at wrapper (C:\Project\node_modules\newrelic\lib\shim\shim.js:2092:17)
at Ping.onOperationComplete (C:\Project\node_modules\mysql\lib\Pool.js:110:5)0|communications_micro_service_index | at Ping.<anonymous> (C:\managecommunicationsmicroservice\node_modules\mysql\lib\Connection.js:526:10)
Hi, @kylefarris
I am sorry, that I write you here, because I haven't found you in facebook/telegram.
As I understood, this npm package aim and purpose is not to be connected to SQL/NOSQL DB, just having CRUD operations and change DB any moment. It is cool idea, I am was interested to do something like this too, but found only Knex to abstract from sql DBs, and not something cool for SQL+NoSQL DBs.
As I see, your development is something slow, or you just don't have any wish to do it?
May be I could help you in my free time, or something like this, because I think that other languages have something like this, but not node.js.
I am looking forward to your reply.
Yehor
can remove pool.get_connection() ?
I want it to have a line number equal to the line number of single connection
can do that ?
If I'm following the installation documentation and try to configure Node Query Builder, I'm getting the following error:
Error: Couldn't load the Connection library for mysql({"user":"root","host":"localhost","database":"myDatabase","password":"myPassword","pool_size":50}): Error: Cannot find module 'underscore'
Perhaps it is a good idea to add npm install underscore to the documentation?
Great work though 👍
Hello, first of all Great work!! 👍
I was using mysql-active-records before,
now switched to this and everything is better but NOT delete
every time I use DELETE the composed query has unnecessary parenthesis that give me SQL error something like:
//tried with:
db .where( { UTOP_USER_K_ID : 3 } ).delete('user_topics','NULL', function(err, rows) {..
//also
db.delete('user_topics', { UTOP_USER_K_ID : 3 } , function(err, rows) {..
//also with from() the result is always :
"DELETE FROM (`user_topics`) WHERE `UTOP_USER_K_ID` = 3 " // --> SQL ERROR!
//this is ok:
"DELETE FROM `user_topics` WHERE `UTOP_USER_K_ID` = 3 " // --> SQL OK !so I have to use the plain .query() method :(
any help with this?
thank you very much!
Using the query builder following the documentation leads to an error: the argument list is not used as in the example (https://www.npmjs.com/package/node-querybuilder#insert_ignoretable-dataon_dupe-callback):
.insert_ignore(table, data[,on_dupe][, callback])
Looks like the third parameter is always expected to be the callback, not the on_dupe as above. Using the above signature will lead the query builder to totally ignore the on_dupe parameter.
That is: using the following code:
conn.insert_ignore('mytable', {a: 10, b: 100}, 'ON DUPLICATE KEY UPDATE ...', (err, res) => {})
is wong and won't build the right query.
The following works fine:
conn.insert_ignore('mytable', {a: 10, b: 100}, (err, res) => {}, 'ON DUPLICATE KEY UPDATE ...')
See comment in snippet.
pool.get_connection(qb => {
qb.get('comments', {id: id}, (err, res) => {
// CONNECTION SHOULD BE CLOSED BEFORE RETURNING
if (err) return console.error(err);
const article_id = res.article_id;
qb.delete('comments', {id: id}, (err, res) => {
qb.release();
if (err) return console.error(err);
const page_data = {
num_removed: res.affected_rows,
}
return res.render('/article/' + article_id, page_data);
});
});
});Once I need to make more than 1 DB call, this library starts to become very hard to manage due to lack of promise support.
The preceding issue would be avoided by using the following async/await syntax with promises returned from node-querybuilder.
async function render() {
let db
try {
db = await pool.get_connection()
let comments = await qb.get('comments', {id})
const article_id = comments.article_id;
const res = await qb.delete('comments', {id})
const page_data = {num_removed: res.affected_rows}
return res.render('/article/' + article_id, page_data)
} catch (err) {
console.error(err)
} finally {
if (db) db.release()
}
}In my project using this module, you module saved my lot of time, but here is a issue, using multiple queries together so there is any way to manage database transactions like commit, roll back ? please let me know as soon as.
I think that the mysql connection is not terminated and eventually gives too many connection error for busy traffic.I solved it for my project by adding conn.end() before callback in query_exec.js.Are there any alternatives?
I've tried using util.promisify as well as just manually building the function below and it seems that promises won't apply properly
function queryBuilder(options, driver, type){ return new Promise(function(fulfill, reject){ qb.QueryBuilder(options, driver, type, function(err, res){ if(err) reject(err); else fulfill(res); }); }); }.
Is there something I'm missing, or is there a way that this could be promisified to work with async/await?
My overarching use case is that I have multiple configurations for X different Databases. I want to create a QueryBuilder pool for each of them so that my service can interact with them all. However, pass configs to QueryBuilder in a for loop or Promise.all->map it only sets up multiple pools for one of the entries.
Thanks for any time and help you can provide.
driver: mysql
qb.from('group_user gu')
.where('gu.groupId', groupId)
.where_in('gu.status', [0, 1])
.join('accounts a', 'a.username=gu.username')
.join('group g', 'g.id=gu.groupId')
.join('accounts a2', 'a2.username=g.username')
.select('gu.*, a.displayname, a.email, g.name as groupName, a2.displayname as invitor')expected
SELECT gu.* ...
actual
sql: 'SELECT `gu`., `a`.`displayname`, `a`.`email`, `g`.`name` as `groupName`, `a2`.`displayname` as `invitor` FROM `group_user` `gu` JOIN `accounts` `a` ON `a`.`username` = `gu`.`username` JOIN `group` `g` ON `g`.`id` = `gu`.`groupId` JOIN `accounts` `a2` ON `a2`.`username` = `g`.`username` WHERE `gu`.`groupId` = 20 AND `gu`.`status` IN (0, 1)' }workaround
.select('gu.*', false')
.select('a.displayname, a.email, g.name as groupName, a2.displayname as invitor')or
.select('gu.*,a.displayname, a.email, g.name as groupName, a2.displayname as invitor', false)Expected behavior
All properties should use 'AND'
qb.delete('tags', {songId: [300], tagName: oldTag}, (err, res) => {})This query results in
DELETE FROM `tags` WHERE `songId` IN (300)Workaround: Re-add ignored fields.
qb.where({tagName: oldTag}).delete('tags', {songId: [300], tagName: oldTag}, (err, res) => {})This query results in
DELETE FROM `tags` WHERE `tagName` = 'test1' AND `songId` IN (300)This applies to at least this 'delete use-case', if not more.
It converts string values like 23842563958810573 into 23842563958810572 which took half day to find out !
Currently an error is thrown if rows to insert is empty. I would like if this did not throw, and simply resolved.
But if it must, the error should be 'No rows specified to insert' or such.
TypeError: cb is not a function
at Query._connection.query (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/node-querybuilder/drivers/mysql/query_exec.js:33:17)
at Query.<anonymous> (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/Connection.js:502:10)
at Query._callback (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/Connection.js:468:16)
at Query.Sequence.end (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/protocol/sequences/Sequence.js:83:24)
at Query.ErrorPacket (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/protocol/sequences/Query.js:90:8)
at Protocol._parsePacket (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/protocol/Protocol.js:278:23)
at Parser.write (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/protocol/Parser.js:76:12)
at Protocol.write (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/Connection.js:91:28)
at Socket.<anonymous> (/Users/steven/dev/projects/other/badges/badges-admin/node_modules/mysql/lib/Connection.js:502:10)
I have to put many checks in my code for filters because where_in('foo',[]) will throw an error.
Please consider allowing these conditions.
Webpack has issues when package.json uses "main": "./".
Workaround without changing node-querybuilder source:
module.exports = {
resolve: {
alias: {
// see https://github.com/webpack/webpack/issues/6026
'node-querybuilder': 'node-querybuilder/index.js',
},
}
}
Im using v2.1.1
When using .like() and .or_like() on numeric, Quote got removed '%123%' became %123% and error of execution
ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%123% OR first_nameLIKE %123% ORlast_nameLIKE %123% ORDER BYidDESC LI' at line
Hello,
Thank you for this wonderful library,however I am stuck in a case
I cannot use mysql DATE_FORMAT function. it gives me error cannot read property substring of undefined.
Need help!!
Thank you
I am bundling this library into a single JS. The driver is not resolved at runtime.
Notice in the log the path ./drivers/mysql//adapters/pool.js
Here is the error.
Error: Couldn't load the "pool" Adapter library for mysql ({"host":"***","user":***","password":"***","database":"***"}): Error: Cannot find module './drivers/mysql//adapters/pool.js'
at module.exports.get_adapter (/Users/steven/dev/projects/other/quickchords-server/src/main/resources/dist/main.js:7:15912)
at new module.exports (/Users/steven/dev/projects/other/quickchords-server/src/main/resources/dist/main.js:7:13859)
at Object.<anonymous> (/Users/steven/dev/projects/other/quickchords-server/src/main/resources/dist/main.js:1:4427)
at __webpack_require__ (/Users/steven/dev/projects/other/quickchords-server/src/main/resources/dist/main.js:1:309)
at Object.<anonymous> (/Users/steven/dev/projects/other/quickchords-server/src/main/resources/dist/main.js:164:370558)
at __webpack_require__ (/Users/steven/dev/projects/other/quickchords-server/src/main/resources/dist/main.js:1:309)
at /Users/steven/dev/projects/other/quickchords-server/src/main/resources/dist/main.js:1:1601
at Object.<anonymous> (/Users/steven/dev/projects/other/quickchords-server/src/main/resources/dist/main.js:1:1648)
at Module._compile (module.js:641:30)
at Object.Module._extensions..js (module.js:652:10)
Here's the call.
case 'pool':
const Pool = require(`${this.driver_info.path}/adapters/pool.js`)
return new Pool(settings);suggestions
Also, the error dumped displays my connection username and password. That's a bit concerning.
qb.where_in('id',[])
qb.get(...)
This results in all rows returned, instead of no rows returned.
It would make sense for the get method to allow a where parameter. This would match the API of the other methods as well, such as insert and delete.
qb.get('gizmos', {color:'red'}, (err, rows) => {
})
or
await qb.get('gizmos', {color:'red'})
When i use .select('field1, if(field2=1, true, false) as field3') this fails with "Cannot read property 'substring' of undefined", if i do the same but defining fields as array .select(['field1', 'if(field2=1, true, false) as field3']) then everything works good, would be nice to fix this :)
same happens if select includes concat(field1, ' ', field2)
I wrapped promises around the return of get() and it works quite well. Not only does it clean up my code, but I can also add a single finally to release the connection after a chain of queries.
Here's an example.
.like('s.title', x(titleQuery), 'both')
.limit(20)
.get_p()
.then(res => {
response.send(res)
})
.catch(reason=>response.status(500).send(reason))
.then(value => qb.release())Could this, or just simply returning a promise from get() if a callback is not provided, be added to the official API?
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.