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_INSTANCEPROJECT=$(vault read -field=project secret/suitable/sherlock/prod/postgres/instance)
export SHERLOCK_DB_INSTANCEREGION=$(vault read -field=region secret/suitable/sherlock/prod/postgres/instance)
export SHERLOCK_DB_INSTANCENAME=$(vault read -field=name secret/suitable/sherlock/prod/postgres/instance)
cloud_sql_proxy -instances=$SHERLOCK_DB_INSTANCEPROJECT:$SHERLOCK_DB_INSTANCEREGION:$SHERLOCK_DB_INSTANCENAME=tcp:5432

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

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

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

Documentation

Overview

Package db contains a variety of utility functions and test helpers for working with Sherlock's database. While we are using Gorm for everyday application-level sql operations, we are opting for golang-migrate in order to provide more robust and reversible sql migrations.

Index

• func ApplyMigrations(changeLogPath string, config *viper.Viper) error
• func Connect(config *viper.Viper) (*gorm.DB, error)

Functions

func ApplyMigrations

func ApplyMigrations(changeLogPath string, config *viper.Viper) error

ApplyMigrations is a utility function intended for use in integration tests and local development where changelogs can be applied to a local postgres instance during startup

func Connect

func Connect(config *viper.Viper) (*gorm.DB, error)

Connect is a utility function that accepts a viper instance containing database configs and returns a gorm database connection