Database

Our primary database system is PostgreSQL. We use SQLAlchemy as ORM and SQL toolkit and Alembic for automated database schema migrations.

Setting up a Development Database

Create database schema and load example data for experimentation in development:

Warning

Check the database section in the config file to avoid overwriting the wrong database!

python tests/create_test_db.py -c config.yml

Running Migrations

Check the current revision:

alembic current

Upgrade the schema in the configured database to the latest version (may include multiple migration steps):

alembic upgrade head

Use the EKKLESIA_PORTAL_CONFIG environment variable if you need to set the path to the app configuration file. The alembic command doesn’t have an option for that.

Downgrading is also possible:

alembic downgrade -1

This rolls back the last migration step.

Schema Changes and Migrations

Migrations can be partially auto-generated by Alembic by comparing the SQLAlchemy model/schema code with the existing database.

First, change the model/schema code for the app in src/ekklesia_*/datamodel.py. Then, generate the migration file with a proper migration message (used for the filename and as a comment in the migration module):

alembic revision --autogenerate -m "Allow no target date for aborted voting phase"

Migration modules can be found in alembic/versions/.

Note

Check the generated code after generating it and remove the comments generated by Alembic!

Run the generated migration and check if it does the right thing. You should also test the downgrade.