Database

Database schema

The database schema is maintained with the help of Alembic. Alembic takes care of both database schema initialization and its changes. It has native support for SQLAlchemy models and can detect model changes and generate queries needed to perform the schema update.

Warning:

Avoid manual changes to the database and schema, use Alembic and its CLI to upgrade and downgrade as needed.

All database schema patches - revisions - are stored in server/alembic/versions/, each represents one distinct change of the schema. Alembic keeps track of these patches and their order and stores additional information the database itself.

Schema revisions

To display what schema revision is currently installed in the database, run following:

$ poetry run alembic current -v
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
Current revision(s) for sqlite:///test.db:
Rev: 57f5d41c7200 (head)
Parent: <base>
Path: /home/mvadkert/git/gitlab.com/testing-farm/artemis/server/alembic/versions/57f5d41c7200_.py

    Intial database schema upgrade

    Revision ID: 57f5d41c7200
    Revises:
    Create Date: 2020-07-21 15:51:55.334506

To display complete history of revisions, run following:

$ poetry run alembic history
a31dd2a66069 -> 7adee582ada3 (head), Adds guest_tags table
51c5e4b2a7ac -> a31dd2a66069, Adds policy rulings metrics
...
57f5d41c7200 -> a3c173dfd6f5, Adding guest_requests.user_data column
<base> -> 57f5d41c7200, Intial database schema import

Revision changes

To initialize an empty database with current schema, run following:

$ poetry run 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  -> 57f5d41c7200, Intial database schema import
INFO  [alembic.runtime.migration] Running upgrade 57f5d41c7200 -> a3c173dfd6f5, Adding guest_requests.user_data column
INFO  [alembic.runtime.migration] Running upgrade a3c173dfd6f5 -> 487f52cc5aef, Guest request user data shall never be NULL
...

To downgrade or upgrade to given revision, run following:

$ poetry run alembic downgrade a31dd2a66069
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade 7adee582ada3 -> a31dd2a66069, Adds guest_tags table
$ poetry run 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 a31dd2a66069 -> 7adee582ada3, Adds guest_tags table

Schema changes

Alembic is smart enough to detect changes made to our DB models (see server/src/tft/artemis/db.py) and prepare a new revision. To create the patch, run following:

Warning:

It is highly recommended to check the file and make adjustments when needed. Alembic can detect schema changes pretty reliably, but it is still a software and cannot be aware of your intentions.

$ poetry run alembic revision --autogenerate -m "Adding user_data table"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added column 'guest_requests.user_data'
  Generating /home/foo/git/artemis/server/alembic/versions/a3c173dfd6f5_adding_user_data_table.py ...  done