Tips for SQLAlchemy

Contents

1. Introduction
2. Autogenerate an Alembic migration
3. Test the Alembic migration

Introduction

Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python. When making changes to the database, it's essential to follow these specific steps to auto-generate and test an Alembic migration:

Autogenerate an Alembic migration

1. Undeploy the current cluster:

   reana-dev cluster-undeploy

2. Stash your reana_db/models.py changes:

   git stash

3. Deploy the old version of the cluster in debug mode:

   reana-dev cluster-deploy --admin-email [email protected] --admin-password mysecretpassword --mode debug

4. Pop your stash:

   git stash pop

5. Propagate changes to all other components:

   reana-dev git-submodule --update

6. Generate the migration:

   kubectl exec deployment/reana-server -- reana-db alembic revision --autogenerate -m "{{{description_of_your_change}}}"

7. Copy your migration from reana-server to reana-db:

   From: reana-server/modules/reana-db/reana_db/alembic/versions/12345678_9123_4a5b6c7d8e9f_{{{description_of_your_change}}}.py

   To: reana-db/reana_db/alembic/versions/12345678_9123_4a5b6c7d8e9f_{{{description_of_your_change}}}.py

You're done! With the migration part... After generating the initial migration, it's crucial to test it since the initial migration often leads to unexpected errors. Follow these steps to ensure the migration works as expected:

Test the Alembic migration

The testing example was taken from this issue. To thoroughly ensure the migration works, you should perform the downgrade and upgrade steps.

1. Check the schema of the table you are changing, in this case it is __reana.job.

   kubectl exec -ti deployment/reana-db -- psql -U reana

   psql (9.6.2)
   Type "help" for help.
   
   reana=# \d __reana.job
                         Table "__reana.job"
           Column       |            Type             | Modifiers 
   --------------------+-----------------------------+-----------
   ...
    finished_at        | timestamp without time zone | 
    started_at         | timestamp without time zone | 
   ...
   reana=# \q

2. View the Alembic migration history.

   kubectl exec deployment/reana-server -- reana-db alembic history

   ad93dae04483 -> 4801b98f6408 (head), Job started and finished times.
   c912d4f1e1cc -> ad93dae04483, Interactive sessions.
   <base> -> c912d4f1e1cc, Quota tables.

3. Check the current Alembic migration version.

   kubectl exec deployment/reana-server -- reana-db alembic current

   INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
   INFO  [alembic.runtime.migration] Will assume transactional DDL.
   4801b98f6408 (head)

4. Downgrade the Alembic migration to a previous version (e.g., ad93dae04483).

   kubectl exec deployment/reana-server -- reana-db alembic downgrade ad93dae04483

   INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
   INFO  [alembic.runtime.migration] Will assume transactional DDL.
   INFO  [alembic.runtime.migration] Running downgrade 4801b98f6408 -> ad93dae04483, Job started and finished times.

5. Verify the __reana.job table schema after downgrading the migration.

   kubectl exec -ti deployment/reana-db -- psql -U reana

   psql (9.6.2)
   Type "help" for help.
   
   reana=# \d __reana.job
                         Table "__reana.job"
           Column       |            Type             | Modifiers 
   --------------------+-----------------------------+-----------
   ...
   👀  NO FINISHED_AT AND STARTED_AT!
   ...
   reana=# \q

6. Upgrade the Alembic migration back to the head version (4801b98f6408).

   kubectl exec deployment/reana-server -- reana-db alembic upgrade

   INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
   INFO  [alembic.runtime.migration] Will assume transactional DDL.
   INFO  [alembic.runtime.migration] Running upgrade ad93dae04483 -> 4801b98f6408, Job started and finished times.

7. Verify the __reana.job table schema after upgrading the migration.

   kubectl exec -ti deployment/reana-db -- psql -U reana

   psql (9.6.2)
   Type "help" for help.
   
   reana=# \d __reana.job
                         Table "__reana.job"
           Column       |            Type             | Modifiers 
   --------------------+-----------------------------+-----------
   ...
    finished_at        | timestamp without time zone | 
    started_at         | timestamp without time zone | 
   ...
   reana=# \q

Congratulations! You've successfully generated an Alembic migration for your database changes and thoroughly tested it to ensure everything works as expected.