Simple Schema Migration With Go

In 2010 I wrote about a simple method for migrating schema with Python . Here’s the same kind of thing, but this time using Go. I’m using the SQLx extensions for database/sql and PostgreSQL as my database. The only requirement is an extra table named schema with a single version in it. This tracks which version of the schema update has been applied. This table is created in the first schema update below.

It uses transactions for each change and aborts if there is an error. Place your changes into separate strings in the schema array at the top of the code.

Each section should end with updating version to the next value using set, not insert, because it needs to have a single value.

package main

import (
	"fmt"
	"github.com/jmoiron/sqlx"
	// Driver for PostgreSQL
	_ "github.com/lib/pq"
)

var schema = []string{
	`CREATE TABLE schema (version int NOT NULL); INSERT INTO schema (version) VALUES(1);`,
	`CREATE TABLE users (name text, password text); UPDATE schema SET version=2;`}

// Setup connects to the database and updates any existing schema
func Setup(host string, port int, database, user, password string) (*sqlx.DB, error) {
	connection := fmt.Sprintf("dbname=%s user=%s password=%s host=%s port=%d sslmode=disable",
		database, user, password, host, port)
	db, err := sqlx.Connect("postgres", connection)
	if err != nil {
		fmt.Printf("Failed to connect to the database: %s\n", err)
		return nil, err
	}

	// Try to get the version from the schema table, default to 0 if there is an error
	var schemaVersion int
	if err := db.Get(&schemaVersion, "SELECT version from schema"); err != nil {
		schemaVersion = 0
	}

	// Then execute all the schema from schemaVersion to the end of the schema array
	for i := schemaVersion; i < len(schema); i++ {
		fmt.Printf("Upgrading schema from %d to %d\n", schemaVersion, i+1)
		tx, err := db.Begin()
		if err != nil {
			fmt.Printf("Error starting transaction: %s\n", err)
			return nil, err
		}

		_, err = db.Exec(schema[i])
		if err != nil {
			fmt.Printf("Error executing SQL '%s': %s\n", schema[i], err)
			return nil, err
		}
		if err = tx.Commit(); err != nil {
			fmt.Printf("Error committing transaction: %s\n", err)
			return nil, err
		}
	}

	return db, nil
}
func main() {
	_, err := Setup("localhost", 5432, "DBNAME", "DBUSER", "DBPASSWORD")
	if err != nil {
		fmt.Println("Database setup failed")
	}
}