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