Skip to main content
Kysely’s type system is built around defining a database interface that represents your database schema. This interface provides compile-time type safety for all your queries.

​
Basic Database Interface

A database interface is a TypeScript type where each key represents a table name and each value is an interface describing that table’s columns: 
interface Database {
  person: {
    id: number
    first_name: string
    last_name: string | null
    created_at: Date
  }
  pet: {
    id: number
    name: string
    owner_id: number
    species: string
  }
}

​
ColumnType<SelectType, InsertType, UpdateType>

The ColumnType type allows you to specify different types for select, insert, and update operations on a single column. 
type ColumnType<
  SelectType,
  InsertType = SelectType,
  UpdateType = SelectType,
> = {
  readonly __select__: SelectType
  readonly __insert__: InsertType
  readonly __update__: UpdateType
}

​
Examples

Read-only column (like database-generated timestamps): 
interface PersonTable {
  id: Generated<number>
  name: string
  modified_at: ColumnType<Date, string, never>
}

// SELECT: returns Date
// INSERT: accepts string
// UPDATE: cannot be updated (never)
Unupdateable column: 
type UnupdateableDate = ColumnType<Date, string, never>

​
Generated<T>

A shortcut for database-generated columns that are optional in inserts and updates: 
type Generated<S> = ColumnType<S, S | undefined, S>

​
Usage

interface UserTable {
  user_id: Generated<string>
  first_name: string | null
  last_name: string | null
  email: string | null
  created_at: Generated<Date>
}
  • SELECT: Returns string (for user_id) or Date (for created_at)
  • INSERT: Optional (string | undefined or Date | undefined)
  • UPDATE: Same as select type

​
GeneratedAlways<T>

For columns that are always database-generated and cannot be inserted or updated: 
type GeneratedAlways<S> = ColumnType<S, never, never>
Useful for PostgreSQL GENERATED ALWAYS AS IDENTITY columns: 
interface PersonTable {
  id: GeneratedAlways<number>
  name: string
}

​
JSONColumnType<SelectType, InsertType, UpdateType>

For JSON columns that are inserted/updated as stringified JSON: 
type JSONColumnType<
  SelectType extends object | null,
  InsertType = string,
  UpdateType = string,
> = ColumnType<SelectType, InsertType, UpdateType>

​
Example

interface PersonTable {
  id: Generated<number>
  metadata: JSONColumnType<{
    tags: string[]
    preferences: Record<string, any>
  }>
}

// Insert with stringified JSON
await db.insertInto('person')
  .values({
    metadata: JSON.stringify({ tags: ['vip'], preferences: {} })
  })
  .execute()

// Select returns parsed object
const person = await db.selectFrom('person')
  .selectAll()
  .executeTakeFirst()

// person.metadata is typed as { tags: string[], preferences: Record<string, any> }

​
Selectable<R>, Insertable<R>, Updateable<R>

These utility types extract the appropriate type for each operation: 
interface UserTable {
  user_id: Generated<string>
  first_name: string | null
  last_name: string | null
  email: string | null
  created_at: Generated<Date>
}

type UserRow = Selectable<UserTable>
// {
//   user_id: string
//   first_name: string | null
//   last_name: string | null
//   email: string | null
//   created_at: Date
// }

type InsertableUserRow = Insertable<UserTable>
// {
//   user_id?: string
//   first_name: string | null
//   last_name: string | null
//   email: string | null
//   created_at?: Date
// }

type UpdateableUserRow = Updateable<UserTable>
// {
//   user_id?: string
//   first_name?: string | null
//   last_name?: string | null
//   email?: string | null
//   created_at?: Date
// }

​
Complete Example

Here’s a complete database interface showing various column types: 
import { 
  Generated, 
  GeneratedAlways,
  ColumnType, 
  JSONColumnType,
  Selectable,
  Insertable,
  Updateable
} from 'kysely'

interface Database {
  user: UserTable
  post: PostTable
}

interface UserTable {
  id: GeneratedAlways<number>
  username: string
  email: string
  password_hash: ColumnType<string, string, never> // Cannot be updated
  created_at: Generated<Date>
  updated_at: ColumnType<Date, Date | undefined, Date>
}

interface PostTable {
  id: Generated<number>
  user_id: number
  title: string
  content: string
  metadata: JSONColumnType<{
    tags: string[]
    views: number
  }>
  published_at: Date | null
  created_at: Generated<Date>
}

// Extract types for use in your application
export type User = Selectable<UserTable>
export type NewUser = Insertable<UserTable>
export type UserUpdate = Updateable<UserTable>

export type Post = Selectable<PostTable>
export type NewPost = Insertable<PostTable>
export type PostUpdate = Updateable<PostTable>

​
Best Practices

Use Generated<T> for auto-increment IDs and timestamp columns that have database defaults.
Use GeneratedAlways<T> for identity columns or computed columns that the database always generates.
Define nullable columns as string | null, not string | undefined. SQL databases distinguish between NULL and missing values.
Export Selectable, Insertable, and Updateable type variants from your table definitions for use throughout your application.

Build docs developers (and LLMs) love

Get started for freeTalk to us