Alembic DDL operations¶
pre-requisites¶
- Basic knowledge of alembic and how to create and apply migrations.
- If you didn't have any prior knowledge then I suggest you to follow below articles
-
To sql from migration we need to run the command
alembic upgrade head --sql - https://alembic.sqlalchemy.org/en/latest/tutorial.html
required import statements¶
from alembic import op
import sqlalchemy as sa
creating table with alembic¶
op.create_table(
'product',
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('name', sa.String(50), nullable=False),
sa.Column('description', sa.Unicode(200)),
)
The above operation produce the below sql
CREATE TABLE product (
id SERIAL NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(200),
PRIMARY KEY (id)
);
- To create table we need to use packages both alembic and sqlalchemy
- sqlalchemy to define the table definitions
- alembic to convert it into
SQLequivalent query and execute it on the database.
add new column to the table with alembic¶
add_column(
table_name: str, column: Column,
schema: Optional[Union[quoted_name, str]] = None) → None
op.add_column('product', sa.Column('category', sa.String(20)))
The above operation produce the below sql
ALTER TABLE product ADD COLUMN category VARCHAR(20);
- It requires two arguments table name and column definition
- For more info check official docs
rename table column with alembic¶
op.alter_column(
'product', 'description', nullable=True, new_column_name='desc'
)
The above operation produce the below sql
ALTER TABLE product ALTER COLUMN description DROP NOT NULL;
ALTER TABLE product RENAME description TO "desc";
- It basically requires table name, column name and other keyword arguments
- For more info check official docs
add unique constraint on a column with alembic¶
op.create_unique_constraint('uq_product_name', 'product', ['name'])
The above operation produce the below sql
ALTER TABLE product ADD CONSTRAINT uq_product_name UNIQUE (name);
- It basically requires constrant name, table name and list of columns as arguments
- For more info check official docs
drop a constraint on a column with alembic¶
op.drop_constraint('uq_product_name', 'product')
The above operation produce the below sql
ALTER TABLE product DROP CONSTRAINT uq_product_name;
- It requires arguments constraint name and table name
- For more info check official docs
add index on a column with alembic¶
op.create_index('idx_product_id_name', 'product', ['id', 'name'], unique=True)
The above operation produce the below sql
CREATE UNIQUE INDEX idx_product_id_name ON product (id, name);
- It requires arguments index name, table name and list of column names and other optional params.
- For more info check official docs
drop index on a column with alembic¶
op.drop_index('idx_product_id_name', table_name='product')
The above operation produce the below sql
DROP INDEX idx_product_id_name;
- It requires arguments index name and optional table name.
- For more info check official docs
execute custom SQL with alembic¶
op.execute('create view product_view as select * from product')
The above operation produce the below sql
create view product_view as select * from product;
- In some cases, we can't complex sql operations with alembic. In such cases, we need to execute custom sql. alembic provides
op.executeto achieve it. - For more info check official docs
add foreign key with alembic¶
drop table column with alembic¶
op.drop_table('product')
The above operation produce the below sql
DROP TABLE product;
- It basically requires table name as param.
- For more info check official docs
