Kysely

A type-safe typescript SQL query builder

koskimas/kyselykoskimas.github.io/kyselykysely

README

Stand With Ukraine
Discord Tests

Kysely


Kysely (pronounce “Key-Seh-Lee”) is a type-safe and autocompletion-friendly typescript SQL query builder. Inspired by
knex. Mainly developed for node.js but also runs on deno
and in the browser.

undefined

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 cmd-click on the module, class
or method you're using to see it. 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.

You can find a more thorough introduction here.

Table of contents


- Installation
    - 3rd party dialects
- Minimal example
- Generating types
- Query examples
  - Select queries
    - Stream select query results
  - Update queries
  - Insert queries
  - Delete queries
- Recipes
- Migrations
    - PostgreSQL migration example
    - MySQL migration example
- Deno
- Browser
- Why not just contribute to knex

Installation


Kysely currently works on PostgreSQL, MySQL and SQLite. You can install it using:

  1. ```
  2. # PostgreSQL
  3. npm install kysely pg

  5. # MySQL
  6. npm install kysely mysql2

  8. # SQLite
  9. npm install kysely better-sqlite3
  10. ```

More dialects will be added soon. Kysely also has a simple interface
for 3rd party dialects.

3rd party dialects


- AWS Data API
- PlanetScale Serverless Driver
- SingleStore Data API
- D1
- SurrealDB

Minimal example


All you need to do is define an interface for each table in the database and pass those
interfaces to the Kysely constructor:

  1. ```ts
  2. import { Pool } from 'pg'
  3. import {
  4.   Kysely,
  5.   PostgresDialect,
  6.   Generated,
  7.   ColumnType,
  8.   Selectable,
  9.   Insertable,
  10.   Updateable,
  11. } from 'kysely'

  13. interface PersonTable {
  14.   // Columns that are generated by the database should be marked
  15.   // using the `Generated` type. This way they are automatically
  16.   // made optional in inserts and updates.
  17.   id: Generated<number>

  19.   first_name: string
  20.   gender: 'male' | 'female' | 'other'

  22.   // If the column is nullable in the database, make its type nullable.
  23.   // Don't use optional properties. Optionality is always determined
  24.   // automatically by Kysely.
  25.   last_name: string | null

  27.   // You can specify a different type for each operation (select, insert and
  28.   // update) using the `ColumnType`
  29.   // wrapper. Here we define a column `modified_at` that is selected as
  30.   // a `Date`, can optionally be provided as a `string` in inserts and
  31.   // can never be updated:
  32.   modified_at: ColumnType<Date, string | undefined, never>
  33. }

  35. interface PetTable {
  36.   id: Generated<number>
  37.   name: string
  38.   owner_id: number
  39.   species: 'dog' | 'cat'
  40. }

  42. interface MovieTable {
  43.   id: Generated<string>
  44.   stars: number
  45. }

  47. // Keys of this interface are table names.
  48. interface Database {
  49.   person: PersonTable
  50.   pet: PetTable
  51.   movie: MovieTable
  52. }

  54. // You'd create one of these when you start your app.
  55. const db = new Kysely<Database>({
  56.   // Use MysqlDialect for MySQL and SqliteDialect for SQLite.
  57.   dialect: new PostgresDialect({
  58.     pool: new Pool({
  59.       host: 'localhost',
  60.       database: 'kysely_test'
  61.     })
  62.   })
  63. })

  65. async function demo() {
  66.   const { id } = await db
  67.     .insertInto('person')
  68.     .values({ first_name: 'Jennifer', gender: 'female' })
  69.     .returning('id')
  70.     .executeTakeFirstOrThrow()

  72.   await db
  73.     .insertInto('pet')
  74.     .values({ name: 'Catto', species: 'cat', owner_id: id })
  75.     .execute()

  77.   const person = await db
  78.     .selectFrom('person')
  79.     .innerJoin('pet', 'pet.owner_id', 'person.id')
  80.     .select(['first_name', 'pet.name as pet_name'])
  81.     .where('person.id', '=', id)
  82.     .executeTakeFirst()

  84.   if (person) {
  85.     person.pet_name
  86.   }
  87. }
  88. ```

  1. ```ts
  2. // You can extract the select, insert and update interfaces like this
  3. // if you want (you don't need to):
  4. type Person = Selectable<PersonTable>
  5. type InsertablePerson = Insertable<PersonTable>
  6. type UpdateablePerson = Updateable<PersonTable>
  7. ```

Generating types


If you want to generate the table types automatically from the database schema please
check out this awesome project.

Query examples


Select queries


You can find examples of select queries in the documentation of the
select method and
the where method
among other places.

Stream select query results


Currently only supported by postgres and mysql dialects.

  1. ```ts
  2. import { Pool } from 'pg'
  3. // or `import * as Cursor from 'pg-cursor'` depending on your tsconfig
  4. import Cursor from 'pg-cursor'
  5. import {
  6.     Kysely,
  7.     PostgresDialect,
  8. } from 'kysely'

  10. const db = new Kysely<Database>({
  11.     // PostgresDialect requires the Cursor dependency
  12.     dialect: new PostgresDialect({
  13.         pool: new Pool({
  14.             host: 'localhost',
  15.             database: 'kysely_test'
  16.         }),
  17.         cursor: Cursor
  18.     }),

  20.     // MysqlDialect doesn't require any special configuration
  21. })

  23. async function demo() {
  24.     for await (const male of db.selectFrom("person")
  25.         .selectAll()
  26.         .where("person.gender", "=", "male")
  27.         .stream()) {
  28.         console.log(`Hello mr. ${male.first_name}!`)

  30.         if (male.first_name === "John") {
  31.           // After this line the db connection is released and no more
  32.           // rows are streamed from the database to the client
  33.           break;
  34.         }
  35.     }
  36. }
  37. ```

Update queries


See the set method and the
updateTable method
documentation.

Insert queries


See the values method and the
insertInto method
documentation.

Delete queries


See the deleteFrom method
documentation.

Recipes


The recipes folder contains a bunch of small tutorials
or "recipes" for common use cases.

Conditional selects
Deduplicate joins
Extending kysely
Raw SQL
Schemas

Migrations


Migration files should look like this:

  1. ```ts
  2. import { Kysely } from 'kysely'

  4. export async function up(db: Kysely<any>): Promise<void> {
  5.   // Migration code
  6. }

  8. export async function down(db: Kysely<any>): Promise<void> {
  9.   // Migration code
  10. }
  11. ```

The up function is called when you update your database schema to the next version and down
when you go back to previous version. The only argument for the functions is an instance of
`Kysely`. It's important to use `Kysely` and not `Kysely`.

Migrations should never depend on the current code of your app because they need to work even when the app
changes. Migrations need to be "frozen in time".

The migrations can use the Kysely.schema
module to modify the schema. Migrations can also run normal queries to modify data.

Execution order of the migrations is the alpabetical order of their names. An excellent way to name your
migrations is to prefix them with an ISO 8601 date string. A date prefix works well in large teams
where multiple team members may add migrations at the same time in parallel commits without knowing
about the other migrations.

You don't need to store your migrations as separate files if you don't want to. You can easily
implement your own MigrationProvider
and give it to the Migrator class
when you instantiate one.

