Adapter for Postgres.js

[darkgnotic]

Would it be possible to have an adapter for the Postgres.js library?

https://github.com/porsager/postgres

[smcclure17]

I'm interested in the same feature--

We could maybe do something like #398 for this, but it would require more work and scoping.

[nascjoao]

Hello!
Just coming here to comment that I would love to have this adapter.
Appreciate all effort on it!

[lucaswalter]

Would also make heavy use of this adapter for our project.

[surprisetalk]

I'd love to use this! Any way I can help on the PR?

[oguimbal]

A first version has been implemented via #411 , released as pg-mem@3.0.0

This was a tough one, mainly because Postgres.js does not offer any simple way to hook query execution.

I had to emulate a postgres db socket (that can be fed to postgres.js) ... luckily, my preliminary works on pg-server for Primsa support was about exacly that, so this was a good first step.

👉 As far as I can tell, postgres.js seems to work:

1. Install peer dependencies: pg-server and postgres
2. Use template:

import { newDb } from 'pg-mem';

// init db
const db = newDb();
const sql = db.adapters.createPostgresJsTag() as import('postgres').Sql;

await sql`create table test(name text)`;
await sql`insert into test values ('Alice'), ('Bob')`;

const pattern = 'A%';
const results = [...await sql`select * from test where name like ${pattern}`];
console.log(results);
// prints [{    name: "Alice",   }]

⚠️ I only tested very simple use cases, and given this passes through the PG procol, there might be things I did not implement correctly. I suspect you might have issues with:

• concurrency, obviously (do not run concurrent queries)
• named prepared statements
• savepoints
• other ?

see unit tests here

[smcclure17]

This may be user error, but I'm having trouble with the createPostgresJsTag method in Node. I created a minimum example here https://github.com/smcclure17/test-pg-mem-postgres-js.

Note that both execution with the Typescript compiler (npm build-and-run) and with TSX (npm run tsx-run) fail with the error:

> tsc index.ts && node index.js

TypeError: pg is not a function
    at Adapters.createPostgresJsTag (/Users/user/Documents/repos/tst-pg/node_modules/pg-mem/index.js:13644:21)
    at /Users/sean.mcclure/Documents/repos/tst-pg/index.js:59:35
    at step (/Users/user/Documents/repos/tst-pg/index.js:37:23)
    at Object.next (/Users/user/Documents/repos/tst-pg/index.js:18:53)
    at /Users/user/Documents/repos/tst-pg/index.js:12:71
    at new Promise (<anonymous>)
    at __awaiter (/Users/user/Documents/repos/tst-pg/index.js:8:12)
    at main (/Users/user/Documents/repos/tst-pg/index.js:53:33)
    at Object.<anonymous> (/Users/user/Documents/repos/tst-pg/index.js:76:1)
    at Module._compile (node:internal/modules/cjs/loader:1376:14)

Haven't had a chance to dig too deeply into this yet FWIW so it may be a config issue

[gmathews]

@smcclure17 are you using ESM? I am also having this issue, and I think that is probably what is causing it. I've done a little investigating by adding printouts and this seems to be what is wrong:

    createPostgresJsTag(queryLatency) {
        const pg = (0, utils_1.doRequire)('postgres').default;
                    console.log(typeof pg); // <- undefined

But I'm not sure why.