choonkeat / dbmigrate

rails migrate inspired approach to database schema migrations but with plain sql files. and much faster.

No Plan.

dbmigrate's Issues

Usage with docker-compose

I'm trying to use docker-compose to have dbmigrate run some schema migration SQL scripts against a MariaDB instance running in another docker container, but for some reason, I can't connect to the database.

This might not be specific to dbmigrate - it feels like a problem with docker-compose networking - but maybe you have faced this kind of problem before?

Any pointers would be appreciated.

docker-compose.yaml

version: '3.5'

services:
    database:
        image: mariadb:latest
        container_name: users_database
        networks:
            backend:
                ipv4_address: 10.6.0.3
        ports:
            - 3306:3306
        environment:
            - MYSQL_DATABASE=usersdb
            - MYSQL_USER=bob
            - MYSQL_PASSWORD=***********
            - MYSQL_ROOT_PASSWORD=***********
        volumes:
            - ./data/mysql:/var/lib/mysql

    schema:
        image: choonkeat/dbmigrate
        container_name: users_schema
        depends_on:
            - database
        environment:
            - DATABASE_DRIVER=mysql
            - DATABASE_URL='bob:***********@tcp(10.6.0.3:3306)/usersdb?multiStatements'
        volumes:
            - ./data/mysql:/var/lib/mysql
            - ./schema/migrations:/db/migrations
        command: ["-up"]

networks:
    backend:
        driver: bridge
        ipam:
            config:
                - subnet: 10.6.0.0/16

schema logs

Attaching to users_schema
schema_1    | 2019/05/25 02:46:38 unable to query existing versions: dial tcp 10.6.0.3:3306: connect: connection timed out
users_schema exited with code 1

List the versions in the database, or list the versions NOT in the database

useful when our migration failed and we want to output something to easily assure what's the schema versions statuses

-versions-applied cli flag that prints the version numbers seems useful, but is easily doable by yourself

SELECT version FROM dbmigrate_versions ORDER BY version ASC

the output tend to be huge too (as migrations accumulates)

-versions-pending cli flag seems more uniquely served by this project; not easily done by yourself

• read the files in -dirname
• de-duplicate the .up.sql and .down.sql pairs
• extract the version number prefix
• compare against the content of dbmigrate_versions table
• print the missing versions

the output tend to be small too: most of the time, especially after a run of -up, most versions would have been applied.

conclusion

so, probably implement a -versions-pending flag

Tempting to add a `-force-version` flag

Original intention to add a -force-version flag is to aid migration from an existing schema tool, i.e.

1. re-use existing up and down sql files
2. figure out the existing version
3. make dbmigrate insert those version numbers into dbmigrate_versions table so they won't be [re]applied when we run dbmigrate -up henceforth

But -force-version N can also be interpreted as "forcibly insert/delete from dbmigrate_versions until it looks like we are at version N". Or interpreted as "only insert missing versions <= N, ignoring higher versions" (which works specifically for original intent)

At this point it is looking like scope creep.

So instead of extending the feature set of dbmigrate, hopefully developers can wield their own migration script.

---

For example, if we're migrating from golang-migrate/migrate, this can help

with Bash

#!/usr/bin/env bash

# config, overwrite where necessary
DATABASE_URL=$DATABASE_URL
DATABASE_DRIVER=$DATABASE_DRIVER
DATABASE_DIR=${DATABASE_DIR:-db/migrations}
PENDING_VERSIONS_COMMAND="dbmigrate -url=$DATABASE_URL -driver=$DATABASE_DRIVER -dir=$DATABASE_DIR -versions-pending"

LATEST_VERSION="$(psql $DATABASE_URL -t -c 'SELECT version FROM schema_migrations;')"
if test -z "$LATEST_VERSION"; then
  echo No versions in schema_migrations
  exit 0
fi

# FYI these are pending before we begin our process
echo [before] dbmigrate -dir=$DATABASE_DIR -versions-pending
$PENDING_VERSIONS_COMMAND
echo ""

