Last Updated: 3/20/2026

CTEs (Common Table Expressions)

Common Table Expressions (CTEs) let you define temporary named result sets that you can reference within a query. They’re created using the WITH clause and are useful for modularizing complex queries, improving readability, and enabling recursive queries.

Basic CTEs with `db.with()`

Use db.with() to create a CTE:


const result = await db
  .with('jennifers', (db) => db
    .selectFrom('person')
    .where('first_name', '=', 'Jennifer')
    .select(['id', 'age'])
  )
  .selectFrom('jennifers')
  .where('age', '>', 18)
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


with "jennifers" as (
  select "id", "age" from "person" where "first_name" = $1
)
select * from "jennifers" where "age" > $2

The first argument to with() is the CTE name. The second argument is a callback that receives a QueryCreator and returns a query. The CTE is then available in the main query.

Chaining Multiple CTEs

You can chain multiple with() calls to create multiple CTEs:


const result = await db
  .with('jennifers', (db) => db
    .selectFrom('person')
    .where('first_name', '=', 'Jennifer')
    .select(['id', 'age'])
  )
  .with('adult_jennifers', (db) => db
    .selectFrom('jennifers')
    .where('age', '>', 18)
    .select(['id', 'age'])
  )
  .selectFrom('adult_jennifers')
  .where('age', '<', 60)
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


with "jennifers" as (
  select "id", "age" from "person" where "first_name" = $1
),
"adult_jennifers" as (
  select "id", "age" from "jennifers" where "age" > $2
)
select * from "adult_jennifers" where "age" < $3

Each CTE can reference previously defined CTEs. The query optimizer can inline CTEs or materialize them depending on what’s faster.

CTEs with Column Names

You can specify column names in the CTE definition. Kysely will validate that the expression returns columns with matching names:


const result = await db
  .with('jennifers(id, age)', (db) => db
    .selectFrom('person')
    .where('first_name', '=', 'Jennifer')
    .select(['id', 'age']) // Must match the column names
  )
  .selectFrom('jennifers')
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


with "jennifers"("id", "age") as (
  select "id", "age" from "person" where "first_name" = $1
)
select * from "jennifers"

CTEs in INSERT, UPDATE, DELETE

Some databases (like PostgreSQL) allow CTEs in INSERT, UPDATE, and DELETE queries. This is extremely powerful for complex data modifications:


const result = await db
  .with('new_person', (db) => db
    .insertInto('person')
    .values({
      first_name: 'Jennifer',
      age: 35,
    })
    .returning('id')
  )
  .with('new_pet', (db) => db
    .insertInto('pet')
    .values({
      name: 'Doggo',
      species: 'dog',
      is_favorite: true,
      owner_id: db
        .selectFrom('new_person')
        .select('id')
    })
    .returning('id')
  )
  .selectFrom(['new_person', 'new_pet'])
  .select([
    'new_person.id as person_id',
    'new_pet.id as pet_id'
  ])
  .execute()

The generated SQL (PostgreSQL):


with "new_person" as (
  insert into "person" ("first_name", "age")
  values ($1, $2)
  returning "id"
),
"new_pet" as (
  insert into "pet" ("name", "species", "is_favorite", "owner_id")
  values ($3, $4, $5, (select "id" from "new_person"))
  returning "id"
)
select
  "new_person"."id" as "person_id",
  "new_pet"."id" as "pet_id"
from "new_person", "new_pet"

This example inserts a person and a pet in a single query, using the person’s ID in the pet insert.

Recursive CTEs with `db.withRecursive()`

Use db.withRecursive() to create recursive CTEs. A recursive CTE references itself in its definition, allowing you to traverse hierarchical data like organizational charts or category trees.


const result = await db
  .withRecursive('tree', (db) => db
    // Base case: select the root node
    .selectFrom('category')
    .where('parent_id', 'is', null)
    .select(['id', 'name', 'parent_id'])
    .unionAll(
      // Recursive case: select children of the current level
      db.selectFrom('category')
        .innerJoin('tree', 'tree.id', 'category.parent_id')
        .select(['category.id', 'category.name', 'category.parent_id'])
    )
  )
  .selectFrom('tree')
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


with recursive "tree" as (
  select "id", "name", "parent_id"
  from "category"
  where "parent_id" is null
  union all
  select "category"."id", "category"."name", "category"."parent_id"
  from "category"
  inner join "tree" on "tree"."id" = "category"."parent_id"
)
select * from "tree"

A recursive CTE has two parts:

Base case — The initial query that selects the starting rows (e.g., root nodes).
Recursive case — A query that references the CTE itself to select the next level of rows.

The two parts are combined with UNION or UNION ALL. The database executes the base case first, then repeatedly executes the recursive case until no more rows are returned.

Materialized Hints

Some databases (like PostgreSQL 12+) support materialized hints to control whether a CTE is inlined or materialized. Use the CTEBuilder callback syntax:


const result = await db
  .with(
    (cte) => cte('jennifers').materialized(),
    (db) => db
      .selectFrom('person')
      .where('first_name', '=', 'Jennifer')
      .select(['id', 'age'])
  )
  .selectFrom('jennifers')
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


with "jennifers" as materialized (
  select "id", "age" from "person" where "first_name" = $1
)
select * from "jennifers"

Use notMaterialized() to explicitly prevent materialization:


const result = await db
  .with(
    (cte) => cte('jennifers').notMaterialized(),
    (db) => db
      .selectFrom('person')
      .where('first_name', '=', 'Jennifer')
      .select(['id', 'age'])
  )
  .selectFrom('jennifers')
  .selectAll()
  .execute()

The generated SQL (PostgreSQL):


with "jennifers" as not materialized (
  select "id", "age" from "person" where "first_name" = $1
)
select * from "jennifers"

Materialization can affect performance. Materialized CTEs are executed once and stored in memory; non-materialized CTEs may be inlined and executed multiple times if referenced multiple times.

Type Safety

CTEs are fully type-safe. When you reference a CTE in the main query, Kysely knows which columns are available:


const result = await db
  .with('jennifers', (db) => db
    .selectFrom('person')
    .where('first_name', '=', 'Jennifer')
    .select(['id', 'age']) // Kysely knows these columns exist
  )
  .selectFrom('jennifers')
  .select(['id', 'age']) // TypeScript validates these references
  .execute()
 
// result[0].id is typed as number
// result[0].age is typed as number

If you try to select a column that doesn’t exist in the CTE, TypeScript will catch the error at compile time.

Recursiveness is Statement-Level

Recursiveness is a property of the entire WITH statement, not individual CTEs. The first with() or withRecursive() call determines whether the statement is recursive. You cannot mix recursive and non-recursive CTEs in the same statement.


// This creates a recursive WITH statement
const result = await db
  .withRecursive('tree', (db) => /* ... */)
  .with('other_cte', (db) => /* ... */) // Also recursive
  .selectFrom('tree')
  .selectAll()
  .execute()

What’s Next

Expressions — Learn how to build complex expressions for CTE queries.
Reusable Helpers — Build reusable CTE helpers for common patterns.

CTEs (Common Table Expressions)

Basic CTEs with db.with()

Chaining Multiple CTEs

CTEs with Column Names

CTEs in INSERT, UPDATE, DELETE

Recursive CTEs with db.withRecursive()

Materialized Hints

Type Safety

Recursiveness is Statement-Level

What’s Next

Basic CTEs with `db.with()`

Recursive CTEs with `db.withRecursive()`