Alembic not working with existing database built with SQLModel

[liberty-rising]

Describe the bug
I have an existing database that I am trying to connect alembic to. Running py -m alembic revision --autogenerate -m "Initial Migration" generates an alembic version that contains the creation of each table in my database. I cannot run alembic upgrade in this case since the database already contains the tables to be created, along with data already existing within those tables.

Therefore I tried creating a temporary empty database and running the migration there. Upon doing so, the temporary database populated with the necessary values. I then switched the connection string within the alembic.ini file to point to the production database, ran py -m alembic stamp head" and then py -m alembic revision --autogenerate -m "Second Migration". This command once again creates a version which creates all of my existing tables.

Expected behavior
Upon use of py -m alembic revision --autogenerate -m "Initial Migration" or py -m alembic revision --autogenerate -m "Second Migration", alembic should generate a version that is empty since nothing was changed in the existing code.

To Reproduce

alembic.ini

# A generic, single database configuration.

[alembic]
# path to migration scripts
script_location = migrations

# template used to generate migration files
# file_template = %%(rev)s_%%(slug)s

# sys.path path, will be prepended to sys.path if present.
# defaults to the current working directory.
prepend_sys_path = .

version_path_separator = os  # Use os.pathsep. Default configuration used for new projects.

sqlalchemy.url = postgresql://pguser:password@127.0.0.1:5432/test_db

# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

script.py.mako

"""${message}

Revision ID: ${up_revision}
Revises: ${down_revision | comma,n}
Create Date: ${create_date}

"""
from alembic import op
import sqlalchemy as sa
import sqlmodel
${imports if imports else ""}

# revision identifiers, used by Alembic.
revision = ${repr(up_revision)}
down_revision = ${repr(down_revision)}
branch_labels = ${repr(branch_labels)}
depends_on = ${repr(depends_on)}


def upgrade():
    ${upgrades if upgrades else "pass"}


def downgrade():
    ${downgrades if downgrades else "pass"}

env.py

from logging.config import fileConfig
from colorama import Fore

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

from sqlmodel import SQLModel
from backend.models.capacity import Capacity
from backend.models.client import Client
from backend.models.demand import Demand
from backend.models.epic_area import EpicArea
from backend.models.epic import Epic
from backend.models.forecast import Forecast
from backend.models.rate import Rate
from backend.models.role import Role
from backend.models.sponsor import Sponsor
from backend.models.team import Team
from backend.models.timelog import TimeLog
from backend.models.user import AppUser

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
config.set_main_option(
    "sqlalchemy.url", "postgresql://pguser:password@127.0.0.1:5432/test_db"
)
# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = SQLModel.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"},
    )

    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)

        with context.begin_transaction():
            context.run_migrations()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

Error

Not applicable.

Versions.

• OS: Windows
• Python: 3.8
• Alembic: 1.7.7
• SQLModel: 0.0.6
• Database: PostgreSQL
• DBAPI: FastAPI

Additional context

Have a nice day!

[CaselIT]

Hi,

Please ensure that in the env you are importing all the modules that define tables in the metadata and that the connection is to rhe correct database/schema.
It seems that alembic is running autogenerate against the wrong database

[liberty-rising]

So the connection url sqlalchemy.url = postgresql://pguser:password@127.0.0.1:5432/test_db definitely is the correct one. I use it outside of the app, and changing any of the details, eg. username, password, name of db, etc., causes the version creation to fail.

Could the engine postgresql not be accessible by alembic? Or is there something else going on?

[liberty-rising]

I would like to point out, that the alembic version does get added to the database when I run the migration. However it is under the public schema whereas the database exists in a different schema. Could this be causing the issue?

[CaselIT]

Yes, that's definitely the issue.

Does the metadata not define the schema to use? Are you using a schema translate map?

[stranik28]

Are u solve it?

[Chokhonelidze]

No I am using MS sql but have same problem

[Chokhonelidze]

I have same problem:(((

[pyjka]

any updates on that ? i was struggling for a few days on this and it seems like the problem is just ignored lol

[CaselIT]

well the user never replied again, so it's a bit hard to solve the problem from this side.

In any case it seemed schema configuration issue that were different between alembic and the metadata in use.

[Chokhonelidze]

I solved this problem by adding :
literal_binds=True,
compare_type=True,
include_schemas=True,

to :
context.configure env.py file