Database migrations¶

Introduction
- Differences between ws-alembic and native alembic commands
Migrations
Running migrations for a third party package
Advanced
- Printing out table creation schemas from command line
Troubleshooting

Introduction ¶

Websauna uses Alembic migration script tool for SQLAlchemy to reflect changes made to models into SQL databases. These changes include creating new tables for models, adding columns to models and changing column types. For your convenience, most of the migrations can be autogenerated, so that you don’t need to manually write and run SQL scripts when you change your tables.

TODO: What migration means here

Differences between ws-alembic and native alembic commands ¶

Websauna provides its own wrapper script ws-alembic which is a wrapper for underlying alembic command. The differences are

Websauna project scaffolds provide Alembic environment and context scripts integration, no need to do alembic init
ws-alembic is designed to read the settings file of a current Websauna package instead of custom alembic.ini
ws-alembic honours Websauna configuration file include mechanism
ws-alembic knows how to deal with multiple packages (see below)
ws-alembic has one SQL table for migrations per package, instead of the default Alembic one global table

Migrations ¶

Preface ¶

This chapter assumes you are using PostgreSQL database, though any SQL database should be fine.

Packages to considered for migration ¶

By default ws-alembic only tries to look models and tables specific to your current package. However, sometimes this behavior is not desirable

Cross-package references are foreign keys are considered
You want to create migration script for default Websauna packages

You can control the behavior what packages are considered with ws-alembic -x packages= option.

Consider models of the package where you run ws-alembic:

ws-alembic -c development.ini revision --autogenerate

Consider all models registered in Initializer:

ws-alembic -x packages=all -c development.ini revision --autogenerate

Consider models of a particular package:

ws-alembic -x packages=fooapp -c development.ini revision --autogenerate

Creating the first migration ¶

Preface: You have edited models.py in your package by introducting new models or columns. Now it is time to generate the code which updates the database to reflect those changes.

Run:

ws-alembic -c development.ini revision --autogenerate -m "Adding Offer model for managing deals"

This will update Python scripts in alembic/versions folder.

Note

Always manually inspect generated migration scripts after they have been created with revision –autogenerate. Sometimes the automatic logic fails to correctly detect database changes.

Backup your database before running a migration:

ws-dump-db development.ini > dump.sql

Then run the script against the database:

ws-alembic -c development.ini upgrade head

You can now manually inspect that the table was created:

ws-db-shell development.ini

Add type in PostgreSQL command list tables:

\dt

Inspect the output to see your tables have been update.

And then quit pgcli:

\q

Creating further migrations ¶

Repeat the tasks of creating the first migration.

Running a migration on the production server ¶

Preface: You have created a migration script and deployed the migration script and changed model Python files on the production server. Now you wish to run the migration in the production.

Backup your database before running a migration:

ws-dump-db production.ini > dump.sql

Checkout the migration scripts from alembic directory on the production server, without upgrading the full codebase. We do not want to pull in models which do not have persistent data models created for them yet:

git fetch
git checkout origin -- alembic

Then run the script against the database:

ws-alembic -c production.ini upgrade head

Now the database is prepared for the upcoming code update. Proceed to update the rest of the codebase and restart the services.

Fixing a migration ¶

In the case your initial attempt to migrate was not succesful, e.g. you forgot to add some columns, you can unwind the migration and construct it again.

List the current migrations:

ws-alembic -c development.ini history

37e1cb6de47 -> 2d970929c35 (head), Adding offers
<base> -> 37e1cb6de47, Initial migration

We are in head, let’s go back to the base:

ws-alembic -c development.ini downgrade 37e1cb6de47

Let’s delete 2d970929c35_xxx.py` migration script.

Edit models.py to add the missing fields.

Regenerate the migration script with fixes:

ws-alembic -c development.ini revision --autogenerate -m "Adding Offer model for managing deals"

Then run the fixed script:

ws-alembic -c development.ini upgrade head

Running migrations for a third party package ¶

Activate your installation virtualenv and give package as -x package option to ws-alembic:

(cd /path/to/websauna.myaddon && ws-alembic -c /path/to/myapp/development.ini upgrade)

Advanced ¶

Printing out table creation schemas from command line ¶

This is sometimes useful for manual migrations.

In the shell:

from sqlalchemy.schema import CreateTable

engine = requet.dbsession.get_bind()
model_class = Delivery

table_sql = CreateTable(model_class.__table__).compile(engine)
print(table_sql)

Troubleshooting ¶

NameError: name ‘datetime’ is not defined ¶

This error can appear when you try to run your Alembic migration script. The default migration script skeleton might miss some imports which you have used in your model columns.

Example if you have a column:

sa.DateTime(timezone=datetime.timezone.utc)

You need to have a import datetime in your migration script.

Edit your migration script in alembic/versions/ and add:

import datetime

Also add this to alembic/script.py.make so that it is present in all future migrations:

import datetime

FAILED: No such revision or branch ‘xxx’¶

This error may appear if you try to run migrations on a database with upgrade head`. The ``alembic_version database table has gotten out of the sync with the actual migration scripts and their ids.

The course of the actions is to drop alembic_version database table and reset the current migration pointer to the migration script matching your database.

Backup your database before doing hardcore database manipulation:

ws-dump-db staging.ini > dump.sql

Drop the alembic migration pointer table:

ws-db-shell staging.ini

DROP TABLE alembic_version

\q

Output the available migration script versions:

ws-alembic -c staging.ini history

# Example output:
37e1cb6de47 -> 3ca5462d497 (head), Adding Offer model for managing deals
<base> -> 37e1cb6de47, Initial migration

Update the alembic migration pointer:

ws-alembic -c staging.ini stamp 37e1cb6de47

Run migrations. Now it should pick migrations from 37e1cb6de47 and run all the way to the latest migration:

ws-alembic -c staging.ini upgrade head

Alternatively, you can also try to fix version history by directly manipulating Alembic history in PostgreSQL:

update alembic_history_trees set version_num="3dd2f080895";

Migrating enums ¶

Alembic does not how to migrate SQLAlchemy and PostgreSQL enum types if you add or remove enum choices.

For this you need to create a manual migration:

ws-alembic -c development.ini revision -m "Adding enum choice payment_under_review"

Then edit generated script in versions:

def upgrade():
    """Perform non-atomic update on PostgreSQL enum."""

    op.execute('COMMIT')  # See https://bitbucket.org/zzzeek/alembic/issue/123

    # payment_status = name of the enum we are altering
    op.execute('ALTER TYPE payment_status ADD VALUE \'payment_under_review\'')

def downgrade():
    # TODO
    # We don't support downgrade yet
    pass

More information