node-postgres

node-postgres (pg, probably more specifically pg-pool) doesn't do too well with serverless, not handling TCP connections killed while in the pool (or something): https://github.com/brianc/node-postgres/issues/2112 (open since 2020) A nice marketing opportunity for Neon/Supabase to get a fix officially released.

One thing that keeps coming up is that SQL equals low productivity. I don't think this is true. I think the culprit is that most developers are using to heavily abstracting SQL using ORMs like Prisma that hides the database and SQL logic. Since building a SQL generator (https://aihelperbot.com) as a side project, I have become much more proficient in SQL and even though I am also locked into Prisma, I use the `queryRaw` all the time to execute raw SQL queries. You can understand the code without knowing Prisma API. It is more performant. For more complex SQL queries, I use the SQL generator for initial suggestions and adapt if needed. For the next projects I build I want to use the minimal Postgres client (https://github.com/brianc/node-postgres) combined with a lightweight migration library.

Looking at the dependencies, I realized that it is now common to implement database bindings purely in the host language (vs. using vendor provided C/C++ SDK.) Deno PG [1] and MySQL [2] does it. This makes sense considering Deno's security model. But Node libs do the same [3][4]! This also kinda make sense, as node-based JS has to be async most of the time. Still it's such a hassle. Anyways, kudos to both communities. You've done a lot! - [1] https://deno.land/x/postgres@v0.11.2 - [2] https://deno.land/x/mysql@v2.9.0 - [3] https://github.com/brianc/node-postgres/tree/master/packages... - [4] https://github.com/mysqljs/mysql

Great to see this package here. We have been using slonik for over a year now with few regrets. I am not a fan of query builders. Query builders work for simple inserts or updates. For really complex queries, nothing beats writing SQL inside of the sql template tag. Another nice thing about slonik is that it is built on top of https://github.com/brianc/node-postgres. One gets all the benefit of node-pg with the nicer dev experience of slonik.

No. When you start a transaction, you hold onto the particular connection you started it with, and that connection is busy in your connection pool and won't be assigned to unrelated queries. See for instance: https://github.com/brianc/node-postgres/wiki/Transactions (If you're just saying "other unrelated queries could happen between opening the transaction and closing it," well, yes, but that's true of any web application, whether it uses threads or events.)

Yes there's connection pooling. But it's handled by the underlying PG library https://github.com/brianc/node-postgres that Massive is based on.

RE: expand array for in-clause, its definitely not supported automatically by node-postgres: http://stackoverflow.com/questions/10720420/node-postgres-ho... https://github.com/brianc/node-postgres/issues/431 which I confirmed: promise catch err: { [error: invalid input syntax for integer: "{"45","56","33"}"] but I can make this work by altering my 'sql' tagged template string function, and inspect for typeof <var> array and automatically expand. I wonder if there are there other purposes for arrays other than the in-clause syntax that I should check for.

I'm a big fan of ORMs in other ecosystems (Rails and Django, for example), but in node I like to write more functional-style code, composing small modules into larger applications. For this, check out the fantastic node-postgres adapter from brianc as well as his node-sql package for authoring SQL statements. https://github.com/brianc/node-postgres https://github.com/brianc/node-sql

Mongo - Sure. I'm hardpressed to find genuine reasons to use MongoDB these days. Postgres provides everything I need in a persistent data store and has a ton of other stuff I didn't know I'd need (till you do!). Mongo was great to use with something like node.js where the async/schema-less model translates perfectly but using Postgres with the async driver[1] isn't bad either. Add in hstore, JSON support, plV8 ... and what exactly would somebody use Mongo for? :D Redis - No clue what you're saying here. If you're using Redis then you're keeping your data in memory and dealing with low level structures. It's for completely different use cases. A classic example is rate limiting for an API. Doing it in Postgres with a disk I/O per request would cripple any semi-popular API. The data doesn't need to be exactly persistent (if we miss a few up

I just found node-postgres and it looks okay. Active development and issues look like they're not major. Do you have any experience with it? https://github.com/brianc/node-postgres EDIT: Whoops, its clearly just a client and not an ORM. How about https://github.com/dresende/node-orm2 instead?

Dang. Yeah - Definitely should have said there's not JSON type known to Node PG, which is actually incorrect as of this morning: https://github.com/brianc/node-postgres/pull/239 Thanks for pointing out the error!

PostgresSQL's libpq supports nonblocking asynchronous operation, and node-postgres takes advantage of that. http://www.postgresql.org/docs/9.1/static/libpq-async.html https://github.com/brianc/node-postgres/blob/master/src/bind... (notice the Connect method on line 325 of binding.cc) At some level a client-server database driver isn't all that different from any other network client; you send a request over a socket and wait for a result. There's no reason you have to block while waiting. Moreover some databases (like Postgres) let you receive asynchronous notifications signaled by transactions on other connections; that's how trigger-based replication systems like Bucardo do their thing. http://www.postgresql.org/docs/9.1/static/sql-notify.html