Feature Request / Idea: PGlite integration?

[cliffordfajardo]

Description

Support PGLite (wasm postgres)

Motivation

Allows user to use this library (postgres) with local version of postgres without needing to install postgres directly on their computer or setup a container.

I love the API for this libray; its so flexible and well thought out 😭

[rmnoon]

This would be so awesome since it'd make it trivial to write high performance unit tests in postgres.js apps!

[rmnoon]

Unfortunately pglite is tightly coupled to a single-user mode for postgres to avoid having to fork processes per-connection (which postgres does and wasm doesn't support) and thus it doesn't actually spin up a server / doesn't speak the normal wire protocol. Integrating it with postgres.js thus seems pretty nontrivial I think.

[mike-marcacci]

I'm currently exploring a separate but related goal and came across pg-gateway, which implements the postgres wire protocol in TS.

It appears built to solve this problem, and they have an example of using it to "wrap" PGLite.

[rmnoon]

Did you try it? Looks like a flurry of activity 8 mos ago. Great find!

[mike-marcacci]

@rmnoon I just spent a bit of time today getting this working here. I want to note a few things:

Handling extra ReadyForQuery messages

There is an insidious bug between PGlite and postgres.js that was pretty hard to track down. Essentially, PGlite is sending extra ReadyForQuery at the end of each socket message. This appears to be a bug in PGlite itself, and while that scenario appears to be undefined behavior in this client, the effect I saw was indefinite hanging. It's not clear to me if this is a scenario is hypothetically possible for a "well behaving" postgres server, or if this is simply a violation of the postgres protocol that should never actually happen.

I currently use pg-gateway to filter out these extraneous messages.

Adding socket option in TS

Currently the socket option is not defined in the TS types for this library, even though it's documented, tested, and does work.

This should be an easy fix.

Consider accepting a WebSocketStream

While this is still not a finalized standard, a WebSocketStream is composed of modern web streams which have gained substantial traction and are by just about all accounts are successors to node's structures.

In my proof-of-concept I convert existing web streams into a node Socket for use by this library, potentially imperfectly.

[gersomvg]

Related (I didn't want to flood the issues with feature requests at the time): #927

I feel like the easiest way to get this in the meantime is building a custom wrapper that wraps either postgres.js or pglite depending on whether you are running tests / dev environment. Drizzle could act as an intermediary as it supports both postgres.js / pglite and it has a sql template literal, but unfortunately its template literal isn't as powerful as postgres.js.