Kysely query building example

kyselyQueryBuilder.ts

import {
    DummyDriver,
    Kysely,
    PostgresAdapter,
    PostgresIntrospector,
    PostgresQueryCompiler,
    sql,
} from "kysely";

// Intentionally using `any` to allow for dynamic schemas
// deno-lint-ignore no-explicit-any
const db = new Kysely<any>({
    // Based on: https://kysely.dev/docs/recipes/splitting-query-building-and-execution#compile-a-query
    dialect: {
        createAdapter: () => new PostgresAdapter(),
        createDriver: () => new DummyDriver(),
        createIntrospector: (db) => new PostgresIntrospector(db),
        createQueryCompiler: () => new PostgresQueryCompiler(),
    },
});

// The examples below output parameterized compiled queries.
// According to the Kysely docs:
// "This output alone can be used with any database driver that understands the sql dialect used (PostgreSQL in this example)."
// Of course, it'd also be simple enough to build our own execution layer on top of this.
// See: https://kysely.dev/docs/recipes/splitting-query-building-and-execution#compile-a-query

// Example 1: Simple SELECT query
const selectQuery = db
    .selectFrom("users")
    .select(["id", "name", "email", "created_at"])
    .where("active", "=", true)
    .where("age", ">", 18)
    .orderBy("created_at", "desc")
    .limit(10);

console.log("🔍 SELECT Query:");
const selectCompiled = selectQuery.compile();
console.log("SQL:", selectCompiled.sql);
console.log("Parameters:", selectCompiled.parameters);
console.log("");

// Example 2: JOIN query
const joinQuery = db
    .selectFrom("users as u")
    .leftJoin("posts as p", "u.id", "p.user_id")
    .select(["u.name", "u.email", "p.title as post_title", "p.content"])
    .where("u.active", "=", true)
    .where("p.published", "=", true)
    .orderBy("p.created_at", "desc");

console.log("🔗 JOIN Query:");
const joinCompiled = joinQuery.compile();
console.log("SQL:", joinCompiled.sql);
console.log("Parameters:", joinCompiled.parameters);
console.log("");

// Example 3: INSERT query
const insertQuery = db
    .insertInto("users")
    .values({
        name: "John Doe",
        email: "[email protected]",
        age: 25,
        created_at: sql`now()`,
    })
    .returning(["id", "name", "email"]);

console.log("➕ INSERT Query:");
const insertCompiled = insertQuery.compile();
console.log("SQL:", insertCompiled.sql);
console.log("Parameters:", insertCompiled.parameters);
console.log("");

// Example 4: UPDATE query
const updateQuery = db
    .updateTable("users")
    .set({
        last_login: sql`now()`,
        login_count: sql`login_count + 1`,
    })
    .where("id", "=", 123)
    .where("active", "=", true);

console.log("✏️ UPDATE Query:");
const updateCompiled = updateQuery.compile();
console.log("SQL:", updateCompiled.sql);
console.log("Parameters:", updateCompiled.parameters);
console.log("");

// Example 5: Complex query with subquery
const subQuery = db
    .selectFrom("posts")
    .select("user_id")
    .where("published", "=", true)
    .groupBy("user_id")
    .having(db.fn.count("id"), ">", 5);

const complexQuery = db
    .selectFrom("users")
    .selectAll()
    .where("id", "in", subQuery)
    .where("created_at", ">", "2023-01-01");

console.log("🧩 Complex Query with Subquery:");
const complexCompiled = complexQuery.compile();
console.log("SQL:", complexCompiled.sql);
console.log("Parameters:", complexCompiled.parameters);
console.log("");

// Example 6: Aggregate query
const aggregateQuery = db
    .selectFrom("posts")
    .select([
        "category",
        db.fn.count("id").as("total_posts"),
        db.fn.avg("views").as("avg_views"),
        db.fn.max("created_at").as("latest_post"),
    ])
    .where("published", "=", true)
    .groupBy("category")
    .having(db.fn.count("id"), ">", 10)
    .orderBy(db.fn.count("id"), "desc");

console.log("📊 Aggregate Query:");
const aggregateCompiled = aggregateQuery.compile();
console.log("SQL:", aggregateCompiled.sql);
console.log("Parameters:", aggregateCompiled.parameters);
console.log("");

// Example 7: DELETE query
const deleteQuery = db
    .deleteFrom("users")
    .where("active", "=", false)
    .where("last_login", "<", "2022-01-01");

console.log("🗑️ DELETE Query:");
const deleteCompiled = deleteQuery.compile();
console.log("SQL:", deleteCompiled.sql);
console.log("Parameters:", deleteCompiled.parameters);
console.log("");

console.log(
    "✅ All queries generated successfully using Kysely query builder!",
);

Output:

🔍 SELECT Query:
SQL: select "id", "name", "email", "created_at" from "users" where "active" = $1 and "age" > $2 order by "created_at" desc limit $3
Parameters: [ true, 18, 10 ]

🔗 JOIN Query:
SQL: select "u"."name", "u"."email", "p"."title" as "post_title", "p"."content" from "users" as "u" left join "posts" as "p" on "u"."id" = "p"."user_id" where "u"."active" = $1 and "p"."published" = $2 order by "p"."created_at" desc
Parameters: [ true, true ]

➕ INSERT Query:
SQL: insert into "users" ("name", "email", "age", "created_at") values ($1, $2, $3, now()) returning "id", "name", "email"
Parameters: [ "John Doe", "[email protected]", 25 ]

✏️ UPDATE Query:
SQL: update "users" set "last_login" = now(), "login_count" = login_count + 1 where "id" = $1 and "active" = $2
Parameters: [ 123, true ]

🧩 Complex Query with Subquery:
SQL: select * from "users" where "id" in (select "user_id" from "posts" where "published" = $1 group by "user_id" having count("id") > $2) and "created_at" > $3
Parameters: [ true, 5, "2023-01-01" ]

📊 Aggregate Query:
SQL: select "category", count("id") as "total_posts", avg("views") as "avg_views", max("created_at") as "latest_post" from "posts" where "published" = $1 group by "category" having count("id") > $2 order by count("id") desc
Parameters: [ true, 10 ]

🗑️ DELETE Query:
SQL: delete from "users" where "active" = $1 and "last_login" < $2
Parameters: [ false, "2022-01-01" ]

✅ All queries generated successfully using Kysely query builder!