PostgreSQL migration example


  1. ```ts
  2. import { Kysely, sql } from 'kysely'

  4. export async function up(db: Kysely<any>): Promise<void> {
  5.   await db.schema
  6.     .createTable('person')
  7.     .addColumn('id', 'serial', (col) => col.primaryKey())
  8.     .addColumn('first_name', 'varchar', (col) => col.notNull())
  9.     .addColumn('last_name', 'varchar')
  10.     .addColumn('gender', 'varchar(50)', (col) => col.notNull())
  11.     .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`now()`).notNull())
  12.     .execute()

  14.   await db.schema
  15.     .createTable('pet')
  16.     .addColumn('id', 'serial', (col) => col.primaryKey())
  17.     .addColumn('name', 'varchar', (col) => col.notNull().unique())
  18.     .addColumn('owner_id', 'integer', (col) =>
  19.       col.references('person.id').onDelete('cascade').notNull()
  20.     )
  21.     .addColumn('species', 'varchar', (col) => col.notNull())
  22.     .execute()

  24.   await db.schema
  25.     .createIndex('pet_owner_id_index')
  26.     .on('pet')
  27.     .column('owner_id')
  28.     .execute()
  29. }

  31. export async function down(db: Kysely<any>): Promise<void> {
  32.   await db.schema.dropTable('pet').execute()
  33.   await db.schema.dropTable('person').execute()
  34. }
  35. ```

MySQL migration example


  1. ```ts
  2. import { Kysely } from 'kysely'

  4. export async function up(db: Kysely<any>): Promise<void> {
  5.   await db.schema
  6.     .createTable('person')
  7.     .addColumn('id', 'integer', (col) => col.autoIncrement().primaryKey())
  8.     .addColumn('first_name', 'varchar(255)', (col) => col.notNull())
  9.     .addColumn('last_name', 'varchar(255)')
  10.     .addColumn('gender', 'varchar(50)', (col) => col.notNull())
  11.     .execute()

  13.   await db.schema
  14.     .createTable('pet')
  15.     .addColumn('id', 'integer', (col) => col.autoIncrement().primaryKey())
  16.     .addColumn('name', 'varchar(255)', (col) => col.notNull().unique())
  17.     .addColumn('owner_id', 'integer', (col) => col.notNull())
  18.     .addColumn('species', 'varchar(255)', (col) => col.notNull())
  19.     .addForeignKeyConstraint(
  20.       'pet_owner_id_fk', ['owner_id'], 'person', ['id'],
  21.       (cb) => cb.onDelete('cascade')
  22.     )
  23.     .execute()

  25.   await db.schema
  26.     .createIndex('pet_owner_id_index')
  27.     .on('pet')
  28.     .column('owner_id')
  29.     .execute()
  30. }

  32. export async function down(db: Kysely<any>): Promise<void> {
  33.   await db.schema.dropTable('pet').execute()
  34.   await db.schema.dropTable('person').execute()
  35. }
  36. ```

You can then use

  1. ```ts
  2. const migrator = new Migrator(migratorConfig);
  3. await migrator.migrateToLatest(pathToMigrationsFolder)
  4. ```

to run all migrations that have not yet been run. See the
Migrator
class's documentation for more info.

Kysely doesn't have a CLI for running migrations and probably never will. This is because Kysely's
migrations are also written in typescript. To run the migrations, you need to first build the
typescript code into javascript. A CLI would cause confusion over which migrations are being
run, the typescript ones or the javascript ones. If we added support for both, the CLI would
need to depend on a typescript compiler, which most production environments don't (and shouldn't)
have. You will probably want to add a simple migration script to your projects like this:

  1. ```ts
  2. import * as path from 'path'
  3. import { Pool } from 'pg'
  4. import { promises as fs } from 'fs'
  5. import {
  6.   Kysely,
  7.   Migrator,
  8.   PostgresDialect,
  9.   FileMigrationProvider
  10. } from 'kysely'

  12. async function migrateToLatest() {
  13.   const db = new Kysely<Database>({
  14.     dialect: new PostgresDialect({
  15.       pool: new Pool({
  16.         host: 'localhost',
  17.         database: 'kysely_test',
  18.       })
  19.     }),
  20.   })

  22.   const migrator = new Migrator({
  23.     db,
  24.     provider: new FileMigrationProvider({
  25.       fs,
  26.       path,
  27.       migrationFolder: 'some/path/to/migrations',
  28.     })
  29.   })

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

  33.   results?.forEach((it) => {
  34.     if (it.status === 'Success') {
  35.       console.log(`migration "${it.migrationName}" was executed successfully`)
  36.     } else if (it.status === 'Error') {
  37.       console.error(`failed to execute migration "${it.migrationName}"`)
  38.     }
  39.   })

  41.   if (error) {
  42.     console.error('failed to migrate')
  43.     console.error(error)
  44.     process.exit(1)
  45.   }

  47.   await db.destroy()
  48. }

  50. migrateToLatest()
  51. ```

