kysely Documentation

File: README.md Copy Chapter

Join the discussion ⠀⠀⠀⠀⠀⠀⠀

Get started

& more!

[Kysely](https://kysely.dev)

Kysely (pronounce “Key-Seh-Lee”) is a type-safe and autocompletion-friendly TypeScript SQL query builder.
Inspired by Knex.js. Mainly developed for Node.js but also
runs on all other JavaScript environments like Deno, Bun, Cloudflare Workers
and web browsers.

Kysely makes sure you only refer to tables and columns that are visible to the part of the query
you're writing. The result type only has the selected columns with correct types and aliases. As an
added bonus you get autocompletion for all that stuff.

As shown in the gif above, through the pure magic of modern TypeScript, Kysely is even able to parse
the alias given to pet.name and add the pet_name column to the result row type. Kysely is able to infer
column names, aliases and types from selected subqueries, joined subqueries, with statements and pretty
much anything you can think of.

Of course there are cases where things cannot be typed at compile time, and Kysely offers escape
hatches for these situations. See the sql template tag
and the DynamicModule for more info.

All API documentation is written in the typing files and you can simply hover over the module, class
or method you're using to see it in your IDE. The same documentation is also hosted here.

If you start using Kysely and can't find something you'd want to use, please open an issue or join our
Discord server.

Getting started

Please visit our documentation site kysely.dev to get started. We also have a comprehensive
API documentation hosted here, but you can access the same
documentation in your IDE by hovering over a class/method/property/whatever.

Core team

Project leads Copy Chapter

Responsible for project direction, API design, maintenance, code reviews, community support, documentation, and working on some of the most
impactful/challenging things.

| Sami Koskimäki

(the [author](https://web.archive.org/web/20211203210043/https://www.jakso.me/blog/kysely-a-type-safe-sql-query-builder-for-typescript)) | Igal Klebanov
            
            
            (the [dynamo](https://github.com/kysely-org/kysely/pull/1414#issuecomment-2781281996)) |

| --- | --- |

Honorable mentions Copy Chapter

People who had special impact on the project and its growth.

| Fernando Hurtado

(1st [docs](https://kysely.dev)) | Wirekang
            
            
            ([playground](https://kyse.link)) | Tim Griesser
            
            
            ([Knex](https://knexjs.org/)) |

| --- | --- | --- |
| Robin Blomberg

([codegen](https://github.com/RobinBlomberg/kysely-codegen)) | Shoubhit Dash
            
            
            (prisma [idea](https://www.nexxel.dev/blog/typesafe-database)) | Valtýr Örn Kjartansson
            
            
            (prisma [impl](https://github.com/valtyr/prisma-kysely)) |

| Dax Raad

(early [adopter](https://thdxr.com/post/serverless-relational-showdown)) | Theo Browne
            
            
            (early [promoter](https://discord.com/channels/966627436387266600/988912020558602331/993220628154961930)) | Lee Robinson
            
            
            (early [promoter](https://x.com/leerob/status/1576929372811849730)) |

| Ethan Resnick

(timely [feedback](https://github.com/kysely-org/kysely/issues/494)) | Harminder Virk
            
            
            (dope [writeup](https://github.com/thetutlage/meta/discussions/8)) | Johan Eliasson
            
            
            ([promoter](https://eliasson.me/articles/crafting-the-perfect-t3-stack-my-journey-with-kysely-atlas-and-clerk)/[educator](https://www.youtube.com/watch?v=u2s39dRIpCM)) |

All contributors Copy Chapter

File: example/README.md Copy Chapter

An example server that uses Kysely

This is a simple but realistic Koa based server that shows one way to use Kysely. Since this example attempts to mimic a real world project, most of the code isn't relevant to learning how to use Kysely. The relevant parts are the repositories and how they are used. This examples is by no means the best or the right way to use Kysely, but simply one possible way.

The server has three main levels of abstraction:

1. Repository: Repositories contain all Kysely code and provide higher level methods for dealing with the database.

2. Service: All business logic is implemented in the service layer. Services use repositories to interact with the database. While repositories deal with database rows and types like UserRow the service layer doesn't leak out those types. For example user service methods return and take User objects instead of UserRow objects.

3. Controller: Controllers define the HTTP API. Controllers validate and convert the inputs and outputs from/to the network and call services to carry out the actual business logic.

Running the example Copy Chapter

All you need to do start poking around with the code is to clone kysely, go to the example folder and run:

npm install
npm test

You need to have postgres running in the default port 5432 and the default postgres user postgres should exist with no password. You can modify the test configuration if you want to use different settings.

File: site/docs/examples/cte/0010-simple-selects.mdx Copy Chapter

title: 'Simple selects' Copy Chapter

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 { Playground } from '../../../src/components/Playground'

import {
simpleSelects
} from './0010-simple-selects'

File: site/docs/examples/cte/0020-inserts-updates-and-deletions.mdx Copy Chapter

title: 'Inserts, updates and deletions' Copy Chapter

Inserts, updates and deletions

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

import { Playground } from '../../../src/components/Playground'

import {
insertsUpdatesAndDeletions
} from './0020-inserts-updates-and-deletions'

File: site/docs/examples/delete/0010-single-row.mdx Copy Chapter

title: 'Single row' Copy Chapter

Single row

Delete a single row:

import { Playground } from '../../../src/components/Playground'

import {
singleRow
} from './0010-single-row'

:::info[More examples]
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:

• deleteFrom method
• returning method
  :::

File: site/docs/examples/insert/0010-single-row.mdx Copy Chapter

title: 'Single row' Copy Chapter

Single row

Insert a single row:

import { Playground } from '../../../src/components/Playground'

import {
singleRow
} from './0010-single-row'

:::info[More examples]
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:

• values method
• onConflict method
• returning method
• insertInto method
  :::

File: site/docs/examples/insert/0020-multiple-rows.mdx Copy Chapter

title: 'Multiple rows' Copy Chapter

Multiple rows

On dialects that support it (for example PostgreSQL) you can insert multiple
rows by providing an array. Note that the return value is once again very
dialect-specific. Some databases may only return the id of the last inserted
row and some return nothing at all unless you call returning.

import { Playground } from '../../../src/components/Playground'

import {
multipleRows
} from './0020-multiple-rows'

:::info[More examples]
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:

• values method
• onConflict method
• returning method
• insertInto method
  :::

File: site/docs/examples/insert/0030-returning-data.mdx Copy Chapter

title: 'Returning data' Copy Chapter

Returning data

On supported dialects like PostgreSQL you need to chain returning to the query to get
the inserted row's columns (or any other expression) as the return value. returning
works just like select. Refer to select method's examples and documentation for
more info.

import { Playground } from '../../../src/components/Playground'

import {
returningData
} from './0030-returning-data'

:::info[More examples]
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:

• values method
• onConflict method
• returning method
• insertInto method
  :::

File: site/docs/examples/insert/0040-complex-values.mdx Copy Chapter

title: 'Complex values' Copy Chapter

Complex values

In addition to primitives, the values can also be arbitrary expressions.
You can build the expressions by using a callback and calling the methods
on the expression builder passed to it:

import { Playground } from '../../../src/components/Playground'

import {
complexValues
} from './0040-complex-values'

:::info[More examples]
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:

• values method
• onConflict method
• returning method
• insertInto method
  :::

File: site/docs/examples/insert/0050-insert-subquery.mdx Copy Chapter

title: 'Insert subquery' Copy Chapter

Insert subquery

You can create an INSERT INTO SELECT FROM query using the expression method.
This API doesn't follow our WYSIWYG principles and might be a bit difficult to
remember. The reasons for this design stem from implementation difficulties.

import { Playground } from '../../../src/components/Playground'

import {
insertSubquery
} from './0050-insert-subquery'

:::info[More examples]
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:

• values method
• onConflict method
• returning method
• insertInto method
  :::