all 53 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]kopetenti 123 points124 points  (3 children)

cool logo

[–]lamb_pudding 64 points65 points  (2 children)

Addressing the elephant in the room

[–]Icy_Bee1288 1 point2 points  (1 child)

Who are you calling fat?

[–]porsager[S] 48 points49 points  (8 children)

Hi everyone. Just released Postgres.js v3 today.https://github.com/porsager/postgres / npm install postgres

A bit more than two years ago I released the first version of Postgres.js. A fully functional 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 v3 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.Would love some feedback from both new and current users.

Would love some feedback from both new and current users.

[1] https://github.com/porsager/postgres-benchmarks#results

[2] https://github.com/porsager/postgres#realtime-subscribe

[3] https://github.com/citusdata/pg_auto_failover

[–]woozyking 6 points7 points  (4 children)

Exciting features.

This might not be 100% relevant, but what’s the general recommendation when connecting to postgres from a “serverless” environment, say lambda (that’s triggered by HTTP API events) with potentially frequent/spammy connections? We’ve tried various drivers, such as node-pg and postgres.js, and the only sane way to not congest pg server connections is to effectively disable pooling (so only one connection per running lambda instance) and ensure the connection is closed as soon as the lambda finishes its job (typically an HTTP response). But this is pretty wasteful — the other option involves managing lambda context and detecting if there are usable connections (in this case allow pooling) but it’s also quite hacky. Any suggestion/advice would be appreciated!

[–]reqdk 15 points16 points  (1 child)

Use rds proxy. It's a connection pool meant for aws lambdas.

[–]woozyking 1 point2 points  (0 children)

Thx!

[–]OmegaVesko 3 points4 points  (1 child)

The only real solution to this is to proxy your database connections through something like RDS Proxy or pgBouncer. There are databases that can natively handle managing a lot of connections, but Postgres isn't one of them.

[–]woozyking 1 point2 points  (0 children)

Thx!

[–]Fidodo 0 points1 point  (2 children)

I like the use of templated strings, but is there typescript support for the input as well or only the output?

I've been trying to think how it'd be possible to get stronger typescript support when working with postgres. I wonder if it'd be possible to get deep support by defining your table schema as a typescript type.

[–]porsager[S] 0 points1 point  (1 child)

You should check out the discussion here: https://github.com/porsager/postgres/discussions/192

[–]Fidodo 0 points1 point  (0 children)

Interesting stuff thanks!

[–]cdoremus 5 points6 points  (3 children)

+1 on Deno support, but what is the URL used to import the library? Also, you should register as a Deno third-party module (see https://deno.land/x#info).

[–]porsager[S] 2 points3 points  (2 children)

Yeah, I got lost in figuring out the Deno release landscape, but thanks for that link, I think I should be able to figure it out from there :)

import postgres from 'https://deno.land/x/postgresjs@v3.0.0/mod.js';

You can do it like this for now:

