Tinqer

A type-safe query builder for TypeScript. Queries are expressed as inline arrow functions, parsed into an expression tree, and compiled into SQL for PostgreSQL or SQLite. The API is similar to DotNet's LINQ-based frameworks.

Installation

Install the core library and adapter for your database:

# Core library
npm install @tinqerjs/tinqer

# PostgreSQL adapter (pg-promise)
npm install @tinqerjs/pg-promise-adapter

# SQLite adapter (better-sqlite3)
npm install @tinqerjs/better-sqlite3-adapter

Quick Start

PostgreSQL Example

import { createSchema } from "@tinqerjs/tinqer";
import { executeSelect } from "@tinqerjs/pg-promise-adapter";
import pgPromise from "pg-promise";

interface Schema {
  users: {
    id: number;
    name: string;
    email: string;
    age: number;
  };
}

const pgp = pgPromise();
const db = pgp("postgresql://user:pass@localhost:5432/mydb");
const schema = createSchema<Schema>();

const results = await executeSelect(
  db,
  schema,
  (q, params: { minAge: number }) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .orderBy((u) => u.name)
      .select((u) => ({ id: u.id, name: u.name })),
  { minAge: 18 },
);
// results: [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }]

The same query works with SQLite - just change the adapter and database connection:

import Database from "better-sqlite3";
import { createSchema } from "@tinqerjs/tinqer";
import { executeSelect } from "@tinqerjs/better-sqlite3-adapter";

// Same schema definition
interface Schema {
  users: {
    id: number;
    name: string;
    email: string;
    age: number;
  };
}

const db = new Database("./data.db");
const schema = createSchema<Schema>();

// Identical query logic
const results = executeSelect(
  db,
  schema,
  (q, params: { minAge: number }) =>
    q
      .from("users")
      .where((u) => u.age >= params.minAge)
      .orderBy((u) => u.name)
      .select((u) => ({ id: u.id, name: u.name })),
  { minAge: 18 },
);
// results: [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }]

SQL Generation Without Execution

execute* functions execute queries and return results. toSql function generates SQL and parameters without executing - useful for debugging, logging, or custom execution:

import {
  createSchema,
  defineSelect,
  defineInsert,
  defineUpdate,
  defineDelete,
} from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; name: string; age: number };
}

const schema = createSchema<Schema>();

// SELECT - returns { sql, params }
const select = toSql(
  defineSelect(schema, (q, params: { minAge: number }) =>
    q.from("users").where((u) => u.age >= params.minAge),
  ),
  { minAge: 18 },
);
// select.sql: SELECT * FROM "users" WHERE "age" >= $(minAge)
// select.params: { minAge: 18 }

// INSERT
const insert = toSql(
  defineInsert(schema, (q, params: { name: string; age: number }) =>
    q.insertInto("users").values({ name: params.name, age: params.age }),
  ),
  { name: "Alice", age: 30 },
);
// insert.sql: INSERT INTO "users" ("name", "age") VALUES ($(name), $(age))

// UPSERT (PostgreSQL + SQLite)
const upsert = toSql(
  defineInsert(schema, (q, params: { id: number; name: string; age: number }) =>
    q
      .insertInto("users")
      .values({ id: params.id, name: params.name, age: params.age })
      .onConflict((u) => u.id)
      .doUpdateSet((_existing, excluded) => ({ name: excluded.name, age: excluded.age })),
  ),
  { id: 1, name: "Alice", age: 30 },
);
// upsert.sql: INSERT INTO "users" (...) VALUES (...) ON CONFLICT ("id") DO UPDATE SET ...

// UPDATE
const update = toSql(
  defineUpdate(schema, (q, params: { newAge: number; userId: number }) =>
    q
      .update("users")
      .set({ age: params.newAge })
      .where((u) => u.id === params.userId),
  ),
  { newAge: 31, userId: 1 },
);
// update.sql: UPDATE "users" SET "age" = $(newAge) WHERE "id" = $(userId)

// DELETE
const del = toSql(
  defineDelete(schema, (q, params: { minAge: number }) =>
    q.deleteFrom("users").where((u) => u.age < params.minAge),
  ),
  { minAge: 18 },
);
// del.sql: DELETE FROM "users" WHERE "age" < $(minAge)

Core Features

Type-Safe Query Building

const schema = createSchema<Schema>();

// Full TypeScript type inference
const query = (q) =>
  q
    .from("users")
    .where((u) => u.age >= 18 && u.email.includes("@company.com"))
    .orderBy((u) => u.name)
    .select((u) => ({ id: u.id, name: u.name, email: u.email }));

// The query builder returns a Queryable whose result type is inferred as
// { id: number; name: string; email: string }

Query Composition

Query plans are immutable and composable - you can chain operations onto plan handles to create reusable base queries and branch into specialized variations.

Chaining Operations on Plans

