db

README

How to rollback the PROD DATABASE to a previous migration version

With Google's cloud_sql_proxy (link) and gcloud authenticated with DevOps user credentials:

export SHERLOCK_DB_INSTANCE_PROJECT=$(vault read -field=project secret/suitable/sherlock/prod/postgres/instance)
export SHERLOCK_DB_INSTANCE_REGION=$(vault read -field=region secret/suitable/sherlock/prod/postgres/instance)
export SHERLOCK_DB_INSTANCE_NAME=$(vault read -field=name secret/suitable/sherlock/prod/postgres/instance)
cloud_sql_proxy -instances=$SHERLOCK_DB_INSTANCE_PROJECT:$SHERLOCK_DB_INSTANCE_REGION:$SHERLOCK_DB_INSTANCE_NAME=tcp:5432

In a new console, with golang-migrate (brew install golang-migrate):

export SHERLOCK_DB_NAME=$(vault read -field=db secret/suitable/sherlock/prod/postgres/sherlock-db-creds)
export SHERLOCK_DB_USER=$(vault read -field=username secret/suitable/sherlock/prod/postgres/sherlock-db-creds)
export SHERLOCK_DB_PASSWORD=$(vault read -field=password secret/suitable/sherlock/prod/postgres/sherlock-db-creds)
export SHERLOCK_DB_URL="postgres://$SHERLOCK_DB_USER:$SHERLOCK_DB_PASSWORD@localhost:5432/$SHERLOCK_DB_NAME?sslmode=disable"
migrate -path ./db/migrations -database $SHERLOCK_DB_URL version

You can then use something like migrate -source ./db/migrations -database $SHERLOCK_DB_URL goto to set the database to a particular numbered migration version. See the migration files for the available numbered migration versions.

Documentation

Index

• Variables

Variables

var MigrationFiles embed.FS

MigrationFiles stores the migration SQL files themselves inside Sherlock's binary. If we were to reference these files from the machine's filesystem, the relative path would change based on the entrypoint file, which is quite inconvenient to handle. Instead, we reference them with a relative path here with `embed` so we can import it like any other variable throughout Sherlock.