Kysely Has a Free API That Is the Type-Safest SQL Query Builder for TypeScript

#typescript #database #javascript #sql

Kysely is a type-safe SQL query builder for TypeScript. No ORM magic — you write SQL, but with full autocompletion and compile-time type checking.

Define Your Database Types

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

interface Database {
  products: {
    id: Generated<number>;
    title: string;
    price: number;
    url: string;
    category: string | null;
    scraped_at: Generated<Date>;
  };
  reviews: {
    id: Generated<number>;
    product_id: number;
    rating: number;
    comment: string;
  };
}

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

Queries: SQL with Autocomplete

// SELECT — fully typed result
const products = await db
  .selectFrom("products")
  .select(["id", "title", "price"])
  .where("price", "<", 50)
  .where("category", "=", "electronics")
  .orderBy("scraped_at", "desc")
  .limit(20)
  .execute();
// Type: { id: number; title: string; price: number }[]

// JOIN
const withReviews = await db
  .selectFrom("products")
  .innerJoin("reviews", "reviews.product_id", "products.id")
  .select(["products.title", "reviews.rating", "reviews.comment"])
  .where("reviews.rating", ">=", 4)
  .execute();

Insert, Update, Delete

// INSERT — returns inserted row
const newProduct = await db
  .insertInto("products")
  .values({
    title: "Widget Pro",
    price: 49.99,
    url: "https://example.com/widget",
    category: "electronics",
  })
  .returningAll()
  .executeTakeFirstOrThrow();

// UPDATE
await db
  .updateTable("products")
  .set({ price: 39.99 })
  .where("id", "=", newProduct.id)
  .execute();

// DELETE
await db
  .deleteFrom("products")
  .where("scraped_at", "<", thirtyDaysAgo)
  .execute();

Subqueries and CTEs

// CTE (Common Table Expression)
const result = await db
  .with("avg_prices", (qb) =>
    qb.selectFrom("products")
      .select(["category", db.fn.avg<number>("price").as("avg_price")])
      .groupBy("category")
  )
  .selectFrom("products")
  .innerJoin("avg_prices", "avg_prices.category", "products.category")
  .select(["products.title", "products.price", "avg_prices.avg_price"])
  .where("products.price", ">", db.ref("avg_prices.avg_price"))
  .execute();

Transactions

await db.transaction().execute(async (trx) => {
  const product = await trx.insertInto("products").values(productData).returningAll().executeTakeFirstOrThrow();
  await trx.insertInto("reviews").values({ product_id: product.id, rating: 5, comment: "Great!" }).execute();
});

Migrations

import { Kysely, sql } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable("products")
    .addColumn("id", "serial", (col) => col.primaryKey())
    .addColumn("title", "varchar", (col) => col.notNull())
    .addColumn("price", "decimal(10,2)", (col) => col.notNull())
    .addColumn("url", "varchar", (col) => col.notNull().unique())
    .addColumn("scraped_at", "timestamp", (col) => col.defaultTo(sql`now()`))
    .execute();

  await db.schema.createIndex("idx_products_price").on("products").column("price").execute();
}