I am working on an application using sqlalchemy, postgres and alembic.
The project structure is as follows:
.
├── alembic.ini
├── main.py
├── migrations
│ ├── env.py
│ ├── README
│ ├── script.py.mako
│ └── versions
├── models
│ ├── base.py
│ ├── datamodel1.py
│ ├── datamodel2.py
│ └── __init__.py
└── requirements.txt
3 directories, 10 files
Where:
the content of models/base.py is :
from sqlalchemy.ext.declarative.api import declarative_base, DeclarativeMeta
Base: DeclarativeMeta = declarative_base()
The content of models/datamodel1.py is :
from models.base import Base
from sqlalchemy.sql.schema import Column
from sqlalchemy.sql.sqltypes import String, Date, Float
class Model1(Base):
__tablename__ = 'model1_table'
model1_id = Column(String, primary_key=True)
col1 = Column(String)
col2 = Column(String)
The content of models/datamodel2.py is :
from models.base import Base
from sqlalchemy.orm import relationship
from sqlalchemy.sql.sqltypes import String, Integer, Date
from sqlalchemy.sql.schema import Column, ForeignKey
# The many to may relationship table
class Model1Model2(Base):
__tablename__ = 'model1_model2_table'
id = Column(Integer, primary_key=True)
model_1_id = Column(String, ForeignKey('model1.model1_id'))
model_2_id = Column(Integer, ForeignKey('model2.model2_id'))
class Model2(Base):
__tablename__ = 'model2_table'
model2_id = Column(Integer, primary_key=True)
model2_col1 = Column(String)
model2_col2 = Column(Date)
# Many to many relationship
model1_model2 = relationship('Model1', secondary='model1_model2_table', backref='model1_table')
The content of migrations/env.py is :
from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
import sys
sys.path.append('./')
# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
# I added the following 2 lines to replace the sqlalchemy.url in alembic.ini file.
db_string = f'postgresql+psycopg2://{db_username}:{db_password}@{db_host}:{db_port}/{db_name}'
config.set_main_option('sqlalchemy.url', db_string)
# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)
# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
from models.datamodel1 import Model1
from models.datamodel2 import Model2, Model1Model2
from models.base import Base
target_metadata = Base.metadata
# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
def run_migrations_offline():
"""Run migrations in 'offline' mode.
This configures the context with just a URL
and not an Engine, though an Engine is acceptable
here as well. By skipping the Engine creation
we don't even need a DBAPI to be available.
Calls to context.execute() here emit the given string to the
script output.
"""
url = config.get_main_option("sqlalchemy.url")
context.configure(
url=url,
target_metadata=target_metadata,
literal_binds=True,
dialect_opts={"paramstyle": "named"},
include_schemas=True,
)
with context.begin_transaction():
context.run_migrations()
def run_migrations_online():
"""Run migrations in 'online' mode.
In this scenario we need to create an Engine
and associate a connection with the context.
"""
connectable = engine_from_config(
config.get_section(config.config_ini_section),
prefix="sqlalchemy.",
poolclass=pool.NullPool,
)
with connectable.connect() as connection:
context.configure(
connection=connection,
target_metadata=target_metadata,
include_schemas=True
)
with context.begin_transaction():
context.run_migrations()
if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
As for alembic.ini file I didn't make any changes, I just commented the line:
sqlalchemy.url = driver://user:pass@localhost/dbname
because I assign it in migrations/env.py
When I make changes and run alembic revision --autogenerate -m 'Add new updates' the migration files are generated correctly and everything works as expected.
But when I run alembic revision --autogenerate -m 'Add new updates' when there are no changes, it shows this in the terminal:
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO [alembic.ddl.postgresql] Detected sequence named 'model2_table_model2_id_seq' as owned by integer column 'model2_table(model2_id)', assuming SERIAL and omitting
INFO [alembic.ddl.postgresql] Detected sequence named 'model1_model2_table_id_seq' as owned by integer column 'model1_model2_table(id)', assuming SERIAL and omitting
Generating /home/user/projects/dev/project/migrations/versions/45c6fbdbd23c_add_new_updates.py ... done
And it generates empty migration file that contains:
"""Add new updates
Revision ID: 45c6fbdbd23c
Revises: 5c17014a7c18
Create Date: 2021-12-27 17:11:13.964287
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '45c6fbdbd23c'
down_revision = '5c17014a7c18'
branch_labels = None
depends_on = None
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 ###
Is this the expected behavior or it has something to do with my architecture?
How to prevent Alembic from generating those empty migration files when there are no changes?
Is this the expected behavior or it has something to do with my architecture?
This is the expected behavior. Command alembic revision --autogenerate always creates a new migration file. If no changes exist than it creates an empty one.
You can use alembic-autogen-check to check if your migrations is in sync with models.
~ $ alembic-autogen-check
INFO [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO [alembic.runtime.migration] Will assume transactional DDL.
INFO: Migrations in sync.
How to prevent Alembic from generating those empty migration files when there are no changes?
Also alembic-autogen-check returns zero code only in migrations are in sync with models. So, you can use it as one command
alembic-autogen-check || alembic revision --autogenerate -m 'Add new updates'
But it seems less convenient than use it separately