drizzle-team
[FEATURE]: Support Raw SQL in CTE Query Builder
Describe what you want
Summary
Developers should be able to define CTEs using the sql escape hatch.
Current State
CTE definitions use a query builder that can be of type TypedQueryBuilder<TSelection, unknown> or (qb: QueryBuilder) => TypedQueryBuilder<TSelection, unknown>). Developers need to implement a custom query builder if they use the first type option or are limited to selects with they use the second option.
Candidate API
Something like this would allow access to the underlying sql:
db.$with('my-cte)
.as(sql<MyCustomType[]>`
select * from sometable
`)
Additional Context
I'm reaching for this because I have a CTE that makes use of a lateral join, which is not currently supported in the select API. I'm watching this MR, which appears to add this support: https://github.com/drizzle-team/drizzle-orm/pull/1079.
This can be very helpful for introducing drizzle into existing codebases – put an existing query into a CTE and then combine it with drizzle queries. Right now you can more or less achieve this with subqueries, but CTEs would be more convenient.
ok answer found. I needed the ( and )
const bla = await drizzle.select({ id: sql<string>`id` }).from(sql`(SELECT * FROM public.user)`);
