🐘 @tilewise/source-postgres

Any resource over one Postgres table: every operator in SQL, counted pages, and versioned writes, from the resource's definition alone.

Give it a driver and it makes a @tilewise/source source for any definition: each field a column, every operator its kind allows as SQL, each sort in its kind's order, a search across the text kinds, groups counted by Postgres, pages by any skip, and every query's rows counted.

✨ Highlights

• 🔌 Any driver: it takes (text, values) => rows, so node-postgres, postgres.js, PGlite and Neon all serve.
• 🛡️ Nothing spliced: every value is a $n parameter, and every name is quoted.
• 🗺️ Conventions first: a field's column is its key in snake_case, typed by its kind; fields says otherwise only where it must.
• 🏷️ Options in order: an option sorts by its place, and its attributes (status.kind) filter, sort and group.
• 🔁 Versioned writes: with roles.versionField, Postgres sets the version on every write, and a stale one answers 409 conflict.
• 🧱 Its own table: tableOf writes the CREATE TABLE it reads, for a test's database or a first migration.

🚀 Quick start

import pg from 'pg';
import { router, serve } from '@tilewise/source';
import { postgres } from '@tilewise/source-postgres';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const query = async (text: string, values: unknown[]) => {
  return (await pool.query(text, values)).rows;
};

const source = postgres({
  query,
  fields: { owner: { type: 'bigint' } }, // a ref to numbered rows
});

const route = router([serve(tasks, source)]);

The driver is one function. Each one's own:

| Driver | query | | ----------------------- | ---------------------------------------------------------------------- | | 🐘 node-postgres (pg) | async (text, values) => (await pool.query(text, values)).rows | | 🐘 postgres.js | (text, values) => sql.unsafe(text, values) | | 🪶 PGlite | async (text, values) => (await db.query(text, values)).rows | | ⚡ Neon serverless | (text, values) => neon(process.env.DATABASE_URL).query(text, values) |

🔎 What a query becomes

The query a client sends:

await served.query({
  filter: {
    all: [
      { field: 'status', op: 'in', value: ['todo', 'doing'] },
      { field: 'tags', op: 'in', value: ['web'] },
      { field: 'points', op: 'gte', value: 2 },
    ],
  },
  search: 'login',
  sort: [{ field: 'dueDate', dir: 'asc' }],
  page: { skip: 0, take: 20 },
});

…and the SQL the adapter sends for it, the values apart:

SELECT "id" AS "id", "title" AS "title", "status" AS "status", "tags" AS "tags",
       "points" AS "points", to_char("due_date", 'YYYY-MM-DD') AS "dueDate", …
FROM "tasks"
WHERE (coalesce("status" = ANY($1::text[]), false)
   AND coalesce("tags" && $2::text[], false)
   AND coalesce("points" >= $3::double precision, false))
  AND (coalesce(strpos(lower("title"), lower($4)) > 0, false)
   OR EXISTS (SELECT 1 FROM unnest("tags") AS tilewise_each
              WHERE strpos(lower(tilewise_each), lower($5)) > 0))
ORDER BY "due_date" ASC NULLS LAST, "id" ASC
LIMIT $6 OFFSET $7
-- values: [["todo","doing"], ["web"], 2, "login", "login", 20, 0]

A count(*) over the same WHERE answers the page's total.

[!NOTE] Each condition is wrapped in coalesce(…, false), so it answers true or false, never NULL: not turns it over as JavaScript would. Rows without a value sort last both ways, and the id breaks ties, so every order is total.

🗺️ Kinds and their columns

| Kind | Column | Notes | | --------------------------------------- | --------------------------------------- | ------------------------------------- | | text markdown url email phone | text | sorted by its lower case | | richtext | text | the doc's JSON, kept as written | | number | bigint when whole, double precision | | | boolean | boolean | | | date · time | date · time | read as YYYY-MM-DD · HH24:MI | | datetime | timestamptz | read as ISO 8601 in UTC | | option | text | sorted by its place | | ref | text | bigint for numbered rows (fields) | | json · file | jsonb | a list is one jsonb array |

Any other list is an array of its kind's type: text[], bigint[].

🧱 Its table

tableOf(definition, options) writes the CREATE TABLE the source reads:

await db.exec(tableOf(tasks, { fields: { owner: { type: 'bigint' } } }));

CREATE TABLE IF NOT EXISTS "tasks" (
  "id" bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "title" text,
  "status" text,
  "tags" text[],
  "points" double precision,
  "due_date" date,
  "owner" bigint,
  "updated_at" timestamptz
)

⚙️ Options

