Golang: pack db migration

We continue a series of tips for a home project.

Today I wanted to write a small article, which should slightly improve your life as a developer, as well as the lives of people supporting your application. In our case, ours too :)

About the benefits of writing migrations, we will not today,
I hope everyone knows about it for a long time, today we’ll talk about how to write self-contained applications (one binary)

Let me describe a few advantages of this approach.

Why do we all love Golang? Of course for one executive file after assembly.
We try to drag everything into it, often we collect it together with static dependencies .

Migration is not an exception.
You must admit that having one binary of our service working with the database and being able to roll them with one command is great:

./myapp migrate up

Well, roll back accordingly

./myapp migrate down

Your application is just spreading. You do not need to download and install and store the dependencies of the utilities and the files of the migrations themselves separately.
Now they are all inside your application.

And to do all this is very simple.

Of the tools we will use

• https://github.com/rubenv/sql-migrate — tool for migrations
• https://github.com/gobuffalo/packr — simple solution for bundling static assets inside of Go binaries.
• https://github.com/urfave/cli — a library for creating console applications.

We create a new project with a migration directory in which our sql migrations will be stored.

Make migration

We set up our migration tool using the full instruction of their project:

First — install the library and command line program, use the following:

go get -v github.com/rubenv/sql-migrate/...

To execute the commands of this tool (create new migration), a configuration file for the database is required (dbconfig.yml). It can describe several of your development environments:

production:
  dialect: postgres
  datasource: host=prodhost dbname=proddb user=produser password=prodpass sslmode=required
  dir: migrations
  table: migrations
development:
  dialect: postgres
  datasource: host=${DB_HOST} dbname=${DB_NAME} user=${DB_USER} password=${DB_PASS} sslmode=disable
  dir: migrations
  table: migrations

I use environment variables here, but you can just fill it yourself.

Now we can create our first migration:

sql-migrate new create_users_table

This command will create a migration in the folder specified in the configuration file. It remains only to fill it:

-- +migrate Up
CREATE TABLE users
(
    id         UUID CONSTRAINT users_pk PRIMARY KEY,
    name       VARCHAR(255)                           NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    deleted_at TIMESTAMP WITH TIME ZONE
);
-- +migrate Down

DROP TABLE users;

You can create a manual migration file. Then you do not need config (dbconfig.yml). The main thing is to write the name of the migration file in the desired format. For example:

20200328085739-creat_user_table.sql

Service template

Now we create a simple console application with urfave.

You will find what to optimize in it and how to create an optimal project structure for you. This is just a demo template:

package main

import (
   "database/sql"
   "fmt"
   "log"
   "os"

   "github.com/gobuffalo/packr/v2"
   _ "github.com/lib/pq"
   migrate "github.com/rubenv/sql-migrate"
   "github.com/urfave/cli/v2"
)

//go:generate packr2

func main() {
   pgConString := fmt.Sprintf(
      "port=%d host=%s user=%s password=%s dbname=%s sslmode=disable",
      os.Getenv("DB_PORT"),
      os.Getenv("DB_HOST"),
      os.Getenv("DB_USER"),
      os.Getenv("DB_PASS"),
      os.Getenv("DB_NAME"),
   )
   db, err := sql.Open("postgres", pgConString)
   if err != nil {
      log.Fatal(err)
   }

   migrationSource := &migrate.PackrMigrationSource{
      Box: packr.New("migrations", "migrations"),
   }

   app := &cli.App{
      Commands: []*cli.Command{
         {
            Name:    "migrate",
            Aliases: []string{"m"},
            Usage:   "work with migration",
            Subcommands: []*cli.Command{
               {
                  Name:  "up",
                  Usage: "migrate up",
                  Action: func(c *cli.Context) error {

                     n, err := migrate.Exec(db, "postgres", migrationSource, migrate.Up)
                     if err != nil {
                        log.Fatalln(err)
                     }
                     fmt.Printf("Applied %d migrations!\n", n)
                     return nil
                  },
               },
               {
                  Name:  "down",
                  Usage: "migrate down",
                  Action: func(c *cli.Context) error {
                     n, err := migrate.ExecMax(db, "postgres", migrationSource, migrate.Down, 1)
                     if err != nil {
                        log.Fatalln(err)
                     }
                     fmt.Printf("rollback %d migrations!\n", n)
                     return nil
                  },
               },
            },
         },
      },
   }

   err = app.Run(os.Args)
   if err != nil {
      log.Fatal(err)
   }
}

Here we created a console application with a migrate command and two up and down subcommands for migration management.

Please note that I roll all the migrations, and roll back one at a time. I do this with different commands.

Sql-migrate already supports the packer, therefore we can only pack our migrations and pass the migrationSource with packr2 box to the sql tool.

Next we perform:

go generate

And look what happened.

We have a packrd folder in which our packed migrations will be stored. And of course now we can use them directly from our code.

Now after each added migration do not forget to pack them! :)