Database migrations¶
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 customalembic.ini
ws-alembic
honours Websauna configuration file include mechanismws-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