sqlkit

🔧 Installation

npm install sqlkit pg

🚀 Usage Examples

interface User {
  id: string;
  name: string;
  email: string;
  age?: number;
}

1. Query Builder (Build Mode)

import { SelectQueryBuilder } from "sqlkit";

const builder = new SelectQueryBuilder<User>("users");

const { sql, values } = builder
  .select(["id", "name"])
  .where({ key: "age", operator: ">", value: 18 })
  // OR .where(eq("age", 18))
  .build();

console.log(sql);
// SELECT "users"."id","users"."name" FROM "users" WHERE "users"."age" > $1
console.log(values); // [18]

2. Query Execution (Commit Mode)

import { PostgresAdapter, SelectQueryBuilder } from "sqlkit";
import { Pool } from "pg";

const pool = new Pool({
  /* your config */
});
const executor = new PostgresAdapter(pool);

const builder = new SelectQueryBuilder<User>("users", executor);

const { rows: users } = await builder
  .select(["id", "name"])
  .where({ key: "age", operator: ">", value: 18 })
  // OR .where(eq("age", 18))
  .commit();

console.log(users);
// => [{ id: "1", name: "John Doe" }, ...]

3. Repository API 🔥

import { Repository, PostgresAdapter, gt, like, and, asc } from "sqlkit";
import { Pool } from "pg";

const pool = new Pool({
  /* your config */
});
const executor = new PostgresAdapter(pool);

const userRepo = new Repository<User>({
  tableName: "users",
  executor,
});

// Read from a view, write to the base table
const itemRepo = new Repository<Item>({
  tableName: "inventory__v_items",
  mutableTableName: "inventory_items",
  executor,
  logging: true,
});

// Find many
const users = await userRepo.find({
  where: and(gt("age", 25), like("name", "%Doe%")),
});

// Paginate (offset is derived from page and limit inside paginate)
const result = await userRepo.paginate({
  page: 1,
  limit: 10,
  where: gt("age", 18),
  columns: ["age", "email"],
  orderBy: [asc("age")],
});

console.log(result.nodes); // array of users
console.log(result.meta);
/*
{
  totalCount: 100,
  currentPage: 1,
  totalPages: 10,
  hasNextPage: true
}
*/

// Find one (returns T | null)
const user = await userRepo.findOne(like("email", "%@example.com"));
// Or: findOne({ where: like(...), columns: ["id", "email"], orderBy: [asc("email")] })

// Count (optional filter — omit `where` to count all rows)
const total = await userRepo.count();
const adults = await userRepo.count(gt("age", 30));

// Insert — one object or an array (returns QueryResult)
const one = await userRepo.insert({
  name: "Rayhan",
  email: "[email protected]",
});
const many = await userRepo.insert([
  { name: "A", email: "[email protected]" },
  { name: "B", email: "[email protected]" },
]);
// one.rows[0], many.rows — inserted rows

// Update
const updated = await userRepo.update({
  data: { name: "Ray" },
  where: like("email", "%ray%"),
});

// Delete
const deleted = await userRepo.delete({
  where: like("name", "Ray%"),
});

Upgrading from 1.x

// 1.x
new Repository("users", executor, { logging: true });

// 2.x
new Repository({ tableName: "users", executor, logging: true });

4. Case-sensitive table names

🔍 Supported Operators