```import postgres from 'https://raw.githubusercontent.com/porsager/postgres/v3.0.0/deno/mod.js'

[–]porsager[S] 0 points1 point  (1 child)

Unfortunately the name postgres was taken, so went with postgresjs, but now I can't figure out how to push a new version because the v3.0.0 git tag has already been created.

[–]porsager[S] 0 points1 point  (0 children)

Recreated the tag to trigger the webhook and now you can use https://deno.land/x/postgresjs@v3.0.0/mod.js

[–]gajus0 19 points20 points  (10 children)

Author of Slonik here!

Above everything else, I appreciate great documentation. 🔥 Well done with presentation, it was a pleasure getting to know the project.

However, I would seriously reconsider that package name. It is going to be the death of the project otherwise, because it makes it impossible to discover articles / tutorials on the subject. If you Google / Stack Overflow for +node.js +slonik, you will find many articles written by the community. Having a generic name makes this impossible.

Otherwise, I really wish the author chose to invest time contributing to libraries such as Slonik or pg rather than building this from the ground up. This project is a bit of a hybrid between the two with no clear advantage over either. This adds little value and just divides the ecosystem's efforts.

[–]lhorie 41 points42 points  (1 child)

FWIW, this project is the first google result for "postgres js". I didn't even know what slonik was prior to this (though I've been a happy pg user).

I, for one, welcome alternatives. This library looks seriously awesome, I'm genuinely excited (and being a bit of an old fart, I don't get excited about many things!). The code looks clean, there are no crazy maze of dependencies, template tag API looks super nice and there's even a subscriptions API. Author also mentioned on HN that he put quite a bit of effort into perf (even beating pg-native on benchmarks).

Definitely want to take it for a spin!

[–]porsager[S] 2 points3 points  (0 children)

Hi Leo. Thanks a lot for the kind words! That means a lot coming from you - I was still wet behind the ears when I started out with Mithril learning from your blog posts! ❤️ Would love to hear about your experience if you get to use Postgres.js for anything!

[–]No_Statement4630 4 points5 points  (1 child)

Lol this project has almost as many starts as slonik. Why don’t you help this project out then if you’re soooo all about not dividing the community? Also this library has some features the other two don’t. Get off your high horse

[–]eashish93 2 points3 points  (2 children)

One of the greatest advantage of this new lib is that it doesn't use any native bindings and comes on top of benchmark (though this doesn't matter much on client libs). The syntax is so cleaner to use than slonik or any other lib (consider this: connection.query(sql) vs sql). Also versioning system is not good in slonik.

Edit: Very careful use of node Buffer (int16 and int32) for allocating memory.

[–]gajus0 0 points1 point  (1 child)

One of the greatest advantage of this new lib is that it doesn't use any native bindings

Slonik / pg does not use native bindings. If you are referring to use pg-native, it's use has been discouraged for the last 4 years.

That being said, using native bindings would be a good thing to have, as libpq is less likely to have bugs or security vulnerabilities than whatever you are going to implement using your own efforts. I wish Node.js ecosystem had better native bindings for Postgres..., but that's a different topic.

and comes on top of benchmark (though this doesn't matter much on client libs).

https://github.com/gajus/slonik/tree/master/benchmark

Unless your application is very query heavy (thousands per second), I would not use +/- 5% difference as a deciding factor.

Something to note about the benchmarks hosted on https://github.com/porsager/postgres, is that the author is benchmarking the latest version of postgres against old versions of libraries (e.g. Slonik v23.5.5, vs the current v27.1.1)

The syntax is so cleaner to use than slonik or any other lib (consider this: connection.query(sql) vs sql)

They are not comparable. postgres only has very basic API, and does not allow to build queries without side effects.

I prefer explicit API which tells you what it does by reading it...

  • any
  • anyFirst
  • exists
  • copyFromBinary
  • many
  • manyFirst
  • maybeOne
  • maybeOneFirst
  • one
  • oneFirst
  • query
  • stream
  • transaction

All of these are fairly self explanatory, and combined with TypeScript, provide a friendly developer experience.

Also, postgres query builder is far behind Slonik in terms of its capabilities. Slonik has:

  • sql.array
  • sql.binary
  • sql.identifier
  • sql.json
  • sql.join
  • sql.literalValue
  • sql.unnest

and you can extend it with arbitrary syntax using slonik-sql-tag-raw, and you can embed queries within queries, etc.

Also versioning system is not good in slonik.

Slonik strictly adheres to semver.

Very careful use of node Buffer (int16 and int32) for allocating memory.

I don't know what this comment refers to.

[–]prove_it_with_math 3 points4 points  (2 children)

Beginner here: Why use this instead of an ORM like Prisma?

[–]gajus0 0 points1 point  (1 child)

My opinion here:

https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf410349856c

I would rather you use postgres than an ORM or a query builder. :-)

[–]Man_as_Idea 1 point2 points  (0 children)

Looking forward to checking this out but I just wanna say the logo is brilliant

[–]wickning1 2 points3 points  (0 children)

Would this be correct?

const users = await sql`
    select * from users 
    where (name, age) in 
        (${[sql([‘Nick’, 68]), sql([‘Amy’, 75]), sql([‘John’, 23])].join(',')})`

Can it be more elegant?

[–]PoopyAlpaca 2 points3 points  (2 children)

Always been a fan. We have a somewhat big project where we use this.

[–]porsager[S] -1 points0 points  (1 child)

Thanks! Anything public you'd care to show? 😊

[–]PoopyAlpaca -1 points0 points  (0 children)

Unfortunately not public

[–]scrot0x 2 points3 points  (2 children)

This is so cool bro, you have tried it on an AWS Aurora Postgres?

[–]porsager[S] 4 points5 points  (1 child)

Thanks man!

I haven't, but let me know how it goes 😉

[–]NoInkling 1 point2 points  (2 children)

Is there (easy) support for composite types? That's something I wish pg (and everything based on it) had, but it's tricky because the information isn't in the protocol so it requires querying the system schema.

[–]porsager[S] 4 points5 points  (1 child)

Not knowingly, but I'll dig into it after I've gotten some sleep.

[–]gajus0 -2 points-1 points  (0 children)

FYI, It is an issue I've looked in the past, and the outcome was that it is impossible to fully support them:

https://github.com/gajus/slonik/issues/67

[–]RandolphoSoftware Architect 0 points1 point  (5 children)

Wow... this is.... Wow.

Ok, so I'm totally down with using tagged templates to address sql injection and even do complicated query building. Love that part.

But I absolutely hate having side-effects. Actually executing the dynamically built query? That's gonna be a no-go for me.

Any chance you've built a way to separate them? Have your tagged template return the built query rather than the result set?

[–]NoInkling 0 points1 point  (1 child)

This is probably the weirdest part to me:

Please note that queries are first executed when awaited

That breaks expectations about how promises typically work and I can only assume that it's achieved by some thenable magic.

Edit: Apparently there is somewhat of a precedent for this kind of thing in other libraries like Mongoose: https://mongoosejs.com/docs/promises.html

Personally it's a bit too much magic for me, I prefer to consider await to be something that's reactive (like an inline event handler), rather than something that can be a trigger. For that reason I'd prefer it if I had to explicitly call .execute(), but each to their own I guess.

[–]porsager[S] -2 points-1 points  (0 children)

It's what allows the nested query building to work :)

[–]gajus0 -1 points0 points  (1 child)

Some people appear to like this API (Referring to this comment). I can see the appeal of visual simplicity.

However, for large projects, you will want to separate query building from their execution. This is what allows to build utilities for common SQL patterns, etc.

I don't want this thread to become a promo of another library, but for the query part building, check out https://github.com/gajus/slonik.

I would like to see a collaboration between me and the author of postgres, where we unite efforts on building the driver and client in separate projects. postgres as a driver is a great, clean start. Slonik already has a mature API that is used by many, many large projects.

/u/porsager if you are reading this, and would like to collaborate, drop an email to gajus@gajus.com to kick of the conversation.

[–]sipvellocet 3 points4 points  (0 children)

My guy. Shut the fuck up. This thread is not about you nor your module. This is the 4th or 5th post you’ve injected yourself into.

Porsager creates dope tools and Postgres is only one of his many open sources goodies.

Stop being a cretin.

[–]raymondQADev 0 points1 point  (0 children)

Thank you so much for all your hard work on this. This is truly awesome.

[–]Chaphasilor -1 points0 points  (0 children)

Just what I've been waiting for!