PostgresJs: PostgreSQL client for Node.js and Deno
github.com
github.com
No “fancy” and bleeding abstractions like something like Prisma, not convoluted type annotations like other TS libs, no, just plain SQL statements in the best JavaScript way of doing it.
I discovered learning to do Postgres a couple years ago, after getting sick of trying to hack Prisma, tried out several others, and after a couple minutes using this, i knew I wasn’t going back.
I can then get them via ``` from(p in Player) |> where([p], p.id in ^player_ids) |> select([p], p) |> Repo.all()
```
need to join with an association?
```
from(p in Player) |> join([p], s in assoc(p, :scores), as: :score) |> where([p], p.id in ^player_ids) |> preload([p, score: s], [score: s]) |> select([p], p) |> Repo.all() ```
this gets me all players with their scores.
need to get the sum of scores for all players?
```
from(p in Player) |> join([p], s in assoc(p, :scores), as: :score) |> where([p], p.id in ^player_ids) |> select([p], %{ player_id: p.id, score: sum(score.value) }) |> group_by([p, score: s], [p.id]) |> Repo.all() ```
as you can see, you can unwind this pretty fast to sql in your head. but you can also let the system just grab the kitchen sink when you jsut need to get somethign working fast. and its all very easy to modify and decuple.
this is hardly exhaustive though, I could spend a day talking about ecto. I spent 8 years developing in nodejs but now I work exclusively in elixir. unless what you're doing is 90% done by existing libraries, its juts a better tool for building highly performant backends.
(note: it produces type-safe results too)
Perhaps they mean “Next generation” as in, the 1987 TV series sense. Which would still be a long time after the concept of joins…
to be clear, dont' use views in production queries. you WILL regret it.
source: made this mistake.
I vastly prefer it over ecto, and ecto is awesome.
<3 Entity Framework Core but also love the new interfaces that let me cast a db connection to Npgsql connection and use its advanced features and raw performance.
It would be great if postgresjs could underpin Knex and MikroOrm.
If snarky, is this based on something that actually happened to you? If so, I would love to hear how that actually went about, and what it is you are unable to grasp when things aren't bogged down by complexity?
If earnest, I'm glad more people prefer code that isn't littered with premature abstractions, redundant types, and useless comments expressing what can be more clearly read in the actual code.
I've built systems on this loading multiple 10k records at time and it crushes.
Can load 10-20k records(ymmv) in 20ms or under where it would otherwise take 150ms. That can be a game changer based on use case by itself, but if you are used to thinking about what's viable in Ruby or etc it's even more inspiring.
It also supports logical decode which opens a lot of doors for processing transitional events.
Very impressive project; don't sleep on it!
Show HN: Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno - https://news.ycombinator.com/item?id=30794332 - March 2022 (83 comments)
Show HN: Postgres.js – Fast PostgreSQL Client for Node.js - https://news.ycombinator.com/item?id=21864245 - Dec 2019 (12 comments)
What does this mean in practice? Like, actual prepared statements are created against the DB session if there are no bound variables, even if that query is only made once?
If so, it's an interesting but highly opinionated approach...
https://www.postgresql.org/docs/current/sql-prepare.html explains it. Read the section called "Notes" for the plan types.
But normally, you use an unnamed prepared statement and/or portal, which PG will clean up for you, essentially only letting you have one of those per session (what we think of as a connection).
I agree that sentence didn't make any sense. So I looked at the code (1) and what they mean is that they'll use a named prepared statement automatically, essentially caching the prepared statement within PG and the driver itself. They create a signature for the statement. I agree, this is opinionated!
(1) The main place where the parse/describe/bind/execute/sync data is created is, in my opinion, pretty bad code: https://github.com/porsager/postgres/blob/bf082a5c0ffe214924...
Maybe that kind of goes with a Nodejs philosophy, though? It seems like an assumption that in most cases a static query will recur... and maybe that's usually accurate with long running persistent connections. I'm much more used to working in PHP and not using persistent connections, and so sparing hitting a DB with any extra prepare call if you don't have to, unless it's directly going to benefit you later in the script.
you'll probably find this bad code too, but it was more of an experiment... I still don't feel safe using nodejs in deployment.
https://github.com/joshstrike/StrikeDB/blob/master/src/Strik...
It is entirely ordinary with an API like that to prepare a statement, bind parameters and columns, and execute and fetch the results. You can then reuse a statement in its prepared state, but usually with different parameter values, as many times as you want within the same session.
The performance advantage of doing this for non-trivial queries is so substantial that many databases have a server side parse cache that is checked for a match even when a client has made no attempt to reuse a statement as such. That is easier if you bind parameters, but it is possible for a database to internally treat all embedded literals as parameters for caching purposes.
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.
PgTyped was the alternative, and 2 years later I’m very glad we made the switch.
CREATE SCHEMA a;
CREATE SCHEMA b;
CREATE TABLE a.foo (id SERIAL PRIMARY KEY);
CREATE TABLE b.foo (id SERIAL PRIMARY KEY);Which isn’t to say it’s not a great tool! You pick what you like :)
It also works with @neondatabase/serverless on platforms without TCP connections (though it’s on my TODO list to make this less fiddly): https://github.com/neondatabase/neon-vercel-zapatos
v1.0.1 - Jan 2020
v2.0.0 - Jun 2020 but never left beta.
v3.0.0 - Mar 2022, which appears to be when the project really got started.
I also wonder how solid it is, because it looks very interesting.
Its use of templating in JS is really intuitive.
The problems start immediately when you realize it’s interchangeably called “Postgres”.
Any other name would have been better as long as it’s distinctive.
Even so I use it, best Postgres javascript library.
npm install postgres
My search results are always excellent. Google and DuckDuckGo deliver the documentation as first result. Bing delivers a stackoverflow answer about pg as the first result but the documentation is the second result.
I don’t really see any other problems or rather the problems are common and most developers have developed habits (like the search pattern above) to solve them.
This reminds me of a friend of mine.. He got struggled with a programming-related task. When he gave up, he turned to StackOverflow, where he found the exact answer there, ready to be copy/paste. Ironically, it turns out that he was the one who answered it a few years back :)
Also the link text (Fastest full-featured node & deno client) should include the word benchmark, for those looking for one.
Here are two:
Take a look at the original IMDB benchmarks on which the Postgres.js benchmarks are done.
https://github.com/edgedb/imdbench#javascript-orms-full-repo...
https://github.com/porsager/postgres/discussions/627#discuss...
200x :O
Is there any plan to move to PostgresJs instead of pg? If not, would you mind explaining why sticking with pg?
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.
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`...`
From what I understand it’s only a function that takes in a string, so this might have just been sql(‘…’).
Edit: I think I understand now from the other comment, thanks!
I have a big Typescript / SQL mixed codebase (No ORM), and its very nice to have VSCode syntax color and code-format TypeScript, GraphQL and SQL in the same file
Postgrest-js is something you can put in your code running in client's browser or phone, or in any place you cannot trust code won't be tampered, thanks to many quirks and limitations of PostgREST.
How to make an actual postgres connection, which is simultaneously limited enough to be secure and safe to your data and data of other users, and still be useful for anything, I have no idea. Is it possible at all? Or, ok, how many different users it could handle?
The only reason to not support JOINs is because of the added complexity. However, since ORMs are supposed to (among other things) reduce the complexity for the user, not the DB, this seems like a poor decision.
There are other ORMs for the JS world. I can't imagine that they're all so much worse than Prisma as to render them non-choices.
> outweigh the need for joins
At toy scale, sure, it doesn't matter. Nothing matters - you can paginate via OFFSET/LIMIT (something else I've seen Prisma unexpectedly do) and still get moderately acceptable query latency. It's embarrassing, though, and will not scale to even _moderate_ levels. A simple SELECT, even one with a few JOINs (if they're actually being performed in the DB) should be executed in sub-msec time in a well-tuned DB with a reasonable query. In contrast, I've seen the same taking 300-500 msec via Prisma due to its behavior.
> especially if you don’t have a ton of referentiality
Then frankly, don't use RDBMS. If what you want is a KV store, use a KV store. It will perform better, be far simpler to maintain, and better meets your needs. There's nothing wrong with that: be honest with your needs.
If you were to do this with joins, you'd have a ton of duplicated data returned (via returning every column from every table to have all the relevant data) and you'd have to manually reduce the rows into the data structure you'd want; either in the db or in biz logic. You may be able to swing it somehow using Postgres's json functions but then it gets _super_ messy.
Prisma avoids that by just requesting the relevant level(s) of data with more discrete queries, which yes, result in _more_ queries but the bottleneck is really the latency between the Rust Prisma engine and the DB. Again, we're sacrificing some speed for DX, which imo, has made things much cleaner and easier to maintain.
You can also use cursors for pagination, which is definitely in their docs.
I see your points but unless there's some extreme network latency between your app(s) and the db (which can be minimized by efficiently colocating stuff) 300-500ms seems extreme. I would be curious if you logged out Prisma's queries and ran them independently of the client, whether you see the same latency.
ARRAY_AGG for Postgres or GROUP_CONCAT for MySQL does what you’re asking without duplicating rows.
Re: JSON, don’t. RDBMS was not designed to store JSON; by definition you can’t even get 1st Normal Form once its involved.
IMO, claims of DX regarding SQL just mean “I don’t want to learn SQL.” It’s not a difficult language, and the ROI is huge.
Again, not saying you _cant_ do it with current db constructs but Prisma deals with all that for you while allowing escape hatches if you need them. Just like with anything related to software engineering, there are footguns a plenty. Being aware of them and taking the good while minizing the bad is the name of the game.
Knowing SQL can help inform the choices a dev makes in the ORM - for example, knowing about semi-joins may let you write code that would cause the ORM to generate those, whereas if you didn’t, you may just write a join and then have to deal with the extra columns.
Re: RDBMS was not designed to store JSON https://youtube.com/watch?v=rpw_x8TtqTo
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 default type - so query plans aren’t great. Indexing JSON columns is also notoriously hard to do well (even moreso in MySQL), as is using the correct operators to ensure the indices are actually used.
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.
I’m begging devs to learn the absolute basics of SQL and relational algebra. It isn’t that hard.
This has been much discussed in many GitHub issues. People get stuck on joins because it's how they've always done it. Same with now Vercel convinced everyone they need SSG and not just a cache
If what you want is NoSQL, then use NoSQL.
Maybe it's time to test the above package or maybe knexjs (I like up/down migrations and Prisma doesn't do 'down'). I'm certainly considering not using Prisma going forward.
Thanks for bringing this up.
We just use db push because prisma migrate fucked up in some unknown way that is impossible to recover from. The process in the docs for re-baselining doesn't work, and we're not about to reset our database.
The problem with ORMs is always that if something goes wrong you need to understand why, but then you need to also understand what the orm is doing. So it doesn't really abstract the database away, it just makes it easier to use. Or if you don't understand your database you're SOL. I mean, there was a big transaction isolation bug that you now have to deal with...which if you knew anything about databases you would have dealt with anyway.
Also, they pack a bunch of prebuilt binary handlers with their stuff, which makes using it awkward in serverless code.
What do you use for up/down migration if you don't mind sharing?
The db push deals with constraints, indexes, types, etc. we do everything else.
On what makes it postgres.js faster, from author himself:
> it seems Postgres.js is actually faster than, not only pg, but of any driver out-there
Also, what the author seems to mean is that this is faster than specific python, js and go clients.
Anyway. At the IO boundary, there’s a lot of syscalls (polls, reads, mallocs especially), and how you manage syscalls can be a major performance sticking point. Additionally, serialization and deserialization can have performance pitfalls as well (do you raw dog offsets or vtable next next next). How you choose to move memory around when dealing with the messages impacts performance greatly (ie, creating and destroying lots of objects in hot loops rather than reusing objects).
The slow bit can definitely be the client.
Yes, but for 99% of "slow database" scenarios : no.
“IO therefor slow” is a “myth”. IO is slow, but the 1,000 millisecond request times from the server you’re sitting beside is not slow because of IO.
When experts say “IO is slow”, they’re not saying that you should therefor disregard all semblance of performance characteristics for anything touching IO. They are saying that you should batch your IO so that instead of doing hundreds of IO calls, you do 1 instead.
```
EdgeDB (Python)
PostgreSQL (Pyhton, psycopg2)
PostgreSQL (Python, asyncpg)
PostgreSQL (Node.js, postgres)
PostgreSQL (Node.js, pg)
PostgreSQL (Go, pgx)What have you seen that's been a problem?
What you might find ugly, others find beautiful. I'm actually pretty proud of this lib, and i enjoy my style of coding. Your comment doesn't change that, it just kills a lot energy.
You aren't writing this for public opinions, presumably.
I've also written a Postgres wire protocol implementation and a blog post on how to do so, which people are welcome to say "It's terrible" about if they wish.
The fact that you've also dabbled in this area could lead to a far more interesting discussion. Wouldn't mind seeing your blog post. Who knows, maybe I've already read it at some point?
btw the reason for extending eg Array is to make for a much better surface API when using the library.
Being able to do const [user] = await sql`...` leads to some very readable concise code.
In my head I’m having to cast these to an if to figure it out anyway - why not just write it like that to begin with?