dbmigrat

README

dbmigrat

Library for maintaining database migrations in several locations (repos).

Installation

go get github.com/graaphscom/monogo/dbmigrat

Example code

• example test
• example ecommerce app

Usage

Let's assume we're building a simple e-commerce app. It's split into the following modules:

• auth (responsible for managing users and access)
• billing (contains logic related to placing order and payment)
• inventory (responsible for managing available products)

Each of the above modules needs to store data in a database. The below diagram represents tables we need to have at the first iteration of our project:

That's how our project's directory layout looks like:

|-- ecommerceapp
|   |-- auth
|   |   `-- migrations
|   |       |-- 0.init.down.sql
|   |       `-- 0.init.up.sql
|   |-- billing
|   |   `-- migrations
|   |       |-- 0.init.down.sql
|   |       `-- 0.init.up.sql
|   |-- inventory
|   |   `-- migrations
|   |       |-- 0.init.down.sql
|   |       `-- 0.init.up.sql
|   `-- main.go

We have separate 0.init.up.sql files containing SQL create table statements for each module.

Now let's build main.go file:

1. Add required imports and load migrations directories (go:embed is available since go 1.16)

package main

import (
	"embed"
	"fmt"
	"github.com/graaphscom/monogo/dbmigrat"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

//go:embed auth/migrations
var auth embed.FS
//go:embed billing/migrations
var billing embed.FS
//go:embed inventory/migrations
var inventory embed.FS

1. Connect to the database, create migrations log table (CreateLogTable does nothing when the table already exists):

package main

import (
	"embed"
	"fmt"
	"github.com/graaphscom/monogo/dbmigrat"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

//go:embed auth/migrations
var auth embed.FS
//go:embed billing/migrations
var billing embed.FS
//go:embed inventory/migrations
var inventory embed.FS

+ func main() {
+ 	db, err := sqlx.Open("postgres", "postgres://dbmigrat:dbmigrat@localhost:5432/dbmigrat?sslmode=disable")
+ 	if err != nil {
+ 		log.Fatalln(err)
+ 	}
+ 
+ 	pgStore := &dbmigrat.PostgresStore{DB: db}
+ 	err = pgStore.CreateLogTable()
+ 	if err != nil {
+ 		log.Fatalln(err)
+ 	}
+

1. Read migrations from filesystems and build migrations map:

package main

import (
	"embed"
	"fmt"
	"github.com/graaphscom/monogo/dbmigrat"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

//go:embed auth/migrations
var auth embed.FS
//go:embed billing/migrations
var billing embed.FS
//go:embed inventory/migrations
var inventory embed.FS

func main() {
	db, err := sqlx.Open("postgres", "postgres://dbmigrat:dbmigrat@localhost:5432/dbmigrat?sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}

	pgStore := &dbmigrat.PostgresStore{DB: db}
	err = pgStore.CreateLogTable()
	if err != nil {
		log.Fatalln(err)
	}

+ 	authMigrations, err := dbmigrat.ReadDir(auth, "auth/migrations")
+ 	if err != nil {
+ 		log.Fatalln(err)
+ 	}
+ 	billingMigrations, err := dbmigrat.ReadDir(billing, "billing/migrations")
+ 	if err != nil {
+ 		log.Fatalln(err)
+ 	}
+ 	inventoryMigrations, err := dbmigrat.ReadDir(inventory, "inventory/migrations")
+ 	if err != nil {
+ 		log.Fatalln(err)
+ 	}
+ 	migrations := dbmigrat.Migrations{
+ 		"auth":    authMigrations,
+ 		"billing": billingMigrations,
+ 		"inventory": inventoryMigrations,
+ 	}
+ 

1. Apply migrations. Notice that billing module has references to auth and inventory. For that reason third argument to dbmigrat.Migrate is dbmigrat.RepoOrder{"auth", "inventory", "billing"}

package main

import (
	"embed"
	"fmt"
	"github.com/graaphscom/monogo/dbmigrat"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

//go:embed auth/migrations
var auth embed.FS
//go:embed billing/migrations
var billing embed.FS
//go:embed inventory/migrations
var inventory embed.FS

func main() {
	db, err := sqlx.Open("postgres", "postgres://dbmigrat:dbmigrat@localhost:5432/dbmigrat?sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}

	pgStore := &dbmigrat.PostgresStore{DB: db}
	err = pgStore.CreateLogTable()
	if err != nil {
		log.Fatalln(err)
	}

    authMigrations, err := dbmigrat.ReadDir(auth, "auth/migrations")
	if err != nil {
		log.Fatalln(err)
	}
	billingMigrations, err := dbmigrat.ReadDir(billing, "billing/migrations")
	if err != nil {
		log.Fatalln(err)
	}
	inventoryMigrations, err := dbmigrat.ReadDir(inventory, "inventory/migrations")
	if err != nil {
		log.Fatalln(err)
	}
	migrations := dbmigrat.Migrations{
		"auth":    authMigrations,
		"billing": billingMigrations,
		"inventory": inventoryMigrations,
	}

+ 	logsCount, err := dbmigrat.Migrate(pgStore, migrations, dbmigrat.RepoOrder{"auth", "inventory", "billing"})
+ 	if err != nil {
+ 		log.Fatalln(err)
+ 	}
+ 	fmt.Printf("[dbmigrat] applied %d migrations\n", logsCount)
+ }

After running main.go three migrations should have been applied. Now, dbmigrat_log table has these rows: Notice that every migration has migration_serial equal to zero. That's because all these migrations were applied by a single run.

Let's add product's description

1. create migrations files with SQL alter table statements:

  |-- ecommerceapp
  |   |-- auth
  |   |   `-- migrations
  |   |       |-- 0.init.down.sql
  |   |       `-- 0.init.up.sql
  |   |-- billing
  |   |   `-- migrations
  |   |       |-- 0.init.down.sql
  |   |       `-- 0.init.up.sql
  |   |-- inventory
  |   |   `-- migrations
  |   |       |-- 0.init.down.sql
  |   |       |-- 0.init.up.sql
