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!