[FEATURE]: Support Postgres CTE Materialization Options

[MarkoH17]

Describe what you want

Currently, Drizzle ORM supports common table expression (CTE) / WITH queries for the Postgres dialect. It would be useful for Drizzle to allow users to configure materialization options for when the query is executed.

Currently, CTEs can be used with something like:

const theCte = db.$with('the_cte').as(db.select().from(users));
const query = db.with(theCte).select().from(theCte);

The above will generate SQL like the following:

with "the_cte" as (
  select
    "id",
    "first_name",
    "last_name"
  from
    "users"
)
select
  "id",
  "first_name",
  "last_name"
from
  "the_cte"

Supporting a new configuration parameter on the .with() method might allow passing materialization options. For example:

db.with(theCte, {
  mode: CteMaterializationMode.NOT_MATERIALIZED
}).select().from(theCte);

Specifying the materialization mode as shown above would presumably generate some SQL like:

with "the_cte" as NOT MATERIALIZED (
  select
    "id",
    "first_name",
    "last_name"
  from
    "users"
)
select
  "id",
  "first_name",
  "last_name"
from
  "the_cte"

The following materialization modes would ideally be supported:

• CteMaterializationMode.DEFAULT (current serialization behavior)
• CteMaterializationMode.MATERIALIZED
• CteMaterializationMode.NOT_MATERIALIZED

[dymoo]

+1 from me
CTE takes 6 mins, materialised is almost instant.

Any idea on how I can do this for the time being?

[dymoo]

+1 from me CTE takes 6 mins, materialised is almost instant.

Any idea on how I can do this for the time being?

const q = await db.execute(
 sql`
    WITH "ranked_matches" as MATERIALIZED ${db
      .with(sq)
      .select()
      .from(sq)}
    SELECT *
    FROM ranked_matches rm
   LIMIT 5
  `
);

Here is my workaround for anyone running into this issue.