Alembic迁移脚本：让数据库变身时间旅行者
title: Alembic迁移脚本：让数据库变身时间旅行者
# 示例：典型模型定义（models.py）
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(50))
email = Column(String(100)) # 新增字段示例
pip install alembic sqlalchemy fastapi
/project
/alembic
/versions
env.py
alembic.ini
main.py
models.py
alembic init alembic
[alembic]
script_location = alembic
sqlalchemy.url = postgresql://user:pass@localhost/dbname
alembic revision --autogenerate -m "add email column"
def upgrade():
op.add_column('users', Column('email', String(100)))
def downgrade():
op.drop_column('users', 'email')
# 修改后的env.py核心部分
from models import Base # 导入模型基类
target_metadata = Base.metadata
def run_migrations_online():
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,
compare_type=True, # 启用字段类型比对
compare_server_default=True # 比对默认值
)
# 迁移脚本示例：安全修改字段类型
def upgrade():
with op.batch_alter_table('users') as batch_op:
batch_op.alter_column('phone',
existing_type=String(20),
type_=Integer(),
existing_nullable=True)
alembic upgrade head
# 在env.py中添加自定义类型映射
from sqlalchemy.dialects.postgresql import JSONB
def include_name(name, type_, parent_names):
if type_ == "geometry":
return False
return True
context.configure(
...
include_name = include_name,
user_module_prefix = 'sa.'
)
alembic history --verbose
alembic downgrade -1
评论
发表评论