Kysely

https://kysely.dev/docs/
Admin
Kysely is a type-safe SQL query builder for TypeScript that provides autocompletion and compile-time...
Tokens:77,326
Snippets:433
Trust Score:9
Update:2 weeks ago
Show doc for...
Tokens:
Raw
### Install kysely-supabase

Source: https://kysely.dev/docs/integrations/supabase

Install the kysely-supabase bridge library as a development dependency using npm.

```bash
npm i -D kysely-supabase
```

--------------------------------

### Install Kysely with Bun

Source: https://kysely.dev/docs/getting-started

Installs Kysely using Bun, a JavaScript runtime with an npm-compatible package manager.

```bash
bun install kysely
```

--------------------------------

### selectAll example with database schema

Source: https://kysely.dev/docs/examples/select/all-columns

Complete TypeScript example showing the Kysely database schema and a query using selectAll to select all columns from the person table.

```typescript
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 persons = await db

  .selectFrom('person')

  .selectAll()

  .execute()
```

--------------------------------

### Generated SQL for withSchema example

Source: https://kysely.dev/docs/recipes/schemas

Example SQL generated when tenant equals 'acme'. Shows how the schema is applied to unqualified table references while explicit 'public' schema is preserved.

```sql
select * from "acme"."user"

inner join "acme"."user_permission" as "up" on "up"."user_id" = "acme"."user"."id"

inner join "public"."permission" as "p" on "p"."id" = "up"."permission_id"
```

--------------------------------

### Install Kysely with Yarn

Source: https://kysely.dev/docs/getting-started

Installs Kysely using Yarn, a fast, reliable and secure dependency manager.

```bash
yarn add kysely
```

--------------------------------

### Install Kysely with npm

Source: https://kysely.dev/docs/getting-started

Installs Kysely using npm, the default package manager for Node.js.

```bash
npm install kysely
```

--------------------------------

### Install Kysely with pnpm

Source: https://kysely.dev/docs/getting-started

Installs Kysely using pnpm, a fast and disk space efficient package manager.

```bash
pnpm install kysely
```

--------------------------------

### Kysely playground example with TypeScript types and query

Source: https://kysely.dev/docs/playground

Example from the Kysely playground showing TypeScript interfaces for database tables, type aliases for selectable/insertable/updateable types, a global declaration for a Kysely instance, and a sample query using selectFrom. Use this to test Kysely features and create reproducible examples for issues, PRs, or Discord messages.

```typescript
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 person = await db

.selectFrom('person')

.select(['id', 'first_name'])

.where('id', '=', 1)

.executeTakeFirst()
```

--------------------------------

### Global installation of DeduplicateJoinsPlugin

Source: https://kysely.dev/docs/recipes/deduplicate-joins

Install the plugin globally by providing it in the Kysely configuration. This applies deduplication to all queries.

```typescript
const db = new Kysely<Database>({

  dialect,

  plugins: [new DeduplicateJoinsPlugin()],

})
```

--------------------------------

### Install pg driver for PostgreSQL dialect

Source: https://kysely.dev/docs/getting-started

Install the pg driver library for Kysely's built-in PostgreSQL dialect. Refer to the official pg documentation for configuration options.

```terminal
npm install pg 
```

--------------------------------

### Database schema and types for nested array example

Source: https://kysely.dev/docs/examples/select/nested-array

Defines the database interfaces and types used in the example, including PersonTable, PetTable, and others. This is the prerequisite schema for the query example.

```typescript
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

}
```

--------------------------------

### Install @electric-sql/pglite driver for PGlite dialect

Source: https://kysely.dev/docs/getting-started

Install the @electric-sql/pglite driver library for Kysely's built-in PGlite dialect. Refer to the official PGlite documentation for configuration options.

```terminal
npm install @electric-sql/pglite 
```

--------------------------------

### Install better-sqlite3 driver for SQLite dialect

Source: https://kysely.dev/docs/getting-started

Install the better-sqlite3 driver library for Kysely's built-in SQLite dialect. Refer to the official better-sqlite3 documentation for configuration options.

```terminal
npm install better-sqlite3 
```

--------------------------------

### Install mysql2 driver for MySQL dialect

Source: https://kysely.dev/docs/getting-started

Install the mysql2 driver library for Kysely's built-in MySQL dialect. Refer to the official mysql2 documentation for configuration options.

```terminal
npm install mysql2 
```

--------------------------------

### Install tedious and tarn for MSSQL dialect

Source: https://kysely.dev/docs/getting-started

Install the tedious driver and tarn resource pool package for Kysely's built-in Microsoft SQL Server (MSSQL) dialect. Refer to the official tedious and tarn documentation for configuration options.

```terminal
npm install tedious tarn
```

