Improve documentation on when and why should we use `pool.connect(connection => { ... })` about slonik HOT 3 CLOSED

I too have read and re-read too and I think I understand where @latobibor is coming from.

The note about being primarily a front-end dev helped me understand the perspective (or at least I think so, please let me know!).

@latobibor my friend I think you are thinking of back-end vs. front-end in the classic web development HR resume keyword sense i.e. backend is like Express app and client aka front-end is like React.

From that angle, yeah, wtf!.

To adjust your angle, forget that web browsers even exist and put yourself in a database library's shoes. Also I think it is helpful to consider why the variable and type names are "pool", "DatabasePool", etc.

A client connection is between a database client and either the database / database cluster or the database cluster’s connection pool (such as pgbouncer). Whenever anything connects to postgres the server will fork a new process (aka a backend to postgres DBA's) to handle it.

Its common to use postgres with connection pool managers like pgbouncer or pgpool-II. You can read a bit about those here (Neon is a postgres provider and at this link they explain how they use pgbouncer): https://neon.tech/docs/connect/connection-pooling

Now imagine you are the mighty @gajus who created slonik for serious CTO level IT problems at scale and you think in terms of database clusters, connection pools, multiple backend database servers (master / slave etc.), replication, etc., etc. The big guns!

In this light it is a very good warning in the docs that you might require a persistent connection to the same backend depending on what setup you have.

I think realistically you will always want to use this syntax so the connection is automatically released:

pool.connect(async (connection) => {
  await connection.query(sql.typeAlias('id')`SELECT 1 AS id`);
});

However... now imagine your Node/Express backend (i.e. backend in the context I think you originally had in mind) and imagine you have split up your code to have different repositories, services, etc.

Now imagine the lifecycle of servicing a single request where many of those different repositories / services are tapped to service the request (e.g. maybe the request has something to do with auth, users, etc).

If each of those repositories, services, etc in play independently had their own pool.connect(...) then each might end up using a different backend (in the context of postgres) and that could be a problem for you. There could be implications with transactions and there could also be extra overhead to grabbing and releasing connections many times.

This is really what the docs are warning you about here.

Some of the setups I've seen for integrating with Fastify, Express, etc, effectively do a pool.connect() at the start of the request and then make the connection available throughout the rest of the lifecycle of the request (so then any repositories/services/etc. that follow would use that same connection to avoid potential problems).

I hope I nailed where your point of confusion was and that this answers your question.

All said, I think your question does raise an important point about the clarity in the docs.

After writing this all I feel I can see how both you and gajus are totally right in your respective understanding of "backend" :)

from slonik.

I read and re-read your comment and I still cannot wrap my head around what you are asking of which part is not clear.

How to create a connection?

await pool.connect(async (connection) => {
  // This is your connection object:
  // connection;
});

from slonik.

Hope this helps @latobibor https://dev.to/gajus/integrating-slonik-with-expressjs-33kn

from slonik.