Prepare for, document, and automate our database migration and deployment strategy

[humphd]

In order to ship the app to staging(#43) we need to get ready to handle database migrations.

Currently, we are working with a development database locally, which can be blown away whenever we need to fix something. That won't work as soon as we move this to staging and production (hopefully in 0.5). In those cases, we'll have to create migrations and run them.

I am not an expert on this, but here is my current understanding of the problem and solution. Hopefully others will help with the research and implementation. I am basing this largely on the Prisma Migrate Mental Model:

1. In development, we use prisma db push to force the local development database to use our schema. This has been fine for development up to now, but won't work going forward
2. In staging and production (henceforth referred to as "prod"), we need to use prisma migrate deploy to run migrations against the database and synchronize it with our desired schema.
3. Doing 2. requires us to first create a set of migrations in prisma/migrations, which are SQL files that describe transformations from one database state to another (e.g., adding a table, creating an index, etc). We do that using prisma migrate dev --name init.
4. Now we can use prisma migrate deploy in prod to update our database (i.e., push schema and/or apply migrations).
5. Whenever we need to alter our schema after we establish our baseline migration, we need to create a new migration SQL file using prisma migrate dev --name <name_of_migration>. This SQL migration would then be applied in prod using prisma migrate deploy
6. We need to include prisma migrate deploy in our webhook deploy step
7. Running prisma migrate deploy automatically in prod will require updates to our Dockerfile, in order to create an image layer that has the necessary dependencies and migrations
8. This entire process needs to be carefully document in our CONTRIBUTING and DEPLOY docs

What else am I missing?

If we want to do any significant database schema changes before 1.0, it might be nice to do them before we set this baseline.

cc @cychu42, @dadolhay, @sfrunza13, @shawnyu5, who might all be interested in parts of this, or have thoughts. Anyone else is also welcome to take bits of this.

[ghost]

@humphd I agree with the above, for multiple reasons

• I have seen with my own eyes prisma db push mess up (did not lose data, but did not become 100% consistent with the schema either) In these cases, even if Prisma messes up the migration script it can be manually corrected (but that individual has to be quite good in mysql)

• This adds a second chance for the reviewer(s) to catch any major mistakes reading through the migration file

• It allows us to (manually) do data migrations (column rename, data type transformations, etc.)

• I think we should only establish the baseline once this becomes delpoyed to prod

• We should mandate for someone to do testing on staging !!with pre-existing data!! before a migration can be deployed to prod: CI and local testing will bring up the db from scratch, but we'd still need to test the migrations too that is only possible on staging, (or on a copy of a deployed database)

TL;DR I agree, but I'd put it off as long as possible

P.S. Could we do a full export (db dump) of the prod database before a migration is run, and keep it in a secure storage for like 1 month?

[humphd]

@dadolhay maybe we should avoid automating the migrations with Prisma, and if we need to do one, we have to do it manually? Then we can bring down the instances, run a backup, migrations, test manually, etc. We'll still have a problem, though. In staging, our idea was to deploy when we land on main. If we ever land a schema change on main, it will cause issues. So we'd have to somehow come-up with a work around for this. With production, we can do it when we tag, so it requires manually opt-in. It's still a bit complicated. Thoughts?

I agree with leaving the baseline as long as possible. I don't want to deal with migrations any more often than we have to. While we're still pre 1.0, it would be good to not get into this.

[cychu42]

Agree. If we can put it off, we should. I like only triggering deployment on certain tags, as long as we keep clear documentation so people know what to do. The only two additional things on my mind now are:

• Can we make shadow database that's part of prisma migrate dev?
• prisma migrate deploy doesn't deal with drift, so no one should directly change schema of the prod database.

[humphd]

I think you're both making great points. I wonder if we should decide to avoid all automatic schema updates pre-1.0, try hard to nail down the schema ASAP, and ask ourselves how to approach ongoing maintenance when we get to 1.0 (i.e, don't assume we'll do it).

This leaves another question that I need to answer ASAP: what does the manual process for creating/updating the databases on staging and production look like?

The Docker image we build for staging/production currently doesn't include the Prisma tools, schema, etc. I could add a build stage that does include this, and we could use that stage's image to manually run prisma db push or whatever.

Help me think through the flow we'll use, since I need to do it in the next two weeks in order to get this up on staging.

[ghost]

I think it is going to be needed to do an automated (pre-scripted to be precise) migration method post-1.0

• I think we should make sure that all instances are killed before a migration is run, to ensure there is no race-condition between a migration and an incompatible (with the new db structure) instance
• Migration should be a separate step in the startup process, blocking all other processes from starting up. If migration fails, startup should also fail, essentially bringing down the whole system
• I think we will need explicit migration scripts (aka. sql files)
• These SQL files will probably be auto-generated, but (especially if data migration / column type change is also in play) manually edited after auto-generation (but before deploy)
• Every time a migration is done, the above sql file should be cross-referenced with the prisma schema changes. Who does this, will really have to know their MySQL which is sub-optimal, but I don't have a better way, especially after prisma auto migration slipped the last time I tried it

[humphd]

OK, I'll leave this open for 1.0, and wrote https://github.com/DevelopingSpace/starchart/pull/325 to try and do a manual solution for the 0.6-1.0.

[humphd]

Adding this to the 0.9 milestone, assigning to @cychu42

[humphd]

Plan/TODO:

• we should audit the existing database schema to make sure we're happy with what it contains (i.e., change if necessary now)
• we should create a baseline and use migrations in production going forward
• on staging, switch to using migrations vs. db push --force automatically so that we can do a dry-run of the update to the db before we go to production.
• on production, use migrations automatically
• figure out how to properly do the updates with multiple instances. Suggestion: bring down all existing instances before we start the update/migrations in docker-compose.
• Update the logic around DATABASE_SETUP to make it more clear that it's dangerous. Use migrations by default instead.

[cychu42]

I'm think we might want to turn the prisma migrate dev command into a script for future users. Also, definitely tell people that they need to create migration files to affect staging and production in documentation, once migration is in place.

[humphd]

We've tested this on staging, and it's all working. Well done @cychu42.