import { defineSelect } from "@tinqerjs/tinqer";
import { toSql } from "@tinqerjs/pg-promise-adapter";

const schema = createSchema<Schema>();

// Start with base query
const plan = defineSelect(schema, (q) => q.from("users"))
  .where((u) => u.age > 18)
  .where((u) => u.isActive)
  .orderBy((u) => u.name)
  .select((u) => ({ id: u.id, name: u.name }));

const { sql, params } = toSql(plan, {});

Reusable Base Queries

Plans are immutable - each operation returns a new plan without modifying the original. This enables creating base queries and branching:

type DeptParams = { dept: number };

// Reusable base query
const usersInDept = defineSelect(schema, (q, p: DeptParams) =>
  q.from("users").where((u) => u.departmentId === p.dept),
);

// Branch 1: Active users only
const activeUsers = usersInDept
  .where((u) => u.isActive === true)
  .where<{ minAge: number }>((u, p) => u.age >= p.minAge);

// Branch 2: Inactive users only
const inactiveUsers = usersInDept
  .where((u) => u.isActive === false)
  .where<{ maxAge: number }>((u, p) => u.age <= p.maxAge);

// Execute branches with different parameters
toSql(activeUsers, { dept: 1, minAge: 25 });
toSql(inactiveUsers, { dept: 1, maxAge: 65 });

Parameter Accumulation

Parameters from the builder function and chained operations are merged:

type BuilderParams = { baseAge: number };
type ChainParams = { maxAge: number };

const plan = defineSelect(schema, (q, p: BuilderParams) =>
  q.from("users").where((u) => u.age > p.baseAge),
).where<ChainParams>((u, p) => u.age < p.maxAge);

// Must provide both parameter types
toSql(plan, { baseAge: 18, maxAge: 65 });

Composition works with all operations: defineSelect, defineInsert, defineUpdate, defineDelete.

Row Filters

Row filters let you attach row-level predicates to a schema so that SELECT/UPDATE/DELETE automatically include them (useful for authorization scoping). This is enforced at plan finalization time and fails closed if you forget to bind context.

import { createSchema } from "@tinqerjs/tinqer";
import { executeSelect } from "@tinqerjs/pg-promise-adapter";

interface Schema {
  users: { id: number; orgId: number; email: string };
  posts: { id: number; orgId: number; title: string };
}

type ScopeContext = { orgId: number };

const dangerousUnrestrictedSchema = createSchema<Schema>();

const rowFilteredSchema = dangerousUnrestrictedSchema.withRowFilters<ScopeContext>({
  users: (u, ctx) => u.orgId === ctx.orgId,
  posts: (p, ctx) => p.orgId === ctx.orgId,
});

const schema = rowFilteredSchema.withContext({ orgId: 7 });

// Any SELECT/UPDATE/DELETE using `schema` includes the row filter automatically.
await executeSelect(db, schema, (q) => q.from("posts"), {});

Notes:

• Filters must be provided for every table (set a table’s filter to null to opt out).
• Row filters are not automatically applied to INSERT statements.
• Unrestricted access is done by using the base schema (dangerousUnrestrictedSchema) directly.

Joins

Tinqer mirrors LINQ semantics. Inner joins have a dedicated operator; left outer and cross joins follow the familiar groupJoin/selectMany patterns from C#.

Inner Join

interface Schema {
  users: { id: number; name: string; deptId: number };
  departments: { id: number; name: string };
}

const schema = createSchema<Schema>();

const query = (q) =>
  q
    .from("users")
    .join(
      q.from("departments"),
      (user) => user.deptId,
      (department) => department.id,
      (user, department) => ({
        userName: user.name,
        departmentName: department.name,
      }),
    )
    .orderBy((row) => row.userName);

Left Outer Join

const query = (q) =>
  q
    .from("users")
    .groupJoin(
      q.from("departments"),
      (user) => user.deptId,
      (department) => department.id,
      (user, deptGroup) => ({ user, deptGroup }),
    )
    .selectMany(
      (group) => group.deptGroup.defaultIfEmpty(),
      (group, department) => ({
        user: group.user,
        department,
      }),
    )
    .select((row) => ({
      userId: row.user.id,
      departmentName: row.department ? row.department.name : null,
    }));

Cross Join

const query = (q) =>
  q
    .from("departments")
    .selectMany(
      () => q.from("users"),
      (department, user) => ({ department, user }),
    )
    .select((row) => ({
      departmentId: row.department.id,
      userId: row.user.id,
    }));

Right and full outer joins still require manual SQL, just as in LINQ-to-Objects.

Grouping and Aggregation

const query = (q) =>
  q
    .from("orders")
    .groupBy((o) => o.product_id)
    .select((g) => ({
      productId: g.key,
      totalQuantity: g.sum((o) => o.quantity),
      avgPrice: g.avg((o) => o.price),
      orderCount: g.count(),
    }))
    .orderByDescending((row) => row.totalQuantity);

Window Functions