Kysely: The Type-Safe SQL Query Builder

Introduction:

In the world of TypeScript, developers often face challenges when dealing with SQL queries. Writing raw SQL statements can be error-prone and lack type safety, leading to potential bugs and security vulnerabilities. However, there’s good news for TypeScript developers - introducing Kysely, a type-safe SQL query builder that simplifies the process of generating SQL queries while ensuring type safety and readability. In this article, we will explore the features of Kysely and provide examples of how it can be used effectively.

What is Kysely?

Kysely is a powerful SQL query builder designed specifically for TypeScript. It allows developers to write SQL queries in a fluent and type-safe manner, eliminating the need for manual string concatenation or external SQL files. By leveraging TypeScript’s static type checking capabilities, Kysely ensures that queries are validated at compile time, reducing the risk of runtime errors.

Key Features of Kysely:

1. Type Safety: Kysely leverages the static typing capabilities of TypeScript, enabling developers to write queries that are verified for correctness at compile time. This prevents common SQL-related bugs, such as misspelled column names or incorrect table references, from creeping into the codebase.
2. Fluent API: Kysely provides a fluent and intuitive API for constructing SQL queries. Developers can chain methods to build complex queries step by step, improving code readability and maintainability. The fluent API also enables easy composition of query parts, such as conditions, joins, and aggregations.
3. IDE Autocomplete and IntelliSense: Kysely integrates seamlessly with modern TypeScript IDEs, such as Visual Studio Code, providing autocomplete suggestions and IntelliSense support for query construction. This feature saves developers time and reduces the chances of making syntax errors.
4. Database Agnostic: Kysely is designed to work with multiple database systems, including PostgreSQL, MySQL, SQLite, and more. It abstracts away the differences in SQL syntax and database-specific features, allowing developers to write database-agnostic queries effortlessly.

Usage of Kysely:

Kysely has built-in support for PostgreSQL, MySQL and SQLite. To install it run

# PostgreSQL
npm install kysely pg

# MySQL
npm install kysely mysql2

# SQLite
npm install kysely better-sqlite3

Postgress Example

import { Pool } from "pg";
import {
  Kysely,
  PostgresDialect,
  Generated,
  ColumnType,
  Selectable,
  Insertable,
  Updateable,
} from "kysely";

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: "male" | "female" | "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 `modified_at` that is selected as
  // a `Date`, can optionally be provided as a `string` in inserts and
  // can never be updated:
  modified_at: ColumnType<Date, string | undefined, never>;
}

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

interface MovieTable {
  id: Generated<string>;
  stars: number;
}

// Keys of this interface are table names.
interface Database {
  person: PersonTable;
  pet: PetTable;
  movie: MovieTable;
}

// You'd create one of these when you start your app.
const db = new Kysely<Database>({
  // Use MysqlDialect for MySQL and SqliteDialect for SQLite.
  dialect: new PostgresDialect({
    pool: new Pool({
      host: "localhost",
      user: "kavin",
      password: "super^secret~password"
      database: "your_db",
    }),
  }),
});

async function demo() {
  const { id } = await db
    .insertInto("person")
    .values({ first_name: "Jennifer", gender: "female" })
    .returning("id")
    .executeTakeFirstOrThrow();

  await db
    .insertInto("pet")
    .values({ name: "Catto", species: "cat", owner_id: id })
    .execute();

  const person = await db
    .selectFrom("person")
    .innerJoin("pet", "pet.owner_id", "person.id")
    .select(["first_name", "pet.name as pet_name"])
    .where("person.id", "=", id)
    .executeTakeFirst();

  if (person) {
    person.pet_name;
  }
}

MySQL Example

import { Kysely, MysqlDialect } from "kysely";
import { createPool } from "mysql2";

interface PersonTable {
  id: Generated<number>;
  first_name: string;
  gender: "male" | "female" | "other";
  last_name: string | null;
  modified_at: ColumnType<Date, string | undefined, never>;
}

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

interface MovieTable {
  id: Generated<string>;
  stars: number;
}

interface Database {
  person: PersonTable;
  pet: PetTable;
  movie: MovieTable;
}

const db = new Kysely<Database>({
  dialect: new MysqlDialect({
    pool: createPool({
      host: "localhost",
      database: "your_db",
      user: "kavin",
      password: "super~secret~password",
    }),
  }),
});

async function demo() {
  const { id } = await db
    .insertInto("person")
    .values({ first_name: "Jennifer", gender: "female" })
    .returning("id")
    .executeTakeFirstOrThrow();

  await db
    .insertInto("pet")
    .values({ name: "Catto", species: "cat", owner_id: id })
    .execute();

  const person = await db
    .selectFrom("person")
    .innerJoin("pet", "pet.owner_id", "person.id")
    .select(["first_name", "pet.name as pet_name"])
    .where("person.id", "=", id)
    .executeTakeFirst();

  if (person) {
    person.pet_name;
  }
}

Conclusion:

Kysely offers TypeScript developers a robust and type-safe approach to building SQL queries. By leveraging its fluent API, type checking capabilities, and IDE integration, developers can write readable and reliable SQL queries without the need for manual string concatenation or external SQL files. With its database-agnostic nature, Kysely ensures portability across different database systems. So, if you’re working on a TypeScript project that requires interacting with SQL databases, give Kysely a try and experience the power of type-safe SQL query building. Happy coding!