hackforla / hackforla/HomeUniteUs

Automate database migrations on EC2 instance's Postgres server

Open
#607 1 comment 0 reactions 0 assignees View on GitHub
Complexity: Large dependency Feature: Architecture Feature: Infrastructure Role: Back End Role: DevOps size: 3pt
Dominant language
Python
Stars
45
Forks
22
Avg merge
15d 1h
Merged PRs (30d)
1

Description

## Dependencies
- [From Tyler in comments:](https://github.com/hackforla/HomeUniteUs/issues/607#issuecomment-2136271619) Recommend moving to ice box until Incubator integration established, the parameters for this solution will hinge on implementation details coordinated with ops/incubator admins
#

### Overview

Although the API has [`alembic`](https://alembic.sqlalchemy.org), 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`](https://github.com/hackforla/HomeUniteUs/blob/26185b6748db75bf662134b6937567bb989a9989/.github/workflows/build-deploy-ec2.yml#L109) 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`](https://github.com/hackforla/HomeUniteUs/blob/26185b6748db75bf662134b6937567bb989a9989/.github/workflows/build-deploy-ec2.yml#L109) 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`](https://github.com/hackforla/HomeUniteUs/blob/26185b6748db75bf662134b6937567bb989a9989/.github/workflows/build-deploy-ec2.yml)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.