Drizzle Team•12mo ago

How to perform multiple queries?

I have multiple queries that I would like to do at the same time in order to save network traffic back and forth N times. I see that the "Batch" section of the docs applies to LibSQL and to D1. But I am using Postgres. Do I have any options in Postgres? (Besides just writing the SQL query raw I guess, which I want to hopefully avoid.)

7 Replies

Angelelz•12mo ago

Promise.all() ? You have to be careful because all the query builders implement a lazy promise, it might not behave as you expect a regular promise to behave If you want a regular promise, you can call execute at the end See this thread https://discord.com/channels/1043890932593987624/1190350820810698842

ZamielOP•12mo ago

Hi Angelelz, thanks for the reply. I think what you are suggesting is something like the following code:

await Promise.all([
  db.select({ field1: foo.field1 }).from(foo).where(eq(foo.userID, userID)),
  db.select({ field2: bar.field2 }).from(bar).where(eq(bar.userID, userID)),
  db.select({ field3: baz.field3 }).from(baz).where(eq(baz.userID, userID)),
]);

await Promise.all([
  db.select({ field1: foo.field1 }).from(foo).where(eq(foo.userID, userID)),
  db.select({ field2: bar.field2 }).from(bar).where(eq(bar.userID, userID)),
  db.select({ field3: baz.field3 }).from(baz).where(eq(baz.userID, userID)),
]);

But this would still result in 3 separate queries being sent to the PostgreSQL server, right? They would just be sent all at the same time. If so, I don't think that solves my problem, as it would be inefficient. Maybe transactions would help? But I'm not quite sure, as I might not need the atomicity of a transaction, since I have a bunch of SELECT statements. (The Drizzle documentation indeed shows a transaction example using INSERT.)

Angelelz•12mo ago

Transactions is even worst because in trasactions all queries will be serialized In SQL databases there is no batch queries. The database will run one query after the other per connection Even LibSQL and D1, they offer that API because those are usually http server wrappers around the underlying database, and they accept your batches and run them to the database internally probably in a similar fashion as promise.all

ZamielOP•12mo ago

Sorry, perhaps I am misusing the term "batch queries". Allow me to clarify. Consider the following two SQL queries:

SELECT field_1 from foo WHERE foo.user_id = $1;
SELECT field_2 from bar WHERE foo.user_id = $1;
SELECT field_3 from baz WHERE foo.user_id = $1;

SELECT field_1 from foo WHERE foo.user_id = $1;
SELECT field_2 from bar WHERE foo.user_id = $1;
SELECT field_3 from baz WHERE foo.user_id = $1;

SELECT (
  (SELECT field_1 from foo WHERE foo.user_id = $1) as field_1,
  (SELECT field_2 from bar WHERE bar.user_id = $1) as field_2,
  (SELECT field_3 from baz WHERE baz.user_id = $1) as field_3
);

SELECT (
  (SELECT field_1 from foo WHERE foo.user_id = $1) as field_1,
  (SELECT field_2 from bar WHERE bar.user_id = $1) as field_2,
  (SELECT field_3 from baz WHERE baz.user_id = $1) as field_3
);

In the first query, 3 SQL queries are sent over the wire. In the second query, 1 SQL query is sent over the wire. Isn't the second one faster? Well, I mean to say that they would run at roughly the same speed, but the second one avoids the fixed cost of generating a network packet, the server acknowledging each request, and so on. Which might be important at scale when N is not just 3 (like it is in the above code) but is instead 100, for example.

Angelelz•12mo ago

I understand what you mean now If the queries have the same keys/shape as you showed here, you could use an union Otherwise the second select you might not be able to map it in drizzle, as drizzle doesn't support select without from just yet

ZamielOP•12mo ago

Ah perfect, I'll use a union, thanks. Any news on the select without from PR?

Angelelz•12mo ago

I guess I need to resolve some conflicts It's still under review

Gaming

Programming

How to perform multiple queries?