Starchart: Migration!

This week, I created worked on utilized Prisma migration for Starchart project.

The Work

This PR allows staging and production to use Prisma migration, through deploying migration files.

Changes

• Add migration in docker-entrypoint.sh, when environment variable DATABASE_SETUP=1
• Add migration file creation script, with required permission, in package.json
• Create an initial migration file in prisma/migrations
• Add documentation for making migration files in DEPLOY.md
• Adjust docker deployment for update

Update (April 20, 2023): we later decided to change DATABASE_SETUP to DANGER_DATABASE_WIPE_REINITIALIZE in this PR to warn future devs about turning this variable on.

Essentially, a migration file is essentially a set of SQL queries Prisma generates that would get a database to a desired state, by comparing your current schema and the latest migration file. Think of Prisma migration as git tracking your commits. For more details, see Prisma documentation.

Migration normally keep data in the database while updating the database schema. A part of this is done through shell script.

Shell Script Snippet (file from the PR)

database_setup() {
  echo "Running database reset and setup..."
  DATABASE_URL=$(</run/secrets/DATABASE_URL)
  export DATABASE_URL

  npx prisma migrate reset --force --skip-seed

  unset DATABASE_URL
  echo "Database setup complete"
}

database_migration() {
  echo "Running database migration..."
  DATABASE_URL=$(</run/secrets/DATABASE_URL)
  export DATABASE_URL

  npx prisma migrate deploy

  unset DATABASE_URL
  echo "Database migration complete"
}

if [[ $DATABASE_SETUP == "1" ]]; then
  database_setup
  clear_redis

else
  database_migration
fi

Only when DATABASE_SETUP =1 is an environment variable, we would wipes the existing database and apply migration files with database_setup. npx prisma migrate reset --force --skip-seed resets/wipes the database and applies migration files, and Prisma would know what migration is applied already and skip them, instead of causing conflict.
--force skips confirmation, and --skip-seed skips seeding.

Otherwise, database_migration applies migration files for normal database updates, assuming no changes to the database schema is done without Prisma migration. npx prisma migrate deploy deploys migration files to change schema without deleting data.

In both database_migration() and database_setup(), we pull the database URL out of secrets, since Prisma requires it to know which database to connect to. After using it, we clear that variable.

Creating A Migration File For Schema Change

Because staging and production use Prisma migration, changes in the schema need to become migration files for Prisma migration to run the files to apply changes, via prisma migrate dev --create-only --skip-seed. It would ask for a name for the file.
The script doesn't seed database or apply migration, just creating a migration file and also generating Prisma client (default behavior) in case a dev forgets.

When Prisma creates a migration file, it requires permission to create a temporary shadow database in a database to do this, which means a user of said database needs CREATE, ALTER, DROP, and REFERENCES privilege to do this. Make sure such user is used for DATABASE_URL environment variable that Prisma use to connect to a database.

Docker

We have multiple instance for production and staging, using Docker.
While Prisma does have advisory locking, which would skip repeated deploy within last 10 seconds (can't be configured), it's probably safer to run only one instance to initiate changes to database schema, then bring up the other instance.

Snippet: (file from the PR)

    deploy:
    ...
      update_config:
        parallelism: 1
        delay: 20s
        failure_action: rollback
        order: stop-first

This is the relevant change in our docker-compose YAML files for staging and production.
It would stop existing containers, and then update one container at a time. It would waiting 20 seconds in-between. If something fails, it would rollback.