Common Lisp Mito DB Error: Connection to database server lost

I'm using mito in a common lisp project to connect to a postgres database. I have a request that runs many times on the DB and I'm getting the following two errors:

DB Error: Connection to database server lost.
DB Error: This connection is still processing another query.

Is there a way to check if the connection to the database is found AKA "not lost" before trying to execute a query?

Is there a way to check if the connection to the database is busy executing another query? If so, how could I wait for it to finish? Similar to await in JS.

It doesn't seem correct that the connection is lost because later requests work if they are not so many of them so close to each other.

To add context: I'm doing this inside a caveman2 project and it could be that for a given session from the browser, the server allocates one thread to process a specific route and because of that, that given thread for that client session for that route has access to only one connection. I'm not sure if this is right or the cause of this issue. Any help would be appreciated!

Edit:

To add more context: My DB setup is like this(redacted):

(setf mito:*connection* 
        (apply 
          #'connect-cached 
          (:postgres 
           :database-name "name" 
           :username "username" 
           :password "*****")))

So it's using connect-cached from cl-dbi. I would assume mito uses a connection pooling by default like postmodern's :pooled-p t. I couldn't find it on the docs nor by searching the repository for "pool".

Only if many requests are sent for the same code do I get the errors above. The mito query is quite simple

(mito:find-dao 'user :username given-username)

Where given-username is a user provided string...

Solution

it could be that for a given session from the browser, the server allocates one thread to process a specific route and because of that, that given thread for that client session for that route has access to only one connection.

I think this is an accurate depiction of what is happening.

The exception comes from: cl-postgres/public.lisp:L328, in macro using-connection, which disable using the same connection while processing a request. The documentation says:

This might also raise an error when you are using a single database connection from multiple threads, but you should not do that at all.

The solution, as hinted by mito/issues/40, is to use dbi:connect-cached in each thread.

The documentation at CL-DBI/Connection pooling says:

dbi:connect-cached returns a existing connection if the database is already connected. Since one cache will be created for each thread, it's safe to use in a multithread application.

So you may be interested in writing your own wrapper macro, like:

(with-db (db)
  ...)

That expands as:

(let ((db (dbi:connect-cached ...)))
  ...)

If later you need to add more code around the body you can update the macro and all the places that use it will be updated after recompilation. You can create a new connection for each request, but it seems to me that creating a thread and a db connection per request might not scale well and is easily subject to DDOS attacks. Maybe you could have a pool of workers and a pool of database connections instead.