echo 1. Inserting version until $LATEST_VERSION ...
# we could do `for CURRENT_VERSION in $(dbmigrate -versions-pending)` ...
# BUT prefer this script to be idempotent, so we do this instead:
# - perform an INSERT for each version we find in the $DATABASE_DIR
# - terminate the loop when we see $LATEST_VERSION
PENDING_VERSIONS=`echo $($PENDING_VERSIONS_COMMAND)`
for file in $DATABASE_DIR/*.up.sql; do
  CURRENT_VERSION="$(echo $file | sed 's/^.*\///g' | sed 's/_.*//g')"
  CURRENT_MATCH=" $CURRENT_VERSION "
  case " $PENDING_VERSIONS " in
  *$CURRENT_MATCH*)
    SQL="INSERT INTO dbmigrate_versions (version) VALUES ('$CURRENT_VERSION');"
    echo $SQL
    psql $DATABASE_URL --quiet --tuples-only --command="$SQL" || exit 1
    ;;
  esac
  if test $LATEST_VERSION = "$CURRENT_VERSION"; then
    echo 2. Stop on $file
    break
  fi
done

# FYI these are pending after we're done
echo 3. Done inserting
echo ""
echo [after] dbmigrate -dir=$DATABASE_DIR -versions-pending
$PENDING_VERSIONS_COMMAND

with Go

(modified from main.go)

package main

import (
	"context"
	"database/sql"
	"flag"
	"fmt"
	"log"
	"net/http"
	"os"
	"time"

	"github.com/choonkeat/dbmigrate"
	"github.com/pkg/errors"

	_ "github.com/lib/pq"
)

var (
	dirname     string
	databaseURL string
	driverName  string
	timeout     time.Duration
)

func main() {
	if err := _main(); err != nil {
		log.Fatalln(err.Error())
	}
}

func _main() error {
	// options
	flag.StringVar(&dirname,
		"dir", "db/migrations", "directory storing all the *.sql files")
	flag.StringVar(&databaseURL,
		"url", os.Getenv("DATABASE_URL"), "connection string to database, e.g. postgres://user:pass@host:5432/myproject_development")
	flag.StringVar(&driverName,
		"driver", os.Getenv("DATABASE_DRIVER"), "drivername, e.g. postgres")
	flag.DurationVar(&timeout,
		"timeout", 5*time.Minute, "database timeout")
	flag.Parse()

	m, err := dbmigrate.New(http.Dir(dirname), driverName, databaseURL)
	if err != nil {
		return errors.Wrapf(err, "dbmigrate.New")
	}
	defer m.CloseDB()

	ctx, cancel := context.WithTimeout(context.Background(), timeout)
	defer cancel()

	db, err := sql.Open(driverName, databaseURL)
	if err != nil {
		return errors.Wrapf(err, "sql.Open")
	}
	defer db.Close()
	var currentSchemaVersion string
	row := db.QueryRowContext(ctx, "SELECT version FROM schema_migrations")
	if err = row.Scan(&currentSchemaVersion); err != nil {
		return errors.Wrapf(err, "row.Scan")
	}
	fmt.Println("schema_migrations.version", currentSchemaVersion)

	pendingVersions, err := m.PendingVersions(ctx)
	if err != nil {
		return errors.Wrapf(err, "pending versions")
	}
	fmt.Println("[BEFORE] dbmigrate_versions.version", pendingVersions)

	for _, pendingVersion := range pendingVersions {
		if pendingVersion == currentSchemaVersion {
			// ok we need to migrate schema_migrations to dbmigrate_versions
			tx, err := db.BeginTx(ctx, &sql.TxOptions{})
			if err != nil {
				return errors.Wrapf(err, "db.BeginTx")
			}
			for _, version := range pendingVersions {
				tx.ExecContext(ctx, "INSERT INTO dbmigrate_versions (version) VALUES ($1)", version)
				fmt.Printf("INSERT INTO dbmigrate_versions (version) VALUES (%s);\n", version)
				if version == currentSchemaVersion {
					fmt.Printf("Ignoring any further migrations in %s\n", dirname)
					break
				}
			}
			if err = tx.Commit(); err != nil {
				return err
			}
		}
	}

	pendingVersions, err = m.PendingVersions(ctx)
	if err != nil {
		return errors.Wrapf(err, "pending versions")
	}
	fmt.Println("[AFTER] dbmigrate_versions.version", pendingVersions)

	return nil
}