Live data from Hacker News

PostgresJs: PostgreSQL client for Node.js and Deno

github.com

141–148 of 148 posts

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#141

Been using pg-promise[1] for years (for Node). How does this compare - is there a reason to switch? [1]: https://github.com/vitaly-t/pg-promise

Author here - I'm actually a happy previous pg-promise user :)

If you check out the documentation I think you should be able to see if it's worth it for you to switch. My personal top reason is the safe query writing using tagged template literals, and the simpler more concise general usage. Then there's a lot around connection handling which is simpler, but also handles more cases. Then there's the performance. Postgres.js implicitly creates prepared statements which not only makes everything faster, but it also lowers the amount of work your database has to do. Pipelining also happens by default. As you can see by the benchmarks listed elsewhere, Postgres.js is quite a bit faster, so you can either get more oomph out of your current setup, or scale down to save some money ;)

Even so, if pg-promise works for you as it is, it might not make much sense to switch, but if you have the connection slots for it, you can run them side by side to get a feel for it too.

Another thing I remember when starting with pg-promise was the return value helpers, which I used a lot. Now - I think it's much nicer to have the simple API, and simple return value being an array, like Postgres.js does[1]. Especially now that we have destructuring, it just looks like this:

    const [user] = await sql`...`
[1] There's a JS Party podcast where we talk about that as well https://changelog.com/jsparty/221

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#142
post #124

Earlier quoted context omitted.

Re: JSON https://www.postgresql.org/docs/current/datatype-json.html Re: RDBMS was not designed to store JSON https://youtube.com/watch?v=rpw_x8TtqTo

Just because RDBMS have added JSON support (MySQL has had it since 5.6 as well) doesn’t mean it’s a good fit. The language and its implementations are designed for relational data that can be normalized; JSON is neither. Have you ever tested JSON vs. normalized data at scale? Millions+ of rows? I have, and I assure you, JSON loses. Last I checked, Postgres does a terrible job at collecting stats on JSONB - the defaul…

Millions of rows with a JSON column? Yes, indeed I have. Recently in fact. When all normalized fields are populated, you're absolutely right. However that wasn't our dataset. We had entries with sparse keys. This was due to the nature of the data we were ingesting.

We ended with multiple partial expression indexes on the JSON column due to the flexibility it provided. Each index ended up relatively small (again, sparse keys), didn't require a boatload of null values in our tables, was more flexible as new data came in from a client with "loose" data, didn't require us to make schema migrations every time the "loose" data popped in, and we got the job done.

In another case, a single GIN index made jsonpath queries trivially easy, again with loose data.

I would have loved to have normalized, strict data to work with. In the real world, things can get loose without fault to my team or even the client. The real world is messy. We try to assert order upon it, but sometimes that just isn't possible with a deadline. JSON makes "loose" data possible without losing excessive amounts of development time.

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#143
post #112

Earlier quoted context omitted.

Have you tried drizzle? https://orm.drizzle.team/docs/select#aggregations (note: it produces type-safe results too)

Their migrations tool is proprietary and not open-source. The project is basically an open source bait and switch riding off the edge computing hype (since prisma and other major JS ORMs have poor support for edge environments).

You don't need to use drizzle migrations. There are much better migration tools.

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#144
post #127
post #10

Also see: https://github.com/gajus/slonik

Slonik author here. Happy to answer any questions.

Hi gajus! slonik is a very handy tool, and I used it in production for a quiet some years with success.

Is there any plan to move to PostgresJs instead of pg? If not, would you mind explaining why sticking with pg?

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#145
post #5

I feel obliged to namedrop Zapatos here, another postgres JS client with a remarkably similar design (sql in template strings), but fully typesafe. If TypeScript is your thing, you might appreciate it: https://jawj.github.io/zapatos/ Personally I feel it’s one of the best designed (and documented) TS libraries out there and I’m sad it’s not very well known.

Do you know why zapatos relies on pg and not PostgresJs?

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#146
post #126
post #90

Earlier quoted context omitted.

I didn’t know support for schemas had landed, but the docs still seem obtuse for joins and upserts. Compared to plain SQL files in PgTyped — which admittedly needs some annotations to work, and lacks transactions —, I find the lateral joins in Zapatos really verbose and ugly. Lots of extra parameters too. Which isn’t to say it’s not a great tool! You pick what you like :)

Glad you are liking pgTyped. Transactions are supported as pgTyped is just a thin wrapper around node-postgres: https://github.com/adelsz/pgtyped/discussions/448

Ah, I meant having a single transaction in a SQL file, as that’s how we’re using it. So having multiple queries with a BEGIN and COMMIT wrapping them.

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#147

Earlier quoted context omitted.

Wow, a project describing itself as a “Next generation ORM” that… doesn’t do joins, with no way to optimise queries. Perhaps they mean “Next generation” as in, the 1987 TV series sense. Which would still be a long time after the concept of joins…

There's One Weird Trick™ in managing joins in this case -- create views that implement the joins and then just treat it as yet another table. Bonus feature is creating a consistent way of working with that relation using native DB tools. ;-)

that works great till your database gets big. Then you will watch your memory use skyrocket to perform joins into that view and it will F@#$@# your shit up. eventually it will take so much time to construct your view table that you'll tiemout your connection pool.

to be clear, dont' use views in production queries. you WILL regret it.

source: made this mistake.

Re: PostgresJs: PostgreSQL client for Node.js and Deno

#148
post #127

Earlier quoted context omitted.

Slonik author here. Happy to answer any questions.

Hi gajus! slonik is a very handy tool, and I used it in production for a quiet some years with success. Is there any plan to move to PostgresJs instead of pg? If not, would you mind explaining why sticking with pg?

You can already use postgres with Slonik.

https://github.com/gajus/slonik#user-content-slonik-how-are-...

It is not going to be the default because it is way slower.

https://github.com/gajus/slonik/actions/runs/6616647651

Test node_version:18 test_only:postgres-integration is taking 3 minutes.

Test node_version:18 test_only:pg-integration is taking 38 seconds.

It is possible that this is an issue with https://github.com/gajus/postgres-bridge, but I was not able to pinpoint anything in particular that would explain the time difference.

Post reply on HN