implementation questions

[hyusetiawan]

This is such a promising library, I am playing around with to see it matches with our current stack:

• tanstack start app server
• drizzle + postgres
• graphql as the transport layer
• pothos as the graphql builder

I envision that the integration points are:

• drizzle -> raw sql string -> linkedql with {live: true}
• graphql expose subscription operations instead of just query
• magic
• we got a realtime graphql backed fully typed app

My questions are:

• For Postgres RLS that depends on current_setting('claims.user_id', true), how do you recommend setting per-request session claims on the exact connection that executes a live query?
• Can two users with different session claims safely share the same LinkedQL live-query engine instance or query window, or must they be isolated per user/claims set?
• If live queries are deduped internally, what is the dedupe boundary: SQL text only, SQL + params, or SQL + auth/session state?
• For a stack that uses Drizzle only as schema and SQL builder, is there a recommended integration pattern where we compile Drizzle queries to SQL and run them through LinkedQL for reads?
• Do you see any issue with this split: LinkedQL for reads/subscriptions, Drizzle transactions for mutations? We open a transaction for all of our mutations per request.
• What exact Postgres setup is required in production: replication slot privileges, publication creation, REPLICA IDENTITY FULL requirements, FOR ALL TABLES vs explicit publication membership?
• Can LinkedQL run against a normal pooled app connection plus a separate replication connection, or does reliable live-query + RLS require pinned non-pooled connections?
• What are the current sharp edges or known missing pieces for Postgres live queries that would most affect a small but real multi-user app?

Apologies for the barrage of questions!

[ox-harris]

@hyusetiawan Happy to have this conversation — and happy to see what you build with LinkedQL too.

I expect this session to be iterative — as clarity improves the more we dig into real use cases.

First, for RLS via current_setting('claims.user_id')

current_setting(...) is tied to the active database connection.

But LinkedQL live queries are:

• long-lived
• potentially shared/deduped
• not guaranteed to run on the same connection as the initial request

So there’s a mismatch here.

The practical recommendation today is to parameterize RLS instead of relying on current_setting.

In other words, push identity into the query:

SELECT * FROM todos WHERE user_id = 10;

This makes the query deterministic for the live-query model, which may span connections and execution contexts.

That said, the pattern you're describing is valid and worth supporting more directly — i.e. bringing connection/session semantics into the live query engine itself. That’s something we can explore at the engine level, but it’s not how things work today.

For question 2

It depends on what defines query identity here.

If identity is part of the query:

SELECT * FROM todos WHERE user_id = 10;

then yes — multiple users can safely share the same engine instance.

If identity is implicit (e.g. via current_setting()), then no — results can leak across users.

So in practice:

• explicit identity in SQL makes it safe to share
• implicit/session identity requires isolation

For question 3

The current dedupe boundary is:

• SQL text only

Not:

• SQL + params
• SQL + auth/session context

And I think that distinction is important:

It means queries that differ only by parameters or auth context are currently treated as the same live query.

So:

per-user filtering must be part of the SQL text itself, not just parameters or session state.

SELECT * FROM todos WHERE user_id = 10; // better

SELECT * FROM todos WHERE user_id = $1; // executed with different params, risky

However, expanding the dedupe boundary to include parameters and execution context is planned.

For question 4

Yes — this is a clean pattern.

const { sql, params } = drizzleQuery.toSQL();
await db.query(sql, { values: params });

• Drizzle for schema, typing, SQL generation
• LinkedQL for execution, optional streaming and live queries

The separation is straightforward and composable.

Longer term, we’re also interested in collapsing some of that layering — e.g. type-safety directly over SQL — but that’s still evolving.

For question 5

This works. Reason is:

• live queries are driven by WAL, not resolved within transaction boundaries
• so Drizzle writes, the commit event eventually reaches the live query engine

But happy to look at a concrete flow if you want to share one.

For question 6

Yes, this was indeed not fully covered in the docs. But full details are in now. Do take a look at the Setup Guide. Let me know if a question here is still unanswered. But to summarize

At minimum, you need:

wal_level = logical

Then, optionally:

• create a replication slot (logical) and pass the name to LinkedQL – otherwise, LinkedQL auto-creates a euphemeral slot
• create a publication and pass the name to LinkedQL – otherwise, LinkedQL auto-creates a publication FOR ALL TABLES

