Kysely Has a Free Type-Safe SQL Query Builder With Zero Magic

#kysely #typescript #sql #database

ORMs hide SQL. Query builders expose it. Kysely is a type-safe TypeScript SQL query builder that gives you full SQL power with compile-time guarantees.

No Magic, Just Types

import { Kysely, PostgresDialect } from "kysely";
import { Pool } from "pg";

interface Database {
  users: {
    id: Generated<number>;
    name: string;
    email: string;
    created_at: Generated<Date>;
  };
  posts: {
    id: Generated<number>;
    title: string;
    author_id: number;
    published: boolean;
  };
}

const db = new Kysely<Database>({
  dialect: new PostgresDialect({ pool: new Pool({ connectionString: "..." }) }),
});

Type-Safe Queries

// Select — every column is typed
const users = await db
  .selectFrom("users")
  .select(["id", "name", "email"])
  .where("email", "like", "%@gmail.com")
  .orderBy("created_at", "desc")
  .limit(10)
  .execute();
// Type: { id: number; name: string; email: string }[]

// Typo in column name? Compile error!
db.selectFrom("users").select("naem"); // ❌ TypeScript error

// Wrong table? Compile error!
db.selectFrom("userz"); // ❌ TypeScript error

Joins

const postsWithAuthors = await db
  .selectFrom("posts")
  .innerJoin("users", "users.id", "posts.author_id")
  .select(["posts.title", "users.name as authorName"])
  .where("posts.published", "=", true)
  .execute();
// Type: { title: string; authorName: string }[]

Subqueries

const prolificAuthors = await db
  .selectFrom("users")
  .select(["users.name", (eb) =>
    eb.selectFrom("posts")
      .whereRef("posts.author_id", "=", "users.id")
      .select(eb.fn.count("posts.id").as("post_count"))
      .as("post_count")
  ])
  .orderBy("post_count", "desc")
  .execute();

Insert, Update, Delete

// Insert
const newUser = await db
  .insertInto("users")
  .values({ name: "Alice", email: "alice@test.com" })
  .returningAll()
  .executeTakeFirstOrThrow();

// Update
await db
  .updateTable("users")
  .set({ name: "Alice Updated" })
  .where("id", "=", newUser.id)
  .execute();

// Delete
await db
  .deleteFrom("posts")
  .where("published", "=", false)
  .execute();

Migrations

import { Kysely, sql } from "kysely";

export async function up(db: Kysely<any>) {
  await db.schema
    .createTable("users")
    .addColumn("id", "serial", (col) => col.primaryKey())
    .addColumn("name", "text", (col) => col.notNull())
    .addColumn("email", "text", (col) => col.notNull().unique())
    .addColumn("created_at", "timestamp", (col) => col.defaultTo(sql`now()`))
    .execute();
}

export async function down(db: Kysely<any>) {
  await db.schema.dropTable("users").execute();
}

Works With Every Database

PostgreSQL: pg, postgres.js
MySQL: mysql2
SQLite: better-sqlite3
MSSQL: tedious
PlanetScale: serverless driver
Cloudflare D1: @miniflare/d1

Why Kysely Over Drizzle/Prisma

	Prisma	Drizzle	Kysely
Approach	ORM	SQL-like DSL	SQL query builder
SQL knowledge	Not needed	Helpful	Required
Complex queries	Limited	Good	Excellent
Raw SQL	Awkward	Good	Native
Bundle size	~2MB	33KB	29KB

Kysely is for developers who KNOW SQL and want TypeScript to check it.

Need database expertise for your project? I build data pipelines and scraping solutions. Email spinov001@gmail.com or explore my Apify tools.

DEV Community