--------------------------------

### Function calls in Kysely select queries

Source: https://kysely.dev/docs/examples/select/function-calls

Complete TypeScript example showing how to create function calls in Kysely select queries. It defines the database schema and demonstrates using the `fn` module for common functions, `fn.agg` for aggregate functions, and the `sql` template tag with `ref` for column references. The example also shows how to pass values with `val` and how to use `having` with a function call.

```typescript
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 { sql } from 'kysely'



const result = await db.selectFrom('person')

  .innerJoin('pet', 'pet.owner_id', 'person.id')

  .select(({ fn, val, ref }) => [

    'person.id',



    // The `fn` module contains the most common

    // functions.

    fn.count<number>('pet.id').as('pet_count'),



    // You can call any function by calling `fn`

    // directly. The arguments are treated as column

    // references by default. If you want  to pass in

    // values, use the `val` function.

    fn<string>('concat', [

      val('Ms. '),

      'first_name',

      val(' '),

      'last_name'

    ]).as('full_name_with_title'),



    // You can call any aggregate function using the

    // `fn.agg` function.

    fn.agg<string[]>('array_agg', ['pet.name']).as('pet_names'),



    // And once again, you can use the `sql`

    // template tag. The template tag substitutions

    // are treated as values by default. If you want

    // to reference columns, you can use the `ref`

    // function.

    sql<string>`concat(

      ${ref('first_name')},

      ' ',

      ${ref('last_name')}

    )`.as('full_name')

  ])

  .groupBy('person.id')

  .having((eb) => eb.fn.count('pet.id'), '>', 10)

  .execute()
```

--------------------------------

### Minimal browser example with DummyDriver and SqliteAdapter

Source: https://kysely.dev/docs/runtimes/browser

Sets up a Kysely instance with a custom dialect using DummyDriver, SqliteAdapter, SqliteIntrospector, and SqliteQueryCompiler. On window load, compiles a simple select query and displays the SQL in a span element.

```typescript
import {

  Kysely,

  Generated,

  DummyDriver,

  SqliteAdapter,

  SqliteIntrospector,

  SqliteQueryCompiler,

} from 'kysely'



interface Person {

  id: Generated<number>

  first_name: string

  last_name: string | null

}



interface Database {

  person: Person

}



const db = new Kysely<Database>({

  dialect: {

    createAdapter() {

      return new SqliteAdapter()

    },

    createDriver() {

      return new DummyDriver()

    },

    createIntrospector(db: Kysely<unknown>) {

      return new SqliteIntrospector(db)

    },

    createQueryCompiler() {

      return new SqliteQueryCompiler()

    },

  },

})



window.addEventListener('load', () => {

  const sql = db.selectFrom('person').select('id').compile()



  const result = document.createElement('span')

  result.id = 'result'

  result.innerHTML = sql.sql



  document.body.appendChild(result)

})
```

--------------------------------

### Combine OR conditions with expression builder

Source: https://kysely.dev/docs/examples/where/or-where

Complete example showing two ways to combine conditions with OR in Kysely: using the expression builder's `or` method with an array of expressions, and chaining `.or()` on a created expression. Includes the required type definitions and database schema setup.

```typescript
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 persons = await db

  .selectFrom('person')

  .selectAll()

  // 1. Using the `or` method on the expression builder:

  .where((eb) => eb.or([

    eb('first_name', '=', 'Jennifer'),

    eb('first_name', '=', 'Sylvester')

  ]))

  // 2. Chaining expressions using the `or` method on the

  // created expressions:

  .where((eb) =>

    eb('last_name', '=', 'Aniston').or('last_name', '=', 'Stallone')

  )

  .execute()
```

--------------------------------

### Set up Kysely with custom dialect on Deno

Source: https://kysely.dev/docs/runtimes/deno

Imports Kysely from npm via jsdelivr and creates a Kysely instance with a custom dialect using PostgresAdapter, PostgresIntrospector, PostgresQueryCompiler, and DummyDriver. The dummy driver never executes queries, so this is for query building only. The example compiles a simple select query and logs the SQL.

```typescript
// We use jsdeliver to get Kysely from npm.

import {

  DummyDriver,

  Generated,

  Kysely,

  PostgresAdapter,

  PostgresIntrospector,

  PostgresQueryCompiler,

} from 'https://cdn.jsdelivr.net/npm/kysely/dist/index.js'



interface Person {

  id: Generated<number>

  first_name: string

  last_name: string | null

}



interface Database {

  person: Person

}



const db = new Kysely<Database>({

  dialect: {

    createAdapter() {

      return new PostgresAdapter()

    },

    createDriver() {

      // You need a driver to be able to execute queries. In this example

      // we use the dummy driver that never does anything.

      return new DummyDriver()

    },

    createIntrospector(db: Kysely<unknown>) {

      return new PostgresIntrospector(db)

    },

    createQueryCompiler() {

      return new PostgresQueryCompiler()

    },

  },

})



const query = db.selectFrom('person').select('id')

const sql = query.compile()



console.log(sql.sql)
```

--------------------------------

### Create a cold Kysely instance with DummyDriver

Source: https://kysely.dev/docs/recipes/splitting-query-building-and-execution

Instantiate Kysely with the built-in DummyDriver to use it purely as a query builder without database driver dependencies. This example compiles to PostgreSQL SQL dialect. Trying to execute queries with this instance returns empty results without communicating with a database.

```typescript
import {

  Generated,

  DummyDriver,

  Kysely,

  PostgresAdapter,

  PostgresIntrospector,

  PostgresQueryCompiler,

} from 'kysely'



interface Person {

  id: Generated<number>

  first_name: string

  last_name: string | null

}



interface Database {

  person: Person

}



const db = new Kysely<Database>({

  dialect: {

    createAdapter: () => new PostgresAdapter(),

    createDriver: () => new DummyDriver(),

    createIntrospector: (db) => new PostgresIntrospector(db),

    createQueryCompiler: () => new PostgresQueryCompiler(),

  },

})
```

--------------------------------

### PostgreSQL migration example with up and down functions

Source: https://kysely.dev/docs/migrations

Defines a migration with an up function that creates 'person' and 'pet' tables and an index, and a down function that drops them. Uses Kysely's schema builder and SQL template for default timestamp.

```typescript
import { Kysely, sql } from 'kysely'



export async function up(db: Kysely<any>): Promise<void> {

  await db.schema

    .createTable('person')

    .addColumn('id', 'serial', (col) => col.primaryKey())

    .addColumn('first_name', 'varchar', (col) => col.notNull())

    .addColumn('last_name', 'varchar')

    .addColumn('gender', 'varchar(50)', (col) => col.notNull())

    .addColumn('created_at', 'timestamp', (col) =>

      col.defaultTo(sql`now()`).notNull(),

    )

    .execute()



  await db.schema

    .createTable('pet')

    .addColumn('id', 'serial', (col) => col.primaryKey())

    .addColumn('name', 'varchar', (col) => col.notNull().unique())

    .addColumn('owner_id', 'integer', (col) =>

      col.references('person.id').onDelete('cascade').notNull(),

    )

    .addColumn('species', 'varchar', (col) => col.notNull())

    .execute()



  await db.schema

    .createIndex('pet_owner_id_index')

    .on('pet')

    .column('owner_id')

    .execute()

}



export async function down(db: Kysely<any>): Promise<void> {

  await db.schema.dropTable('pet').execute()

  await db.schema.dropTable('person').execute()

}
```

--------------------------------

### Full migration script with FileMigrationProvider and PostgresDialect

Source: https://kysely.dev/docs/migrations

A complete migration script for a Postgres database using FileMigrationProvider. It creates a Kysely instance, runs all pending migrations, logs results, and exits with code 1 on error. The migrationFolder must be an absolute path.

```typescript
import * as path from 'path'

import { Pool } from 'pg'

import { promises as fs } from 'fs'

import { Kysely, PostgresDialect } from 'kysely'

import { FileMigrationProvider, Migrator } from 'kysely/migration'

import { Database } from './types'



async function migrateToLatest() {

  const db = new Kysely<Database>({

    dialect: new PostgresDialect({

      pool: new Pool({

        host: 'localhost',

        database: 'kysely_test',

      }),

    }),

  })



  const migrator = new Migrator({

    db,

    provider: new FileMigrationProvider({

      fs,

      path,

      // This needs to be an absolute path.

      migrationFolder: path.join(__dirname, 'some/path/to/migrations'),

    }),

  })



  const { error, results } = await migrator.migrateToLatest()



  results?.forEach((it) => {

    if (it.status === 'Success') {

      console.log(`migration "${it.migrationName}" was executed successfully`)

    } else if (it.status === 'Error') {

      console.error(`failed to execute migration "${it.migrationName}"`)

    }

  })



  if (error) {

    console.error('failed to migrate')

    console.error(error)

    process.exit(1)

  }



  await db.destroy()

}



migrateToLatest()
```

--------------------------------

### MySQL join update with updateTable and set

Source: https://kysely.dev/docs/examples/update/my-sql-joins

TypeScript definitions for the Kysely database schema used in the MySQL join update example. It declares the Database interface, table interfaces, exported types, and a global db instance. The example updates both the person and pet tables in a single statement by passing them as a list to updateTable and using whereRef to join on person.id = pet.owner_id.

