Alembic autogenerate empty migration file

Question

I'm trying to connect the alembic library to the databases and sqlalchemy libraries. As a guide, I use this example link

My projects file:

db.py

from databases import Database
from sqlalchemy import MetaData, create_engine

DATABASE_URL = "postgresql://....@localhost:5432/db"

engine = create_engine(DATABASE_URL)
metadata = MetaData()

database = Database(DATABASE_URL)

models.py

from sqlalchemy import Table, Column, Integer, String, DateTime
from sqlalchemy.sql import func

from db import metadata

notes = Table(
    "notes",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("title", String(50)),
    Column("description", String(50)),
    Column("created_date", DateTime, default=func.now(), nullable=False),
)

env.py (alembic settings)

from db import DATABASE_URL, metadata

....
#add new
target_metadata = metadata
...
#change
def run_migrations_online():

    config.set_main_option('sqlalchemy.url', str(DATABASE_URL))
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

When I run

alembic revision --autogenerate -m 'Add notest table'

the new file at migrations/versions this context is created

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###

I suppose it may be related to the use of the target_metadata = metadata variable. It seems to be all according to instructions, but the migrations do not work as expected.

Yolk

Answer 1

If anyone has a similar problem. All you have to do is import the tables from models.py into the env.py file before metadata object.

env.py

...
from models.notes import notes
from db import DATABASE_URL, metadata
...

Yolk

If they are not imported those classes are never defined and metadata is never populated. – Nalley

@GabrielCappelli but what shall I do in the case when I have dozens or even hundreds of models? Import all of them? Maybe there is a better way than from mymodels import *? – Fractionize

You could try to import those dynamically. #301634 – Nalley

Answer 2

I have a different solution (or hit a different problem), in case anyone hits this problem. My issue was that the database had already been created and I was adding alembic afterwards. Because it was looking actively at the database and diffing it against the code I kept getting blank migrations. So creating an empty db as the starting point was able to then yield my initial migration.

Hospodar

Answer 3

I had the same problem when running my FastAPI server using uvicorn with --reload flag (which restarts/reloads the server on save/changes applied).

Problem

The problem is that blank migrations are generated when you create a revision and tables are already created (using init_db()).

in main.py:

from app.db.database import init_db

init_db()

in /db/database.py:

def init_db():
    

Solution

• Do not create/instantiate your db using Base.metadata.create_all(bind=engine).
• This is because each time you apply changes to your models.py or just configure env.py (which is a Python file that will cause the server to restart when you save it), the database will be created by the server before you try to add a new revision (remember Base.metadata.create_all(bind=engine) will be executed).

Hardner