Knex query building example

knexQueryBuilder.ts

import knex from "knex";

// Create a knex instance purely for query building (no database connection)
const queryBuilder = knex({ client: "pg" }); // We specify a client but don't connect

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

console.log("🔍 SELECT Query:");
console.log(selectQuery.toString());
console.log("");

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

console.log("🔗 JOIN Query:");
console.log(joinQuery.toString());
console.log("");

// Example 3: INSERT query
const insertQuery = queryBuilder
    .insert({
        name: "John Doe",
        email: "[email protected]",
        age: 25,
        created_at: queryBuilder.fn.now(),
    })
    .into("users")
    .returning(["id", "name", "email"]);

console.log("➕ INSERT Query:");
console.log(insertQuery.toString());
console.log("");

// Example 4: UPDATE query
const updateQuery = queryBuilder
    .table("users")
    .update({
        last_login: queryBuilder.fn.now(),
        login_count: queryBuilder.raw("login_count + 1"),
    })
    .where("id", 123)
    .andWhere("active", true);

console.log("✏️ UPDATE Query:");
console.log(updateQuery.toString());
console.log("");

// Example 5: Complex query with subquery
const subQuery = queryBuilder
    .select("user_id")
    .from("posts")
    .where("published", true)
    .groupBy("user_id")
    .having(queryBuilder.raw("count(*) > ?", [5]));

const complexQuery = queryBuilder
    .select("*")
    .from("users")
    .whereIn("id", subQuery)
    .andWhere("created_at", ">", "2023-01-01");

console.log("🧩 Complex Query with Subquery:");
console.log(complexQuery.toString());
console.log("");

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

console.log("📊 Aggregate Query:");
console.log(aggregateQuery.toString());
console.log("");

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

console.log("🗑️ DELETE Query:");
console.log(deleteQuery.toString());
console.log("");

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

Output:

🔍 SELECT Query:
select "id", "name", "email", "created_at" from "users" where "active" = true and "age" > 18 order by "created_at" desc limit 10

🔗 JOIN Query:
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" = true and "p"."published" = true order by "p"."created_at" desc

➕ INSERT Query:
insert into "users" ("age", "created_at", "email", "name") values (25, CURRENT_TIMESTAMP, '[email protected]', 'John Doe') returning "id", "name", "email"

✏️ UPDATE Query:
update "users" set "last_login" = CURRENT_TIMESTAMP, "login_count" = login_count + 1 where "id" = 123 and "active" = true

🧩 Complex Query with Subquery:
select * from "users" where "id" in (select "user_id" from "posts" where "published" = true group by "user_id" having count(*) > 5) and "created_at" > '2023-01-01'

📊 Aggregate Query:
select "category", count(*) as "total_posts", avg("views") as "avg_views", max("created_at") as "latest_post" from "posts" where "published" = true group by "category" having "total_posts" > 10 order by "total_posts" desc

🗑️ DELETE Query:
delete from "users" where "active" = false and "last_login" < '2022-01-01'

✅ All queries generated successfully using Knex query builder!