SQL Resource Builder

A small, opinionated library for building type-safe SQL queries, relationship-aware resources, and REST-like data access paths on top of PostgreSQL.

This is not an ORM that hides SQL from you.
This is a tool that helps you write SQL deliberately, while still getting:

• composable query building
• relationship graphs
• automatic aggregation
• resource-style access (/users/1/roles)
• pagination, sorting, and filtering hooks

If SQL is the truth, this library is a polite assistant, not a liar.

Features

• Declarative SQL query builder
• Explicit table aliasing (no magic joins)
• Composable WHERE conditions (and, or, equal, etc.)
• JSON aggregation for relationships
• Relationship graph definition
• Resource-style path access (/users/:id/:relation)
• Pagination, sorting, and filtering support
• Works directly with PostgreSQL JSON output

Installation

npm install @jjmyers/pg-crud

Core Concepts

SQLBuilder

SQLBuilder is a fluent SQL construction tool.
It builds SQL, it does not execute it.

const { select, alias, op, value, compile } = SQLBuilder();

const users = alias("users");
const userId = value(1);

select(users.field("id"), users.field("first_name"))
  .from(users)
  .where(op.equal(users, "id", userId));

const { statement, values } = compile();

Aliases Are Mandatory

Every table must be aliased.
This avoids ambiguous SQL and hidden joins.

Joins and Aggregation

Aggregation is explicit and opt-in.

roles.as("roles", { aggregate: true, distinct: true });

Relationship Builder

Define how tables connect:

relationshipBuilder({
  users: {
    "users.[users_id]addresses": { alias: "address", resource: "eager" },
    "users[companies_id].companies": { alias: "company" },
  },
});

Resource API

Expose SQL as resource paths:

resources.get("/users/1/roles");

Supported paths:

• /users
• /users/:id
• /users/:id/:relation

Design Philosophy

• SQL is not the enemy
• Explicit > implicit
• Control beats convenience
• No magic queries

License

MIT