Feature request: Expose client instance via postgres:// protocol

[kamilogorek]

Not sure exactly how to go about this yet, but it would be great if we could create in-memory, or temporary local instances of Postgres and marry it together with Postgrest https://postgrest.org/en/v12/

This way you can have an trivial REST API setup _or _ like in my case, allow for creating fully replicable test environment for your unit/e2e tests.

[samwillis]

Great idea!

Once we swap to using the pg wire protocol for all I/O (#31, with #17 laying the foundations for input) it should be relatively easy to wrap pglite in a node socket server to make it appear as a pg instance.

Key thing to note though is that PGlite is single process/user, so multiple connections wouldn't be supported. With a socket server we would probably want to enforce this, but that's also probably fine for tests.

[kamilogorek]

Lovely, looking forward to this!

Currently, there's no easy way to test PostgREST other than mocking on the network layer (eg. with nock or msw), but as you can imagine those tests are not only fragile but also very verbose and they completely beat the purpose of having fully typed queries like we do thanks to https://github.com/supabase/postgres-meta + https://github.com/supabase/postgrest-js combo.

Being able to have something that could create in-memory instance + expose it's connection would definitely allow us to write better testing infrastructure (that I'll be more than happy to open-source afterwords as well :)).

[samwillis]

#39 introduces full IO using the PG protocol and exposes a .execProtocol(message: Uint8Array) method for executing PG protocol messages. It returns Array<[BackendMessage, Uint8Array]> where the Uint8Array is the raw response message from pg. I think it should be possible wrapped in a simple server to allow a network connection.

Note that it won't do the initial connect/auth handshake, that will probably need to be simulated.

[xeoshow]

Strongly need the feature of supporting "postgres:// protocol" ...

[kamilogorek]

@samwillis it's only beginning, but thanks to an awesome post from @GavinRay97 (thanks!), I managed to make execProtocol work for most basic use cases now directly via psql.

I'll see how much I can push it, and in the future, we can work on wiring it up to the pglite itself if it's on your roadmap :)

https://gist.github.com/kamilogorek/fdba0412e4f1c0b582da99c511cc0a42

[kamilogorek]

Added playground repo for reference https://github.com/kamilogorek/pglite-server

[kamilogorek]

Aaaand it's live :) - https://www.npmjs.com/package/pglite-server

[BracketJohn]

Thanks @kamilogorek for this great implementation ❤️ I tested it out for a local development setup and it was mostly working with [email protected]. With [email protected] it gives me the following:

[::1:51153]: Client connected
[::1:51153]: Client error: Error: write EPIPE
    at afterWriteDispatched (node:internal/stream_base_commons:161:15)
    at writeGeneric (node:internal/stream_base_commons:152:3)
    at Socket._writeGeneric (node:net:954:11)
    at Socket._write (node:net:966:8)
    at writeOrBuffer (node:internal/streams/writable:564:12)
    at _write (node:internal/streams/writable:493:10)
    at Socket.Writable.write (node:internal/streams/writable:502:10)
    at Socket.<anonymous> (file://Users/BracketJohn/test-project/node_modules/.pnpm/[email protected]/node_modules/pglite-server/dist/index.js:274:16)
    at processTicksAndRejections (node:internal/process/task_queues:95:5) {
  errno: -32,
  code: 'EPIPE',
  syscall: 'write'
}

I'm guessing that this is due to protocol changes coming up in 0.2?

I'm just leaving this here for now instead of opening an issue as I know that 0.2 was not even properly released yet. Also I'm uncertain whether it's something that needs to be addressed on the pglite or on the pglite-server side of things.

[kamilogorek]

@BracketJohn take a look at https://github.com/supabase-community/pg-gateway which is way more powerful and works with 0.2.0 :)

[BracketJohn]

Oh wow, thanks @kamilogorek - also very nice of you to point to an alternate package that you have not implemented (: Thanks a ton 💯 ❤️

[BracketJohn]

fyi @kamilogorek: there was an interesting possible limitation I found in pg-gateway (although it could also be user-error on my side), see my issue + initial feedback by the author on this here: supabase-community/pg-gateway#4 (comment)