Migrations
Tags: go, postgres
Published: 2021-06-24 17:15:03

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](https://github.com/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`:

```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](https://github.com/go-pg/migrations/blob/v8/example/main.go) 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`:

```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:

```bash
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`:

```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.

![SignUp with database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8uu3hp9txxzy8ck2c2pc.png)

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

![User entry in database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r7mbpeocq2qvhdywh4g0.png)

It's also possible to create migrations executable file:

```bash
cd migrations/
go build -o migrations *.go
```

And run it using:

```bash
./migrations init
./migrations up
```