| Option | What | | ----------- | ------------------------------------------------------------------------ | | query | the driver: runs one statement, numbered $1, $2…, and answers rows | | table | the table, a schema's too ('app.tasks'); the resource's name otherwise | | fields | by field key: its column, type, and what a write may do with it | | kinds | the columns of the app's own kinds, beside tilewise's (baseColumns) | | operators | the SQL of the app's own operators, beside tilewise's (baseSql) |

A field's own options say where it differs from the defaults:

postgres({
  query,
  table: 'app.tasks',
  fields: {
    title: { column: 'name', required: true }, // its column, and a write must set it
    owner: { type: 'bigint' }, // a ref to numbered rows
    createdAt: { create: false, update: false }, // the database fills it
  },
});

🔁 Versions

With roles.versionField (a datetime, or a whole number), Postgres sets the version on every write. A write given a stale one changes nothing:

const task = await served.create({ values: { title: 'Ship it' } });

// ✅ at the version it was read at
await served.update(task.id, {
  values: { status: 'done' },
  version: task.updatedAt,
});

// ❌ 409 conflict: that version is stale now
await served.update(task.id, {
  values: { status: 'todo' },
  version: task.updatedAt,
});

🧩 The app's own kinds and operators

An app's own kind brings its column, and its own operator its SQL, beside tilewise's. Register both in the registry you serve with:

import { baseKinds, baseOperators, createRegistry } from '@tilewise/resource';
import { cast, postgres, sql } from '@tilewise/source-postgres';

const registry = createRegistry({
  kinds: baseKinds.register({ color }),
  operators: baseOperators.register({ between }),
});

const source = postgres({
  query,
  kinds: { color: { type: () => 'text' } },
  operators: {
    between: (on, value) => {
      const [low, high] = value as [number, number];
      const from = cast(low, on.type);
      const to = cast(high, on.type);
      return sql`coalesce(${on.column} BETWEEN ${from} AND ${to}, false)`;
    },
  },
});

serve(swatches, source, { registry });

🚫 What it leaves out

[!WARNING]

• Sorting by a ref, which would order by the title of the row it points at, through a join.
• A cursor: one made from an offset shifts as rows are added before it.
• A templated field is read as none; filling it is the server's job.

🧪 Tests

Its tests run on PGlite, a real Postgres in the process: the table made by tableOf, rows written through the resource, and every operator and sort held to the contract with @tilewise/source/testing.

🗂️ Layout

src/
├── index.ts              the package's exports
├── postgres.ts           postgres(options): the factory
├── types/
│   ├── options.ts        what an app gives: the driver, table, fields, kinds, operators
│   └── rows.ts           a row, a page, a write's body
├── table/
│   ├── table.ts          the table a definition is: its columns
│   ├── options.ts        an option field's places and attributes
│   └── table-of.ts       tableOf: the CREATE TABLE
├── columns/
│   └── columns.ts        each kind as a column
├── operators/
│   └── operators.ts      each operator as SQL
├── query/
│   ├── query.ts          the whole query, with its count
│   ├── filter.ts         the filter
│   ├── sort.ts           the sort
│   ├── search.ts         the search
│   ├── groups.ts         the groups, counted by Postgres
│   └── where.ts          the filter and search as one WHERE
├── sql/
│   └── sql.ts            sql, join, raw, ident, cast
├── writes/
│   └── writes.ts         read, create, update, delete
└── postgres.test.ts      the whole adapter on PGlite

🔑 Key files

• table/table.ts: turns a definition into the table everything else reads: each field's quoted column, its type, how it's read back. The query and the writes are made from this, never from the options again.
• columns/columns.ts: how each kind lives in Postgres: its column type, how a list of it is kept, and how it's read, sent and ordered where the column alone isn't the contract's value.
• operators/operators.ts: tilewise's operators as SQL, each holding as matchesFilter says: a list's by any of its values, contains with its case ignored, notEquals holding where there's no value.
• query/query.ts: puts the filter, search, sort, groups and offset paging together, and counts the rows each query keeps.
• writes/writes.ts: reads a row by its id, and creates, updates and deletes it, setting the version and telling a stale write from a missing row.
• sql/sql.ts: the only place SQL text is built: sql-template-tag for fragments and parameters, a quoted name for a schema's dotted table, a cast.

🧱 Built on

| Library | Used for | | ---------------------------------------------------------------------------------- | ----------------------------------------------------- | | sql-template-tag | building and nesting SQL, each value a $n parameter | | @databases/escape-identifier | quoting the table's and the columns' names | | change-case | a field's key as its column's name, in snake_case | | valibot | reading a kind's settings by its schema |