-
Notifications
You must be signed in to change notification settings - Fork 2.1k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
How to execute multiple queries in one run? #257
Comments
Nope, you'll have to execute them as two statements. |
Well, this is a bit limiting solution |
You could try calling knex.raw(knex.raw(query, bindings) + '').exec(function(err, result) {
assert.isNotError(err);
}); |
Well, I will see what to do with that, basically I would like just to turn off prepared statements and keep the ability of having query string and bindings, but I don't know if it's possible, i found no documentation related to these. |
Anyway, I tried
but it throws - Object object has no method 'clone'. |
If you want to try the 0.6.0 branch, I know it'll work there, that should be released once I finish up the tests for a few things. Why though, would you need to execute two query statements using a single string like that? Doesn't it sort of defeat the purpose of using the package? |
Personally, I need to make multiple |
I have been playing with various query builders for node.js after we found Knex limitations (which resulted basically using raw queries most of the time). In the end I just coded sql builder myself - QSql Demo. It's not complete or production ready, but basically summarizes the way I'm thinking. |
Very cool demo, I'd been planning on adding something like that after the next release - curious, had you given the 0.6 branch a try? I wonder if there's any room for collaboration here, where QSQL might be able to fill the need for a more robust query builder, while Knex deals with connection pooling & smoothing over the warts between different dialects. Anyway, nice work, I'll be taking a look through! |
Well no, we didn' try 0.6 as we had a lot of code using knex on one place and then doing some raw queries somewhere else. After some research I decided to replace the whole stack with something that can be maintained and where features can be added instantly. Well, I think there is definitely a room for collaboration, however it will take some time to stabilize QSql first. I mean to define a nice API that matches 99% needs and use-cases (as you can see some constructs are still a bit ugly) and to implement proper abstraction so other backends can be added. |
maybe ability to pass an array of queries and then the results is an array as well? something simple like this would be cool, could use something like async.parallel to do it |
@niftylettuce Bluebird already comes with the "async.parallel" feature out of the box. But this isn't the problem. We really need a way to execute coma separated queries in one run. |
Isn't this possible by just marking
in the connection block of one's configuration:
|
Okay so if anyone here is interested - I'm working on some new things in the upcoming refactor to make this possible. I'm wondering about what the ideal api would be for this... do we want to have a single chain? knex
.update('records_raw')
.set({title: x}).where({id: 1})
.end()
.update('records_raw')
.set({title: y}).where({id: 2})
.spread((resultA, resultB) => {
}) or something more like: knex.multiQuery([
knex.update('records_raw').set({title: x}).where({id: 1})
knex.update('records_raw').set({title: y}).where({id: 2})
]).spread((resultA, resultB) => {
}) Also looking at making the possibility of making the |
Splitting on semicolon on raw queries would be a great start. |
I'm running into a problem right now where my delete is not completed in time before my insert, so I'm getting duplicate key errors being thrown. I'm doing something like
you get the gist.. The del doesn't complete in time. |
@tgriesser my vote is for knex.multiQuery |
@tgriesser any update on the ability to execute multiple statements in a single query? I would prefer: .update('records_raw') |
+1 |
In order to do proper splitting on semicolons, we'd need to parse the entire raw query using a dialect-specific parser. Specifically, if strings contain -- generic SQL
SELECT * FROM book WHERE title = 'Lord of the Rings; The Fellowship of the Ring'; -- MySQL specific
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END Also note in the first case we'd end up with 2 statements, so the code would need to take into account empty results from the split. Honestly, overall, I think this is a bad idea. There's just too much of a chance for SQL injection and not splitting queries correctly. Also, when executing multiple statements, you almost always want that to happen in the same transaction. Knex already solves this problem really well with So because of these issues, my opinion is that this shouldn't happen. 👎 |
I can tell you from experience that in certain cases, there's a huge performance difference between these two approaches and batching multiple values to the server as one command. I understand it's very hard to implement cleanly, which is why it hasn't been done yet, but this issue has greatly limited my use of knex for a project. |
I had a similar issue and was able to solve it. See over here. #1075 |
I'm failing to see any usecase for this feature. Would love to close this ticket as won't fix. If one is depending order of the queries being run on the same connection then you probably want to use transactions. |
The one thing I see that makes this 'multiple query' usage into something that we'd like to have is that it's a potential performance win in some cases. It requires less round-trips to communicate all the queries and the results to and from the server. But it's not like I have a measurable use case available. Only speaking from past experience/memory... |
@jurko-gospodnetic It could be the case when you don't have connection pooling. With pooling sending multiple queries is basically just putting data to already created TCP socket. Also if your performance depends on TCP buffers not being filled enough then knex is already too slow for you :) In that case better to use driver directly. |
I'm using node-mysql (mysqljs) on an enterprise grade project for this particular reason. We are looking to migrate the whole project to knex.js for a multitude of reasons There's a considerable performance improvement on such operations that I leverage by taking advantage of the multiple-statement queries feature of the node-mysql (mysqljs) driver. It might prove a showstopper for me if Knex doesn't support this. So I'll revive this thread to ask just that.
|
@nicholaswmin can you tell a bit more specific what do you mean by "considerable performance improvement"? How sending multiple queries this way is considerably more efficient than just sending multiple queries through the same connection? Any benchmarks? |
@elhigu No benchmarks but right off the bat I can say this: A single call to the DB (packed with all the statements) eliminates the server-db network round-trips. On the other hand, sending multiple queries through the same connection is not a silver bullet. This solution would only work with non-transactional flows, see this Issue |
@nicholaswmin Are you sending huge amount of small queries and mostly ignored or small results? In that case I suppose difference may be noticeable, since it allows driver to pack multiple queries to each TCP packet, where otherwise each TCP packet would have mostly headers and really small amount of payload. That kind of performance difference should be able to be measured easily even from amount of network traffic. I don't know if drivers supports sending multiple queries from separate |
@elhigu They are small queries indeed but their results are needed so they can be used further down the transaction/query chain. Is there any way for me to see the TCP packets being sent via a debug or similar option? Not the queries themselves being sent but the actual packets. A use case:When updating the data of a User in my system, I'd like to compute the Audit Trail (what has changed) for that User. // # PSEUDOCODE
// get current data of user
getUserData();
// set data of user
setUserData()
// get new data of user
getUserData()
// compute the audit trail by comparing the difference between before-set/after-set datums
computeAuditTrail(previousData, newData); Each of the above calls does multiple DB calls, so as you can imagine these are a lot of network roundtrips. As stated on #1806 I can work around this by using That would work with non-transactional flows, since the queries can be sent on different connections from the pool. As soon as I use a transaction to perform the above flow, it slows down (around 4x) since the queries are sent on a single connection. As a side note I'm using MSSQL. |
Wireshark is pretty common cross-platform tool used to analyse network traffic. I don't believe that there is any way to get see to that level with node. One way could be maybe to use iptraf or anything to measure amount of sent / received data when sending the same amount of queries with packed in one query or passed separately to driver. |
I would love to see a |
Any update ? |
var knex = require("knex");
var _ = require("lodash");
var Promise = require("bluebird");
var knex = require('knex')({
client: 'sqlite3',
connection: {
filename: "./data.sqlite"
}
});
// Create Schema
let createScript = `
CREATE TABLE Class (
Id integer NOT NULL ,
Name varchar(100) ,
CONSTRAINT Pk_Classes_Id PRIMARY KEY ( Id )
);
CREATE TABLE Material (
Id integer NOT NULL ,
Description varchar(500) ,
CONSTRAINT Pk_Material_Id PRIMARY KEY ( Id )
)
-- ... and so on (leave off the last semi or remove it later) ...
`;
let statementPromises = _.map(createScript.split(';'), (statement) => {
return knex.raw(statement);
});
Promise.all(statementPromises).then(function() {
console.log('Schema generated. Populating...');
// ... |
that's just a loop, not multiple requests made into one, and it is SLOW |
@mscheffer the question was:
Which my answer solves. If you have a faster better solution please provide it. |
@VictorioBerra Splitting from Also in your case, where you are creating SQL strings in inside template string and then splitting it, using As far as I know there is no way to achieve this feature of running multiple queries in single command for all dialect drivers. I think it was mysql and oracledb or mssql, which had that support and with mysql you still get just the result of the last query as a response (which might be ok though). |
Remember to use transactions if you are modifying data using multiple queries so that you can make sure they run in succession! This rule of thumb applies even if the queries are running in one execution. MULTIPLE QUERY EXECUTION USING RAW SQL
After that, you can use a raw statement like so:
MULTIPLE QUERY EXECUTION USING KNEX QUERY BUILDER
This also helps reduce human error when writing redundant queries that need to be executed in succession. Again with something like this I would suggest wrapping it in a transaction. |
I think this can be closed since this is mostly limited by db drivers. At least proper feature request is needed. |
@AksharaKarikalan even if you do multiple subqueries and subtraction between those, you are still making just single query. So I removed comment which is irrelevant to this issue. Stackoverflow is correct place for knex usage requests. |
I'm facing a problem that I would like to execute multiple queries separated by ';' by a single exec, is that possible?
My test code which is failing looks like:
The error:
Is there a way to disable prepared statements for such queries?
The text was updated successfully, but these errors were encountered: