Building Database Connections and Migrations in Go With GORM and Goose
This article walks you through setting up a robust database client, handling migrations, preloading associations, and querying data efficiently.
Join the DZone community and get the full member experience.
Join For FreeManaging 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.
Tutorial
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.
Published at DZone with permission of Bhanuprakash Jirra. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments