Get Started With Alembic SQLAlchemy

Oct 18th, 2019 - written by Kimserey with .

In a previous post, I covered Flyway, a database migration tool which allows us to apply incremental migration to an existing database or build the new database from scratch. In the same line, Alembic is a migration tools which uses SQLAlchemy underneath, ideal when our application itself is in Python. Today we will look at how to setup Alembic and create migrations.

Install Alembic

Start by installing Alembic in your project with:

1

pip install alembic

Alembic should now be available with the alembic command. Before starting to use Alembic, we must initialize the configuration files and folders:

1

alembic init alembic

The init alembic will create an /alembic folder to handle migrations, and creates an alembic.ini files for the configuration of alembic. In the alembic.ini, we can modify the sqlalchemy.url to point to a SQLite instance for our example.

1

sqlalchemy.url = sqlite:////tmp/helloworld.db

The URL follows the same format as SQLAlchemy.

Additionally we can also enable the echo from SQLAlchemy if we need to SQL generated by Alembic:

1

sqlalchemy.echo = True

Create Migration

To create our first migration we use alembic revision:

1
2

$ alembic revision -m "create user table"
Generating C:\Projects\test_alembic\alembic\versions\d6695a405895_create_user_table.py ...  done

This will generate a {slug}_create_user_table.py which contains a skeleton of a migration.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

"""create user table

Revision ID: d6695a405895
Revises: 
Create Date: 2019-10-06 16:37:17.135564

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'd6695a405895'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    pass

def downgrade():
    pass

We have the revision ID d6695a405895, the revision it revises, here empty as this is our first revision and the create date. We then fill up upgrade and downgrade to specify the operation to execute on upgrade or downgrade.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

"""create user table

Revision ID: d6695a405895
Revises: 
Create Date: 2019-10-06 16:37:17.135564

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'd6695a405895'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'user',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('username', sa.String(), nullable=False),
        sa.Column('email', sa.String(), nullable=False)
    )

def downgrade():
    op.drop_table('user')

On upgrade we create a table user with three columns and on downgrade we drop the table. We can then first check where our database state is at with alembic current:

1
2
3

$ alembic current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.

The first line indicates that Alembic understands that the url we provided targets SQLite. Then we can look at the history:

1
2

$ alembic history
<base> -> d6695a405895 (head), create user table

We see that we have one migration pending d6695a405895 where head points to. We can then upgrade using upgrade head to upgrade to the latest revision:

1
2
3
4

$ alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> d6695a405895, create user table

After running the migration, we can see current

1
2
3
4

$ alembic current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
d6695a405895 (head)

We can then see that our database was created with sqlite3:

1
2
3
4
5
6
7
8
9
10

$ sqlite3 helloworld.db                   
SQLite version 3.25.2 2018-09-25 19:08:10 
Enter ".help" for usage hints.            
sqlite> .schema user                      
CREATE TABLE user (                       
        id INTEGER NOT NULL,              
        username VARCHAR NOT NULL,        
        email VARCHAR NOT NULL,           
        PRIMARY KEY (id)                  
);

Upgrade and Downgrade

We saw how to create our first migration, following the same path, we can create a second migration which we will use to add a constraint on the username.

1
2

$ alembic revision -m "add username constraint on user"
Generating C:\Projects\test_alembic\alembic\versions\fb6192cd49e8_add_username_constraint_on_user.py ...  done

We can see the revision created specifies that the new revision fb619 revises d6695

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

"""add username constraint on user

Revision ID: fb6192cd49e8
Revises: d6695a405895
Create Date: 2019-10-06 16:56:17.529434

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'fb6192cd49e8'
down_revision = 'd6695a405895'
branch_labels = None
depends_on = None

We can then see that the head changed:

1
2
3

$ alembic history
d6695a405895 -> fb6192cd49e8 (head), add username constraint on user
<base> -> d6695a405895, create user table

In the same way as git, we can use show to see more details about a particular revision:

1
2
3
4
5
6
7
8
9
10

$ alembic show fb619
Rev: fb6192cd49e8 (head)
Parent: d6695a405895
Path: C:\Projects\test_alembic\alembic\versions\fb6192cd49e8_add_username_constraint_on_user.py

    add username constraint on user

    Revision ID: fb6192cd49e8
    Revises: d6695a405895
    Create Date: 2019-10-06 16:56:17.529434

Now that we have our next migration, we can fill the upgrade and downgrade to add a CHECK constraint:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

"""add username constraint on user

Revision ID: fb6192cd49e8
Revises: d6695a405895
Create Date: 2019-10-06 16:56:17.529434

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'fb6192cd49e8'
down_revision = 'd6695a405895'
branch_labels = None
depends_on = None


def upgrade():
    table = sa.Table(
        'user',
        sa.MetaData(),
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('username', sa.String(), nullable=False),
        sa.Column('email', sa.String(), nullable=False)
    )

    with op.batch_alter_table('user', copy_from=table) as batch_op:
        batch_op.create_check_constraint(
            'ck_user_username_len', 
            'length(username) <= 10')