+ |   |       |-- 1.product_description.down.sql
+ |   |       `-- 1.product_description.up.sql
  |   `-- main.go

1. run the main.go which we have already built

Now, dbmigrat_log table has additional fourth row: Notice that this time migration serial is equal to one.

Rollback

Rollback allows for reverting migrations to a specified migration serial.

Let's roll back the addition of the product's description. Edit the main.go:

package main

import (
	"embed"
	"fmt"
	"github.com/graaphscom/monogo/dbmigrat"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

//go:embed auth/migrations
var auth embed.FS

//go:embed billing/migrations
var billing embed.FS

//go:embed inventory/migrations
var inventory embed.FS

func main() {
	db, err := sqlx.Open("postgres", "postgres://dbmigrat:dbmigrat@localhost:5432/dbmigrat?sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}

	pgStore := &dbmigrat.PostgresStore{DB: db}
	err = pgStore.CreateLogTable()
	if err != nil {
		log.Fatalln(err)
	}

	authMigrations, err := dbmigrat.ReadDir(auth, "auth/migrations")
	if err != nil {
		log.Fatalln(err)
	}
	billingMigrations, err := dbmigrat.ReadDir(billing, "billing/migrations")
	if err != nil {
		log.Fatalln(err)
	}
	inventoryMigrations, err := dbmigrat.ReadDir(inventory, "inventory/migrations")
	if err != nil {
		log.Fatalln(err)
	}
	migrations := dbmigrat.Migrations{
		"auth":      authMigrations,
		"billing":   billingMigrations,
		"inventory": inventoryMigrations,
	}

-	logsCount, err := dbmigrat.Migrate(pgStore, migrations, dbmigrat.RepoOrder{"auth", "inventory", "billing"})
+	logsCount, err := dbmigrat.Rollback(pgStore, migrations, dbmigrat.RepoOrder{"billing", "inventory", "auth"}, 0)
	if err != nil {
		log.Fatalln(err)
	}
-	fmt.Printf("[dbmigrat] applied %d migrations\n", logsCount)
+	fmt.Printf("[dbmigrat] rolled back %d migrations\n", logsCount)
}

Notice that comparing to the Migrate function, RepoOrder argument is reversed.

If we would like to roll back all migrations, we would provide -1 as the last argument to the Rollback.

Credits

ER diagram built with https://staruml.io

Documentation

Overview

Package dbmigrat allows for organizing database migrations across multiple locations (eg. across multiple repositories in monorepo project)

Example

package main

import (
	"embed"
	"fmt"
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"log"
)

//go:embed fixture
var exampleFixture embed.FS

func main() {
	// resetDB only for testing purposes - you may ignore it
	err := th.resetDB()
	if err != nil {
		log.Fatalln(err)
	}

	db, err := sqlx.Open("postgres", "postgres://dbmigrat:dbmigrat@localhost:5432/dbmigrat?sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}

	pgStore := &PostgresStore{DB: db}
	err = pgStore.CreateLogTable()
	if err != nil {
		log.Fatalln(err)
	}

	authMigrations, err := ReadDir(exampleFixture, "fixture/auth")
	if err != nil {
		log.Fatalln(err)
	}
	billingMigrations, err := ReadDir(exampleFixture, "fixture/billing")
	if err != nil {
		log.Fatalln(err)
	}
	migrations := Migrations{
		"auth":    authMigrations,
		"billing": billingMigrations,
	}

	checkRes, err := CheckLogTableIntegrity(pgStore, migrations)
	if err != nil {
		log.Fatalln(err)
	}
	if checkRes.IsCorrupted {
		log.Fatalln(fmt.Sprintf("Db migrations are corrupted: %+v", checkRes))
	}

	logsCount, err := Migrate(pgStore, migrations, RepoOrder{"auth", "billing"})
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Printf("[dbmigrat] applied %d migrations\n", logsCount)

	// Rollback migrations
	logsCount, err = Rollback(pgStore, migrations, RepoOrder{"billing", "auth"}, -1)
	if err != nil {
		log.Fatalln(err)
	}
	fmt.Printf("[dbmigrat] rolled back %d migrations\n", logsCount)

}

Output:

[dbmigrat] applied 3 migrations
[dbmigrat] rolled back 3 migrations

Index

• func Migrate(s store, migrations Migrations, repoOrder RepoOrder) (int, error)
• func Rollback(s store, migrations Migrations, repoOrder RepoOrder, toMigrationSerial int) (int, error)
• type IntegrityCheckResult
  • func CheckLogTableIntegrity(s store, migrations Migrations) (*IntegrityCheckResult, error)
• type Migration
  • func ReadDir(fileSys fs.FS, path string) ([]Migration, error)
• type Migrations
• type PostgresStore
  • func (s PostgresStore) CreateLogTable() error
• type Repo
• type RepoOrder

Examples

• Package

Functions

func Migrate

func Migrate(s store, migrations Migrations, repoOrder RepoOrder) (int, error)

Migrate applies migrations to the store in given repoOrder.

store is implemented by PostgresStore

migrations parameter is a map where keys are repositories names (string), values are arrays of properly ordered Migration.

repoOrder parameter is an array of repositories names (string). It determines order in which values from migrations map will be applied. eg. if migrations in repo "A" have foreign keys to repo "B" - then repoOrder should be {"B", "A"}

func Rollback

func Rollback(s store, migrations Migrations, repoOrder RepoOrder, toMigrationSerial int) (int, error)

Rollback rolls back migrations applied by Migrate func

repoOrder should be reversed one passed to Migrate func

migration serial represents applied migrations (from different repos) in single run of Migrate func. When toMigrationSerial == -1, then all applied migrations will be rolled back.

Types

type IntegrityCheckResult

type IntegrityCheckResult struct {
	IsCorrupted         bool
	RedundantRepos      map[Repo]bool
	RedundantMigrations map[Repo][]migrationLog
	InvalidChecksums    map[Repo][]migrationLog
}

IntegrityCheckResult contains information about objects which exist in DB log but not in passed migrations to the CheckLogTableIntegrity func.

func CheckLogTableIntegrity

func CheckLogTableIntegrity(s store, migrations Migrations) (*IntegrityCheckResult, error)

CheckLogTableIntegrity compares provided migrations with saved ones in migration log. It returns error when log contains migrations not present in migrations passed as argument to this func.

type Migration

type Migration struct {
	Description string
	Up          string
	Down        string
}

func ReadDir

func ReadDir(fileSys fs.FS, path string) ([]Migration, error)

ReadDir is helper func which allows for reading migrations from directory. Directory under provided path must contain files only. Files names must follow convention: a.b.c where a is incrementing int (0,1,2,3,..), b is description, c is direction - "up" or "down". Every migration must have corresponding up and down file. Up and down file for same migration must have same description.

Examples of valid files names:

0.create_users_table.up
0.create_users_table.down.sql
1.add_username_column.up
1.add_username_column.down.sql

type Migrations

type Migrations map[Repo][]Migration

type PostgresStore

type PostgresStore struct {
	DB *sqlx.DB
	// contains filtered or unexported fields
}

func (PostgresStore) CreateLogTable

func (s PostgresStore) CreateLogTable() error

CreateLogTable creates table in db where applied migrations will be saved. This should be called before use of other functions from dbmigrat lib.

type Repo

type Repo string

Repo is set of migrations. It allows for storing migrations in several locations. Example: e-commerce app might store authentication related migrations in repo "auth" while billing migrations in repo "billing".

type RepoOrder

type RepoOrder []Repo