Can't connect PostgreSQL database to FastAPI

Question

So, hi. Everything works with SQLite, but when I try to add PostgreSQL according to the user's guide on FastAPI, nothing works and I get:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) invalid dsn: invalid connection option "check_same_thread"

My database.py is:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

#SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Josh · Accepted Answer · 2022-07-06 12:54:55Z

24

check_same_thread is an argument specific to sqlite. As you've specified a Postgres URL, you can remove that argument and you should have no issue creating an engine.

i.e:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

engine = create_engine(SQLALCHEMY_DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

answered Jul 6, 2022 at 12:54

Josh

2,0061 gold badge14 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Serhei Over a year ago

Thanks for reply! Unfortunately still doesn't work

Serhei Over a year ago

Now it is

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "postgres" to address: Temporary failure in name resolution

H. Sinan Alioglu · Accepted Answer · 2022-07-06 19:59:53Z

0

SQLAlchemy needs a little bit different dsn. To make sure, use PostgresDsn from pydantic.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from pydantic import PostgresDsn

SQLALCHEMY_DATABASE_URI = PostgresDsn.build(
    scheme="postgresql",
    user="POSTGRES_USER",
    password="POSTGRES_PASSWORD",
    host="POSTGRES_SERVER",
    path=f"/{'POSTGRES_DB' or ''}",
)
engine = create_engine(
    SQLALCHEMY_DATABASE_URI,
    pool_pre_ping=True,
)
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine
)
try:
    db = SessionLocal()
    db.execute("SELECT 1")
except Exception as e:
    raise e

answered Jul 6, 2022 at 19:59

H. Sinan Alioglu

112 bronze badges

Collectives™ on Stack Overflow

Can't connect PostgreSQL database to FastAPI

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related