Error "Forbidden concurrent usage of the query runner was detected when it tried to get a database connection from the pool"

[DGCarramona]

Hello, I've just got error "Forbidden concurrent usage of the query runner was detected when it tried to get a database connection from the pool". What does exactly mean that error ? Does it mean I cannot execute multiple queries in parallel ? What if I receive multiple http calls for instance ? Is there a specific param to set to allow it ? I'm using PgPoolQueryRunner and instantiating connexion as const connexion = new DBConnection(new PgPoolQueryRunner(pool)); (DBConnection extends PostgreSqlConnection)

[juanluispaz]

Hi,

DBConnection represents a dedicated connection even when they are coming from the pool. Please create a new DBConnection instance for each HTTP request you receive.

Please let me know if that works for you.

[DGCarramona]

Thank you for the answer, sorry I didn't realized it while reading the documentation.

[juanluispaz]

Don't worry 😄

I'm thinking to put a noter in every query runner that receives a connection pool.

If you have any idea how to improve it, please share with me.

[DGCarramona]

I'm still seeing something strange. At the start of the main function I create the query runner with the pool, then I create a dedicated connection just before executing a new query but I still have the aforementioned error.

[juanluispaz]

Ensure you have it per request base and as well you are not running query in parallel. It is easy to forget an await and start doing queries in parallel agains your database.

[DGCarramona]

I'm creating the connection right before executing the query so even for a same http query, there will be multiple connections (one for each query). I'm sure there is no missing await. If I create each time a connection AND a query runner it works, issue arises only when using same query runner even for multiple connections.

[juanluispaz]

Can you tell me What QueryRunner are you using?

We usually create the DBConnection (with all its arguments, that means all query runner path) when the request is received in the server and we put in the context.

Example:

function createDBConnectionInstance() {
    // NOTE: PGPool can be global, but, DBConnection, neither any query runner can be global
    return new DBConnection(new ConsoleLogQueryRunner(new PgPoolQueryRunner(pool)))
}

[DGCarramona]

I'm using PgPoolQueryRunner. Maybe I'm not fully understanding the meaning of the connection and the query runner. Reading the documentation you pointed me at, I've understood that the connection is the concrete current connection to the database, which means the query runner is kind of an orchestrator between the connection and the sql builder, isn't it ? Otherwise, what it the point of using PgPoolQueryRunner instead of PgQueryRunner ?

[juanluispaz]

PgPoolQueryRunner extracts the connection from the pool the first time you execute a query and keeps it until it is no longer required. Database connections (the ones in the pool) are not intended for concurrent use, so all the chains in ts-sql-query, like DBConnection and any QueryRunner, are not valid for concurrent usage.

Please be aware that PgPool contains some handy methods like pool.query, which, under the hood, extracts a connection from the pool, executes a query, and returns the connection to the pool. They are very useful for basic situations. If you don't open a transaction, PgPoolQueryRunner extracts and releases the connection automatically but in a stateful way (not valid for concurrent usage).

Under the hood, PgPoolQueryRunner calls pool.connect(), even when you are not in a transaction, the connection is extracted and released to the pool.

I'm exploring removing this restriction sung a LocalStorage (in Node) to track the transaction status, but that will be for late ts-sql-query 2.0, where I will not need to keep supporting old node versions. I'm working in the 2.0 version right now, but what I'm commenting will not be on the first 2.x release.

Understand the new DBConnection (and all its arguments) as pool.connect() call.

Let me know if this helps.

[DGCarramona]

I'm still struggling to fully understand the purpose of the query runner and its difference with the connection but indeed this helps.

[juanluispaz]

There are several reasons:

1. It allows you to put a chain of interceptors for the query executions; in the previous example, I put ConsoleLogQueryRunner; in our development, we have a more complex thing that allows us to track and monitor what is happening with our queries, collect information to detect when the system is not behaving properly. See InterceptorQueryRunner. In our projects, we have several interceptors. See Query execution metadata for some ideas.

2. It allows you to create tests mocking the database without changing your code; in that case, a real PgPool or PgConnection will not be used. We prefer tests that run against a real database, but this can be useful if you do more unit testing instead of integration testing; we do it occasionally, and I know several ts-sql-query users are using it heavily.

3. It allows you to have multiple implementations depending on the connector you want to use; ts-sql-query 1 allows you to connect to PostgresSql using 9 different ways (Pg represents only two of them). Yes it can be archived using 9 different kind of PostgreSqlConnection, but you will understand that will be messy.