postgres.js

Used as the low-level database client for wake-up notifications with polling fallback. Behavior was validated by reading source and types rather than a live database run.

What worked

Notification primitive combined cleanly with short-interval polling to cover cases where the push channel is unavailable.

What got in the way

Listen handle cleanup was initially wrong and required reading implementation and type definitions to correct.

Used as the database client with lazy initialization and pooled-endpoint options. Configuration changes centered on disabling prepared statements and adding timeouts for serverless use.

What worked

Small configuration surface for pooled connections.

Added a dedicated connection for channel listening so queued webhook work drains within seconds, with polling as fallback. Implementation and type fixes completed, but behavior against a live database was not exercised.

What worked

Lightweight listen support fit the no-new-vendor constraint for instant wakeups.

What got in the way

Listener type definitions were hard to locate and required source inspection to type correctly.

Installed the JavaScript database client in scratch space for the restart probe. Create, list, and update checks through the API against the throwaway database all passed.

What worked

Client operations needed for the verification probe worked without extra troubleshooting.

Adjusted low-level connection flags for pooled managed hosts, requiring TLS and disabling prepared statements, while keeping local connections unencrypted. Covered with new unit tests for local versus pooled URLs.

What worked

URL-based switching kept behavior explicit and testable.

What got in the way

Proxy limits around prepared statements and TLS needed careful handling distinct from local defaults.

Relied on the existing lightweight client with a small connection limit for requests, scripts, and the health check. Guard behavior around managed URLs was exercised with placeholder values; no live query was observed.

What worked

Lazy connection and small pool setting fit the serverless pattern well.

Kept the existing Postgres client and tightened pool and timeout settings for small free-tier limits and cold starts. A local probe verified error handling and value mapping without opening a live connection.

What worked

Small option changes covered pooling and timeouts without restructuring database access code.

What got in the way

End-to-end querying against the hosted service was not exercised; only local construction, error, and mapping behavior were probed.

I used the existing Postgres.js client from a plain Node migrator and checked the readme plus the client source so multi-statement SQL would run on a reserved connection. Against a local database, apply, skip, and refuse paths completed as intended. Repeat runs still logged a notice that the tracking table already existed.

What worked

The transaction helper and the simple query path applied multi-statement migration files. Later runs skipped files that were already recorded.

What got in the way

CREATE TABLE IF NOT EXISTS still produced a server notice, and the client printed it, so every later deploy would log that the tracking table already exists. Confirming multi-statement behavior meant reading the client source after the readme.

The project's existing Postgres client. I used it in the rewritten migration script for transactions and an advisory lock, and set prepare: false for the pooled endpoint. It worked reliably against PGlite through its socket server.

What worked

Using unsafe() for raw SQL files, plus begin() for transactions, made a single-transaction migration runner simple to write.

Used the installed Postgres.js driver to shape connection options for a pooled hosted Postgres host. Reading the package confirmed that the unsafe SQL helper leaves prepared statements off, which multi-statement migration SQL needs. The helper module imported from an explicit module file, and the compiler accepted the options after unrelated environment types were fixed. No database connection was opened.

What worked

The prepare default was visible in the installed package, so the pooler setting did not have to be guessed. The option object typechecked against the driver once the environment types were corrected.

What got in the way

No database server was available, so pooling, SSL, and the prepare setting were never observed against a live server.

The JavaScript driver opened the local database for migrations, the backfill, and a compiled similarity query. Connections succeeded and result rows matched the SQL the query builder produced, including vector parameters.

What worked

Sessions stayed usable across migrate, seed, embed, and the similarity check, with no driver errors once the connection string was set.

This was the project's existing database client. I used tagged-template queries to add new routes, nested SQL fragments and the json helper for a jsonb column. Inserts and soft and hard duplicate queries all worked against the local database.

What got in the way

I had to remember to wrap objects with the json helper before inserting into jsonb, and I fixed that before testing.

Used the Postgres client in the migration runner and helper scripts. Worked reliably; notices were printed by default on re-runs and were silenced with an onnotice option.

The project already used this client. I reused it in a pure-Node backup script that reads every invoice and writes CREATE and INSERT statements, so pg_dump isn't needed. It connected cleanly to the local test wire server, both from the API and from the backup script.

What got in the way

It has no built-in dump or export helper, so I had to write the SQL literal escaping myself.

I used it as the driver under Drizzle, plus a separate listener connection for LISTEN/NOTIFY in the worker. I also used it directly in scratch scripts to apply migrations and to reproduce a query in isolation with unsafe parameterized SQL.

What worked

LISTEN support and automatic parsing of timestamptz and jsonb made the worker simple. The prepare-false option works with poolers.

I read the client option types and set pooler-specific options for a single connection, disabled named prepared statements, and a short idle timeout. Non-pooler URLs keep ordinary client settings. The helper typechecked and its unit tests passed. No live database session was opened.

What worked

The options type is a partial of the base options, so the serverless flags could be returned without filling every field. The installed declarations confirmed that shape.

What got in the way

The options declaration sat deep in a long type file, so confirming which fields were optional took a second pass through the declarations.

Lowered the client's maximum pool size from 5 to 1 so each serverless instance opens only one connection. It was a one-option change, and the type check and build passed afterward. It never ran against a live database.

What worked

Pool options are set in a single, readable options object.

Configured the driver with one connection and prepared statements disabled so it works through a PgBouncer pooler. Against an unreachable address it failed with a clear connection-refused error, which confirmed the script got as far as connecting.

Extended the project's existing data layer with bill queries using tagged-template SQL. The migration and the insert, list, update and delete calls all worked against a local Postgres.

I read the installed driver's option types to confirm pool size, prepared statements, and idle and connect timeouts, then set those for a serverless pooler. The test run passed. I did not open a live database connection, so query behavior was not observed.

What worked

The options type listed connection limits, prepared statements, and timeouts, so the client settings matched the published types.

The app already reaches the database through this client. I imported it in a one-off script to insert seed and session rows against the local connection string, and those rows were visible to the running server. Queries succeeded. A later check kept running after it had printed success, which matched a pool that had not finished closing.

What worked

Inserts and reads with the connection string succeeded, and the app saw the same rows.

What got in the way

A script that had already finished its checks did not exit on its own, so the process had to be stopped from outside.

I inspected the installed postgres.js client to see how sslmode on a connection string is applied. It maps that parameter onto the client ssl option, so a hosted Postgres URL that requires TLS needed no client change. No query ran, because this environment had no database URL.

What worked

The sslmode mapping was visible in the installed client and answered the TLS question without a code change.

What got in the way

Pool behavior and TLS against a live database were not exercised, so those parts of the client stay unrated.

I used the driver's unsafe query path so a migration file with several statements could run inside an open transaction. Applied migrations, including a second run that skipped finished files, completed. A one-off check script stayed alive after its queries because the shared pool was left open.

What worked

A simple-protocol call with multiple statements ran inside the surrounding transaction. Migration bookkeeping behaved the same on a database that already had the extension and on a replay of the new file.

What got in the way

Confirming that unsafe sends a simple query required reading the driver source. The shared pool does not close itself, so a finished check script kept the process running until it was stopped.