Migrations

With database created, we also need to create tables in which our data will be store. And best way to do that is using migrations. We will be using go-pg/migrations module. On GitHub page you can find basic installation and usage guide, and we will use that. Let's start by creating new directory migrations/ inside of project root and file file migrations/main.go:

package main

import (
  "flag"
  "fmt"
  "os"

  "rgb/internal/database"
  "rgb/internal/store"

  "github.com/go-pg/migrations/v8"
)

const usageText = `This program runs command on the db. Supported commands are:
  - init - creates version info table in the database
  - up - runs all available migrations.
  - up [target] - runs available migrations up to the target one.
  - down - reverts last migration.
  - reset - reverts all migrations.
  - version - prints current db version.
  - set_version [version] - sets db version without running migrations.
Usage:
  go run *.go <command> [args]
`

func main() {
  flag.Usage = usage
  flag.Parse()

  store.SetDBConnection(database.NewDBOptions())
  db := store.GetDBConnection()

  oldVersion, newVersion, err := migrations.Run(db, flag.Args()...)
  if err != nil {
    exitf(err.Error())
  }
  if newVersion != oldVersion {
    fmt.Printf("migrated from version %d to %d\n", oldVersion, newVersion)
  } else {
    fmt.Printf("version is %d\n", oldVersion)
  }
}

func usage() {
  fmt.Print(usageText)
  flag.PrintDefaults()
  os.Exit(2)
}

func errorf(s string, args ...interface{}) {
  fmt.Fprintf(os.Stderr, s+"\n", args...)
}

func exitf(s string, args ...interface{}) {
  errorf(s, args...)
  os.Exit(1)
}

As you can see this is similar to official example with some small changes. We are using SetDBConnection() and GetDBConnection() functions that we defined before in out store package. This is main entry point for running migrations, and next to this file, we need to add our actual migration files. Let’s create first migration file 1_addUsersTable.go:

package main

import (
  "fmt"

  "github.com/go-pg/migrations/v8"
)

func init() {
  migrations.MustRegisterTx(func(db migrations.DB) error {
    fmt.Println("creating table users...")
    _, err := db.Exec(`CREATE TABLE users(
      id SERIAL PRIMARY KEY,
      username TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      modified_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )`)
    return err
  }, func(db migrations.DB) error {
    fmt.Println("dropping table users...")
    _, err := db.Exec(`DROP TABLE users`)
    return err
  })
}

Run migrations using commands:

cd migrations/
go run *.go init
go run *.go up

This will create users table. As you can see, we added created_at and modified_at columns to our database table, so we also need to add them in our User struct definition in internal/store/users.go:

type User struct {
  ID         int
  Username   string `binding:"required,min=5,max=30"`
  Password   string `binding:"required,min=7,max=32"`
  CreatedAt  time.Time
  ModifiedAt time.Time
}

Try to create new account again to see that it's working now.

And we can see that user entry is created in database.

It's also possible to create migrations executable file:

cd migrations/
go build -o migrations *.go

And run it using:

./migrations init
./migrations up