Starchart: Migration!
Tags: javascript, webdev, prisma, docker
Published: 2023-04-14 01:05:48

This week, I created worked on utilized Prisma migration for [Starchart](https://github.com/DevelopingSpace/starchart) project.

## The Work

This [PR](https://github.com/DevelopingSpace/starchart/pull/601) allows staging and production to use [Prisma migration](https://www.prisma.io/docs/concepts/components/prisma-migrate), 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](https://www.prisma.io/docs/concepts/components/prisma-migrate/mental-model
). 

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](https://github.com/DevelopingSpace/starchart/pull/601/files#diff-79738685a656fe6b25061bb14181442210b599f746faeaba408a2401de45038a))

```
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`](https://www.prisma.io/docs/reference/api-reference/command-reference#migrate-reset) 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`](https://www.prisma.io/docs/reference/api-reference/command-reference#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`](https://www.prisma.io/docs/reference/api-reference/command-reference#migrate-dev). 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](https://www.prisma.io/docs/concepts/components/prisma-migrate/migrate-development-production#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](https://github.com/DevelopingSpace/starchart/pull/601/files#diff-15f21dad8b8f4edc4748223f6f275c8b4bb7a406a9aedcb4c05ec88123089de1))
```
    deploy:
    ...
      update_config:
        parallelism: 1
        delay: 20s
        failure_action: rollback
        order: stop-first
```
This is the relevant change in our [docker-compose](https://docs.docker.com/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.