SQLAlchemy + alembic: create schema migration

问题:

I'm not sure how to define a create schema foo migration? My Model looks like this (I'm using Flask-Migrate):

class MyTable(db.Model):
    __tablename__ = "my_table"
    __table_args__ = {"schema": "foo"}

    id = ColumnPrimKey()
    name = Column(Text, unique=True, nullable=False)

When I execute mange db upgrade I get a failure because the schema "foo" does not exist. How can I add a migration for the schema with SQLAlchemy and Alembic?

回答1:

I accomplished this by modifying the migration upgrade command to first run:

op.execute("create schema foo")

And in the downgrade function

op.execute("drop schema foo")

So the whole migration file looks something like:

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '6c82c972c61e'
down_revision = '553260b3e828'
branch_labels = None
depends_on = None


def upgrade():
    op.execute("create schema foo")
    ...

def downgrade():
    ...
    op.execute("drop schema foo")