I'm new to alembic so this is very likely a beginners fallacy. I have a FastAPI server which runs SQL migrations on startup.
The problem is, that these migrations are being executed every time the server is starting up. This results in an error where the table is already created:
sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1050, "Table 'signal' already exists")
Why is alembic running this migration again and how would it even know that the migration already happened? For example, I wouldn't want to run INSERT statements repeatedly either.
Important detail here (maybe): I am attaching to a Docker container which starts the server each time I connect.
Here's the relevant code:
def run_sql_migrations():
# retrieves the directory that *this* file is in
migrations_dir = os.path.join(os.path.dirname(os.path.realpath(db.__file__)), "alembic")
# this assumes the alembic.ini is also contained in this same directory
config_file = os.path.join(migrations_dir, "..", "alembic.ini")
db_url = f"mysql://{os.environ['DB_USER']}:{os.environ['DB_PASSWORD']}" \
f"@{os.environ['DB_HOST']}:{os.environ['DB_PORT']}/{os.environ['DB_NAME']}"
config = Config(file_=config_file)
config.set_main_option("script_location", migrations_dir)
config.set_main_option("sqlalchemy.url", db_url)
# upgrade the database to the latest revision
upgrade(config, "head")
app = FastAPI()
run_sql_migrations()
My first migration is nothing more than the creation of one table:
"""testing revisioning
Revision ID: 2287ab5f2d12
Revises:
Create Date: 2023-01-27 11:02:48.341141
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = '2287ab5f2d12'
down_revision = None
branch_labels = None
depends_on = None
def upgrade() -> None:
op.create_table(
'signal',
sa.Column('id', sa.Integer, primary_key=True),
)
def downgrade() -> None:
op.drop_table("signal")
