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

# Getting started

## Prerequisites[​](#prerequisites "Direct link to Prerequisites")

* [TypeScript](https://www.typescriptlang.org/)

  * Minimum supported version [4.6](https://devblogs.microsoft.com/typescript/announcing-typescript-4-6/#indexed-access-inference-improvements).

  * For even more type-safety and accurate inference, use version [5.4](https://devblogs.microsoft.com/typescript/announcing-typescript-5-4/#notable-behavioral-changes) or later.

  * For improved compilation performance, use version [5.9](https://devblogs.microsoft.com/typescript/announcing-typescript-5-9/#cache-instantiations-on-mappers) or later.

  * You must enable `strict` mode in your `tsconfig.json` file's `compilerOptions`:

    tsconfig.json

    ```
    {
      // ...
      "compilerOptions": {
        // ...
        "strict": true
        // ...
      }
      // ...
    }
    ```

## Installation[​](#installation "Direct link to Installation")

Kysely can be installed using any of the following package managers:

* npm
* pnpm
* Yarn
* Deno
* Bun

[npm](https://npmjs.com) <!-- -->is the default package manager for [Node.js](https://nodejs.org), and to where Kysely is published.<br />Your project is using <!-- -->npm<!-- --> if it has a<!-- --> `package-lock.json` file in its root folder.

**Run the following command in your terminal:**

terminal

```
npm install kysely
```

[pnpm](https://pnpm.io) is a fast, disk space efficient package manager for [Node.js](https://nodejs.org).<br />Your project is using <!-- -->pnpm<!-- --> if it has a<!-- --> `pnpm-lock.yaml` file in its root folder.

**Run the following command in your terminal:**

terminal

```
pnpm install kysely
```

[Yarn](https://yarnpkg.com) <!-- -->is a fast, reliable and secure dependency manager for [Node.js](https://nodejs.org).<br />Your project is using <!-- -->Yarn<!-- --> if it has a<!-- --> `yarn.lock` file in its root folder.

**Run the following command in your terminal:**

terminal

```
yarn add kysely
```

[Deno](https://deno.com/runtime) <!-- -->is a secure runtime for [JavaScript](https://developer.mozilla.org/en-US/docs/Web/JavaScript) and<!-- --> [TypeScript](https://www.typescriptlang.org).

****Your root&#x20;**`deno.json`**'s "imports" field should include the following dependencies:****

deno.json

```
{
  "imports": {
    "kysely": "npm:kysely@^0.28.11"
  }
}
```

[Bun](https://bun.sh) is a new [JavaScript](https://developer.mozilla.org/en-US/docs/Web/JavaScript) runtime built for speed, with a native bundler, transpiler, test runner, and <!-- -->npm<!-- -->-compatible package manager baked-in.

**Run the following command in your terminal:**

terminal

```
bun install kysely
```

## Types[​](#types "Direct link to Types")

For Kysely's type-safety and autocompletion to work, it needs to know your database structure. This requires a TypeScript `Database` interface, that contains table names as keys and table schema interfaces as values.

**Let's define our first database interface:**

src/types.ts

```
import {
  ColumnType,
  Generated,
  Insertable,
  JSONColumnType,
  Selectable,
  Updateable,
} from 'kysely'

export interface Database {
  person: PersonTable
  pet: PetTable
}

// This interface describes the `person` table to Kysely. Table
// interfaces should only be used in the `Database` type above
// and never as a result type of a query!. See the `Person`,
// `NewPerson` and `PersonUpdate` types below.
export interface PersonTable {
  // Columns that are generated by the database should be marked
  // using the `Generated` type. This way they are automatically
  // made optional in inserts and updates.
  id: Generated<number>

  first_name: string
  gender: 'man' | 'woman' | 'other'

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

  // You can specify a different type for each operation (select, insert and
  // update) using the `ColumnType<SelectType, InsertType, UpdateType>`
  // wrapper. Here we define a column `created_at` that is selected as
  // a `Date`, can optionally be provided as a `string` in inserts and
  // can never be updated:
  created_at: ColumnType<Date, string | undefined, never>

  // You can specify JSON columns using the `JSONColumnType` wrapper.
  // It is a shorthand for `ColumnType<T, string, string>`, where T
  // is the type of the JSON object/array retrieved from the database,
  // and the insert and update types are always `string` since you're
  // always stringifying insert/update values.
  metadata: JSONColumnType<{
    login_at: string
    ip: string | null
    agent: string | null
    plan: 'free' | 'premium'
  }>
}

// You should not use the table schema interfaces directly. Instead, you should
// use the `Selectable`, `Insertable` and `Updateable` wrappers. These wrappers
// make sure that the correct types are used in each operation.
//
// Most of the time you should trust the type inference and not use explicit
// types at all. These types can be useful when typing function arguments.
export type Person = Selectable<PersonTable>
export type NewPerson = Insertable<PersonTable>
export type PersonUpdate = Updateable<PersonTable>

export interface PetTable {
  id: Generated<number>
  name: string
  owner_id: number
  species: 'dog' | 'cat'
}

export type Pet = Selectable<PetTable>
export type NewPet = Insertable<PetTable>
export type PetUpdate = Updateable<PetTable>
```

Codegen

For production apps, it is recommended to automatically generate your `Database` interface by introspecting your production database or Prisma schemas. Generated types might differ in naming convention, internal order, etc. Find out more at ["Generating types"](https://kysely.dev/docs/generating-types).

Runtime types

Kysely only deals with types in the TypeScript level. The runtime JavaScript types are decided by the underlying third-party driver such as `pg` or `mysql2` and it's up to you to select the correct TypeScript types in the database interface. Kysely never touches the runtime output types in any way. Find out more at ["Data types"](https://kysely.dev/docs/recipes/data-types).

## Dialects[​](#dialects "Direct link to Dialects")

For Kysely's query compilation and execution to work, it needs to understand your database's SQL specification and how to communicate with it. This requires a `Dialect` implementation.<br /><br />There are 4 built-in dialects for PostgreSQL, MySQL, Microsoft SQL Server (MSSQL), and SQLite. Additionally, the community has implemented several dialects to choose from. Find out more at<!-- --> ["Dialects"](https://kysely.dev/docs/dialects.md).

### Driver installation

A `Dialect` implementation usually requires a database driver library as a peer dependency. Let's install it using the same package manager command from before:

* PostgreSQL
* MySQL
* Microsoft SQL Server (MSSQL)
* SQLite

Kysely's built-in <!-- -->PostgreSQL<!-- --> dialect uses the "<!-- -->pg<!-- -->" driver library under the hood. Please refer to its<!-- --> [official documentation](https://node-postgres.com/) for configuration options.

**Run the following command in your terminal:**

terminal

```
npm install pg 
```

Kysely's built-in <!-- -->MySQL<!-- --> dialect uses the "<!-- -->mysql2<!-- -->" driver library under the hood. Please refer to its<!-- --> [official documentation](https://github.com/sidorares/node-mysql2/tree/master/documentation) for configuration options.

**Run the following command in your terminal:**

terminal

```
npm install mysql2 
```

Kysely's built-in <!-- -->Microsoft SQL Server (MSSQL)<!-- --> dialect uses the "<!-- -->tedious<!-- -->" driver library under the hood. Please refer to its<!-- --> [official documentation](https://tediousjs.github.io/tedious/index.html) for configuration options.

Additionally, Kysely's <!-- -->Microsoft SQL Server (MSSQL)<!-- --> dialect uses the "<!-- -->tarn<!-- -->" resource pool package for connection pooling. Please refer to its<!-- --> [official documentation](https://github.com/vincit/tarn.js) for configuration options.

**Run the following command in your terminal:**

terminal

```
npm install tedious tarn
```

Kysely's built-in <!-- -->SQLite<!-- --> dialect uses the "<!-- -->better-sqlite3<!-- -->" driver library under the hood. Please refer to its<!-- --> [official documentation](https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md) for configuration options.

**Run the following command in your terminal:**

terminal

```
npm install better-sqlite3 
```

[I use a different package manager (not <!-- -->npm<!-- -->)](#installation)

Driverless

Kysely can also work in compile-only mode that doesn't require a database driver. Find out more at

<!-- -->

["Splitting query building and execution"](https://kysely.dev/docs/recipes/splitting-query-building-and-execution.md).

## Instantiation[​](#instantiation "Direct link to Instantiation")

**Let's create a Kysely instance****&#x20;using the built-in&#x20;**`PostgresDialect`**&#x20;dialect****:**

src/database.ts

```
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,
})
```

[I use a different package manager (not <!-- -->npm<!-- -->)](#installation)

[I use a different dialect (not <!-- -->PostgreSQL<!-- -->)](#dialects)

Singleton

In most cases, you should only create a single Kysely instance per database. Most dialects use a connection pool internally, or no connections at all, so there's no need to create a new instance for each request.

keeping secrets

Use a secrets manager, environment variables (DO NOT commit \`.env\` files to your repository), or a similar solution, to avoid hardcoding database credentials in your code.

kill it with fire

When needed, you can dispose of the Kysely instance, release resources and close all connections by invoking the `db.destroy()`

<!-- -->

function.

## Querying[​](#querying "Direct link to Querying")

**Let's implement the person repository:**

src/PersonRepository.ts

```
import { db } from './database'
import { PersonUpdate, Person, NewPerson } from './types'

export async function findPersonById(id: number) {
  return await db.selectFrom('person')
    .where('id', '=', id)
    .selectAll()
    .executeTakeFirst()
}

export async function findPeople(criteria: Partial<Person>) {
  let query = db.selectFrom('person')

  if (criteria.id) {
    query = query.where('id', '=', criteria.id) // Kysely is immutable, you must re-assign!
  }

  if (criteria.first_name) {
    query = query.where('first_name', '=', criteria.first_name)
  }

  if (criteria.last_name !== undefined) {
    query = query.where(
      'last_name',
      criteria.last_name === null ? 'is' : '=',
      criteria.last_name
    )
  }

  if (criteria.gender) {
    query = query.where('gender', '=', criteria.gender)
  }

  if (criteria.created_at) {
    query = query.where('created_at', '=', criteria.created_at)
  }

  return await query.selectAll().execute()
}

export async function updatePerson(id: number, updateWith: PersonUpdate) {
  await db.updateTable('person').set(updateWith).where('id', '=', id).execute()
}

export async function createPerson(person: NewPerson) {
  return await db.insertInto('person')
    .values(person)
    .returningAll()
    .executeTakeFirstOrThrow()
}

export async function deletePerson(id: number) {
  return await db.deleteFrom('person').where('id', '=', id)
    .returningAll()
    .executeTakeFirst()
}
```

[I use a different dialect (not <!-- -->PostgreSQL<!-- -->)](#dialects)

But wait, there's more!

This is a simplified example with basic CRUD operations. Kysely supports many more SQL features including: joins, subqueries, complex boolean logic, set operations, CTEs, functions (aggregate and window functions included), raw SQL, transactions, DDL queries, etc.

<br />

Find out more at [Examples](https://kysely.dev/docs/category/examples).

## Summary[​](#summary "Direct link to Summary")

We've seen how to install and instantiate Kysely, its dialects and underlying drivers. We've also seen how to use Kysely to query a database.<br /><br />**Let's put it all to the test:**

src/PersonRepository.spec.ts

```
import { sql } from 'kysely'
import { db } from './database'
import * as PersonRepository from './PersonRepository'

describe('PersonRepository', () => {
  before(async () => {
    await db.schema.createTable('person')
      .addColumn('id', 'serial', (cb) => cb.primaryKey())
      .addColumn('first_name', 'varchar', (cb) => cb.notNull())
      .addColumn('last_name', 'varchar')
      .addColumn('gender', 'varchar(50)', (cb) => cb.notNull())
      .addColumn('created_at', 'timestamp', (cb) =>
        cb.notNull().defaultTo(sql`now()`)
      )
      .execute()
  })
    
  afterEach(async () => {
    await sql`truncate table ${sql.table('person')}`.execute(db)
  })
    
  after(async () => {
    await db.schema.dropTable('person').execute()
  })
    
  it('should find a person with a given id', async () => {
    await PersonRepository.findPersonById(123)
  })
    
  it('should find all people named Arnold', async () => {
    await PersonRepository.findPeople({ first_name: 'Arnold' })
  })
    
  it('should update gender of a person with a given id', async () => {
    await PersonRepository.updatePerson(123, { gender: 'woman' })
  })
    
  it('should create a person', async () => {
    await PersonRepository.createPerson({
      first_name: 'Jennifer',
      last_name: 'Aniston',
      gender: 'woman',
    })
  })
    
  it('should delete a person with a given id', async () => {
    await PersonRepository.deletePerson(123)
  })
})
```

[I use a different dialect (not <!-- -->PostgreSQL<!-- -->)](#dialects)

Migrations

As you can see, Kysely supports DDL queries. It also supports classic "up/down" migrations. Find out more at

<!-- -->

[Migrations](https://kysely.dev/docs/migrations.md).