Kysely for TypeScript: Complete Guide to type-safe SQL query builder

\n\n

1. What is it and why is it needed

\n\n

Kysely (pronounced "kee-se-lee") is a modern, type-safe SQL query builder for TypeScript and Node.js that allows you to write SQL queries with full type support at all stages: from query building to result retrieval. Unlike traditional ORMs (Object-Relational Mapping) such as TypeORM or Prisma, Kysely does not abstract SQL but provides a convenient API for writing it while maintaining full control over the generated queries. This means you can use the full power of SQL (JOIN, subqueries, window functions, CTE) without having to fight with ORM limitations.

\n\n

The main problem that Kysely solves is type safety in SQL queries. In regular JavaScript/TypeScript code, SQL queries are often written as strings, which leads to runtime errors: typos in column names, data type mismatches, incorrect JOINs. Kysely solves this problem at the compilation level: if you try to access a non-existent column or pass an incorrect data type, TypeScript will throw an error before the application even runs. This significantly reduces debugging time and increases code reliability.

\n\n

Kysely is ideal for projects that require maximum performance and control over SQL, while still wanting all the benefits of static typing. It supports PostgreSQL, MySQL, SQLite, and MSSQL, has built-in protection against SQL injection (through parameterized queries), support for migrations, and type generation from an existing database schema. The library is actively developed, has good documentation and a community.

\n\n

2. Installation

\n\n

Installing Kysely is done via npm (or yarn/pnpm). You will need to install the library itself and the driver for your database.

\n\n

# Main package\nnpm install kysely\n\n# For PostgreSQL\nnpm install pg\n\n# For MySQL\nnpm install mysql2\n\n# For SQLite\nnpm install better-sqlite3\n\n# For MSSQL\nnpm install tedious\n

\n\n

If you are using TypeScript, make sure you have the types for the driver installed (they usually come bundled). For the best development experience, it is also recommended to install ts-node for running TypeScript files:

\n\n

npm install -D ts-node typescript @types/node\n

\n\n

To work with migrations, additionally install:

\n\n

npm install -D @kysely/cli\n

\n\n

3. Quick Start — Minimal Working Example

\n\n

Let's create a simple application that connects to a SQLite database, creates a users table, and executes several queries.

\n\n

First, define the types for our database:

\n\n

// types.ts\nexport interface User {\n  id: number;\n  name: string;\n  email: string;\n  age: number | null;\n  created_at: Date;\n}\n\nexport interface Database {\n  users: User;\n  // Other tables can be added here\n}\n

\n\n

Now let's create the main application:

\n\n

// index.ts\nimport { Kysely, SqliteDialect } from 'kysely';\nimport Database from 'better-sqlite3';\nimport type { Database as MyDatabase } from './types';\n\n// Create a Kysely instance\nconst db = new Kysely<MyDatabase>({\n  dialect: new SqliteDialect({\n    database: new Database('my_database.db'),\n  }),\n});\n\nasync function main() {\n  // Create the table (if it does not exist)\n  await db.schema\n    .createTable('users')\n    .ifNotExists()\n    .addColumn('id', 'integer', (col) => col.primaryKey().autoIncrement())\n    .addColumn('name', 'varchar(255)', (col) => col.notNull())\n    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())\n    .addColumn('age', 'integer')\n    .addColumn('created_at