Example Migration Script

migrate.go

package main

import (
	"encoding/json"
	"encoding/xml"
	"log"
	"strconv"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var (
	oldDB *sqlx.DB
	newDB *sqlx.DB
	err   error
)

type Client struct {
	ID      int    `db:"hp_c_id"`
	Ready   string `db:"hp_c_ready"`
	Invited string `db:"hp_c_invited"`
	Client  string `db:"hp_c_client"`
	User    string `db:"hp_c_user"`
	Year    string `db:"hp_c_year"`
	Content string `db:"hp_c_content"`
	Creator string `db:"hp_c_creator"`
	Editor  string `db:"hp_c_editor"`
}

func main() {

	oldDB, err = sqlx.Open("mysql", "user:pass@tcp(hostname)/dbname?parseTime=true")
	if err != nil {
		log.Fatalf("Error connecting to old database: %s\n", err)
	}

	newDB, err = sqlx.Open("mysql", "user:pass@tcp(hostname)/dbname?parseTime=true")
	if err != nil {
		log.Fatalf("Error connecting to new database: %s\n", err)
	}

	clients := []Client{}
	oldDB.Select(&clients, `
		SELECT
			hp_c_id,
			hp_c_ready,
			hp_c_invited,
			hp_c_client,
			hp_c_user,
			hp_c_year,
			hp_c_content,
			hp_c_creator,
			hp_c_editor
		FROM
			hp_client
		ORDER BY
			hp_c_id ASC
	`)

	if _, err := oldDB.Exec(`TRUNCATE handlingsplan_client_entry`); err != nil {
		log.Fatalf("Truncate command failed: %s\n", err)
	}

	for i := 0; i < len(clients); i++ {
		if clients[i].ID <= 867 {
			newDB.Exec(`
				INSERT INTO handlingsplan_client_entry (
					hp_c_id,
					hp_c_ready,
					hp_c_invited,
					hp_c_client,
					hp_c_user,
					hp_c_year,
					hp_c_content,
					hp_c_creator,
					hp_c_editor
				) VALUES (
					:hp_c_id,
					:hp_c_ready,
					:hp_c_invited,
					:hp_c_client,
					:hp_c_user,
					:hp_c_year,
					:hp_c_content,
					:hp_c_creator,
					:hp_c_editor
				)
			`, &clients[i])

			continue
		}

		var xmlData interface{}
		err = xml.Unmarshal([]byte(clients[i].Content), &xmlData)
		if err != nil {
			log.Printf("Failed to parse XML: %s\n", err)
		}

		var form Form
		// create new structure

		counting := 0
		subcounting := 0

		for k, v := range xmlData["maincat"] {
			// add to json structure
		}

		bytes, err := json.Marshal(form)
		if err != nil {
			log.Printf("Error encoding JSON: %s\n", err)
		}

		newDB.Exec(`
			INSERT INTO handlingsplan_client_entry (
				hp_c_client,
				hp_c_year,
				hp_c_content,
				hp_c_creator
			) VALUES ($1, $2, $3, $4)
			`,
			clients[i].Client,
			strconv.FormatInt(int64(time.Now().Year()), 10),
			bytes,
			clients[i].Creator,
		)

	}

}

type Form struct {
	Meta   Meta     `json:"meta"`
	Fields []string `json:"fields"`
}

type Meta struct {
	Company       string         `json:"company"`
	Department    string         `json:"department"`
	Collaborators []Collaborator `json:"collaborators"`
}

type Collaborator struct {
	date time.Time `json:"date"`
}