DEV Community

loading...

Migrations

Matija Krajnik
QA automation engineer experienced in writing GUI automated tests for Web and Mobile apps, Web REST API automated tests, load/performance tests, and developing custom testing tools from scratch.
Originally published at letscode.blog Updated on ・2 min read

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)
}
Enter fullscreen mode Exit fullscreen mode

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
  })
}
Enter fullscreen mode Exit fullscreen mode

Run migrations using commands:

cd migrations/
go run *.go init
go run *.go up
Enter fullscreen mode Exit fullscreen mode

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
}
Enter fullscreen mode Exit fullscreen mode

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

SignUp with database

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

User entry in database

It's also possible to create migrations executable file:

cd migrations/
go build -o migrations *.go
Enter fullscreen mode Exit fullscreen mode

And run it using:

./migrations init
./migrations up
Enter fullscreen mode Exit fullscreen mode

Discussion (0)