Migrations in GoLang

Migrations in GoLang

Gaurav Bharadwaj's photo
Gaurav Bharadwaj
·

3 min read

Before our app can start up, we need to ensure that the database is set up correctly. This is as true for our local machine setup as it is in production.

If we need new columns or a different table structure then we need a mechanism that will update our tables prior to our app starting up and serving new requests based on this structure. This is where database migrations comes in to action.

For this blog we are using golang-migrate library and in database we are using postgres

Database Migration Strategy

When our app starts up, we can effectively run our SQL files in order until our database is in the exact state we need for it to back our application.

We can craft SQL statements that are idempotent and can be run as many times as our app starts up, and yet we’ll still be left with the exact database schema we need.

1_efAX7XpKmDFf80nrl8dzXw.png

Migration Code Example

Let’s have a look at how we can define a Migrate method that will hang off and perform our migrations for us.

package database

import (
"fmt"
"github.com/golang-migrate/migrate/v4"
"github.com/golang-migrate/migrate/v4/database/postgres"

    // source/file import is required for migration files to read
    _ "github.com/golang-migrate/migrate/v4/source/file"
    "github.com/jmoiron/sqlx"
    "github.com/sirupsen/logrus"

    // load pq as database driver
    _ "github.com/lib/pq"
)

var (
DB *sqlx.DB
)

type SSLMode string

const (
SSLModeEnable  SSLMode = "enable"
SSLModeDisable SSLMode = "disable"
)

// ConnectAndMigrate function connects with a given database and returns error if there is any error
func ConnectAndMigrate(host, port, databaseName, user, password string, sslMode SSLMode) error {
fmt.Println("inside")
var err error
connStr := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=%s", host, port, user, password, databaseName, sslMode)
DB, err = sqlx.Open("postgres", connStr)

    if err != nil {
        return err
    }

    err = DB.Ping()
    if err != nil {
        return err
    }

    return migrateUp(DB)
}

// migrateUp function migrate the database and handles the migration logic
func migrateUp(db *sqlx.DB) error {
driver, err := postgres.WithInstance(db.DB, &postgres.Config{})
if err != nil {
return err
}
m, err := migrate.NewWithDatabaseInstance(
"file://database/migrations",
"postgres", driver)

    if err != nil {
        return err
    }
    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }
    return nil
}

With this in place, we can now create our migrations/ directory within the root of our application that will store both our up and down sql files.

Up Migration

The up migrations must be created with following file name format unique_migration_name.up.sql

Let’s start off by creating the up migration which will create our user table consisting of id and name.

CREATE TABLE   users
(
id            UUID PRIMARY KEY  DEFAULT gen_random_uuid(),
name TEXT
);

Down Migration

The down migrations must be created with following file name format unique_migration_name.down.sql

The down migrations are used to clean the db schema.

Let’s drop the user table created in the up migration..

DROP TABLE users ;

Startup Code

package main

import (
"audioPhile/database"
"audioPhile/server"
"fmt"
"github.com/sirupsen/logrus"
"os"
)

func main() {
err := database.ConnectAndMigrate(os.Getenv("host"), os.Getenv("port"), os.Getenv("databaseName"), os.Getenv("user"), os.Getenv("password"), database.SSLModeDisable)
if err != nil {
logrus.Fatal(err)
return
}
fmt.Println("connected")
//cmd.Execute()

    srv := server.SetupRoutes()
    err = srv.Run(":8080")
    if err != nil {
        logrus.Error(err)
    }

}

DIRTY DATABASE

Whenever we run the migration our schema will have one table called schema_migrations(for golang-migrate can be different for other libraries) which will have two columns that is version(the last migration that gets executed) and dirty(FALSE when migrations runs successfully else TRUE) . Whenever a migration fails to execute then the dirty column of the table has the value true and the migrations after the failed migrations will not get executed. This is situation is known as dirty database.

To resolve the dirty database situation firstly we have to change version value to last successfully run migration and dirty to false and correct the failed migration and again run the migrations.

visit our next post of the series [Github-Actions](series blog.golang.llc/github-actions)

migrationGo Language