Alembic "migrations" with existing postgresql database -> Changes only upwards

[thinkORo]

I have an existing and production ready database in my PostgreSQL cluster.

With SQLACodeGen I've created a models.py which represents the structure of my database. And it looks fine to.

Now I want use Alembic as my main database migration (aka modification) framework. How can I achieve this?

I did the following already:

1. alembic init any_name
2. I adjusted env.py to import Base from my models.py and added Base.metadata to the target_metadata
3. If I then run alembic revision --autogenerate -m "inital setup" I get my first version

But I don't want to migrate to this "initial setup" version as it is already in my database.

Is there any way to inform Alembic that this "initial setup" version is already there and only new modification to models.py (new tables or new columns) should be taken into account.

[CaselIT]

Hi,

You can delay your fist alembic revision --autogenerate -m "message here" until you have something to change to the database. It should not prevent other usages of alembic from succeeding

[thinkORo]

Hi and thanks for your quick reply.

Unfortunately I don't want to delay the first initial version. I want to use it as base version without any commit first the first upgrade.

I tried the following:

1. alembic revision --autogenerate -m "inital setup" based on my initla models.py
2. I modified models.py by adding a new table "huhu"
3. alembic revision --autogenerate -m "new table huhu"

In step 3 I get an error or information that the database isn't up to date.

What did I do wrong?

[CaselIT]

I don't think I'm following you.

In step 3 I get an error or information that the database isn't up to date.

You have created a migration, not applied it to the db and are trying to create another using autogenerate. From alembic point of view the db is not up to date, since you never applied the first migration.

I'm assuming that if you already have the database setup the first migration will be empty. Either apply that empty migration or don't generate it as suggested.

If the first migration is not empty I'm not following what you are trying to do, but if you want to tell alembic that your db is already at migration X you can use stamp

[thinkORo]

I'm assuming that if you already have the database setup the first migration will be empty. Either apply that empty migration or don't generate it as suggested.

Hmm, you wrote "will be empty". But I think it is not. Let me double-check.

[thinkORo]

This is my setup:

alembic.py

sqlalchemy.url = postgresql://user:password@localhost/any_dbname

env.py

import sys, os
sys.path.append(os.path.abspath(os.path.join('..')))
from models import Base
target_metadata = Base.metadata

Execution

alembic revision --autogenerate -m "initial setup"

The .py file (here: d73198a528f8_initial_setup.py) in the version folder contains the cmplete structure from models.py

Is it that what you expected?

[CaselIT]

well you said that

I have an existing and production ready database in my PostgreSQL cluster.

if the state of the db is the same as the one in specified in the metadata then no changes would be detected.

If the db is empty then yes the first migration would include all the changes required to create the state described in the metadata

[thinkORo]

If I then add some new tables or columns to my models.py I cannot create any new revision. If I would delete everything in 'upgrade' and 'downgrade' in my 1st version I get not only the modifications but the complete structure of my inital setup (which will cases errors as the other tables already exists).

Hmm, I think I did something wrong. And I have a misunderstanding how to start with an existing database structure (I tries everything I mentioned with an empty DB and that works fine. But not if the database already have existing objects).

[thinkORo]

Hi @CaselIT

I tried the "stamp" command as well as you suggested.

1. alembic revision --autogenerate -m "initial setup"
2. alembic stamp 839bbb9e0c64 (hint: 839bbb9e0c64 was the generated revision number)
3. Some modifications on models.py by adding a new table "huhu"
4. alembic revision --autogenerate -m "new table huhu"

Problem: The new version not only contains information about table "huhu" but also the information from the stamp revision. Which ends in an error: psycopg2.errors.DuplicateTable: relation "one_table_name" already exists

What did I do wrong?

My understanding of stamp would be: stamp the revision "as is" without any further processing. Am I wrong?

[thinkORo]

Hi @CaselIT

I made a backup of my database and started from skretch. No table in database but models.py available.
And I ran in the same problems Shubhendra Kushwaha reported in #1416.

The second "--autogenerate" with a slightly modified models.py generated a version which contains all tables from the first revision and not only the modifications.

It seems that there is a problem with "autogenerate" in combination with (modifed) models.py.

[CaselIT]

can you try adding include_schemas=True to context.configure?

[thinkORo]

As you mentioned I modified my env.py in "run_migrations_offline" to:

context.configure(
   url=url,
   target_metadata=target_metadata,
   literal_binds=True,
   dialect_opts={"paramstyle": "named"},
   include_schemas=True,
)

Unfortunately the behaviour is the same:

