I built a Supabase-style query client that talks directly to Postgres

I like the Supabase JS client. .from('todos').select('id, task').eq('done', false) reads like what I mean, and { data, error } means I'm not wrapping every query in try/catch.

What I don't always want is the stuff around it: a project URL, an anon key, and a PostgREST service sitting between my Node server and a Postgres I already control.

So I built pg-fluent: the postgrest-js query builder, executed directly against PostgreSQL through node-postgres.

import { createClient } from 'pg-fluent'

const db = createClient({ connectionString: process.env.DATABASE_URL })

const { data, error } = await db
  .from('todos')
  .select('id, task, done')
  .eq('done', false)
  .order('id')
  .limit(10)

If you've used supabase-js, that's already familiar. The difference is there's no HTTP request on the other end, just one parameterized SQL statement over a normal Postgres connection.

npm install pg-fluent

Repo: https://github.com/gerald-mathew/pg-fluent

Who it's for

People running long-lived servers (Express, Fastify, a VPS, containers) who like the Supabase query API but want to connect straight to Postgres, or who are moving off Supabase's hosted stack and don't want to rewrite every query. It's server-side only, more on that below.

How it works

I didn't reimplement the builder. pg-fluent uses the real @supabase/postgrest-js and hands it a custom fetch. When the builder "sends" a request, pg-fluent catches it, parses the URL, headers and body the way PostgREST would, plans one parameterized SQL statement, runs it through pg, and returns a response postgrest-js unpacks as if a server had answered.

Results are aggregated to JSON inside Postgres, so you get the same shape as Supabase. Embeds like select('id, title, author:users(name)') become subqueries that aggregate to JSON, with relationships resolved from the live schema (cached, with auto-refresh).

Since I'm intercepting what the builder emits, an upstream change could quietly change the SQL. So the dependency is pinned to an exact version, and docs/UPSTREAM.md records every bump with the date and reason. A bump that changes observable SQL or results is at least a minor release.

What you get beyond the basics

The whole postgrest-js surface works: filters, or()/not()/filter(), JSON paths like meta->>author, range(), single()/maybeSingle(), count, upsert, rpc, csv(), explain(), and embeds (many-to-one, one-to-many, many-to-many, !inner, aliases, spread). Then the things that only make sense with a direct connection:

See the SQL without running it:

const { text, values } = await db.from('todos').select('id').eq('done', false).toSQL()

Transactions with savepoints:

const user = await db.transaction(async (trx) => {
  const { data, error } = await trx.from('users').insert({ name: 'G' }).select().single()
  if (error) throw error
  await trx.from('profiles').insert({ user_id: data.id })
  return data
}, { isolationLevel: 'read committed' })

Raw SQL, safe by default. The tagged template always binds interpolations as parameters. The only way to run SQL text you built yourself is db.unsafe(), and the name is deliberate.

const { data } = await db.sql`select * from users where id = ${id}`

Streaming, COPY, LISTEN/NOTIFY:

for await (const row of db.from('events').select('*').stream()) {
  await handle(row)
}

await db.copyFrom('events', rows, { columns: ['id', 'kind'], format: 'csv' })

const sub = await db.listen('orders', (payload) => console.log(payload))

The listener gets its own non-pooled connection and reconnects with backoff.

Named connections and read replicas. Reads can go to replicas, while writes, RPC, transactions and LISTEN always hit the primary. Add .primary() when you need read-your-writes.

Type generation:

npx pg-fluent gen-types --connection "$DATABASE_URL" --schema public --out ./database.types.ts

The output is a Database type compatible with postgrest-js, so createClient<Database>(...) just works.

There are also logger and onQuery hooks and optional OpenTelemetry spans if @opentelemetry/api is installed. Parameter values are never logged unless you opt in.

Guardrails by default

• Unfiltered UPDATE/DELETE is refused with PGFLUENT_UNFILTERED_MUTATION. Opt in with .allowUnfiltered() or a client option.
• Reads are capped: maxRows defaults to 1000 when you set no limit or range.
• Statements time out: statementTimeoutMs defaults to 30 seconds.
• Retries are read-only. Transient connection failures retry with backoff for reads, and writes are never retried.

How I tested it

I didn't want to say "drop-in" on a hunch, so:

• Differential tests against a real PostgREST server. The same requests go through both, and the payloads must match. One difference is intentional and documented: a reserved parameter name like columns or select used as a filter is rejected with PGFLUENT_INVALID_QUERY. PostgREST silently ignores it, which turns a scoped read into a full-table read.
• Property-based injection tests with fast-check. Hostile strings go in as filter values, the SQL text must match the baseline, the values must land in parameters only, and a canary table must survive.
• A real CI matrix: Node 22 and 24 against Postgres 15, 16, 17 and 18.

That's 200+ tests. There's also a benchmark harness against raw pg, but I'm not publishing performance claims. Network and pooling change the numbers too much, so it only reports methodology and variance for you to run yourself.

The RLS caveat (please read)

pg-fluent runs statements as the connection's own role, like psql or any other driver. It doesn't set request.jwt.claims and doesn't emulate Supabase's anon and authenticated roles.

So RLS policies written for Supabase's roles don't automatically protect you here. Connect as the table owner or a BYPASSRLS role and RLS is skipped entirely. Use a least-privilege role, keep this on the server, and never let database credentials reach a browser. For per-user enforcement, connect as a non-owner role and set SET ROLE and the JWT claims yourself. If you're migrating from supabase-js, treat this as step zero.

What's missing or rough

It's 0.2.x, so, honestly:

• Embedding views isn't supported yet (you can read them, just not embed them).
• No auth, storage or functions. It's a database client, not a BaaS SDK.
• LISTEN/NOTIFY doesn't work through transaction-mode poolers like PgBouncer. Use session-mode or a direct connection.
• Replica reads are asynchronous, and there's no lag measurement in v1.
• Requires Node 22+ and Postgres 15 to 18.

Try it, break it

If you have a Supabase-style codebase and a Postgres you control, point pg-fluent at a test database and tell me what breaks. Issues and PRs are welcome at https://github.com/gerald-mathew/pg-fluent, and security reports go through the private channel in SECURITY.md.

I especially want to know which postgrest-js queries behave differently from what you expect. Real queries find things my fixtures won't.