Automate database migrations on EC2 instance's Postgres server

[paulespinosa]

Overview

Although the API has alembic, a SQLAlchemy database migration tool, as one of its dependencies, its use has not yet been implemented for API deployments. In fact, a solution to database migrations has yet to exist outside of a developer's environment. At present, there is a placeholder in the build-deploy-ec2.yml workflow to run database migrations on the EC2 instance's PostgreSQL server. Without database migrations, future database developments could break when new API changes are deployed.

A database migration is a necessary step in the API deployment to allow existing data to continue to be available in an updated database schema. For example, say the user table contains existing data and a new version of the API creates new tables guest, coordinator, and host tables. These new tables will need to be populated with IDs of existing users. This is where an alembic migration script comes into play. When the new version of the API is developed, an alembic migration script should also be included to populate the new tables with existing data. During API deployment, the alembic database migration tool ought to be used to run the migration script to update the existing database so that the new version of the API can continue to use the existing data correctly.

Bear in mind that, although, the HUU project will be deployed to containerized deployments with dedicated database servers in the future (known as the incubator environment), there is still value in implementing migrations on the existing EC2 instance. Fundamental database migration techniques will be learned and tried. This can help pave the way to towards deploying in the incubator environment.

This issue will use knowledge about SQLAlchemy, alembic, GitHub workflows, and Linux system administration.

Action Items

• [ ] Study alembic database migrations and where migration scripts are currently stored
• [ ] What is the ideal way to run the migrations?
• [ ] Study the EC2 instance and the build-deploy-ec2.yml GitHub workflow. Note that the source code doesn't live on the EC2 instance. Are the migration scripts included in the sdist package that is created by the workflow? The EC2 instance has a .env file with the database in the /opt/dev.homeunite.us directory. How can you enter the python virtual environment to execute alembic? Where is alembic.ini?
• [ ] What changes need to be made to the EC2 instance and/or build-deploy-ec2.yml workflow to make database migration work?

Resources/Instructions

Share what you learn; it'll help future deployment implementations.

To access the EC2 instance, create SSH key pair, contact Tyler or Paul with your public to be able to login to the EC2 instance.

https://alembic.sqlalchemy.org/ build-deploy-ec2.yml

[tylerthome]

Recommend moving to ice box until Incubator integration established, the parameters for this solution will hinge on implementation details coordinated with ops/incubator admins