How to do persistent database connection in fastapi?

@raphael_tillman 

To create a persistent database connection in FastAPI, you can use a database client library like asyncpg for asynchronous communication with PostgreSQL database. Here's a step-by-step guide on how to create a persistent database connection in FastAPI:

1. Install asyncpg library: You can install the asyncpg library using pip:

1

pip install asyncpg

1. Create a database configuration file: Create a separate file (e.g., database.py) to store your database configuration settings. Here's an example of how you can set up the database connection pool using asyncpg:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

import asyncpg

async def create_db_pool():
    pool = await asyncpg.create_pool(
        user='your_username',
        password='your_password',
        database='your_database',
        host='localhost'
    )
    return pool

1. Create a dependency for database connection: In your FastAPI application, you can create a dependency that establishes a connection to the database pool as follows:

1
2
3
4
5
6

from fastapi import Depends
from .database import create_db_pool

async def get_db_pool():
    db_pool = await create_db_pool()
    return db_pool

1. Use the dependency in your route handlers: You can now use the get_db_pool dependency in your route handlers to access the database connection pool:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

from fastapi import FastAPI, Depends
from .database import create_db_pool

app = FastAPI()

@app.get("/users")
async def read_users(db_pool = Depends(get_db_pool)):
    async with db_pool.acquire() as connection:
        result = await connection.fetch("SELECT * FROM users;")
        return result

By following these steps, you can create a persistent database connection in FastAPI using asyncpg library. Make sure to handle database connections properly to avoid connection leaks and ensure proper resource management.