Alembic for SQL upgrades/downgrades

[krayzpipes]

Just getting a conversation started around using Alembic to manage database upgrades/downgrades. Is there benefit in using Alembic instead of shell scripts?

You can define the database schema/structure and Alembic can help auto-detect those changes (with access to the SQLAlchemy metadata) and create a upgrade/downgrade script (but should always be manually reviewed before trusting them)

Example from: https://alembic.sqlalchemy.org/en/latest/autogenerate.html

$ alembic revision --autogenerate -m "Added account table"
INFO [alembic.context] Detected added table 'account'
Generating /path/to/foo/alembic/versions/27c6a30d7c24.py...done

That would create the following:

"""empty message

Revision ID: 27c6a30d7c24
Revises: None
Create Date: 2011-11-08 11:40:27.089406

"""

# revision identifiers, used by Alembic.
revision = '27c6a30d7c24'
down_revision = None

from alembic import op
import sqlalchemy as sa

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
    'account',
    sa.Column('id', sa.Integer()),
    sa.Column('name', sa.String(length=50), nullable=False),
    sa.Column('description', sa.VARCHAR(200)),
    sa.Column('last_transaction_date', sa.DateTime()),
    sa.PrimaryKeyConstraint('id')
    )
    ### end Alembic commands ###

def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_table("account")
    ### end Alembic commands ###

[unixfreak0037]

Yes, 100% support this.

[unixfreak0037]

First step here is to make the SQLAlchemy schema match the database schema. Things like indexes and column charset are missing.