Skip to main content

Simple selects

Common table expressions (CTE) are a great way to modularize complex queries. Essentially they allow you to run multiple separate queries within a single roundtrip to the DB.

Since CTEs are a part of the main query, query optimizers inside DB engines are able to optimize the overall query. For example, postgres is able to inline the CTEs inside the using queries if it decides it's faster.

import type {
ColumnType,
Generated,
GeneratedAlways,
Insertable,
Kysely,
Selectable,
SqlBool,
Updateable,
} from 'kysely'

export interface Database {
audit: AuditTable
person: PersonTable
person_backup: PersonTable
pet: PetTable
toy: ToyTable
wine: WineTable
wine_stock_change: WineStockChangeTable
}

interface AuditTable {
id: Generated<number>
action: string
}

interface PersonTable {
id: Generated<number>
address: { city: string } | null
age: number | null
birthdate: ColumnType<Date | null, string | null | undefined, string | null>
created_at: GeneratedAlways<Date>
deleted_at: ColumnType<Date | null, string | null | undefined, string | null>
experience: { role: string }[] | null
first_name: string
gender: 'male' | 'female' | 'other' | null
has_pets: Generated<'Y' | 'N'>
last_name: string | null
middle_name: string | null
nicknames: string[] | null
nullable_column: string | null
profile: {
addresses: { city: string }[]
website: { url: string }
} | null
updated_at: ColumnType<Date | null, string | null | undefined, string | null>
marital_status: 'single' | 'married' | 'divorced' | 'widowed' | null
}

interface PetTable {
id: Generated<number>
created_at: GeneratedAlways<Date>
is_favorite: Generated<SqlBool>
name: string
owner_id: number
species: Species
}

interface ToyTable {
id: Generated<number>
name: string
pet_id: number
price: number
}

interface WineTable {
id: Generated<number>
name: string
stock: number
}

interface WineStockChangeTable {
id: Generated<number>
stock_delta: number
wine_name: string
}

export type Person = Selectable<PersonTable>
export type NewPerson = Insertable<PersonTable>
export type PersonUpdate = Updateable<PersonTable>
export type Pet = Selectable<PetTable>
export type NewPet = Insertable<PetTable>
export type PetUpdate = Updateable<PetTable>
export type Species = 'dog' | 'cat' | 'hamster'

declare global {
// @ts-ignore
export class Buffer {
static isBuffer(obj: unknown): obj is { length: number }
static compare(a: Buffer, b: Buffer): number
}
export const db: Kysely<Database>
export function functionThatExpectsPersonWithNonNullValue(
person: Person & { nullable_column: string },
): void
}


const result = await db
// Create a CTE called `jennifers` that selects all
// persons named 'Jennifer'.
.with('jennifers', (db) => db
.selectFrom('person')
.where('first_name', '=', 'Jennifer')
.select(['id', 'age'])
)
// Select all rows from the `jennifers` CTE and
// further filter it.
.with('adult_jennifers', (db) => db
.selectFrom('jennifers')
.where('age', '>', 18)
.select(['id', 'age'])
)
// Finally select all adult jennifers that are
// also younger than 60.
.selectFrom('adult_jennifers')
.where('age', '<', 60)
.selectAll()
.execute()