Getting started
Installation
Kysely can be installed using any of the following package managers:
- npm
- pnpm
- Yarn
- Deno
- Bun
npm is the default package manager for Node.js, and to where Kysely is published.
Your project is using npm if it has a package-lock.json
file in its root folder.
Run the following command in your terminal:
npm install kysely
pnpm is a fast, disk space efficient package manager for Node.js.
Your project is using pnpm if it has a pnpm-lock.yaml
file in its root folder.
Run the following command in your terminal:
pnpm install kysely
Yarn is a fast, reliable and secure dependency manager for Node.js.
Your project is using Yarn if it has a yarn.lock
file in its root folder.
Run the following command in your terminal:
yarn add kysely
Deno is a secure runtime for JavaScript and TypeScript.
Your root deno.json
's "imports" field should include the following dependencies:
{
"imports": {
"kysely": "npm:kysely@^0.25.0"
}
}
Bun is a new 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:
bun install kysely
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:
import { ColumnType, Generated, Insertable, Selectable, Updateable } from 'kysely'
export interface Database {
person: PersonTable
pet: PetTable
}
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 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.
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>
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".
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.
There are 3 built-in Node.js dialects for PostgreSQL, MySQL and SQLite. Additionally, the community has implemented several dialects to choose from. Find out more at "Dialects".
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
- SQLite
Kysely's built-in PostgreSQL dialect uses the "pg" driver library under the hood. Please refer to its official documentation for configuration options.
Run the following command in your terminal:
npm install pg
Kysely's built-in MySQL dialect uses the "mysql2" driver library under the hood. Please refer to its official documentation for configuration options.
Run the following command in your terminal:
npm install mysql2
Kysely's built-in SQLite dialect uses the "better-sqlite3" driver library under the hood. Please refer to its official documentation for configuration options.
Run the following command in your terminal:
npm install better-sqlite3
I use a different package manager (not npm)
Instantiation
Let's create a Kysely instance using the built-in PostgresDialect
dialect:
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)
I use a different database (not PostgreSQL)
db.destroy()
function.Querying
Let's implement the person repository:
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 database (not PostgreSQL)
Find out more at Examples.
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.
Let's put it all to the test:
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 database (not PostgreSQL)