Drizzle Team•4d ago

Executing a query that was made with QueryBuilder

I have a pnpm workspace where the schema and some queries are defined in one subproject, but the db connection is another subproject. I want to use a QueryBuilder to build my queries, which I'd then import and execute once I have my connection. So the question is simple – how can I execute the result of a QueryBuilder using the drizzle object?

7 Replies

AlexDanielOP•4d ago

One way to do it is:

const qb = new QueryBuilder();
const query = qb.select(…)…().as('q');

const result = await db.select({ foo: query.foo, …}).from(query);

const qb = new QueryBuilder();
const query = qb.select(…)…().as('q');

const result = await db.select({ foo: query.foo, …}).from(query);

This is somewhat awkward (e.g. the need to use .as). Is there any better way?

Mario564•3d ago

You can build query with the db object rather than the qb object if you want, it'll produce the same output

AlexDanielOP•3d ago

@Mario564 but I don't have the db object in that subproject, that's the point

Mario564•3d ago

Ah, I see In that case, there's no other way of doing it. Although, pretty sure in the above query, you don't need to alias query unless you're using it as a CTE for another query

AlexDanielOP•3d ago

@Mario564 without .as it gives a long typescript error like:

ts: Argument of type 'Omit<PgSelectQueryBuilderBase<PgSelectQueryBuilderHKT, … … … is missing the following properties from type 'SQL<unknown>': queryChunks, shouldInlineParams, append, toQuery, and 5 more.

That's pretty much what made me ask this question – to me it's unclear what I can do with the query created from QueryBuilder. If I alias it, then I can do await db.select().from(queryFromQueryBuilder), but that is a bit strange/awkward.

Mario564•3d ago

My bad, it is required. Here are the docs for this feature: https://orm.drizzle.team/docs/select#select-from-subquery

Drizzle ORM - Select

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

AlexDanielOP•3d ago

But that's the thing – the result from the QueryBuilder is already a query. I shouldn't really need to select from a subquery to use it. Ideally I should be able to just give the result of the querybuilder to the db object, but it seems like this is not possible :S

Gaming

Programming

Executing a query that was made with QueryBuilder