(database)=

# Database

Our primary database system is [PostgreSQL](https://www.postgresql.com).
We use [SQLAlchemy](https://www.sqlalchemy.org) as ORM and SQL toolkit and
[Alembic](https://alembic.sqlalchemy.org) 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!
:::

```shell
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 {file}`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 {file}`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.