For when you do create your publications:

• LinkedQL accepts a list of publication names as you'll see in the setup guide
• FOR ALL TABLES may be easier for your to manage
• but for safer for control, explicit tables per publication

For privileges, user must have:

• REPLICATION privilege
• or superuser

For REPLICA IDENTITY, the live query engine works with both:

• DEFAULT (primary key only on update and delete payloads)
• FULL (full "old" rows)

The engine is happy either way, as primary keys are sufficient for diffing.

What may influence what you choose is if you do direct table-level subscription: db.wal.subscribe() and care about payload event payload shapes. db.wal.subscribe() is different from db.query(live: true), but both are driven by the same WAL stream.

Again,

• with db.query(live: true), either mode works for the engine

• but with db.wal.subscribe(), your expectation determines what you choose. (See Changefeeds for details)

  • choose DEFAULT if keys are enough
  • choose FULL if you need full before-state in your callbacks

Back to live queries, one extra note is that:

tables without a primary key are ignored by the live query engine

A stable row identity is required for change tracking.

For question 7

LinkedQL supports both modes:

new PGClient({ poolMode: true });

But single connection by default.

In pool mode:

• query execution uses pooled connections
• a persistent connection is reserved internally for WAL decoding

Also, from the app side, you can always check out a connection for a session-sensitive query:

const client = await db.connect(); // always gives you back a client
// ...
client.release(); // return after use

For question 8

I expect edge cases to be discovered over use.

But currently, the above are things to keep in mind for real-world apps:

• RLS via connection/session state does not compose well with live queries today
• dedupe boundary is SQL-only (params/context not included yet)
• live queries are eventually consistent (WAL-driven)
• tables without primary keys aren't supported by live queries

A long list of questions, TBH. But very much welcome 😁

Awaiting your taking a look.

[hyusetiawan]

shoot, the RLS might be a deal breaker here. We already have decided architecturally to go all in on RLS. Let me get back to you with follow up questions. I do see how those two can be conflicting, the holding of connection will exhaust the pool real quick.

The rest is clear in terms of integration, thank you for taking the time answering!

[ox-harris]

Hey — yeah, it's is exactly the right concern to surface.

But looking at how close we are, I don’t think this is a blocker or a fundamental incompatibility — I thinking it's just a matter of decide how to bind live queries to session boundaries.

What I’d like to do is converge on the exact execution model first (especially: how we want RLS context to attach to a live query over time), and then I can turn around a patch quickly once that’s stable.

To avoid going back-and-forth in comments, I’ll open a follow-up issue where we can lock this down as a concrete design before implementation.

[ox-harris]

Issue is in: #7

[ox-harris]

@hyusetiawan

We’ve been tightening the live query execution model over the past few days based on the issues surfaced here, and I’ve pushed changes that address both dedupe correctness and RLS compatibility.

1. Live query deduplication boundary

Previously, dedupe was effectively based on SQL text only, which exposed incorrect merging behavior across parameterized queries.

That’s been corrected.

Dedupe is now scoped at least to:

• normalized SQL
• query parameters
• transaction scope (when present)

So parameterized queries like:

SELECT * FROM todos WHERE user_id = $1;

can no longer collapse across differing parameter bindings or execution contexts.

---

2. RLS / session state model

The original execution model didn’t have a concept of session or transaction affinity, which made RLS patterns relying on:

current_setting('claims.user_id', true)

unsafe in a pooled/live-query environment.

That model has been updated.

Live queries can now execute within a transaction-scoped context, allowing session claims to be bound using set_config(..., true) within the same transaction:

const tx = await db.begin();

await db.query(
  `SELECT set_config('claims.user_id', 'user_abc_123', true)`,
  { tx }
);

const result = await db.query(
  `SELECT id, title FROM public.posts ORDER BY id`,
  { live: true, tx }
);

This ensures RLS evaluation remains consistent for the lifetime of the live query subscription under a single execution context.

---

Docs

Updated details here:
Visibility And Security

[ox-harris]

@hyusetiawan I thought to check with you how it's going so far.

Did you find a sweetspot in the current options for access control and security enforcement in live queries?

Does the current shape of LinkedQL match your architecture?

Curious how that's all going.