How to persist a connection pool for asyncpg and utilise it in Databases wrapper?

Question

As per FastApi documentation, I'm using the Databases wrapper and Sqlalchemy Core to do async operations on the postgres database.

I have come across an issue where the connection gets closed in the middle of operation. As it turns out it is an issue with asyncpg and can be resolved by using a pool.

However I'm not using asyncpg directly, but use the Database wrapper as it was recommended by FastAPI. How can I create a pool like this:

await asyncpg.create_pool(database="dbname", 
    user="username", 
    password="dbpw",
    max_inactive_connection_lifetime=3)

and utilise it within the databases wrapper?

import databases
from sqlalchemy import MetaData

db = databases.Database(settings.SQLALCHEMY_DATABASE_URI)
metadata = MetaData(schema='main')

score 1 · Accepted Answer · answered Jun 27 '21 at 15:26

Under hood database.connect uses asyncpg.create_pool, your SQLALCHEMY_DATABASE_URI already has connection vars, you could also add additional connection options:

db = databases.Database(settings.SQLALCHEMY_DATABASE_URI, max_inactive_connection_lifetime=3)

They will be passed to asyncpg.create_pool on connection.

How to persist a connection pool for asyncpg and utilise it in Databases wrapper?

1 Answers1