I am working on one a Python application which uses a FastAPI, SQLAlchemy, Pydantic/psycopg2, and Uvicorn stack. The project is structured in a way similar to the “Bigger Applications – Multiple Files” by FastAPI at fastapi[dot]tiangolo[dot]com/tutorial/bigger-applications/, where an SQLAlchemy MetaData named Base is created that contains all of the application’s tables as well as an SQLAlchemy Engine object named engine. In the main function, I then call:
Base.metadata.create_all(bind=engine)
which is run by the uvicorn bash command.
When running this application with a single ASGI worker, starting up for the first time is fine, but I have noticed that if I try and use multiple workers (i.e. --workers 4, some of them will try and execute this call before the first caller has marked that the MetaData is created. This results in the error:
sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "pg_class_relname_nsp_index"
DETAIL: Key (relname, relnamespace)=(accounts_id_seq, 2200) already exists.
[SQL:
CREATE TABLE accounts (
id SERIAL NOT NULL,
PRIMARY KEY (id)
)
]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
which is not necessarily unexpected but is undesired. The full stack trace can be found at github[dot]com/mwhicks-dev/pyacct/issues/15.
I can run my server with one worker on the first use with a clean Postgre DB, but that seems like more of a workaround than a solution.
Is there a fix for this problem that I have overlooked?
There is a checkfirst flag that can be passed to create_all., ie. create_all(bind=engine, checkfirst=True) If you just rebuild your database everytime you deploy that would probably work. Although this won’t detect changes to pre-existing tables:
If you need to evolve with your app and database over time (the normal case) then you probably want to run alembic to upgrade your database to the latest version as part of your deployment process. In the initial case that would be building all the tables.
If you didn’t want to use alembic and checkfirst wasn’t working then I would just create a CLI command in your app that creates all the databases and run that before you even start uvicorn and it forks out all the processes.