I finally found the answer myself.

Turns out the easiest way is to use the URL function from sqlalchemy.engine where you can pass in the SSL mode.

from sqlalchemy.engine import URL


# PostgreSQL connection string
SQLALCHEMY_DATABASE_URI: str = URL.create(
    drivername="postgresql+asyncpg",
    username=DB_USER,
    password=DB_PASSWD,
    host=DB_HOST,
    port=5432,
    database=DB_NAME,
    query={
        "ssl": "require"
    }
)