Skip to content

README.md

Latest commit

History

History
206 lines (159 loc) · 5.03 KB

README.md

File metadata and controls

206 lines (159 loc) · 5.03 KB
Raw

sqlkit

sqlkit is a zero-dependency TypeScript query builder and repository layer for PostgreSQL. Install the pg driver alongside it when you execute queries.

🔧 Installation

npm install sqlkit pg

API Documentation

🚀 Usage Examples

Define Your Domain Model

interfaceUser{id: string;name: string;email: string;age?: number;}

1. Query Builder (Build Mode)

import{SelectQueryBuilder}from"sqlkit";constbuilder=newSelectQueryBuilder<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" > $1console.log(values);// [18]

2. Query Execution (Commit Mode)

import{PostgresAdapter,SelectQueryBuilder}from"sqlkit";import{Pool}from"pg";constpool=newPool({/* your config */});constexecutor=newPostgresAdapter(pool);constbuilder=newSelectQueryBuilder<User>("users",executor);const{rows: users}=awaitbuilder.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 🔥

Repository is configured with a single RepositoryConfig object:

FieldRequiredDescription
tableNameyesTable or view for reads (find, findOne, paginate, count)
mutableTableNamenoBase table for writes (insert, update, delete). Defaults to tableName
executoryesSqlExecutor instance (e.g. PostgresAdapter)
loggingnoLog SQL, values, and row counts to the console
import{Repository,PostgresAdapter,gt,like,and,asc}from"sqlkit";import{Pool}from"pg";constpool=newPool({/* your config */});constexecutor=newPostgresAdapter(pool);constuserRepo=newRepository<User>({tableName: "users",
executor,});// Read from a view, write to the base tableconstitemRepo=newRepository<Item>({tableName: "inventory__v_items",mutableTableName: "inventory_items",
executor,logging: true,});// Find manyconstusers=awaituserRepo.find({where: and(gt("age",25),like("name","%Doe%")),});// Paginate (offset is derived from page and limit inside paginate)constresult=awaituserRepo.paginate({page: 1,limit: 10,where: gt("age",18),columns: ["age","email"],orderBy: [asc("age")],});console.log(result.nodes);// array of usersconsole.log(result.meta);/*{ totalCount: 100, currentPage: 1, totalPages: 10, hasNextPage: true}*/// Find one (returns T | null)constuser=awaituserRepo.findOne(like("email","%@example.com"));// Or: findOne({ where: like(...), columns: ["id", "email"], orderBy: [asc("email")] })// Count (optional filter — omit `where` to count all rows)consttotal=awaituserRepo.count();constadults=awaituserRepo.count(gt("age",30));// Insert — one object or an array (returns QueryResult)constone=awaituserRepo.insert({name: "Rayhan",email: "ray@example.com",});constmany=awaituserRepo.insert([{name: "A",email: "a@example.com"},{name: "B",email: "b@example.com"},]);// one.rows[0], many.rows — inserted rows// Updateconstupdated=awaituserRepo.update({data: {name: "Ray"},where: like("email","%ray%"),});// Deleteconstdeleted=awaituserRepo.delete({where: like("name","Ray%"),});

Upgrading from 1.x

The constructor no longer accepts positional (tableName, executor, options?). Use a config object:

// 1.xnewRepository("users",executor,{logging: true});// 2.xnewRepository({tableName: "users", executor,logging: true});

4. Case-sensitive table names

All generated SQL quotes table and column identifiers ("users", "inventory__v_items"), so mixed-case names in PostgreSQL resolve correctly. The quoteTableName() helper is exported if you build raw SQL alongside sqlkit.

🔍 Supported Operators

Comparison

  • eq("field", value) – Equal (=)
  • neq("field", value) – Not Equal (!=)
  • gt("field", value) – Greater Than (>)
  • gte("field", value) – Greater Than or Equal (>=)
  • lt("field", value) – Less Than (<)
  • lte("field", value) – Less Than or Equal (<=)
  • between("field", min, max) – BETWEEN
  • like("field", pattern) – LIKE
  • ilike("field", pattern) – ILIKE (case-insensitive)
  • regexp("field", pattern) – REGEXP
  • iregexp("field", pattern) – Case-insensitive REGEXP
  • inArray("field", [a, b, c]) – IN
  • notInArray("field", [a, b]) – NOT IN
  • isNull("field") – IS NULL
  • isNotNull("field") – IS NOT NULL

Logical

  • and(...conditions)
  • or(...conditions)
  • not(condition)
  • xor(condA, condB) [⚠️ -- Not tested properly]

Sorting

  • asc("field")
  • desc("field")
  • nullsFirst("field")
  • nullsLast("field")