I’d be curious to know, what’s the difference between the first pg snippet and doing the same thing “imperatively” in node?(e.g make a req to insert first, then get the id, the make the second request).
Is it that with the first approach, everything is happening under the same TCP connection(or the same transaction)?
In the first PG snippet, since the row/id can be returned automatically, it eliminates a step. For example, in node, you could save the result of that first INSERT (the id) to a variable, then use the variable in another query. Without this, you would have to do three steps like you mentioned: INSERT, SELECT, and whatever else. Either way it would be done in one connection, but it's shorter with PG, and it can really help if you want more columns than just the id. It can also be helpful if you do an UPDATE, which is another situation where you might not really be sure what happened, like the auto-incrementing id. Does that answer your question?
Yes, thank you very much. So with both approaches, only one connection is made? I find it a bit strange, since with the “node” approach we have multiple requests and with the other one we have only one big query. Or is the same connection used among all the requests?
Yes, it would be one connection if I understand what you are asking, though you would presumably do multiple steps within a transaction, so that you have better control of how the overall operation behaves (for ACID compliance).
Great article, thank you for sharing?
I’d be curious to know, what’s the difference between the first pg snippet and doing the same thing “imperatively” in node?(e.g make a req to insert first, then get the id, the make the second request).
Is it that with the first approach, everything is happening under the same TCP connection(or the same transaction)?
Hi Andrei!
In the first PG snippet, since the row/id can be returned automatically, it eliminates a step. For example, in node, you could save the result of that first
INSERT
(the id) to a variable, then use the variable in another query. Without this, you would have to do three steps like you mentioned:INSERT
,SELECT
, and whatever else. Either way it would be done in one connection, but it's shorter with PG, and it can really help if you want more columns than just the id. It can also be helpful if you do anUPDATE
, which is another situation where you might not really be sure what happened, like the auto-incrementing id. Does that answer your question?Yes, thank you very much. So with both approaches, only one connection is made? I find it a bit strange, since with the “node” approach we have multiple requests and with the other one we have only one big query. Or is the same connection used among all the requests?
Yes, it would be one connection if I understand what you are asking, though you would presumably do multiple steps within a transaction, so that you have better control of how the overall operation behaves (for ACID compliance).
Got it! Thank you!