If we use an object-relational mapper (ORM), such as SQLAlchemy, we map classes to tables and class attributes to table columns. Often, due to new business requirements, we need to add a table column and corresponding class attribute. We will probably need to populate the column immediately after adding it.

If we deal with a production database, then probably you do not have direct access. Luckily, we can generate SQL with Alembic, which a database administrator can review.

How to do it…

Alembic has its own versioning system, which requires extra tables. It also creates a versions directory under the alembic directory with generated Python code files. We need to specify the types of change necessary for migration in these files:

1. Create a new revision, as follows:

   $ alembic revision -m "Add a column"
   

2. Open the generated Python file (for instance, 27218d73000_add_a_column.py). Replace the two functions in there with the following code, which adds the link_type string column:

   def upgrade():
       # MODIFIED Ivan Idris
       op.add_column('links', sa.Column('link_type', sa.String(20)))
   
   
   def downgrade():
       # MODIFIED Ivan Idris
       op.drop_column('links', 'link_type')

3. Generate SQL, as follows:

   $ alembic upgrade head --sql
   

Refer to the following screenshot for the end result: