Python Alembic: Add, Drop, and Rename Columns
Learn how to add, drop, and rename columns with Alembic migrations in Python, including batch operations for SQLite and the key data-loss and locking trade-offs.

Schema changes that add, drop, or rename columns are common Alembic migration tasks. The syntax for each operation is simple, but the constraints around non-nullable columns, SQLite support, table locking, and autogenerate detection matter in practice. This article covers the calls and the decisions you need to make when writing these migrations.
Adding a Column with Alembic
The op.add_column() function is used to add a new column to a table. It requires the table name and a Column object from SQLAlchemy. Here is a minimal example:
from alembic import op
import sqlalchemy as sa
def upgrade():
op.add_column('users', sa.Column('age', sa.Integer(), nullable=True))This adds an age column to the users table. The nullable=True parameter is important: if you add a non-nullable column to a table that already has rows, the migration will fail because existing rows have no value for the new column. If you need a non-nullable column, you must either provide a server default or add the column as nullable, backfill data, and then alter the column to be non-nullable in a separate migration.
For example, to add a non-nullable column with a default value, you can use server_default:
def upgrade():
op.add_column('users', sa.Column('is_active', sa.Boolean(), nullable=False, server_default=sa.true()))The server_default is applied at the database level, so existing rows get the default value. After the migration, you can remove the server default if it is only meant for the backfill.
Dropping a Column
Dropping a column is equally simple with op.drop_column():
def upgrade():
op.drop_column('users', 'age')This permanently removes the column and all data stored in it. There is no undo at the database level unless you have a backup or a downgrade migration that recreates the column. The downgrade for this operation would be the corresponding op.add_column() call, but it cannot restore the original data. That is a critical operational point: dropping a column is destructive, and you should only do it when you are certain the data is no longer needed.
Some databases lock the table during a DROP COLUMN operation, which can block reads and writes. On large tables, this can cause downtime. If you are dropping a column from a very large table, consider whether the operation can be performed during a maintenance window or if you need a strategy that minimizes locking, such as creating a new table without the column and copying data over.
Renaming a Column
Renaming a column is done with op.alter_column() and the new_column_name parameter:
def upgrade():
op.alter_column('users', 'age', new_column_name='years_old')This changes the column name from age to years_old. The alter_column function can also change type, nullability, and other attributes, but when you only need to rename, this is the correct call.
However, ALTER TABLE RENAME COLUMN is not supported by every database. SQLite, for example, does not support renaming a column directly until version 3.25.0 (2018-09-15), and even then, Alembic's default behavior may not work because SQLite's ALTER TABLE support is limited. For SQLite, use Alembic's batch mode, which recreates the table with the new schema.
Using batch_alter_table for SQLite
Alembic provides op.batch_alter_table() to handle schema changes that are not natively supported by the database. For SQLite, this is the recommended way to rename a column. The batch operation creates a new table with the desired schema, copies data from the old table, drops the old table, and renames the new one. Here is an example:
def upgrade():
with op.batch_alter_table('users') as batch_op:
batch_op.alter_column('age', new_column_name='years_old')Inside the with block, you can use the same operations (add_column, drop_column, alter_column) as you would on op; Alembic collects them and applies them when it rebuilds the table. This approach is not limited to renaming; it is also useful for adding or dropping multiple columns in a single batch on SQLite, because each operation would otherwise require a separate table recreation.
Batch mode has a performance cost: it copies the entire table. For large tables, this can be slow and may temporarily double the storage usage. Always test the migration on a copy of the production data to estimate the duration.
Autogenerate vs Manual Migration Scripts
Alembic's autogenerate feature compares your SQLAlchemy model metadata with the current database schema and can generate migration scripts for changes it detects. For adding and dropping columns, autogenerate works well. Renaming columns is different: Alembic cannot infer that a column was renamed. It will see a drop of the old column and an add of the new one. If you run autogenerate after renaming a column in your model but before the database has been updated, the generated migration will contain drop_column and add_column, which can cause data loss when applied.
Therefore, write rename migrations manually using op.alter_column or batch_alter_table. If you also want autogenerate to produce the remaining changes, apply the rename migration to the database first; otherwise autogenerate will still compare the model to the old schema. Review any autogenerated revision before running it, and combine the manual and generated edits into one revision only after verifying the result.
Writing Downgrade Functions
Every migration should have a downgrade() function that reverses the upgrade(). For adding a column, the downgrade is op.drop_column(). For dropping a column, the downgrade is op.add_column() with the original column definition. For renaming, the downgrade uses alter_column with the original name.
def downgrade():
op.alter_column('users', 'years_old', new_column_name='age')When using batch operations, the downgrade must also use batch mode. A downgrade must be able to run without errors, but it may not restore data that was destroyed by an upgrade. For a dropped column, the downgrade recreates the column structure but the data is gone. Use database backups for destructive migrations; a downgrade is not a substitute.
SQLite's batch mode in the downgrade will again recreate the table, which is necessary to reverse the rename. Make sure the downgrade uses batch mode as well.
Operational Considerations
When you run migrations that add, drop, or rename columns, consider the following:
- Data loss: Dropping a column is irreversible without a backup. Renaming a column is safe, but a poorly written autogenerate migration can drop the column and lose data.
- Table locking:
ALTER TABLEmay lock the table. For large tables, this can block writes and even reads, depending on the database. Batch operations on SQLite copy the entire table, which can be slow. - Downtime: If your application is running while a migration is applied, the application code must be compatible with both the old and new schema during the migration window. For a rename, you might need to deploy the code change after the migration, or use a two-phase deployment where the code can handle both column names.
- Testing: Always test migrations on a staged copy of your data, especially for large tables or destructive operations. Measure the time it takes and verify the data integrity after the migration.
- Transaction support: Some databases allow DDL statements inside a transaction, others do not. Alembic runs migrations inside a transaction by default, but if the database does not support transactional DDL, a failed migration may leave partial changes. Understand your database's behavior and plan accordingly.
By understanding the exact behavior of each operation and the limitations of your database, you can write Alembic migrations that are safe and reliable for production use.
Try matching browser tools
All toolsSmall utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related Python guides
All Python guidesPython iter(): How the Built-in Function Works
Explains Python's iter() built-in: how it creates iterators, the two-argument sentinel form, custom iterator classes, and common edge cases.
Python Pika: Queues, Exchanges, Routing Keys, and Acknowledgements
Learn how to use Python's Pika client with RabbitMQ: declare queues and exchanges, publish with routing keys, consume messages, and handle acknowledgements reliably.
Python Kafka: kafka-python vs Confluent Client
python kafka python vs confluent kafka: Compare kafka-python and Confluent Kafka Python clients: API differences, performance, feature coverage, and when to choose eac...
Python Kafka Offsets, Partitions, and Manual Commits
Learn how Kafka offsets and partitions work and how to manually commit offsets in Python consumers with kafka-python and confluent-kafka. Covers sync vs async commits, rebalances, and seeking.
More in Python
All Python guidesThe guides filed next to this one in Python, in library order.