DEV Community

Alex Spinov
Alex Spinov

Posted on

Kysely Has a Free Query Builder: Type-Safe SQL Without an ORM — Write Real Queries With Full TypeScript Inference

#typescript #database #sql #backend

ORMs hide SQL behind methods. When your query gets complex — subqueries, CTEs, window functions — you fight the ORM instead of writing the query. So you drop to raw SQL and lose type safety.

What if you wrote actual SQL syntax but got full TypeScript autocompletion and type checking?

That's Kysely. Not an ORM — a type-safe SQL query builder.

The Difference 

// Prisma (ORM) — abstracts SQL
const users = await prisma.user.findMany({
  where: { age: { gte: 18 } },
  include: { posts: true },
});

// Kysely (query builder) — IS SQL, but typed
const users = await db
  .selectFrom("users")
  .innerJoin("posts", "posts.author_id", "users.id")
  .where("users.age", ">=", 18)
  .select(["users.id", "users.name", "posts.title"])
  .execute();
Enter fullscreen mode Exit fullscreen mode

You're writing SQL. TypeScript knows every column, every table, every join relationship.

Setup 

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

// Define your database schema
interface Database {
  users: {
    id: Generated<number>;
    name: string;
    email: string;
    age: number;
    created_at: Generated<Date>;
  };
  posts: {
    id: Generated<number>;
    title: string;
    content: string;
    author_id: number;
    published: boolean;
    created_at: Generated<Date>;
  };
}

const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({ connectionString: process.env.DATABASE_URL }),
  }),
});
Enter fullscreen mode Exit fullscreen mode

Query Examples

SELECT with conditions 

const activeUsers = await db
  .selectFrom("users")
  .where("age", ">=", 18)
  .where("email", "like", "%@gmail.com")
  .select(["id", "name", "email"])
  .orderBy("name", "asc")
  .limit(20)
  .execute();
// TypeScript knows: { id: number; name: string; email: string }[]
Enter fullscreen mode Exit fullscreen mode

INSERT 

const newUser = await db
  .insertInto("users")
  .values({
    name: "Aleksej",
    email: "dev@example.com",
    age: 28,
    // TypeScript error if you miss required fields or add unknown ones
  })
  .returningAll()
  .executeTakeFirstOrThrow();
Enter fullscreen mode Exit fullscreen mode

Complex JOIN 

const userPosts = await db
  .selectFrom("users as u")
  .innerJoin("posts as p", "p.author_id", "u.id")
  .where("p.published", "=", true)
  .select([
    "u.name",
    "p.title",
    db.fn.count<number>("p.id").as("post_count"),
  ])
  .groupBy(["u.name", "p.title"])
  .having(db.fn.count("p.id"), ">", 5)
  .execute();
Enter fullscreen mode Exit fullscreen mode

Subqueries 

const prolificAuthors = await db
  .selectFrom("users")
  .where("id", "in",
    db.selectFrom("posts")
      .select("author_id")
      .groupBy("author_id")
      .having(db.fn.count("id"), ">", 10)
  )
  .selectAll()
  .execute();
Enter fullscreen mode Exit fullscreen mode

Transactions 

const result = await db.transaction().execute(async (trx) => {
  const user = await trx
    .insertInto("users")
    .values({ name: "New User", email: "new@example.com", age: 25 })
    .returningAll()
    .executeTakeFirstOrThrow();

  await trx
    .insertInto("posts")
    .values({ title: "First Post", content: "Hello!", author_id: user.id, published: false })
    .execute();

  return user;
});
Enter fullscreen mode Exit fullscreen mode

Kysely vs Prisma vs Drizzle vs Raw SQL

Feature Kysely Prisma Drizzle Raw SQL
Approach Query builder ORM ORM + builder Strings
Type safety Full Full Full None
SQL control Complete Limited High Complete
Learning curve Know SQL Learn Prisma Learn Drizzle Know SQL
Migrations Via kysely-ctl Built-in Built-in Manual
Complex queries Excellent Struggles Good Manual

When to Choose Kysely

Choose Kysely when:

  • You know SQL and want type safety without abstraction
  • You need complex queries (CTEs, window functions, recursive)
  • You want predictable performance (you see the exact SQL)
  • You're migrating from raw SQL and want gradual type safety

Skip Kysely when:

  • You want auto-generated migrations from models (use Prisma)
  • You prefer ORM-style relations over JOIN syntax
  • Your team doesn't know SQL well

The Bottom Line

Kysely is for developers who think in SQL. It doesn't hide the database — it types it. Every column autocompletes, every typo is caught, every query is real SQL.

Start here: kysely.dev

Need custom data extraction, scraping, or automation? I build tools that collect and process data at scale — 78 actors on Apify Store and 265+ open-source repos. Email me: Spinov001@gmail.com | My Apify Actors

Top comments (0)