12 ms·
PostgresJs: PostgreSQL client for Node.js and Deno
- jasfi 3y agoThe benchmarks linked to don't compare PostgresJs to Prisma. That would be useful. Also the link text (Fastest full-featured node & deno client) should include the word benchmark, for those looking for one.
- porsager 3y agoThere's a link to a simple Benchmark right after the title. Here are two: https://github.com/porsager/postgres-benchmarks https://github.com/porsager/postgres-benchmarks https://porsager.github.io/imdbench/sql.html https://porsager.github.io/imdbench/sql.html
- pier25 3y agoIt's not even close. Prisma is extremely slow. Take a look at the original IMDB benchmarks on which the Postgres.js benchmarks are done. https://github.com/edgedb/imdbench#javascript-orms-full-report https://github.com/edgedb/imdbench#javascript-orms-full-repo... https://porsager.github.io/imdbench/sql.html https://porsager.github.io/imdbench/sql.html
- surprisetalk 3y agoPostgres.js is one of the best libraries I’ve ever used! Its use of templating in JS is really intuitive.
- qwertox 3y agoI was wondering how old this project is: 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.
- promiseofbeans 3y agoI've been using it in production (Deno) for a while. It's pretty solid, but their documentation is sorely lacking, so you often have to feel your way to getting more complex things to work.
- twosdai 3y agoCould you comment on what you're using deno in prod for? I'm curious to see and learn more about deno for production cases. I'm a big ts and nodejs user, and deno always seemed interesting to me. However I am afraid of being an early mover, so I like to learn more about use cases people have validated.
- promiseofbeans 3y agoWe use it for processing real-time UDP streams, as well as storing the data in postgres (as well as a hosting small API for exporting said data). We also use it to re-publish the UDP streams in a WebSocket format, for consumption by web applications.
- skrebbel 3y agoI 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/ 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.
- willtemperley 3y agoAt first glance Zapatos doesn't appear to be Cloudflare workers compatible though I'd love to be corrected.
- gmac 3y agoIt should be, now that pg runs fine there. If it’s not, please file an issue! 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 https://github.com/neondatabase/neon-vercel-zapatos
- 9dev 3y agoSeconded. The lateral join features in combination with the shortcut functions make it exceptionally easy to build backends with actual SQL, without compromising on ease of use.
- xctr94 3y ago
- rco8786 3y agoThe demo gif is fabulous.
- porsager 3y agoThanks a lot!
- iddan 3y agoFor people who would like a more safe and structured approach (but almost as powerful and with the option to resort to SQL strings if needed) I highly recommend checking out Prisma https://www.prisma.io/ https://www.prisma.io/
- sgarland 3y agoPrisma doesn't do JOINs [0], it just SELECTs everything and joins them in its own engine. It should not be taken seriously. [0]: https://github.com/prisma/prisma/discussions/12715 https://github.com/prisma/prisma/discussions/12715
- halfmatthalfcat 3y agoAre there no times this would be preferable? Just curious. It seems like the DX Prisma provides could potentially outweigh the need for joins, especially if you don’t have a ton of referentiality. I’ve been a very happy Prisma user for a couple years now and take it very seriously fwiw.
- sgarland 3y agoTake this with the bias of a grumpy SRE/DBRE. 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.
- noduerme 3y ago>> Prepared statements will automatically be created for any queries where it can be inferred that the query is static 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...
- seanhunter 3y agoPostgres supports 2 types of prepared statements - "generic" prepared statements (which can accept parameters) and "custom" which are prepared for a specific execution so bake the parameters in. https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html explains it. Read the section called "Notes" for the plan types.
- latch 3y agoIf you're using parameterized queries, then you _have_ to use PostgreSQL's "Extended Query" flow, which is what most people would think of as a "prepared statement". This is hardly opinionated. 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/bf082a5c0ffe214924cd54752a7aeb4e618d279b/src/connection.js#L184 https://github.com/porsager/postgres/blob/bf082a5c0ffe214924...
- noduerme 3y agoThanks... I haven't got the mental energy to follow their code ATM but yeah, it seems weird to buffer a static query as a prepared statement if it's only going to be used once. 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.
- sheaton 3y agoAlso see: https://github.com/gajus/slonik https://github.com/gajus/slonik
- indeyets 3y ago+1. I’m a happy user of slonik for several years. Zod integration is very useful
- hn_throwaway_99 3y agoTotally agree. Slonik has had typed queries for some time now, but the integration with zod means that those types are verified at runtime. Incredibly useful with Typescript.
- gajus 3y agoSlonik author here. Happy to answer any questions.
- jokesterfr 3y agoHi 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?
- gajus 3y agoYou can already use postgres with Slonik. https://github.com/gajus/slonik#user-content-slonik-how-are-they-different-postgres-vs-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 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 https://github.com/gajus/postgres-bridge, but I was not able to pinpoint anything in particular that would explain the time difference.
- terraplura 3y agoPerhaps not as featured as OP’s but I’ve found https://pgtyped.dev/ https://pgtyped.dev/ useful. Queries are written as SQL files, and pgtyped creates typed functions you call with query parameters.
- ttfkam 3y agoImpressive
- joshxyz 3y agoreposting my comment on its benchmarks: 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 - https://github.com/porsager/postgres/discussions/627 https://github.com/porsager/postgres/discussions/627 - https://porsager.github.io/imdbench/sql.html https://porsager.github.io/imdbench/sql.html
- mhh__ 3y agoWhat does fast actually mean specifically? The slow bit isn't the client, surely?
- postalrat 3y agoIt means once again JavaScript is faster than rust.
- satvikpendem 3y agoThat is still unknown, as the Rust implementation is not on the benchmarks page.
- wredue 3y agoSorry, but you’ve been fooled by the web and functional programmers propaganda against performance. 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.
- dboreham 3y ago> The slow bit can definitely be the client. Yes, but for 99% of "slow database" scenarios : no.
- hhfdsbvc 3y agoB very careful with json and postgressjs, there are strange bugs. Also there are various gotchas on the edges with typescript. AFAIK the code uses a lot of regex and that breaks json in particular.
- porsager 3y agoUff. Author here. Care to share more? There's no regexp related to json handling, maybe you're thinking of the dynamic query helpers? If not could point to an issue or create one on the repo it'd be great.
- Etheryte 3y agoThis is all very vague and hardly stands up to any kind of scrutiny. Do you have any concrete examples of what you mean?
- claytongulick 3y agoI've been using postgresjs in production for a while now with jsonb etc... and haven't run into any issues. What have you seen that's been a problem?
- collaborative 3y agoI use pg in Node and these results are impressive
- jzig 3y agoI wonder if this (or Zapatos) could replace Supabase’s postgrest-js, or how it compares.
- codesnik 3y agoreplace how? it can complement it, probably, in edge functions, but replace? 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?
- fatih-erikli 3y agoI like the idea of using tagged templates for sanitizing sql query inputs. Kudos
- Coxa 3y agoSuper happy user of this [1] it's rather minimalistic but great. [1] https://github.com/Ff00ff/mammoth https://github.com/Ff00ff/mammoth
- darkest_ruby 3y ago[dead]
- gavinray 3y ago[flagged]
- deleted 3y ago[deleted]
- patricius 3y agoReally deep analysis. Thanks!
- porsager 3y agoI think you should just keep a thing like this to yourself. It's attitudes like this that keeps people from daring to share their code with the world. 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.
- gavinray 3y agoIf you find it beautiful, who cares what I think? 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.
- porsager 3y agoMost people care to some degree what other people say about them / their work. 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.
- sgarland 3y agoIt’s the giant nested ternaries that kill me. In nearly all circumstances I can think of (Terraform being a notable exception), ternaries could be replaced with an if/else if block and be far more readable. 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?
- hestefisk 3y agoLooks neat. Is there a table generation / migration library included?
- porsager 3y agoNo but I'm using this simple forwards only little side library https://github.com/porsager/postgres-shift https://github.com/porsager/postgres-shift
- brap 3y agoCan anyone explain to me the purpose of tagged template strings? From what I understand it’s only a function that takes in a string, so this might have just been sql(‘…’).
- whenlambo 3y agoYou can use expressions inside template literals and those are auto-escaped for you by postgres.js
- brap 3y agoWhat do you mean? This is true for any template string, not specifically the tagged template strings, no? Edit: I think I understand now from the other comment, thanks!
- joshuacc 3y agoTags are functions that receive an array of strings and then a series of arguments corresponding to values passed in the interpolation slots, allowing you to use template literal syntax while the tag function does something much more complex under the hood, potentially including returning non-string values.
- brap 3y agoOhhh got it, thanks!
- crubier 3y agoAlso, template strings like sql`foobar`, graphql`foobar` and others enable syntax awareness in IDEs. 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
- christophilus 3y agoThis is a great library. I’ve been using it in production for a while (since 2019, IIRC). No complaints.
- foreigner 3y agoShout out to https://www.npmjs.com/package/sql-template-strings https://www.npmjs.com/package/sql-template-strings, which does a similar template string SQL escaping standalone.
- 8n4vidtmkvmk 3y agoHad to check if I wrote that lib. I wrote pretty much the same thing, not sure if I published it standalone...
- redbell 3y ago> Had to check if I wrote that lib.. 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 :)
- erhaetherth 3y agoOh, that happens to me quite a bit (I'm a top 500 SO user). I find some great question or answer and then try to upvote it and then get confused why it won't let me.
- netcraft 3y agoAnd my own similar version https://github.com/hagerty-dev/pitwall-pg https://github.com/hagerty-dev/pitwall-pg
- pier25 3y agoUsed pg for years then migrated to postgres.js about two years ago. I love it and have had zero issues.
- wg0 3y agoWhat will it take for it to run directly in browser?
- DrBenCarson 3y agoCommitment to a bad idea
- killingtime74 3y agoIf you really want a database call direct from browser, use Firebase or something like it designed for the use case.
- lolovaldez 3y agoHave to say, if you like to do bare metal SQL, using the nice features of Postgres, working with this lib is a total Joy! 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.
- cultofmetatron 3y agoI'd love to see a library like ecto (eilxir) for javascript. its easily the best toolkit for working with sql I've ever found. simple, powerful and easy to reason about
- jmondi 3y agoCurious what you think makes it better than other examples out there? How is it different than Active Record or Prisma?
- cultofmetatron 3y agothe list would merit its own dedicated blog post but to highlight a few nice parts, ecto lets me write sql code in elixir. it does not try to wrap it all into objects but I do get schemas. so for instance I can create a schema `Player` which maps to a table "players" 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.
- Rapzid 3y agoThis library is proper fast. Don't get distracted comparing this to other libraries just on the basis of similar string template syntax; it doesn't have a dependency on pg because it implements its own low level client. I've built systems on this loading multiple 10k records at time and it crushes.
- Rapzid 3y agoTo expand on my own reply.. 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!
- dang 3y agoRelated: Show HN: Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno - https://news.ycombinator.com/item?id=30794332 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 https://news.ycombinator.com/item?id=21864245 - Dec 2019 (12 comments)
- andrewstuart 3y agoTerrible name, makes it impossible to search on. 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.
- hluska 3y agoTry searching: 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.
- pyrolistical 3y agoI like how it has zero deps
- geniium 3y agoRare enough to highlight it
- porsager 3y agoAuthor here. Worth noting is that Cloudflare is now also supported :)
- laurels-marts 3y agothis library looks incredible. well done!
- somecommit 3y agoplease consider sequelize, I'm an old developer and I don't miss at all writing sql statement
- sam_goody 3y agoBeen 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 https://github.com/vitaly-t/pg-promise
- porsager 3y agoAuthor 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 https://changelog.com/jsparty/221