@render-lab/tasks-render-postgres

⚠️ Experimental: proof of concept. This package is part of the Render Tasks POC and is published for testing only. It is not fully tested or production ready. Task names, inputs, outputs, and behavior can change or break in any release. Pin exact versions and expect breaking changes.

Durable Render Postgres tasks for Render Workflows. A first-party pack (ADR-0015): it owns the zero-config private-network connection to your Render Postgres, rather than being one driver behind a generic capability.

import { query, upsert, migrate } from "@render-lab/tasks-render-postgres";

| Task | Input | Output | | ---------------------- | ------------------------------------------------------- | ----------------------- | | postgres.query | { sql, params? } | { rows, rowCount } | | postgres.execute | { sql, params? } | { rowCount } | | postgres.upsert | { table, rows, conflictColumns, updateColumns? } | { rowCount } | | postgres.transaction | { statements: [{ sql, params? }] } | { rowCounts } | | postgres.migrate | { migrations: [{ name, sql }] } | { applied: string[] } | | postgres.setupVectorStore | { table, dimensions, index? } | { table, extensionReady, tableCreated, indexCreated } | | postgres.upsertVectors | { table, rows: [{ id, content?, metadata?, embedding }] } | { table, upserted } | | postgres.searchVectors | { table, embedding, limit?, maxDistance?, filter? } | { rows: [{ id, content, metadata, similarity }] } | | postgres.deleteVectors | { table, ids? } or { table, filter? } | { table, deleted } |

• query runs a parameterized SELECT (values via $1, $2, …) and returns the rows.
• execute runs a parameterized write and returns only the affected row count.
• upsert builds one INSERT … ON CONFLICT (…) DO UPDATE from a batch of rows. Values are parameterized; the table and column names are interpolated — pass trusted identifiers, never user input. Idempotent, so it's safe under the durable retry.
• transaction runs statements in a single BEGIN/COMMIT; any error rolls the batch back.
• migrate applies named migrations at most once via a _render_migrations ledger, in one transaction with their ledger inserts — a crash/retry never double-applies. Re-running with the same input is a no-op.
• setupVectorStore / upsertVectors / searchVectors / deleteVectors — pgvector primitives over a canonical schema; see Vector store (pgvector) below.

Install

pnpm add @render-lab/tasks-render-postgres @renderinc/sdk

@renderinc/sdk is a peer dependency. The pg driver is a regular dependency, encapsulated inside the default port.

Environment contract

| Variable | Required | Purpose | | -------------- | -------- | ------- | | DATABASE_URL | yes (at first use) | Postgres connection string. Render injects it for a linked Postgres, reachable over the private network. Read lazily on the first query. | | DATABASE_SSL | no | TLS for EXTERNAL connection strings (e.g. a Postgres in another workspace): true/require = TLS with certificate verification, no-verify = TLS without chain verification. Unset for the private-network URL. Alternatively pass ?ssl=true or ?sslmode=no-verify in the URL itself. |

Durability notes

The durable retry re-runs the whole task, so a non-idempotent single write (execute of a plain INSERT) that partially succeeded before a crash could double-apply. Prefer upsert / idempotent SQL, or group writes in transaction (one atomic attempt). Timestamps come back as JS Dates and serialize to ISO strings across the task boundary.

Vector store (pgvector)

Four tasks build a pgvector store on top of the same Postgres connection, over one opinionated canonical schema — the only configurable identifier is the table name:

CREATE TABLE "t" (
  id text PRIMARY KEY,
  content text,
  metadata jsonb,
  embedding vector(N) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

• setupVectorStore runs CREATE EXTENSION IF NOT EXISTS vector, then creates the table above and a cosine ANN index, each as an idempotent IF NOT EXISTS statement (three separate statements, not a transaction, so a re-run after a partial failure converges). index is "hnsw" (default), "ivfflat", or "none" — validated at runtime; anything else throws before any SQL runs. tableCreated/indexCreated report false when the object already existed (or index: "none"), so the task is safe to call on every workflow run.
  • CREATE EXTENSION needs privilege. On Render Managed Postgres the default role can create extensions on its own database; on a locked-down instance this fails with Postgres's own permission error, surfaced untouched.
  • IVFFlat on an empty table builds a useless index — its lists are computed from existing data. Prefer hnsw (the default), or create an ivfflat index only after bulk-loading rows.
• upsertVectors batches one INSERT … ON CONFLICT (id) DO UPDATE per row inside a single transaction. Every embedding is validated (non-empty, finite numbers) before the transaction opens, so one malformed row fails fast instead of partway through. Dimension consistency across rows is not pre-checked — pgvector itself rejects a wrong-dimension vector, and that error propagates loudly.
• searchVectors ranks by cosine distance (embedding <=> $1::vector, v1 supports only the cosine metric) with an optional metadata @> filter containment filter and an optional maxDistance cutoff, limit capped at 1000. The embedding column itself is never returned — it's the large, rarely-wanted one; use postgres.query when you need it back. If provided, filter must serialize to something other than {} — an empty or all-undefined filter ({}, { k: undefined }) throws instead of silently matching every row; omit filter entirely to search unfiltered.
• deleteVectors takes exactly one of ids (non-empty array) or filter (metadata containment filter). Both, neither, or a filter that serializes to {} all throw — an empty filter would match, and delete, every row, so "delete all" is deliberately not an affordance here; use postgres.execute with explicit SQL instead.

Identifier validation: every table (and the index name derived from it) is checked against ^[a-zA-Z_][a-zA-Z0-9_]{0,62}$ and double-quoted before being interpolated into SQL — this is the one place these tasks build SQL from input; everything else is a bound parameter. Anything outside the pattern throws before a query runs.

Env contract is unchanged — these tasks reuse DATABASE_URL / DATABASE_SSL above.

Extending & testing

Every task exports its raw *Impl, which depends on a PostgresPort. Tests inject a fake port — no database, no network:

import { upsertImpl } from "@render-lab/tasks-render-postgres";

await upsertImpl(
  { table: "users", rows: [{ id: 1, name: "a" }], conflictColumns: ["id"] },
  { db: { query: async () => ({ rows: [], rowCount: 1 }), batch: async () => ({ rowCounts: [] }) } },
);

Run the tests with pnpm -C packages/tasks-render-postgres test.