Building Robust Database Connections and Migrations in Go with GORM and Goose

Introduction

Managing database connections and migrations is crucial for any application. In Go, we can leverage powerful libraries such as GORM for ORM functionality and Goose for database migrations. This article walks you through setting up a robust database client, handling migrations, preloading associations, and querying data efficiently.

Prerequisites

Before we dive into the implementation, ensure you have the following installed:

• Go (version 1.16 or higher)
• MySQL or PostgreSQL database

Project Structure

Here’s a brief overview of the project structure:

myapp/
├── main.go
├── config.go
├── client.go
├── migrations/
│   └── 202106010001_create_users_table.sql
├── go.mod
└── go.sum

Step 1: Define Configuration

First, we define the configuration structure to hold database details.

// config.go
package main

type Config struct {
    Dialect       string
    Host          string
    Name          string
    Schema        string
    Port          int
    Replicas      string
    User          string
    Password      string
    DbScriptsPath string
    Timeout       int
}

Step 2: Implement Database Client

Next, we implement the Client struct with functions to initialize the connection, handle migrations, and query data. This implementation supports both MySQL and PostgreSQL.

// client.go
package main

import (
    "context"
    "fmt"
    "github.com/pressly/goose/v3"
    "gorm.io/driver/mysql"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
    "gorm.io/plugin/dbresolver"
    "strings"
    "time"
)
type Client struct {
    db     *gorm.DB
    Config *Config
}
var databaseMigrationPath = "migrations"
func (client *Client) Initialize(dbConfig Config) error {
    var err error
    client.Config = &dbConfig
    client.db, err = gorm.Open(
        getDialector(dbConfig),
        &gorm.Config{
            Logger: gormLogger(),
        },
    )
    if err != nil {
        return fmt.Errorf("database connection could not be established: %w", err)
    }
    err = client.DbMigration(dbConfig.Dialect)
    if err != nil {
        return err
    }
    replicaDSNs := strings.Split(dbConfig.Replicas, ",")
    if len(replicaDSNs) > 0 {
        replicas := make([]gorm.Dialector, 0)
        for _, replica := range replicaDSNs {
            replicas = append(replicas, getDialector(dbConfig, replica))
        }
        err = client.db.Use(dbresolver.Register(dbresolver.Config{
            Replicas: replicas,
            Policy:   dbresolver.RandomPolicy{},
        }))
    }
    if err != nil {
        return fmt.Errorf("database replica connection could not be established: %w", err)
    }
    return nil
}
func (client *Client) DbMigration(dialect string) error {
    err := goose.SetDialect(dialect)
    if err != nil {
        return fmt.Errorf("failed to set dialect: %w", err)
    }
    sqlDb, sqlDbErr := client.db.DB()
    if sqlDbErr != nil {
        return fmt.Errorf("an error occurred receiving the db instance: %w", sqlDbErr)
    }
    if client.Config.DbScriptsPath == "" {
        client.Config.DbScriptsPath = databaseMigrationPath
    }
    err = goose.Up(sqlDb, client.Config.DbScriptsPath)
    if err != nil {
        return fmt.Errorf("an error occurred during migration: %w", err)
    }
    return nil
}
func (client *Client) Db() *gorm.DB {
    return client.db
}
// Preloads associations because GORM does not preload all child associations by default.
func (client *Client) FindById(ctx context.Context, model interface{}, id interface{}) error {
    return PreloadAssociationsFromRead(client.db, model).Preload(clause.Associations).WithContext(ctx).First(model, "id = ?", id).Error
}
// Helper function to preload child associations.
func PreloadAssociationsFromRead(db *gorm.DB, model interface{}) *gorm.DB {
    associations := FindChildAssociations(model) // Assume FindChildAssociations is a utility function to find child associations.
    for _, association := range associations {
        db = db.Clauses(dbresolver.Read).Preload(association)
    }
    return db
}
func getDialector(dbConfig Config, host ...string) gorm.Dialector {
    dbHost := fmt.Sprintf("%s:%d", dbConfig.Host, dbConfig.Port)
    if len(host) > 0 && host[0] != "" {
        dbHost = host[0]
    }
    if dbConfig.Timeout == 0 {
        dbConfig.Timeout = 6000
    }
    switch dbConfig.Dialect {
    case "mysql":
        dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8&parseTime=True&net_write_timeout=%d",
            dbConfig.User, dbConfig.Password, dbHost, dbConfig.Name, dbConfig.Timeout)
        return mysql.Open(dsn)
    case "postgres":
        dsn := fmt.Sprintf("postgres://%s:%s@%s/%s?search_path=%s",
            dbConfig.User, dbConfig.Password, dbHost, dbConfig.Name, dbConfig.Schema)
        return postgres.Open(dsn)
    default:
        panic(fmt.Sprintf("unsupported dialect: %s", dbConfig.Dialect))
    }
}
func gormLogger() gorm.Logger {
    return gorm.Logger{
        LogLevel: gorm.Warn,
    }
}

Preloading Associations

GORM does not preload all child associations by default. To address this, we implemented the PreloadAssociationsFromRead function. This function preloads all necessary associations for a given model, ensuring that related data is loaded efficiently.

Step 3: Migration Scripts

Create your migration scripts under the migrations directory. For example:

-- 202106010001_create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE NOT NULL
);

Step 4: Main Function

Finally, we use the Client struct in our main function to initialize the database connection, run migrations, and query a user by ID.

Using MySQL

To use MySQL, you would configure the Config struct as follows:

// main.go
package main

import (
    "context"
    "fmt"
)
func main() {
    dbConfig := Config{
        Dialect:       "mysql",
        Host:          "localhost",
        Name:          "mydatabase",
        Port:          3306,
        User:          "myuser",
        Password:      "mypassword",
        DbScriptsPath: "migrations",
    }
    client := &Client{}
    err := client.Initialize(dbConfig)
    if err != nil {
        fmt.Printf("Failed to initialize database client: %v\n", err)
        return
    }
    fmt.Println("Database connection established and migrations applied.")
    // Example of finding a user by ID
    type User struct {
        ID    uint
        Name  string
        Email string
    }
    user := &User{}
    err = client.FindById(context.Background(), user, 1)
    if err != nil {
        fmt.Printf("Failed to find user: %v\n", err)
    } else {
        fmt.Printf("User found: %+v\n", user)
    }
}

Using PostgreSQL

To use PostgreSQL, you would configure the Config struct as follows:

// main.go
package main

import (
    "context"
    "fmt"
)
func main() {
    dbConfig := Config{
        Dialect:       "postgres",
        Host:          "localhost",
        Name:          "mydatabase",
        Schema:        "public",
        Port:          5432,
        User:          "myuser",
        Password:      "mypassword",
        DbScriptsPath: "migrations",
    }
    client := &Client{}
    err := client.Initialize(dbConfig)
    if err != nil {
        fmt.Printf("Failed to initialize database client: %v\n", err)
        return
    }
    fmt.Println("Database connection established and migrations applied.")
    // Example of finding a user by ID
    type User struct {
        ID    uint
        Name  string
        Email string
    }
    user := &User{}
    err = client.FindById(context.Background(), user, 1)
    if err != nil {
        fmt.Printf("Failed to find user: %v\n", err)
    } else {
        fmt.Printf("User found: %+v\n", user)
    }
}

Conclusion

In this article, we’ve demonstrated how to set up a robust database client using GORM, handle database migrations with Goose, and query data efficiently in a Go application. We also addressed the importance of preloading associations in GORM, as it does not do so by default, ensuring that related data is loaded as needed. Importantly, the provided solution works seamlessly with both MySQL and PostgreSQL databases.