Nested array
While kysely is not an ORM and it doesn't have the concept of relations, we do provide
helpers for fetching nested objects and arrays in a single query. In this example we
use the jsonArrayFrom
helper to fetch person's pets along with the person's id.
Please keep in mind that the helpers under the kysely/helpers
folder, including
jsonArrayFrom
, are not guaranteed to work with third party dialects. In order for
them to work, the dialect must automatically parse the json
data type into
JavaScript JSON values like objects and arrays. Some dialects might simply return
the data as a JSON string. In these cases you can use the built in ParseJSONResultsPlugin
to parse the results.
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
}
import { jsonArrayFrom } from 'kysely/helpers/postgres'
const result = await db
.selectFrom('person')
.select((eb) => [
'id',
jsonArrayFrom(
eb.selectFrom('pet')
.select(['pet.id as pet_id', 'pet.name'])
.whereRef('pet.owner_id', '=', 'person.id')
.orderBy('pet.name')
).as('pets')
])
.execute()
The API documentation is packed with examples. The API docs are hosted here, but you can access the same documentation by hovering over functions/methods/classes in your IDE. The examples are always just one hover away!
For example, check out these sections: