@auth/kysely-adapter

Official Kysely adapter for Auth.js / NextAuth.js.

Installation

npm install kysely @auth/kysely-adapter

pnpm add kysely @auth/kysely-adapter

yarn add kysely @auth/kysely-adapter

bun add kysely @auth/kysely-adapter

KyselyAuth<DB, T>

Wrapper over the original Kysely class in order to validate the passed in database interface. A regular Kysely instance may also be used, but wrapping it ensures the database interface implements the fields that Auth.js requires. When used with kysely-codegen, the Codegen type can be passed as the second generic argument. The generated types will be used, and KyselyAuth will only verify that the correct fields exist.

Extends

• Kysely<DB>

Type parameters

Constructors

new KyselyAuth(args)

new KyselyAuth<DB, T>(args): KyselyAuth<DB, T>

Parameters

Returns

KyselyAuth<DB, T>

Inherited from

Kysely<DB>.constructor

new KyselyAuth(args)

new KyselyAuth<DB, T>(args): KyselyAuth<DB, T>

Parameters

Returns

KyselyAuth<DB, T>

Inherited from

Kysely<DB>.constructor

Properties

#private

private #private: any;

Inherited from

Kysely.#private

#private

private #private: any;

Inherited from

Kysely.#private

Accessors

dynamic

get dynamic(): DynamicModule

Returns a the DynamicModule module.

The DynamicModule module can be used to bypass strict typing and passing in dynamic values for the queries.

Returns

DynamicModule

fn

get fn(): FunctionModule<DB, keyof DB>

Returns a FunctionModule that can be used to write somewhat type-safe function calls.

const { count } = db.fn
 
await db.selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select([
    'id',
    count('pet.id').as('person_count'),
  ])
  .groupBy('person.id')
  .having(count('pet.id'), '>', 10)
  .execute()

The generated SQL (PostgreSQL):

select "person"."id", count("pet"."id") as "person_count"
from "person"
inner join "pet" on "pet"."owner_id" = "person"."id"
group by "person"."id"
having count("pet"."id") > $1

Why “somewhat” type-safe? Because the function calls are not bound to the current query context. They allow you to reference columns and tables that are not in the current query. E.g. remove the innerJoin from the previous query and TypeScript won’t even complain.

If you want to make the function calls fully type-safe, you can use the ExpressionBuilder.fn getter for a query context-aware, stricter FunctionModule.

await db.selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .select((eb) => [
    'person.id',
    eb.fn.count('pet.id').as('pet_count')
  ])
  .groupBy('person.id')
  .having((eb) => eb.fn.count('pet.id'), '>', 10)
  .execute()

Returns

FunctionModule<DB, keyof DB>

introspection

get introspection(): DatabaseIntrospector

Returns a DatabaseIntrospector | database introspector.

Returns

DatabaseIntrospector

isTransaction

get isTransaction(): boolean

Returns true if this Kysely instance is a transaction.

You can also use db instanceof Transaction.

Returns

boolean

schema

get schema(): SchemaModule

Returns the SchemaModule module for building database schema.

Returns

SchemaModule

Methods

case()

case(undefined)

case(): CaseBuilder<DB, keyof DB, unknown, never>

Creates a case statement/operator.

See ExpressionBuilder.case for more information.

Returns

CaseBuilder<DB, keyof DB, unknown, never>

Inherited from

Kysely.case

case(value)

case<V>(value): CaseBuilder<DB, keyof DB, V, never>

Type parameters

Parameters

Returns

CaseBuilder<DB, keyof DB, V, never>

Inherited from

Kysely.case

connection()

connection(): ConnectionBuilder<DB>

Provides a kysely instance bound to a single database connection.

Examples

await db
  .connection()
  .execute(async (db) => {
    // `db` is an instance of `Kysely` that's bound to a single
    // database connection. All queries executed through `db` use
    // the same connection.
    await doStuff(db)
  })
 
async function doStuff(kysely: typeof db) {
  // ...
}

Returns

ConnectionBuilder<DB>

Inherited from

Kysely.connection

deleteFrom()

deleteFrom(from)

deleteFrom<TR>(from): DeleteQueryBuilder<DB, ExtractTableAlias<DB, TR>, DeleteResult>

Creates a delete query.

See the DeleteQueryBuilder.where method for examples on how to specify a where clause for the delete operation.

The return value of the query is an instance of DeleteResult.

Examples

<!-- siteExample("delete", "Single row", 10) -->

Delete a single row:

const result = await db
  .deleteFrom('person')
  .where('person.id', '=', 1)
  .executeTakeFirst()
 
console.log(result.numDeletedRows)

The generated SQL (PostgreSQL):

delete from "person" where "person"."id" = $1

Some databases such as MySQL support deleting from multiple tables:

const result = await db
  .deleteFrom(['person', 'pet'])
  .using('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .where('person.id', '=', 1)
  .executeTakeFirst()

The generated SQL (MySQL):

delete from `person`, `pet`
using `person`
inner join `pet` on `pet`.`owner_id` = `person`.`id`
where `person`.`id` = ?

Type parameters

Parameters

Returns

DeleteQueryBuilder<DB, ExtractTableAlias<DB, TR>, DeleteResult>

Inherited from

Kysely.deleteFrom

deleteFrom(tables)

deleteFrom<TR>(tables): DeleteQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TR> ? ExtractRowTypeFromTableExpression<DB, TR, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TR>>, DeleteResult>

Type parameters

Parameters

Returns

DeleteQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TR> ? ExtractRowTypeFromTableExpression<DB, TR, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TR>>, DeleteResult>

Inherited from

Kysely.deleteFrom

deleteFrom(from)

deleteFrom<TR>(from): DeleteQueryBuilder<DB, ExtractTableAlias<DB, TR>, DeleteResult>

Type parameters

Parameters

Returns

DeleteQueryBuilder<DB, ExtractTableAlias<DB, TR>, DeleteResult>

Inherited from

Kysely.deleteFrom

deleteFrom(table)

deleteFrom<TR>(table): DeleteQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TR> ? ExtractRowTypeFromTableExpression<DB, TR, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TR>>, DeleteResult>

Type parameters

Parameters

Returns

DeleteQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TR> ? ExtractRowTypeFromTableExpression<DB, TR, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TR>>, DeleteResult>

Inherited from

Kysely.deleteFrom

destroy()

destroy(): Promise<void>

Releases all resources and disconnects from the database.

You need to call this when you are done using the Kysely instance.

Returns

Promise<void>

Inherited from

Kysely.destroy

executeQuery()

executeQuery<R>(query, queryId?): Promise<QueryResult<R>>

Executes a given compiled query or query builder.

See splitting build, compile and execute code recipe for more information.

Type parameters

Parameters

Returns

Promise<QueryResult<R>>

Inherited from

Kysely.executeQuery

insertInto()

insertInto<T>(table): InsertQueryBuilder<DB, T, InsertResult>

Creates an insert query.

The return value of this query is an instance of InsertResult. InsertResult has the InsertResult.insertId | insertId field that holds the auto incremented id of the inserted row if the db returned one.

See the InsertQueryBuilder.values | values method for more info and examples. Also see the ReturningInterface.returning | returning method for a way to return columns on supported databases like PostgreSQL.

Examples

const result = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .executeTakeFirst()
 
console.log(result.insertId)

Some databases like PostgreSQL support the returning method:

const { id } = await db
  .insertInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .returning('id')
  .executeTakeFirstOrThrow()

Type parameters

Parameters

Returns

InsertQueryBuilder<DB, T, InsertResult>

Inherited from

Kysely.insertInto

mergeInto()

mergeInto(targetTable)

mergeInto<TR>(targetTable): MergeQueryBuilder<DB, TR, MergeResult>

Creates a merge query.

The return value of the query is a MergeResult.

See the MergeQueryBuilder.using method for examples on how to specify the other table.

Examples

<!-- siteExample("merge", "Source row existence", 10) -->

Update a target column based on the existence of a source row:

const result = await db
  .mergeInto('person as target')
  .using('pet as source', 'source.owner_id', 'target.id')
  .whenMatchedAnd('target.has_pets', '!=', 'Y')
  .thenUpdateSet({ has_pets: 'Y' })
  .whenNotMatchedBySourceAnd('target.has_pets', '=', 'Y')
  .thenUpdateSet({ has_pets: 'N' })
  .executeTakeFirstOrThrow()
 
console.log(result.numChangedRows)

The generated SQL (PostgreSQL):

merge into "person"
using "pet"
on "pet"."owner_id" = "person"."id"
when matched and "has_pets" != $1
then update set "has_pets" = $2
when not matched by source and "has_pets" = $3
then update set "has_pets" = $4

<!-- siteExample("merge", "Temporary changes table", 20) -->

Merge new entries from a temporary changes table:

const result = await db
  .mergeInto('wine as target')
  .using(
    'wine_stock_change as source',
    'source.wine_name',
    'target.name',
  )
  .whenNotMatchedAnd('source.stock_delta', '>', 0)
  .thenInsertValues(({ ref }) => ({
    name: ref('source.wine_name'),
    stock: ref('source.stock_delta'),
  }))
  .whenMatchedAnd(
    (eb) => eb('target.stock', '+', eb.ref('source.stock_delta')),
    '>',
    0,
  )
  .thenUpdateSet('stock', (eb) =>
    eb('target.stock', '+', eb.ref('source.stock_delta')),
  )
  .whenMatched()
  .thenDelete()
  .executeTakeFirstOrThrow()

The generated SQL (PostgreSQL):

merge into "wine" as "target"
using "wine_stock_change" as "source"
on "source"."wine_name" = "target"."name"
when not matched and "source"."stock_delta" > $1
then insert ("name", "stock") values ("source"."wine_name", "source"."stock_delta")
when matched and "target"."stock" + "source"."stock_delta" > $2
then update set "stock" = "target"."stock" + "source"."stock_delta"
when matched
then delete

Type parameters

Parameters

Returns

MergeQueryBuilder<DB, TR, MergeResult>

Inherited from

Kysely.mergeInto

mergeInto(targetTable)

mergeInto<TR>(targetTable): MergeQueryBuilder<DB & PickTableWithAlias<DB, TR>, ExtractTableAlias<DB & PickTableWithAlias<DB, TR>, TR>, MergeResult>

Type parameters

Parameters

Returns

MergeQueryBuilder<DB & PickTableWithAlias<DB, TR>, ExtractTableAlias<DB & PickTableWithAlias<DB, TR>, TR>, MergeResult>

Inherited from

Kysely.mergeInto

replaceInto()

replaceInto<T>(table): InsertQueryBuilder<DB, T, InsertResult>

Creates a replace query.

A MySQL-only statement similar to InsertQueryBuilder.onDuplicateKeyUpdate that deletes and inserts values on collision instead of updating existing rows.

The return value of this query is an instance of InsertResult. InsertResult has the InsertResult.insertId | insertId field that holds the auto incremented id of the inserted row if the db returned one.

See the InsertQueryBuilder.values | values method for more info and examples.

Examples

const result = await db
  .replaceInto('person')
  .values({
    first_name: 'Jennifer',
    last_name: 'Aniston'
  })
  .executeTakeFirst()
 
console.log(result.insertId)

Type parameters

Parameters

Returns

InsertQueryBuilder<DB, T, InsertResult>

Inherited from

Kysely.replaceInto

selectFrom()

selectFrom(from)

selectFrom<TE>(from): SelectQueryBuilder<DB, ExtractTableAlias<DB, TE>, {}>

Creates a select query builder for the given table or tables.

The tables passed to this method are built as the query’s from clause.

Examples

Create a select query for one table:

db.selectFrom('person').selectAll()

The generated SQL (PostgreSQL):

select * from "person"

Create a select query for one table with an alias:

const persons = await db.selectFrom('person as p')
  .select(['p.id', 'first_name'])
  .execute()
 
console.log(persons[0].id)

The generated SQL (PostgreSQL):

select "p"."id", "first_name" from "person" as "p"

Create a select query from a subquery:

const persons = await db.selectFrom(
    (eb) => eb.selectFrom('person').select('person.id as identifier').as('p')
  )
  .select('p.identifier')
  .execute()
 
console.log(persons[0].identifier)

The generated SQL (PostgreSQL):

select "p"."identifier",
from (
  select "person"."id" as "identifier" from "person"
) as p

Create a select query from raw sql:

import { sql } from 'kysely'
 
const items = await db
  .selectFrom(sql<{ one: number }>`(select 1 as one)`.as('q'))
  .select('q.one')
  .execute()
 
console.log(items[0].one)

The generated SQL (PostgreSQL):

select "q"."one",
from (
  select 1 as one
) as q

When you use the sql tag you need to also provide the result type of the raw snippet / query so that Kysely can figure out what columns are available for the rest of the query.

The selectFrom method also accepts an array for multiple tables. All the above examples can also be used in an array.

import { sql } from 'kysely'
 
const items = await db.selectFrom([
    'person as p',
    db.selectFrom('pet').select('pet.species').as('a'),
    sql<{ one: number }>`(select 1 as one)`.as('q')
  ])
  .select(['p.id', 'a.species', 'q.one'])
  .execute()

The generated SQL (PostgreSQL):

select "p".id, "a"."species", "q"."one"
from
  "person" as "p",
  (select "pet"."species" from "pet") as a,
  (select 1 as one) as "q"

Type parameters

Parameters

Returns

SelectQueryBuilder<DB, ExtractTableAlias<DB, TE>, {}>

Inherited from

Kysely.selectFrom

selectFrom(from)

selectFrom<TE>(from): SelectQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TE> ? ExtractRowTypeFromTableExpression<DB, TE, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TE>>, {}>

Type parameters

Parameters

Returns

SelectQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TE> ? ExtractRowTypeFromTableExpression<DB, TE, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TE>>, {}>

Inherited from

Kysely.selectFrom

selectFrom(from)

selectFrom<TE>(from): SelectQueryBuilder<DB, ExtractTableAlias<DB, TE>, {}>

Type parameters

Parameters

Returns

SelectQueryBuilder<DB, ExtractTableAlias<DB, TE>, {}>

Inherited from

Kysely.selectFrom

selectFrom(from)

selectFrom<TE>(from): SelectQueryBuilder<DB & PickTableWithAlias<DB, TE>, ExtractTableAlias<DB & PickTableWithAlias<DB, TE>, TE>, {}>

Type parameters

Parameters

Returns

SelectQueryBuilder<DB & PickTableWithAlias<DB, TE>, ExtractTableAlias<DB & PickTableWithAlias<DB, TE>, TE>, {}>

Inherited from

Kysely.selectFrom

selectFrom(from)

selectFrom<TE>(from): SelectQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TE> ? ExtractRowTypeFromTableExpression<DB, TE, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TE>>, {}>

Type parameters

Parameters

Returns

SelectQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TE> ? ExtractRowTypeFromTableExpression<DB, TE, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TE>>, {}>

Inherited from

Kysely.selectFrom

selectNoFrom()

selectNoFrom(selections)

selectNoFrom<SE>(selections): SelectQueryBuilder<DB, never, Selection<DB, never, SE>>

Creates a select query builder without a from clause.

If you want to create a select from query, use the selectFrom method instead. This one can be used to create a plain select statement without a from clause.

This method accepts the same inputs as SelectQueryBuilder.select. See its documentation for more examples.

Examples

const result = await db.selectNoFrom((eb) => [
  eb.selectFrom('person')
    .select('id')
    .where('first_name', '=', 'Jennifer')
    .limit(1)
    .as('jennifer_id'),
  eb.selectFrom('pet')
    .select('id')
    .where('name', '=', 'Doggo')
    .limit(1)
    .as('doggo_id')
])
.executeTakeFirstOrThrow()
 
console.log(result.jennifer_id)
console.log(result.doggo_id)

The generated SQL (PostgreSQL):

select (
  select "id"
  from "person"
  where "first_name" = $1
  limit $2
) as "jennifer_id", (
  select "id"
  from "pet"
  where "name" = $3
  limit $4
) as "doggo_id"

Type parameters

Parameters

Returns

SelectQueryBuilder<DB, never, Selection<DB, never, SE>>

Inherited from

Kysely.selectNoFrom

selectNoFrom(callback)

selectNoFrom<CB>(callback): SelectQueryBuilder<DB, never, CallbackSelection<DB, never, CB>>

Type parameters

Parameters

Returns

SelectQueryBuilder<DB, never, CallbackSelection<DB, never, CB>>

Inherited from

Kysely.selectNoFrom

selectNoFrom(selection)

selectNoFrom<SE>(selection): SelectQueryBuilder<DB, never, Selection<DB, never, SE>>

Type parameters

Parameters

Returns

SelectQueryBuilder<DB, never, Selection<DB, never, SE>>

Inherited from

Kysely.selectNoFrom

transaction()

transaction(): TransactionBuilder<DB>

Creates a TransactionBuilder that can be used to run queries inside a transaction.

The returned TransactionBuilder can be used to configure the transaction. The TransactionBuilder.execute method can then be called to run the transaction. TransactionBuilder.execute takes a function that is run inside the transaction. If the function throws an exception,

1. the exception is caught,
2. the transaction is rolled back, and
3. the exception is thrown again. Otherwise the transaction is committed.

The callback function passed to the TransactionBuilder.execute | execute method gets the transaction object as its only argument. The transaction is of type Transaction which inherits Kysely. Any query started through the transaction object is executed inside the transaction.

Examples

<!-- siteExample("transactions", "Simple transaction", 10) -->

This example inserts two rows in a transaction. If an exception is thrown inside the callback passed to the execute method,

1. the exception is caught,
2. the transaction is rolled back, and
3. the exception is thrown again. Otherwise the transaction is committed.

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()
})

Setting the isolation level:

import type { Kysely } from 'kysely'
 
await db
  .transaction()
  .setIsolationLevel('serializable')
  .execute(async (trx) => {
    await doStuff(trx)
  })
 
async function doStuff(kysely: typeof db) {
  // ...
}

Returns

TransactionBuilder<DB>

Inherited from

Kysely.transaction

updateTable()

updateTable(table)

updateTable<TR>(table): UpdateQueryBuilder<DB, ExtractTableAlias<DB, TR>, ExtractTableAlias<DB, TR>, UpdateResult>

Creates an update query.

See the UpdateQueryBuilder.where method for examples on how to specify a where clause for the update operation.

See the UpdateQueryBuilder.set method for examples on how to specify the updates.

The return value of the query is an UpdateResult.

Examples

const result = await db
  .updateTable('person')
  .set({ first_name: 'Jennifer' })
  .where('person.id', '=', 1)
  .executeTakeFirst()
 
console.log(result.numUpdatedRows)

Type parameters

Parameters

Returns

UpdateQueryBuilder<DB, ExtractTableAlias<DB, TR>, ExtractTableAlias<DB, TR>, UpdateResult>

Inherited from

Kysely.updateTable

updateTable(table)

updateTable<TR>(table): UpdateQueryBuilder<DB & PickTableWithAlias<DB, TR>, ExtractTableAlias<DB & PickTableWithAlias<DB, TR>, TR>, ExtractTableAlias<DB & PickTableWithAlias<DB, TR>, TR>, UpdateResult>

Type parameters

Parameters

Returns

UpdateQueryBuilder<DB & PickTableWithAlias<DB, TR>, ExtractTableAlias<DB & PickTableWithAlias<DB, TR>, TR>, ExtractTableAlias<DB & PickTableWithAlias<DB, TR>, TR>, UpdateResult>

Inherited from

Kysely.updateTable

updateTable(table)

updateTable<TR>(table): UpdateQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TR> ? ExtractRowTypeFromTableExpression<DB, TR, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TR>>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TR>>, UpdateResult>

Type parameters

Parameters

Returns

UpdateQueryBuilder<DrainOuterGeneric<{ [C in any]: C extends ExtractAliasFromTableExpression<DB, TR> ? ExtractRowTypeFromTableExpression<DB, TR, C<C>> : C extends keyof DB ? DB[C<C>] : never }>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TR>>, DrainOuterGeneric<ExtractAliasFromTableExpression<DB, TR>>, UpdateResult>

Inherited from

Kysely.updateTable

with()

with<N, E>(nameOrBuilder, expression): QueryCreatorWithCommonTableExpression<DB, N, E>

Creates a with query (Common Table Expression).

Examples

<!-- siteExample("cte", "Simple selects", 10) -->

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.

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()

<!-- siteExample("cte", "Inserts, updates and deletions", 20) -->

Some databases like postgres also allow you to run other queries than selects in CTEs. On these databases CTEs are extremely powerful:

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,
      // Use the id of the person we just inserted.
      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 CTE name can optionally specify column names in addition to a name. In that case Kysely requires the expression to retun rows with the same columns.

await db
  .with('jennifers(id, age)', (db) => db
    .selectFrom('person')
    .where('first_name', '=', 'Jennifer')
    // This is ok since we return columns with the same
    // names as specified by `jennifers(id, age)`.
    .select(['id', 'age'])
  )
  .selectFrom('jennifers')
  .selectAll()
  .execute()

The first argument can also be a callback. The callback is passed a CTEBuilder instance that can be used to configure the CTE:

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

Type parameters

Parameters

Returns

QueryCreatorWithCommonTableExpression<DB, N, E>

Inherited from

Kysely.with

withPlugin()

withPlugin(plugin): Kysely<DB>

Returns a copy of this Kysely instance with the given plugin installed.

Parameters

Returns

Kysely<DB>

Inherited from

Kysely.withPlugin

withRecursive()

withRecursive<N, E>(nameOrBuilder, expression): QueryCreatorWithCommonTableExpression<DB, N, E>

Creates a recursive with query (Common Table Expression).

Note that recursiveness is a property of the whole with statement. You cannot have recursive and non-recursive CTEs in a same with statement. Therefore the recursiveness is determined by the first with or withRecusive call you make.

See the with method for examples and more documentation.

Type parameters

Parameters

Returns

QueryCreatorWithCommonTableExpression<DB, N, E>

Inherited from

Kysely.withRecursive

withSchema()

withSchema(schema): Kysely<DB>

Parameters

Returns

Kysely<DB>

Inherited from

Kysely.withSchema

withTables()

withTables<T>(): Kysely<DrainOuterGeneric<DB & T>>

Returns a copy of this Kysely instance with tables added to its database type.

This method only modifies the types and doesn’t affect any of the executed queries in any way.

Examples

The following example adds and uses a temporary table:

await db.schema
  .createTable('temp_table')
  .temporary()
  .addColumn('some_column', 'integer')
  .execute()
 
const tempDb = db.withTables<{
  temp_table: {
    some_column: number
  }
}>()
 
await tempDb
  .insertInto('temp_table')
  .values({ some_column: 100 })
  .execute()

Type parameters

Returns

Kysely<DrainOuterGeneric<DB & T>>

Inherited from

Kysely.withTables

withoutPlugins()

withoutPlugins(): Kysely<DB>

Returns a copy of this Kysely instance without any plugins.

Returns

Kysely<DB>

Inherited from

Kysely.withoutPlugins

Database

Properties

Account

Account: AdapterAccount;

Session

Session: AdapterSession;

User

User: AdapterUser;

VerificationToken

VerificationToken: VerificationToken;

Codegen

type Codegen: { [K in keyof Database]: { [J in keyof Database[K]]: unknown } };

format

const format: {
  from: T;
  to: T;
};

Type declaration

from()

Type parameters

Parameters

Returns

T

to()

Type parameters

Parameters

Returns

T

KyselyAdapter()

KyselyAdapter(db): Adapter

Parameters

Returns

Adapter