migrations

Documentation

Overview

Package migrations provides standardized database migration tools.

Typical usage of the package requires a little bit of preparation in your project. The goal is that you will be able to write SQL files that are then bundled in your final compiled binary automatically. We use vfsgendev for this.

The final file system may look like this

.
├── assets_dev.go
├── assets_vfsdata.go
├── migrations.go
├── migrations
│   ├── 000_init.sql
│   ├── 001_helper_functions.sql
│   ├── 002_move_bigint_to_timestamptz.sql
│   ├── 003_hash_tokens.sql
│   ├── 004_service_account_creator.sql
│   ├── 005_retention_task_upsert.sql
│   ├── 006_project_timestamps.sql
│   ├── 007_project_stats.sql
│   └── 008_hard_delete_projects.sql
└── views
   └── 001_views.sql

The migrations and views folders will contain the raw SQL files for setting up and migrating your application. They are required to exist.

Additionally, you will need to setup the assets_dev.go and migrations.go files.

// assets_dev.go

// +build dev
package db

import (
	"net/http"

	"github.com/contiamo/go-base/v2/pkg/fileutils/union"
)

// Assets contains the static SQL file assets for setup and migrations
var Assets = migrations.NewSQLAssets(migrations.SQLAssets{
	Migrations: http.Dir("migrations"),
	Views:      http.Dir("views"),
})

and then

// migrations.go

package db

import (
	"github.com/contiamo/go-base/v2/pkg/db/migrations"
	"github.com/contiamo/go-base/v2/pkg/queue/postgres"
	"github.com/contiamo/app/pkg/config"
)

var dbConfig = migrations.MigrationConfig{
	MigrationStatements: []string{
		"001_helper_functions.sql",
		"002_move_bigint_to_timestamptz.sql",
		"003_hash_tokens.sql",
		"004_service_account_creator.sql",
		"005_retention_task_upsert.sql",
		"006_project_timestamps.sql",
		"007_project_stats.sql",
		"008_hard_delete_projects.sql",
	},
	ViewStatements: []string{},
	Assets: Assets,
}

var queue = migrations.QueueDBConfig{
	References: []postgres.ForeignReference{
		// To add a new reference you have to write a separate migration.
		// Once this table structure created for the first time, it will never be modified
		{
			ColumnName:       "message_id",
			ColumnType:       "UUID",
			ReferencedTable:  "messages",
			ReferencedColumn: "message_id",
		},
	},
}

var PrepareDatabase = migrations.NewPrepareDatabase(dbConfig, &queue, config.Version)
var Init = migrations.NewIniter(Assets, &queue)
var ConfigureViews = migrations.NewPostIniter(dbConfig.ViewStatements, Assets)

Index

• func GetJitter(interval time.Duration) time.Duration
• func NewIniter(assets http.FileSystem, queueConfig *QueueDBConfig) func(context.Context, *sql.DB) error
• func NewMigrater(stmts []string, assets http.FileSystem) func(context.Context, *sql.DB) error
• func NewPostIniter(stmts []string, assets http.FileSystem) func(context.Context, *sql.DB) error
• func NewPrepareDatabase(config MigrationConfig, queueConfig *QueueDBConfig, appVersion string) func(context.Context, *sql.DB) error
• func NewSQLAssets(assets SQLAssets) http.FileSystem
• type MigrationConfig
• type QueueDBConfig
• type SQLAssets

Functions

func GetJitter

func GetJitter(interval time.Duration) time.Duration

GetJitter returns a duration within [0.05*interval, interval]

func NewIniter

func NewIniter(assets http.FileSystem, queueConfig *QueueDBConfig) func(context.Context, *sql.DB) error

NewIniter creates a db init command that will execute the 000_init.sql

The assets FileSystem must contain `migrations/000_init.sql`.

If queueConfig is nil, then the postgres queue will not be initialized.

func NewMigrater

func NewMigrater(stmts []string, assets http.FileSystem) func(context.Context, *sql.DB) error

NewMigrater creates a migration command that will execute the given list of migrations

func NewPostIniter

func NewPostIniter(stmts []string, assets http.FileSystem) func(context.Context, *sql.DB) error

NewPostIniter creates a db init command that will execute the view sql, and other post init sql.

func NewPrepareDatabase

func NewPrepareDatabase(config MigrationConfig, queueConfig *QueueDBConfig, appVersion string) func(context.Context, *sql.DB) error

NewPrepareDatabase is the standard entrypoint for setting up and migrating the application db. This command will ensure that migration tracking is configured, check for any required migration commands that need to be run, and then update the migration tracking table.

You can inspect the migrations table of your app using

select * from migrations;
select * from migrations WHERE applied_at > '<timestamp>';
select * from mgirations WHERE version = '<app version>';

The migration tracking will track _both_ the individual migrations (using a hash of the sql) _and_ the migrations in a specific version of the app. If the app version is found in the migration history then it assumes that all of the required migrations have been run and exists early.

To force a migration to rerun, you will need to delete the record from the tracking table

delete from migrations where version = '<migration version>';

func NewSQLAssets

func NewSQLAssets(assets SQLAssets) http.FileSystem

NewSQLAssets creates a new filesystem that is compatible with the migration utilities.

Types

type MigrationConfig

type MigrationConfig struct {
	MigrationStatements []string
	ViewStatements      []string
	Assets              http.FileSystem
	JitterInterval      time.Duration
}

MigrationConfig contains the ordered migration and view sql statements that will be run during startup as well as the assets filesystem object. Use NewSQLAssets to generate this filesystem object.

type QueueDBConfig

type QueueDBConfig struct {
	References []postgres.ForeignReference
}

QueueDBConfig hold the db configuration values required to initialize the db tables for the queue. Specifically, it holds the definitions for any required foreign keys.

type SQLAssets

type SQLAssets struct {
	Migrations http.FileSystem
	Views      http.FileSystem
}

SQLAssets determines which filesystem object is used for the migrations flow and which for the views flow. The `000_init.sql` but live within the migrations file system.