[FEATURE]: Support Raw SQL in CTE Query Builder

[san4d]

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: #1079.

[mwanago]

That would be great

[AlexDaniel]

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.

[johanneskares]

how can you achieve that with subqueries?

[johanneskares]

ok answer found. I needed the ( and )

const bla = await drizzle.select({ id: sql<string>`id` }).from(sql`(SELECT * FROM public.user)`);

[AndriiSherman]

Available in [email protected]

Release Notes