```typescript
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

  .updateTable(['person', 'pet'])

  .set('person.first_name', 'Updated person')

  .set('pet.name', 'Updated doggo')

  .whereRef('person.id', '=', 'pet.owner_id')

  .where('person.id', '=', 1)

  .executeTakeFirst()
```

--------------------------------

### Create Kysely instance with PostgresDialect

Source: https://kysely.dev/docs/getting-started

Creates a Kysely instance using the PostgresDialect and a pg Pool. The Database interface is passed to the constructor so Kysely knows the database structure. The dialect is passed so Kysely knows how to communicate with the database. The instance is exported as 'db'.

```typescript
import { Database } from './types.ts' // this is the Database interface we defined earlier

import { Pool } from 'pg'

import { Kysely, PostgresDialect } from 'kysely'



const dialect = new PostgresDialect({

  pool: new Pool({

    database: 'test',

    host: 'localhost',

    user: 'admin',

    port: 5434,

    max: 10,

  })

})



// Database interface is passed to Kysely's constructor, and from now on, Kysely 

// knows your database structure.

// Dialect is passed to Kysely's constructor, and from now on, Kysely knows how 

// to communicate with your database.

export const db = new Kysely<Database>({

  dialect,

})
```

--------------------------------

### Use $notNull() and $narrowType to handle not-null values

Source: https://kysely.dev/docs/examples/select/not-null

Complete example showing how to use `$notNull()` and `$narrowType` to make nullable expressions non-null in Kysely query results. Includes the full database schema and type definitions. The `$notNull()` method is applied to a `jsonObjectFrom` subquery, and `$narrowType` uses the `NotNull` type to narrow the output type. The example assumes the person has at least one pet and uses `where('last_name', 'is not', null)`.

```typescript
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 { NotNull } from 'kysely'

import { jsonObjectFrom } from 'kysely/helpers/postgres'



const persons = db

  .selectFrom('person')

  .select((eb) => [

    'last_name',

     // Let's assume we know the person has at least one

     // pet. We can use the `.$notNull()` method to make

     // the expression not null. You could just as well

     // add `pet` to the `$narrowType` call below.

     jsonObjectFrom(

       eb.selectFrom('pet')

         .selectAll()

         .limit(1)

         .whereRef('person.id', '=', 'pet.owner_id')

     ).$notNull().as('pet')

  ])

  .where('last_name', 'is not', null)

  // $narrowType can be used to narrow the output type.

  // The special `NotNull` type can be used to make a

  // selection not null. You could add `pet: NotNull`

  // here and omit the `$notNull()` call on it.

  // Use whichever way you prefer.

  .$narrowType<{ last_name: NotNull }>()

  .execute()
```

--------------------------------

### Simple transaction with Kysely: insert person and pet

Source: https://kysely.dev/docs/examples/transactions/simple-transaction

Defines the Kysely Database schema and types for the example, then runs a transaction that inserts a person and a pet. If the callback throws, the transaction rolls back and the exception is rethrown; otherwise it commits.

```typescript
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 catto = await db.transaction().execute(async (trx) => {

  const jennifer = await trx.insertInto('person')

    .values({

      first_name: 'Jennifer',

      last_name: 'Aniston',

      age: 40,

    })

    .returning('id')

    .executeTakeFirstOrThrow()



  return await trx.insertInto('pet')

    .values({

      owner_id: jennifer.id,

      name: 'Catto',

      species: 'cat',

      is_favorite: false,

    })

    .returningAll()

    .executeTakeFirst()

})
```

--------------------------------

### Using json helper in insert and where

Source: https://kysely.dev/docs/recipes/extending-kysely

Shows how to use the simplified json helper in insert and where clauses, mirroring the earlier JsonValue example but with the function-based approach.

```typescript
interface DB {

  person: {

    address: {

      postalCode: string

      street: string

    }

  }

}



async function test(db: Kysely<DB>) {

  await db

    .insertInto('person')

    .values({

      address: json({

        postalCode: '123456',

        street: 'Kysely avenue 42',

      }),

    })

    .execute()



  await db

    .selectFrom('person')

    .selectAll()

    .where(

      'address',

      '@>',

      json({ postalCode: '123456', street: 'Kysely avenue 42' })

    )

    .execute()

}
```

--------------------------------

### Compiled SQL for the subquery helper

Source: https://kysely.dev/docs/recipes/reusable-helpers

Shows the SQL that the subquery helper compiles to, demonstrating that it is inlined into the parent query.

```sql
select

  person.*

from

  person

where

  person.id in (

    select pet.owner_id

    from pet

    where pet.species = 'dog'

    and pet.name = ?

  )
```
Tokens:
Raw