[db] A CI job to run alembic migrations would be useful

[frgfm]

Following up on #112 (more specifically https://github.com/pyronear/pyro-api/pull/112#discussion_r584346620), we need to automate the alembic migration whenever it needs to be run. Depending on the integration, Github Workflow could help with that!

[jean-pasquier]

Just a draft for a new github action, no idea if it works. Questions:

• is it okay running the job within the VM and not in a container ?
• the job runs the upgrade migration method, need a process to rollback running the downgrade (eg connecting to db directly and running alembic downgrade -1) or a new migration with fixes (as upgrade) is sufficient
• storing production database URL in github secrets is feasible ?
• SUPERUSER_LOGIN and SUPERUSER_PWD should be set as well to retrieve app.config ?

name: dbmigration-alembic
on:
  push:
    branches: master
    paths:
      - 'src/alembic/**'
      - '!src/alembic/README.md'

jobs:
  run-migration:
    runs-on: ubuntu-latest
    needs: install-deps
    defaults:
      run:
        working-directory: src
    steps:
      - uses: actions/checkout@v2
      - name: Install dependencies
        run: |
          python -m pip install --upgrade pip
          pip install -r requirements-dev.txt
      - name: Run alembic migration
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          alembic upgrade head

[frgfm]

• When we use docker, we bind the volume of the DB (meaning that actions inside the container about the corresponding folder/data, will also take place outside) so I guess it will do!
• Not sure I understood?
• yes just like all the other secrets! But the codebase (github) may be deployed into multiple environments (let's imagine 1 instance per country), so the github secrets should only be used for CI testing purposes. Currently on Heroku we are also using a secret system to achieve this
• I remember hesitating between storing it in the config and just passing it as an env variable. Why do you think it would be better? (still unsure about the best option)

Thanks a lot for the draft ! Is the paths argument used to limit the event to changes to the files mentioned?

[frgfm]

I think this was fixed by #112, but let me check if everything still works :+1:

[ajubin]

As we're using heroku, what we can do is to perform the migrtation before the app deploy

in Procfile

release: alembic upgrade head # not sure of how to migrate alembic, but it should be something similar

The doc from heroku: https://devcenter.heroku.com/articles/release-phase