10 ms·
Show HN: Postgres.js – Fastest Full-Featured PostgreSQL Client for Node and Deno
- porsager 5y agoHi everyone. A bit more than two years ago I released the first version of Postgres.js. A fully featured PostgreSQL driver for Node.js written as a learning experience out of curiosity and annoyance with the current options. It greatly outperformed the alternatives[1] using pipelining and prepared statements, while providing a much better development experience safe from SQL injections. Since then I've been busy building things using it, now running in production, and although quite delayed I'm so happy to release a new major today with some really exciting new features: 1. Realtime subscribe to changes through Logical Replication [2] It's now possible to use logical replication to subscribe in realtime to any changes in your database with a simple api like `sql.subscribe('insert:events', row => ...)`. Inspired from Supabase Realtime you can now have it yourself in Node. 2. A Safe Dynamic Query Builder Nesting the sql`` tagged template literal function allows building highly dynamic queries while staying safe and using parameterized queries at the same time. 3. Multi-host connection URLs for High Availability support It's really nice to be able to quickly spin up a High Availability Postgres setup using pg_auto_failover[3] and connect using Postgres.js with automatic failover and almost 0 downtime. 4. Deno support It also works with Deno now, completing all tests except a few SSL specific ones which requires fixes in Deno. 5. And much more Large object support, efficient connection handling for large scale use, cancellation of requests, Typescript suport, async cursors. [1] https://github.com/porsager/postgres-benchmarks#results https://github.com/porsager/postgres-benchmarks#results [2] https://github.com/porsager/postgres#realtime-subscribe https://github.com/porsager/postgres#realtime-subscribe [3] https://github.com/citusdata/pg_auto_failover https://github.com/citusdata/pg_auto_failover
- kiwicopple 5y agoCongrats on the release - as I mentioned on twitter, your templating design is the best DX i've seen for any PG client. > Inspired from Supabase Realtime you can now have it yourself in Node. Very cool! We have some Node servers internally, I'll see if the team want to switch.
- porsager 5y agoThanks - that's really nice to hear! That would be really interesting. Are you using `pg` currently, and are you using WebSocket connections in Node to Elixir for realtime, or not using realtime in Node?
- kiwicopple 5y agoyes, we use `pg` in all our Node servers (and no realtime)
- porsager 5y agoAlright. I've got a pg wrapper lying around that can help with migration I can send you ;) I've DM'ed you on twitter, so let me know if that could be useful.
- jadbox 5y agoI hit a small type issue migrating from v2 to v3 that's not listed in the migration: https://github.com/porsager/postgres/issues/283 https://github.com/porsager/postgres/issues/283
- twomoonsbysurf 5y agoSuper cool, looks great. Interesting to see you built it with Typescript. I was wondering-- and I know this is a long story, but I am curious: How does one go about building a PostgreSQL driver for NodeJS in Typescript? How do you design and begin that sort of thing? What sort of knowledge resources did you rely on (i.e. documentation, other references)? I googled 'database driver' to get a definition: "A database driver is a computer program that implements a protocol (ODBC or JDBC) for a database connection. The driver works like an adaptor which connects a generic interface to a specific database vendor implementation."
- porsager 5y agoThanks a lot. I did not built it with Typescript, just plain js - I don't use Typescript at all(not my cup of tee), but a kind soul (thanks @minigugus) contributed the typings ;) I actually began it out of curiosity and because I was missing features in the current options. I had been using a wrapper around pg-promise to give me the API i wanted for quite a while, and one day when going through the PostgreSQL documentation I landed on the protocol (https://www.postgresql.org/docs/current/protocol.html https://www.postgresql.org/docs/current/protocol.html) pages. Now the PostgreSQL documentation might seem daunting at first, but once you figure out the structure and semantics it's such an insanely good resource. I had the first POC version working over a weekend, and from there it was about 80% of the way to support all the things I needed. Now the Postgres protocol is actually fairly simple, and I suppose that's why other databases have chosen to use it as well (eg. Postgres.js also works with cockroachdb). I would love to write a longer post about the progress at some point, but I haven't found the time for it yet.
- domlebo70 5y agoHey. Love this lib. I am curious why you decided against using TS here? You mentioned it's not your cup of tea.
- lukaszkups 5y agoI can only assume that I've finally found a soulmate that just don't like writing TS code. Like, in my full-time job I'm using 100% TypeScript, but I don't really ENJOY writing in it - that's why in my all side-projects I use good old JavaScript. I think it's just a personal syntax preference. But I don't want to answer this for the OP, just added my 2cents, maybe he has similar feeling about it :)
- aPoCoMiLogin 5y agoOne question - is there a way to explicitly start the connection, instead of auto connection at the initialization?
- porsager 5y agoYeah, just make a simple query :) await sql`select 1`
- aPoCoMiLogin 5y agowell.. my bad, somehow I didn't think about that.. anyway this lib looks really cool! definitely will use that instead of hand crafted tagged template functions passed to `pg`.
- whenlambo 5y agoThis is the example of a great nodejs library. Congrats on v3!
- somebee 5y agoWhat a coincidence seeing this on HN. Great lib! I actually experimented with replacing our use of node-postgres with this today. Your lib was the reason I finally got around to shipping support for tagged template literals in Imba today (https://github.com/imba/imba/commit/ae8c329d1bb72eec6720108d2847858d092d4ac7 https://github.com/imba/imba/commit/ae8c329d1bb72eec6720108d...) :) Are you open for a PR exposing the option to return rows as arrays? It's pretty crucial for queries joining multiple tables with duplicate column names.
- porsager 5y agoThanks a lot! What a coincidence - and perfect timing since v3 supports .raw()[1] to receive results as arrays. I'd also be very curious to hear how replacing pg goes :) And also good job on Imba! I'm a really big fan of stripping syntax down to the bare essentials, and what you've done with Imba is really impressive! [1] https://github.com/porsager/postgres#raw https://github.com/porsager/postgres#raw
- mpfundstein 5y agowow i really love this. will try it out tmrw in a new greenfield project.
- rubyist5eva 5y ago``` const users = await sql` select name, age from users where age > ${ age } ` ``` Do template literals like this in JS work differently than just straight up string interpolation? As a rails dev this set off alarm bells.
- porsager 5y agoYeah, when a function is called as a "tagged template literal"[1] the function takes control over how the parameters are handled. Postgres.js uses this to replace the value with $1, $2, etc and send over the value as parameters to the database, thereby preventing any chance of SQL injection[2]. [1] https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals#tagged_templates https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe... [2] https://github.com/porsager/postgres#await-sql---result https://github.com/porsager/postgres#await-sql---result
- rubyist5eva 5y agonice!
- lowwave 5y agogreat to use template lilerals, but any way to use in perpare statement in order to escape sql injection etc type of stuff?
- lowwave 5y agoJust saw this on the page: >Parameters are automatically extracted and handled by the database so that SQL injection isn't possible. No special handling is necessary, simply use tagged template literals as usual. Dynamic queries and query building can be seen in the next section. // todo this is great! Can't wait to give a try.
- throwthere 5y agoLooks good from what I see. How does this compare with slonik? Also what do the error messages look like?
- porsager 5y agoSlonik is a wrapper around another Node.js postgres driver (pg). I guess slonik could be built on top of Postgres.js, but I'm not sure what the benefit would be since you get the same and more from Postgres.js. If you have an existing project using slonik it might benefit from the better performance if replacing pg with Postgres.js underneath (https://github.com/porsager/postgres-benchmarks#results https://github.com/porsager/postgres-benchmarks#results)
- toastercat 5y agoExcellent work as usual, Rasmus! :) Loved my time with postgres.js in past projects, looking forward to checking out v3 in an upcoming project of mine. The speed and lack of any dependencies, while being so full-featured is really attractive.
- porsager 5y agoHey keb_ :) Thanks a lot!
- starsol 5y agoIs raw using binary protocol? (for ex: send\receive UInt8Array Buffer without converting to hex string for bytea column)
- porsager 5y agoNo, that unfortunately didn't make it into this release. I want to add that in a future update, first at least for bytea, but then also for other types that benefit from it.
- 999900000999 5y agoVery cool! Would you consider this production ready ? I rarely if ever use relational databases for my personal projects, but this could be a great fit at work. Also , anyway to add an orm layer. I use Firebase right now, so I'm use to that vs writing queries.
- porsager 5y agoThanks. Definitely production ready, and I've been using it for quite a while in production myself, towards this release. About the ORM layer I wouldn't know, since I much prefer to avoid that at any cost.
- 999900000999 5y agoOk, I'd have to double check to see if the Unlicense is ok for work projects, but this is a great contribution to the node ecosystem.
- porsager 5y agoI actually replaced WTFPL with Unlicense since someone at google couldn't use it, so I would think it's ok?
- 999900000999 5y agoAny reason you can't use MIT. For hobbyist projects Unlicense is great, but it may create issues with bigger companies. I think the main sticking point, is public domain that doesn't really exist in countries like Germany, so the unlicensed is unclear. Companies don't like unclear things. Facebook uses MIT, and their tools like React and Jest are cornerstones of the NodeJS ecosystem. https://github.com/facebook/react/blob/main/LICENSE https://github.com/facebook/react/blob/main/LICENSE
- pier25 5y agoHow can this be so much faster than even pg-native that uses the C library libpq? https://github.com/porsager/postgres-benchmarks#results https://github.com/porsager/postgres-benchmarks#results
- jerrysievert 5y ago> How can this be so much faster than even pg-native that uses the C library libpq? the c++ to javascript membrane in v8 is expensive to pass. there are many projects that are faster implementing protocols in javascript vs c/c++ because of this. redis is another.
- eyelidlessness 5y agoThis can’t be overstated. Crossing the JS<->native barrier is inherently a perf hit. I’ve measured it trying a large variety of high performance messaging protocols and postMessage with structured clone nearly always wins. If that sounds like a bold claim, there are troves of Node and Deno issues where they’ve improved perf by staying in JS specifically because calling into native and back has been the bottleneck. There are countless similar issues in V8 and WebKit as well. You can get a perf boost with WASM or NAPI, but only for workloads which are CPU bound and where you expect to do that work in compiled code.
- w1nk 5y agoI responded to the OP, but I'll add here too for the conversation. Where are these projects that are implementing network protocols, where the nodejs versions are faster than the native ones? As I noted in my other comment, in -any- of those implementations, you're still going to be required to traverse from libc somewhere to nodejs (even if it's just to read the network data out of the socket and send it to v8). The performance gains would have to either come from a faster (JIT'd) protocol parser, or from eliminating additional FFI calls (that it isn't obvious why they'd exist). So, would you mind linking to one or some of those projects? I'd love to see what happened to the implementations.
- ruffrey 5y agoThanks porsager. Any plans for Knex support?
- hn_throwaway_99 5y ago> Any plans for Knex support? That question doesn't really make sense. I'm not familiar with this library, but the developer interface is very similar to slonik. The whole point of this is that SQL tagged template literals replace the need for a query builder like Knex. Here is a good blog post from the author of slonik explaining: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
- deleted 5y ago[deleted]
- spiffytech 5y agoI'm broadly a fan of just writing SQL, but Knex does have one important advantage: it automatically declares the return types from a query based on my DB structure. If I write SQL by hand, I have to annotate the query with the return types, and those annotations can get out of sync with the query.
- hn_throwaway_99 5y agoSlonik has a typegen package that works great, https://www.npmjs.com/package/@slonik/typegen https://www.npmjs.com/package/@slonik/typegen
- whenlambo 5y agoPostgres.js does the same, transparently. No advantages of Knex here.
- porsager 5y agoYeah, I think it could make sense for performance reasons to make a version for Knex, but since I try to stay away from ORMs as much as possible I'm probably not the right one to make it ;) I also wouldn't put it in the core library anyway, so it should be straight forward for someone else to make a knex-postgres package that does this?
- jordonias 5y agoI spent some time today trying to replace pg but I ran into an issue. When using an rds proxy with iam authentication, it seems to repeatedly retry authentication and eventually my lambda functions time out. If I switch to using regular credentials it works fine. Using the exact same options with pg+iam authentication also works fine which leads me to believe it's an issue with this project. I'll open an issue on GitHub tomorrow.
- porsager 5y agoThat's interesting - haven't heard of that issue before, so do please create an issue on github or let me know if you figure it out ;)
- lowwave 5y agoBe very interesting to know if it is the rds proxy that is causing the problem. It is one things that annoys me about rds and aws services.
- porsager 5y agoIt was indeed the RDS Proxy. It appers to be very strict about the client_encoding parameter. Postgres.js was sending 'utf-8' which PostreSQL will understand, but RDS Proxy would just hang until this was changed to UTF8 (uppercase with no dash). https://github.com/porsager/postgres/issues/288 https://github.com/porsager/postgres/issues/288
- elnygren 5y agoI’m curious, how does this project compare to slonik that is also a fast, lightweight, full featured, using SQL template literals etc ? Is there any reason to consider switching, for example?
- porsager 5y agoThere is quite a big difference, but I'll highlight some of the main points here. Note I'm the author of Postgres.js so I'm obviously biased. Slonik is a wrapper around another node driver (pg), so it's performance is the same as that, where Postgres.js is significantly faster (2-5x)[1]. Postgres.js is also a zero dependency module, whereas Slonik has quite the dependency graph meaning - compare https://npmgraph.js.org/?q=slonik https://npmgraph.js.org/?q=slonik with https://npmgraph.js.org/?q=postgres https://npmgraph.js.org/?q=postgres. That makes it more difficult to audit the code and preventing chain attacks etc. Slonik also doesn't have the same lean straight forward developer experience as Postgres.js - again I'm biased saying that ;) Postgres.js also does things that will make your queries perform better out of the box by implicitly creating prepared statements and using pipelining. I suppose you could build Slonik on top of Postgres.js instead of pg as well, but that would probably only make sense if migrating to Postgres.js. [1] http://github.com/porsager/postgres-benchmarks#results http://github.com/porsager/postgres-benchmarks#results
- sdoering 5y ago> Postgres.js is also a zero dependency module, whereas Slonik has quite the dependency graph meaning - compare https://npmgraph.js.org/?q=slonik https://npmgraph.js.org/?q=slonik with https://npmgraph.js.org/?q=postgres https://npmgraph.js.org/?q=postgres. This one just made my day. Thanks. I remember trying to build a tool at work with as little as possible dependencies (in python) and how satisfying it was to see quite a few dependencies just being wrappers replaces with 5 lines of my own code that i could easily audit and ensure no supply chain attack was possible for that functionality.
- juhanakristian 5y agoThis looks great! I really like the idea of using tagged template literals for queries.
- Existenceblinks 5y agoHere is the bundlephobia of this: https://bundlephobia.com/package/postgres@3.0.0 https://bundlephobia.com/package/postgres@3.0.0 33.6 kB Minified 12.5 kB Minified + Gzipped
- porsager 5y agoHey - that's pretty nice :) Didn't think of trying that - curious if it includes the cjs and esm version in the sum, since that's double of almost identical code.
- Existenceblinks 5y agoOh you think it should be just ~ 6kB min.gz? I think the site looks for `package.json` and find es6 style entrypoint. It should only build from a single entry. --- EDIT: according to https://github.com/pastelsky/bundlephobia/discussions/546 https://github.com/pastelsky/bundlephobia/discussions/546 they say it looks for `main` entry.
- kamilafsar 5y agoVery interesting project! The source is inspiring, it seems the author has made an explicit choice to minimize dependencies. Even transpilation from ES modules into commonjs is done by the author himself: https://github.com/porsager/postgres/blob/master/transpile.cjs#L32 https://github.com/porsager/postgres/blob/master/transpile.c... I’m not sure if I’d made the same choice, but it’s fun the see it can work out great :)
- ttty 5y agoTbh I think this is quite risky to mess it up. Using a simple regex to convert… https://github.com/porsager/postgres/blob/master/transpile.cjs#L32 https://github.com/porsager/postgres/blob/master/transpile.c...
- Rygian 5y agoManipulating source code via regex... I wonder what could possibly go wrong.
- porsager 5y agoThe results are run through the same tests ;)
- toastercat 5y agoWith NPM being such a security nightmare everytime you rope in another dependency, and myself being a long-time user of postgres.js (and much of porsagers' other work in the Node community), I do think this was the right decision.
- gmac 5y agoThis looks really nice. The template strings and querying work somewhat similarly to my own library, Zapatos[1] (but that sits on top of the pg package and is TypeScript-specific, being more focused on having everything typed). [1] https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
- fabiospampinato 5y agoThe logo is amazing!
- joshxyz 5y agoMan I've been surprised with the v3 updates in my inbox in the past few days (subscribed as watching in github repo). Thanks for your work @porsager!
- ecmascript 5y agoWow this is very cool and comes with a perfect timing for me since I am starting a new project. Thanks a bunch!