def downgrade():
    table = sa.Table(
        'user',
        sa.MetaData(),
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('username', sa.String(), nullable=False),
        sa.Column('email', sa.String(), nullable=False),
        sa.CheckConstraint('length(username) <= 10',
                           name='ck_user_username_len'),
    )

    with op.batch_alter_table('user', copy_from=table) as batch_op:
        batch_op.drop_constraint('ck_user_username_len')

This migration will add a check constraint on the database as part of a batch alter table operation. This is required as SQLite does not support alter table therefore we use batch alter table provided by Alembic to create a temporary table with the new constraint, copy the data into it, drop the old table and rename the temporary table. And we do the same to downgrade the check constraint.

We named the constraint ck_user_username_len as recommended by Alembic naming convention:

  • Index ix: ix_%(column_0_label)s,
  • Unique uq: uq_%(table_name)s_%(column_0_name)s,
  • Check ck: ck_%(table_name)s_%(constraint_name)s,
  • Foreign key fk: fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s,
  • Primary key pk: pk_%(table_name)s

We can then upgrade our database:

1
2
3
4

$ alembic upgrade +1
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade d6695a405895 -> fb6192cd49e8, add username constraint on user

Upgrade +1 is another variation used to upgrade databases which will upgrade the current state by a single revision. We can then see that our database contains the upgrade in schema:

1
2
3
4
5
6
7
8

sqlite> .schema user                                                   
CREATE TABLE IF NOT EXISTS "user" (                                    
        id INTEGER NOT NULL,                                           
        username VARCHAR NOT NULL,                                     
        email VARCHAR NOT NULL,                                        
        PRIMARY KEY (id),                                              
        CONSTRAINT ck_user_username_len CHECK (length(username) <= 10) 
);

And we can try to insert a value which would trigger the constraint:

1
2

sqlite> insert into user(id, username, email) values (0, 'abcdefghabcdefgh', 'abc');
Error: CHECK constraint failed: ck_user_username_len

We can also downgrade in the same way we’ve upgraded:

1
2
3
4
5

$ alembic downgrade base
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade fb6192cd49e8 -> d6695a405895, add username constraint on user
INFO  [alembic.runtime.migration] Running downgrade d6695a405895 -> , create user table

The opposite of upgrade head is downgrade base which run backward to the beginning.

1
2
3
4
5

$ alembic upgrade fb61
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> d6695a405895, create user table
INFO  [alembic.runtime.migration] Running upgrade d6695a405895 -> fb6192cd49e8, add username constraint on user

We can also upgrade to a particular revision using a slug just like git commits and similarly downgrade to a particular revision:

1
2
3
4

$ alembic downgrade d6695
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade fb6192cd49e8 -> d6695a405895, add username constraint on user

Offline Mode

Alembic also allows us to generate SQL queries in the offline mode using --sql parameter particularly useful for debugging:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

$ alembic upgrade d66:fb6 --sql
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade d6695a405895 -> fb6192cd49e8, add username constraint on user
-- Running upgrade d6695a405895 -> fb6192cd49e8

CREATE TABLE _alembic_tmp_user (
    id INTEGER NOT NULL,
    username VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT ck_user_username_len CHECK (length(username) <= 10)
);

INSERT INTO _alembic_tmp_user (id, username, email) SELECT user.id, user.username, user.email
FROM user;

DROP TABLE user;

ALTER TABLE _alembic_tmp_user RENAME TO user;

UPDATE alembic_version SET version_num='fb6192cd49e8' WHERE alembic_version.version_num = 'd6695a405895';

We can specify a range of revision from:to for example here we specified from d6695a405895 to fb6192cd49e8 and we can see how Alembic created a temporary table _alembic_tmp_user, inserted data and dropped user, then renamed _alembic_tmp_user to user and finally upgrade the alembic_version. Upgrade head --sql will provide the whole SQL from base to head.

For downgrade, we have to specify a revision from where to start and where to end:

1
2
3
4
5
6
7
8
9
10
11
12

$ alembic downgrade d66:base --sql
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Generating static SQL
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade d6695a405895 -> , create user table
-- Running downgrade d6695a405895 ->

DROP TABLE user;

DELETE FROM alembic_version WHERE alembic_version.version_num = 'd6695a405895';

DROP TABLE alembic_version;

We can see that downgrading from d6695 to base will drop the user table and revert the alembic_version and finally drop the alembic_version table.

And that concludes today’s post!

Conclusion

Today we looked into Alembic, a database migration tool written by the author of SQLAlchemy. Alembic provides a way to deal with the limits of databases like SQLite by providing a generic interface to deal with altering table or adding constraints but most importantly it handles versioning of our database allowing us to upgrade or downgrade to particular revisions, check the current revision our database is at and generate the SQL scripts which would be applied to the database for debugging. It is an ideal tool if in our application we are already using SQLAlchemy and if our project is in Python. I hope you like this post and I see you on the next one!

External Sources

Designed, built and maintained by Kimserey Lam.

LinkedIn icon LinkedIn| GitHub icon GitHub| Twitter icon Twitter | YouTube icon Youtube | RSS