Migrating our Database

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.

Now, we could log into our Postgres instance and create the tables or make the updates manually, however this gets messy really quickly if we have to manually setup our database on our local machine whenever we want to test.

This is where database migrations comes in to save the day.

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.

Our Migration Code

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

package db

import (
	"github.com/golang-migrate/migrate/v4"
	"github.com/golang-migrate/migrate/v4/database/postgres"
	_ "github.com/golang-migrate/migrate/v4/source/file"
	_ "github.com/lib/pq"
)

// Migrate - handles migrations on our database
func (s *Store) Migrate() error {
	// we have a database connection setup within our store
	// we can reference the underlying sql.DB pointer using s.db.DB
	driver, err := postgres.WithInstance(s.db.DB, &postgres.Config{})
	if err != nil {
		return err
	}

	// here we want to run our migrations located within the
	// migrations directory. These migrations will create our tables
	// if they dont exist and perform actions such as adding new columns.
	// These will run in order and basically act as a list of steps that
	// eventually setup your database the way you need for production.
	m, err := migrate.NewWithDatabaseInstance(
		"file:///migrations",
		"postgres",
		driver,
	)
	if err != nil {
		return err
	}

	m.Steps(2)
	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. Let’s create a new migration by creating the following 2 SQL files:

migrations/
- 1_create_table.up.sql
- 1_create_table.down.sql

Our Up Migration

Let’s start off by creating the up migration which will create our initial rockets table. We need this to be runnable even if the rockets table already exists as we may have multiple instances of our application running in behind a load balancer and we may see these restart for a number of reasons outwith our control.

-- TODO - need to figure out sql syntax for
-- creating tables
CREATE TABLE rockets IF NOT EXISTS(
    ID int
);

Our Down Migration

In the unlikely instance that our migration fails for any reason, we need to have a down migration that will clear up our mess. Let’s do this with the DROP TABLE statement:

-- TODO
DROP TABLE rockets IF EXISTS;

Perfect, if our up migration fails, we now have a cleanup script that will save us from disaster.

Updating our App Startup Code

With these migrations in place, we now need to update our Run function so that it runs our migration on startup:

package main

import (
	"log"

	"github.com/TutorialEdge/go-grpc-services-course/internal/db"
	"github.com/TutorialEdge/go-grpc-services-course/internal/rocket"
	"github.com/TutorialEdge/go-grpc-services-course/internal/transport/grpc"
)

// Run - handles the setup and starting of our application
// using this approach makes testing easier and we can more
// gracefully handle errors
func Run() error {
	log.Println("Starting up Rocket gRPC Service")

	// rktStore - the store responsible for holding
	// our rocket inventory
	rktStore, err := db.New()
	if err != nil {
		return err
	}

	// trigger our migration so that the database we are connecting
	// to has the latest database schema changes
	if err := rktStore.Migrate(); err != nil {
		return err
	}

	// rktService the service responsible for updating our
	// rocket inventory
	rktService := rocket.New(rktStore)

	// rktHandler instantiates a new gRPC handler
	// which we pass our rktService into
	rktHandler := grpc.New(rktService)

	// Start our gRPC listener, this is a blocking
	// function call so it should be the last thing
	// we run in this function
	if err := rktHandler.Serve(); err != nil {
		return err
	}
	return nil
}

func main() {
	// our main function is super small, only responsible
	// for calling Run and then handling the error
	if err := Run(); err != nil {
		log.Fatal(err.Error())
	}
}

Conclusion

Awesome, we have successfully added the ability for our gRPC application to run database migrations upon startup and ensure that the database is in a good state prior to us serving any incoming gRPC requests!