Find a Way to Update Migrations as You Go

[sydneyrd]

Find a Way to Update Migrations as You Go
Description: Implement or integrate migration tool for database schema changes throughout development.
AC:
Backend: Research and choose migration tool (Alembic for Python/FastAPI)
Backend: Setup migration configuration files
Backend: Create initial migration from existing schema
Backend: Script to run migrations on deployment
Backend: Rollback capability for migrations
Backend: Migration version tracking in database
Documentation: How to create new migrations
Documentation: How to apply and rollback migrations
Tech notes:

[dylanjohnson131]

Tech Notes:

"Research and Choose Migration Tool"
--When working with Alembic, we should also use the ORM SQLAlchemy, as it will help define our database further and seems to be a great "Sidekick" to Alembic as they seem to work in tandem with each other nicely. Alembic will allow us to automatically update migrations as we add new features through these two scripts:

1. (alembic revision --autogenerate -m "Lorem Ipsum") --- Which will allow us to compare our SQLAlchemy models to our current state in our database and create a migration file based on the changes made.

2. (alembic upgrade head) --- This is the script that will run the migration and update the database to the latest version.

"Setup Migration Configuration Files"
-- When setting up migration config files, it seems we already have everything we need currently, as we have both alembic.ini and alembic/env.py files.

"Create Initial Migration from Existing Schema"
-- When creating the initial migration from existing schema, you would run:
(alembic revision --autogenerate -m "initial migration") *it is important to note here that when working with existing schema, you should run an additional script afterwards:
(alembic stamp head) It is very important to run this so that you don't duplicate the CREATE TABLES statement.

"Script to Run Migrations on Deployment"
-- (alembic upgrade head) will allow you to run migrations on deployment and it will go in our docker file.

"Rollback Capability for Migrations"
-- The rollback capabilities for migrations are defined by running the script
(alembic downgrade -1) which rolls back a migration by one. If you have a specific version you would like to rollback to, you would run (alembic downgrade ). Finally, if you decide you need to roll everything back you would run (alembic downgrade base). In running any of these, it's best practice to test every time you autogenerate a downgrade, as there is potential for data loss during this process. In order to test, you would run (alembic revision --autogenerate -m "the changes made") to review the changes made.

"Migration Version Tracking in Database"
-- We would run "alembic current" to understand what and where our current database is. But let's say we wanted to see all migrations that exist? That's when we would run "alembic history".

"How to Create New Migrations"
-- Once we have made changes to our models, we will generate the migration by running the command (alembic revision --autogenerate -m "Lorem Ipsum"). We will then open the file associated with the migration we are creating and review it. After the reviewing the file and making sure all of the data is still there, we will then apply the migration locally by running (alembic upgrade head). Once verified that it works, you can run the git commands needed to push for the team to test and apply it.

"How to Apply and Rollback Migrations"
-- To apply a migration, you would run (alembic upgrade head) and to rollback a migration you would run (alembic downgrade -1) to rollback 1 migration.

[preston-robbins]

Dylan... hell of a job on this man. only suggestion i would say is to look into alternatives for your own knowledge.

approved