1. alembic revision --autogenerate -m "initial setup"
2. alembic stamp 205cf8548c2d (hint: 205cf8548c2d was the generated revision number)
3. Some modifications on models.py by adding a new table "huhu"
4. alembic revision --autogenerate -m "new table huhu"

In step 4 I've got all the objects from the initial autogenerate + the new huhu table.

What did I do in addition: between step 2 and 3 I tried to apply the changes by alembic upgrade head. But that makes no diference.

[CaselIT]

Well if you add a stamp that's the expected behaviour.
Try removing the stamp

[zzzeek]

this parameter must be applied to the "run_migrations_online" section. "online" means "connects to a database when it runs". autogenerate requires a database connection. "run_migrations_offline" only applies to when alembic is run with the --sql parameter.

[thinkORo]

@CaselIT Unfortunately it doesn't work.

Let me quickly summaries the setup:

1. initial models.py which represents the database structure (which is in place as well)

class Customer(Base):
    __tablename__ = 'customer'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='pk_customer_id'),
        {'schema': 'dev'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

2. modified models.py with a new Class called Product

class Customer(Base):
    __tablename__ = 'customer'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='pk_customer_id'),
        {'schema': 'dev'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(100))

class Product(Base):
    __tablename__ = 'product'
    __table_args__ = (
        PrimaryKeyConstraint('id', name='pk_product_id'),
        {'schema': 'dev'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    price: Mapped[decimal.Decimal] = mapped_column(Numeric(5, 2))

What do I have to do to:
a. define the first revision which ignores the customer table
b. define a second revision which only contains the product table

Thanks for your patient.

[thinkORo]

Since I simply could not imagine that this function would not work, I transferred the data model from the specific (dev) schema to the public schema.

And I can confirm that it works as expected.

So we don't have a "discussion" here, but a bug.

I have to say (for all those who have not yet dealt with databases and PostgreSQL in particular): Not all databases support different schemas. And in production environments they would be used less frequently than in development environments.

Therefore, we should state the status here in the discussion as follows:

1. the use of a models.py in conjunction with --autogenerate works well if the data models are not outside of the public schema
2. the support from the Alembic team here on Github is great
3. i will post a bug so that maybe someone will take a look at it, because in my opinion the use of schemas is very helpful at least in a development environment and at least common with PostgreSQL.

@CaselIT: Thank you for your support and patient. Should I close this discussion here?

[zzzeek]

autogenerate works with a multischema database, if you make sure to set include_schemas=True in your env.py, and use online configuration as well (autogenerate does not apply to "offline" configuration). please dont open a bug report as we have not been shown anything to fix.

[thinkORo]

@zzzeek What would such a context entry for online look like?

[zzzeek]

starting with the standard env.py template at https://github.com/sqlalchemy/alembic/blob/main/alembic/templates/generic/env.py , modify context.configure() inside of run_migrations_online():

def run_migrations_online() -> None:
    """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     # <---- add this
        )

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

further docs on how to control for specific schema names is at https://alembic.sqlalchemy.org/en/latest/autogenerate.html#controlling-what-to-be-autogenerated

[thinkORo]

Okay, I found the context. Sorry.

I switched from

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

to

    with connectable.connect() as connection:
        context.configure(
            connection=connection, target_metadata=target_metadata, include_schemas=True,
        )

And I can confirm that it is working as well. Just a small change with a hugh impact.

Again: Your support is superb!

[CaselIT]

@zzzeek Do you think changing the options include_schemas would be useful?

I mean something the following:

• False -> use only the default schema
• True -> check all schemas
• 'from_metadata' -> new default, behave as False if the metadata.schema is None or as True with a name filter to the schema specified in the metadata if the metadata.schema is defined?

I think this new default would do the correct thing in most cases, meaning that an user has to change the include_schemas only if having tables on multiple schemas at the same time

Or does the above sound too complex/magic?

[zzzeek]

I think that's a great idea

[CaselIT]

I kinda though you would be -1 because it feels magic. But I'll open an issue

[zzzeek]

if we name it well then it wont seem magic

[CaselIT]

from_metadata seems clear to me as in "include schema from the provided metadata"

[JamshidS]

ken into accou

I realize this is a bit late, but for anyone else facing a similar issue when implementing Alembic with an existing production database that already has initialized tables, here’s a solution that worked for me.

Instead of using --autogenerate, you can create a manual migration script by running:

alembic revision -m "initial script"

This will generate a migration file for you. In that file, you can write custom SQL queries to create tables, using conditional logic to ignore them if they already exist.

For example:

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '1927398123'
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:

    op.execute(
        """
            CREATE TABLE IF NOT EXISTS user ( 
              id INT PRIMARY KEY,
              name VARCHAR
          );

        """
    )
    ```