pg-promise VS PostgreSQL

Personally with more time I would also use JavaScript so I can improve it. Last time I wrote something with Express and a database connection is a long time ago and not for work, only for my personal testing. But for JavaScript database connection to PostgreSQL I recommend pg-promise. That works like a charm.

I've always loved this project / product:

https://github.com/outline/outline

I think the guy who built it posts here. There's also pg-promise, a "Postgres interface for Node.js":

https://github.com/vitaly-t/pg-promise

Raw pgPromise with string templating.

i do have a couple of them like websocket server using ws, bullmq for sending emails and postgres listen/notify to push data to websocket clients. Would you happen to have any ideas on how to go about these? I did some research

Yes. You can also take at the pg-promise library. https://github.com/vitaly-t/pg-promise

Is there any difference to the named parameters already supported? https://github.com/vitaly-t/pg-promise#named-parameters

During my years as a dev i have really started to dislike ORMs. They always fail in the end. SQL is universal, and transfers between languages and tech fields. This is why im pro-sql, and always try to avoid unnecessary abstractions.

I have actually went back to writing pure SQL in files, and using those as params for whatever db engine i use, this makes it even possible to reuse the code in other projects (even its unlikely that you can use the exact same query, but just as a "it would work" in theory).

For node based projects i have used and would probably still choose pg-promise (https://github.com/vitaly-t/pg-promise).

Since I'm decent at writing SQL queries and I don't want to learn additional syntax to work with ORMs/query builders, I stay as close as possible to the database driver. pg-promise is an excellent library built on top of the Node.js driver for PostgreSQL. It's built with promises in mind, it's well-tested and typed.

dont believe me? look at how many open issues they have https://github.com/vitaly-t/pg-promise/issues

I just used pg-promise - it handles the connections for you, up to you to write the SQL.

pg doesn't do too well with serverless, dead connections are left in the pool (or something)

https://github.com/brianc/node-postgres/issues/2112

If you don't want to use ORM then there are some other packages as well! For PostgreSQL we have node-postgres

Interface with PostgreSQL database

(Instead of the following with pg.)

So far every JS framework that uses https://node-postgres.com works great and so no reason to think Drizzle wouldn't.

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.

For newer projects I use the small Postgres client. Initially my leap into SQL was lead by AI but as I refreshed and relearned SQL, I now use a mixture of AI and self-written SQL queries. Something like this is just easier to have AI do the grunt work and then adjustment as needed.

This was a known issue for pg developers, and they managed to fix it a long time ago (at the pg level), but the knowledge of this problem didn't reach Knex maintainers.

Integrate the database into your application itself with a postgres client library allowing your applications to make infrastructure changes (like provisioning sharded resources for a client that wants isolation, or using a more accurate forecasting model to pre-allocate more resources before the storm hits).

node-postgres (raw sql, without ORM)