Prevent alembic from autogenerating tables

Question

I'm new to so alembic so i might miss a point in its concept but here is the question.

i have some sqlalchemy tables in a flask app like this:

class Data(Base):
__tablename__ = 'Data'
__table_args__ = {'schema': 'schema'}
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)

i initialize my tables:

Base = declarative_base()
engine = create_engine(db_link, pool_size=100, max_overflow=0)
Base.metadata.create_all(engine)
Session = sessionmaker()
Session.configure(bind=engine)

To that point i created the tables in my database manually and everything worked well. To later go productive with my project i want to be able to migrate my database using alembic. Because some tables i'll use (in a different schema) are read-only and created by another program i only want to migrate some of the sqlalchemy tables. Therfore my upgrade script looks like (created by alembic revision --autogenerate):

revision = 'bb1d39b7eee1'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('Data',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    schema='schema'
    )
    ...

when i now use an empty database to migrate my schema into with:

alembic upgrade head

i get the following error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S01', "[42S01] [M
icrosoft][SQL Server Native Client 11.0][SQL Server]There is already an object n
amed 'Data' in the database. (2714) (SQLExecDirectW)") [SQL: '\nCREATE TABLE schema.
[Data] (\n\tid INTEGER NOT NULL IDENTITY(1,1), \n\tname VARCHAR(max) NOT NULL, \
 \n\tPRIMARY KEY (id), \n\tCHECK (IN (0, 1))\n)\n\n']

It looks like alembic automatically creates all tables and then tries to create those tables in my revision script again. If thats true how can i tell alembic not to create any tables automatically and only run the scripts i create?

Answer 1

Your migration explicitly create a Data table:

def upgrade():
    ...
    op.create_table('Data',
    ...

So if your Data table already exists because you already created it manually, it is normal to get an error.

EDIT: I am not sure to understand when it is executed but you may want to try commenting the Base.metadata.create_all(engine) line in your database initialization script. I suspect it to create the tables. I have never seen alembic create tables before running the migrations (this is the job of migrations to create tables), if it does not solve your problem I think the problem is not from alembic.

---

Alembic is intended to manage your database migration from the start, it does not assume that you already created your tables.

Basically it creates a table to keep history of migrations applied to the database. When you run the first upgrade, there is no applied migration yet, so Alembic will try to run all the migration upgrades from the root one (whose down_revision is None) to the head one. At each applied migration, it also updates its history table to reflect the database state.

You could (ordered by my preference level):

1. drop your already existing tables and let alembic create them. This way, Alembic just create the table as declared in the migration and updates its history.

2. make Alembic believe that it already applied the first migration by filling manually its history table (I have never done that but I think it is possible). This way it will not try to apply it again

3. remove the create_table directives from your root migration's upgrade() function (and probably the drop_table from the downgrade() function). This way, Alembic will run the migration without trying to create the already existing tables and it should work. It will also record the migration as applied in its own history.

4. add a test in your migration to create the table only if it does not already exist, but in this case how will you manage the downgrade?