The migration methods use a lock on the database level and parallel calls are executed serially.
This means that you can safely call migrateToLatest and other migration methods from multiple
server instances simultaneously and the migrations are guaranteed to only be executed once. The
locks are also automatically released if the migration process crashes or the connection to the
database fails.

Deno


Kysely doesn't include drivers for deno, but you can still use Kysely as a query builder
or implement your own driver:

  1. ```ts
  2. // We use jsdeliver to get Kysely from npm.
  3. import {
  4.   DummyDriver,
  5.   Generated,
  6.   Kysely,
  7.   PostgresAdapter,
  8.   PostgresIntrospector,
  9.   PostgresQueryCompiler,
  10. } from 'https://cdn.jsdelivr.net/npm/kysely/dist/esm/index.js'

  12. interface Person {
  13.   id: Generated<number>
  14.   first_name: string
  15.   last_name: string | null
  16. }

  18. interface Database {
  19.   person: Person
  20. }

  22. const db = new Kysely<Database>({
  23.   dialect: {
  24.     createAdapter() {
  25.       return new PostgresAdapter()
  26.     },
  27.     createDriver() {
  28.       // You need a driver to be able to execute queries. In this example
  29.       // we use the dummy driver that never does anything.
  30.       return new DummyDriver()
  31.     },
  32.     createIntrospector(db: Kysely<unknown>) {
  33.       return new PostgresIntrospector(db)
  34.     },
  35.     createQueryCompiler() {
  36.       return new PostgresQueryCompiler()
  37.     },
  38.   },
  39. })

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

  44. console.log(sql.sql)
  45. ```

Browser


Kysely also runs in the browser:

  1. ```ts
  2. import {
  3.   Kysely,
  4.   Generated,
  5.   DummyDriver,
  6.   SqliteAdapter,
  7.   SqliteIntrospector,
  8.   SqliteQueryCompiler,
  9. } from 'kysely'

  11. interface Person {
  12.   id: Generated<number>
  13.   first_name: string
  14.   last_name: string | null
  15. }

  17. interface Database {
  18.   person: Person
  19. }

  21. const db = new Kysely<Database>({
  22.   dialect: {
  23.     createAdapter() {
  24.       return new SqliteAdapter()
  25.     },
  26.     createDriver() {
  27.       return new DummyDriver()
  28.     },
  29.     createIntrospector(db: Kysely<unknown>) {
  30.       return new SqliteIntrospector(db)
  31.     },
  32.     createQueryCompiler() {
  33.       return new SqliteQueryCompiler()
  34.     },
  35.   },
  36. })

  38. window.addEventListener('load', () => {
  39.   const sql = db.selectFrom('person').select('id').compile()

  41.   const result = document.createElement('span')
  42.   result.id = 'result'
  43.   result.innerHTML = sql.sql

  45.   document.body.appendChild(result)
  46. })
  47. ```

Why not just contribute to knex


Kysely is very similar to knex, but it also attempts to fix things that I personally find not-so-good
in knex. Bringing the type system and the changes to knex would mean very significant breaking changes
that aren't possible at this point of the project. Knex was also originally written for javascript and
the typescript typings were added afterwards. That always leads to compromises in the types. Designing
a library for typescript from the ground up produces much better and simpler types.

How to contribute to Kysely


See CONTRIBUTING.md.