Drizzle Team•2mo ago

How to resolve mutiple queries in a single request to postgres database?

I have more than one sql select statements that need to act independently and are not related to each other. Is there a clean, type-safe way to fetch results for all these select statements in one request to postgres instead of having to use Promise.all and making a seperate request for each select statement? Most probably what I'm asking for is a single select statement that contains the logic for all the three select statements below.

const usersQuery = db.select().from(users).where(eq(users.age, 24));
const accountsQuery = db.select().from(accounts).where(eq(accounts.verified, false));
const sessionsQuery = db.select().from(sessions).where(inArray(sessions.userId, userIds));
const [users, accounts, sessions] = await Promise.all([usersQuery, accountsQuery, sessionsQuery]);

const usersQuery = db.select().from(users).where(eq(users.age, 24));
const accountsQuery = db.select().from(accounts).where(eq(accounts.verified, false));
const sessionsQuery = db.select().from(sessions).where(inArray(sessions.userId, userIds));
const [users, accounts, sessions] = await Promise.all([usersQuery, accountsQuery, sessionsQuery]);

3 Replies

Angelelz•2mo ago

This is by far the easiest way IMO With this you take advantage of your pool and possibly use several connections

adrtivvOP•2mo ago

I would need to use Promise.all at multiple places so it can compound a lot. I'm not relying on postgres exceptions so in my code I have to do constraint checks, there's also authorization checks and business logic checks etc., and all that in a single operation. So, for example in a bulk update operation this could result in too many promises.

Angelelz•2mo ago

I feel like this is not the bottleneck you'd want to address. There might be drivers that allows several queries in the same request, but it's not a great gain

Gaming

Programming

How to resolve mutiple queries in a single request to postgres database?