Skip to main content

Gin Database Migration

When building web applications with Gin, your database schema will inevitably change over time. Adding new features, fixing bugs, or optimizing performance often requires modifications to your database structure. Database migrations provide a systematic approach to evolve your database schema while preserving existing data and maintaining application functionality.

What are Database Migrations?​

Database migrations are version-controlled, incremental changes to database schemas that allow you to:

  • Track changes to your database structure over time
  • Apply updates systematically across different environments
  • Rollback changes when needed
  • Collaborate effectively in a team setting

In the context of Gin applications, implementing a robust migration system ensures your application's data layer evolves smoothly alongside your codebase.

Before diving into implementation, let's explore some popular Go migration tools that work well with Gin:

  1. golang-migrate/migrate: A standalone migration library with database support for PostgreSQL, MySQL, and others
  2. GORM Migrations: If you're using GORM as your ORM, it provides built-in migration capabilities
  3. goose: A database migration tool that supports various SQL databases
  4. Atlas: A modern tool for managing database schemas

For this guide, we'll focus on two approaches: using the standalone migrate tool and implementing migrations with GORM.

Setting Up Migrations with golang-migrate​

Step 1: Installing the migrate CLI​

First, install the migration tool:

bash
# Using Go install
go install -tags 'postgres mysql' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Or using Homebrew on macOS
brew install golang-migrate

Step 2: Creating Migration Files​

Create your first migration files:

bash
migrate create -ext sql -dir db/migrations -seq create_users_table

This creates two files:

  • db/migrations/000001_create_users_table.up.sql (for applying the migration)
  • db/migrations/000001_create_users_table.down.sql (for rolling back the migration)

Step 3: Writing Migration SQL​

Edit the up migration to create your users table:

sql
-- db/migrations/000001_create_users_table.up.sql
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Write the down migration to undo these changes:

sql
-- db/migrations/000001_create_users_table.down.sql
DROP TABLE IF EXISTS users;

Step 4: Running Migrations​

To apply migrations:

bash
migrate -path db/migrations -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" up

To roll back the last migration:

bash
migrate -path db/migrations -database "postgresql://username:password@localhost:5432/dbname?sslmode=disable" down 1

Integrating Migrations in Your Gin Application​

Let's see how to integrate migrations into your Gin application:

Step 1: Project Structure​

Here's a recommended structure for a Gin project with migrations:

my-gin-app/
├── cmd/
│   └── server/
│       └── main.go
├── internal/
│   ├── config/
│   ├── handlers/
│   ├── models/
│   └── database/
│       ├── db.go
│       └── migrate.go
└── migrations/
    ├── 000001_create_users_table.up.sql
    ├── 000001_create_users_table.down.sql
    ├── 000002_add_user_roles.up.sql
    └── 000002_add_user_roles.down.sql

Step 2: Create a Migration Manager​

Let's implement a simple migration manager in internal/database/migrate.go:

go
package database

import (
	"fmt"
	"log"
	
	"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"
)

// MigrateDB handles database migrations
func MigrateDB(db *sql.DB, migrationPath string) error {
	driver, err := postgres.WithInstance(db, &postgres.Config{})
	if err != nil {
		return fmt.Errorf("could not create migration driver: %w", err)
	}

	m, err := migrate.NewWithDatabaseInstance(
		"file://"+migrationPath,
		"postgres", driver)
	if err != nil {
		return fmt.Errorf("migration initialization failed: %w", err)
	}

	if err := m.Up(); err != nil && err != migrate.ErrNoChange {
		return fmt.Errorf("migration failed: %w", err)
	}

	log.Println("Database migration completed successfully")
	return nil
}

Step 3: Call Migrations on Startup​

In your main.go, call the migration function during startup:

go
package main

import (
	"database/sql"
	"log"

	"github.com/gin-gonic/gin"
	_ "github.com/lib/pq"
	"your-module/internal/database"
)

func main() {
	// Initialize the database
	db, err := sql.Open("postgres", "postgresql://username:password@localhost:5432/dbname?sslmode=disable")
	if err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}
	defer db.Close()

	// Run migrations
	err = database.MigrateDB(db, "./migrations")
	if err != nil {
		log.Fatalf("Database migration failed: %v", err)
	}

	// Setup Gin router
	router := gin.Default()
	
	// Register routes and middleware
	// ...
	
	// Start the server
	router.Run(":8080")
}

Using GORM for Migrations​

If you're already using GORM, you can leverage its built-in migration capabilities:

Step 1: Define Your Models​

go
package models

import (
	"time"
	"gorm.io/gorm"
)

type User struct {
	ID           uint           `gorm:"primaryKey"`
	Username     string         `gorm:"size:100;not null;unique"`
	Email        string         `gorm:"size:255;not null;unique"`
	PasswordHash string         `gorm:"size:255;not null"`
	CreatedAt    time.Time
	UpdatedAt    time.Time
	DeletedAt    gorm.DeletedAt `gorm:"index"`
}

type Post struct {
	ID        uint           `gorm:"primaryKey"`
	Title     string         `gorm:"size:200;not null"`
	Content   string         `gorm:"type:text"`
	UserID    uint           `gorm:"not null"`
	User      User           `gorm:"foreignKey:UserID"`
	CreatedAt time.Time
	UpdatedAt time.Time
	DeletedAt gorm.DeletedAt `gorm:"index"`
}

Step 2: Auto Migrate in Your Application​

go
package database

import (
	"log"

	"gorm.io/gorm"
	"your-module/internal/models"
)

// AutoMigrate performs automatic database migrations using GORM
func AutoMigrate(db *gorm.DB) error {
	log.Println("Running database migrations...")
	
	// Add all models that should be migrated
	err := db.AutoMigrate(
		&models.User{},
		&models.Post{},
	)
	
	if err != nil {
		return err
	}
	
	log.Println("Database migration completed successfully")
	return nil
}

Step 3: Initialize GORM and Run Migrations​

go
package main

import (
	"log"

	"github.com/gin-gonic/gin"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	
	"your-module/internal/database"
)

func main() {
	// Connect to the database using GORM
	dsn := "host=localhost user=username password=password dbname=dbname port=5432 sslmode=disable"
	db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
	if err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}
	
	// Run auto migrations
	if err := database.AutoMigrate(db); err != nil {
		log.Fatalf("Database migration failed: %v", err)
	}
	
	// Setup Gin router
	router := gin.Default()
	
	// Register routes and handlers
	// ...
	
	// Start the server
	router.Run(":8080")
}

Real-World Example: Building a Blog API with Migrations​

Let's create a practical example of a blog API that includes proper migration handling:

Step 1: Define Your Database Migrations​

Create the following migration files:

sql
-- migrations/000001_create_users_table.up.sql
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- migrations/000001_create_users_table.down.sql
DROP TABLE IF EXISTS users;

-- migrations/000002_create_posts_table.up.sql
CREATE TABLE IF NOT EXISTS posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- migrations/000002_create_posts_table.down.sql
DROP TABLE IF EXISTS posts;

Step 2: Create a Database Package​

go
// internal/database/db.go
package database

import (
	"database/sql"
	"fmt"
	"log"
	"os"

	_ "github.com/lib/pq"
)

// Initialize connects to the database and returns a database instance
func Initialize() (*sql.DB, error) {
	// Get database details from environment variables
	host := os.Getenv("DB_HOST")
	port := os.Getenv("DB_PORT")
	user := os.Getenv("DB_USER")
	password := os.Getenv("DB_PASSWORD")
	dbname := os.Getenv("DB_NAME")

	// Create connection string
	connStr := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable",
		host, port, user, password, dbname)

	// Connect to database
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		return nil, fmt.Errorf("failed to connect to database: %w", err)
	}

	// Test the connection
	err = db.Ping()
	if err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	log.Println("Successfully connected to database")
	return db, nil
}

Step 3: Implement the Gin API with Migration Support​

go
// cmd/server/main.go
package main

import (
	"log"
	"os"

	"github.com/gin-gonic/gin"
	"github.com/joho/godotenv"
	
	"your-module/internal/database"
	"your-module/internal/handlers"
)

func main() {
	// Load environment variables from .env file
	if err := godotenv.Load(); err != nil {
		log.Println("Warning: .env file not found")
	}

	// Initialize database
	db, err := database.Initialize()
	if err != nil {
		log.Fatalf("Database initialization failed: %v", err)
	}
	defer db.Close()

	// Run migrations
	migrationPath := os.Getenv("MIGRATION_PATH")
	if migrationPath == "" {
		migrationPath = "./migrations" // Default path
	}
	
	if err := database.MigrateDB(db, migrationPath); err != nil {
		log.Fatalf("Migration failed: %v", err)
	}

	// Initialize Gin router
	router := gin.Default()

	// Setup API routes
	apiV1 := router.Group("/api/v1")
	{
		// User routes
		apiV1.POST("/users", handlers.CreateUser(db))
		apiV1.GET("/users/:id", handlers.GetUser(db))
		
		// Post routes
		apiV1.POST("/posts", handlers.CreatePost(db))
		apiV1.GET("/posts", handlers.ListPosts(db))
		apiV1.GET("/posts/:id", handlers.GetPost(db))
	}

	// Start the server
	port := os.Getenv("PORT")
	if port == "" {
		port = "8080" // Default port
	}
	
	log.Printf("Server starting on port %s", port)
	if err := router.Run(":" + port); err != nil {
		log.Fatalf("Failed to start server: %v", err)
	}
}

Step 4: Create a Handler (Example)​

go
// internal/handlers/post_handler.go
package handlers

import (
	"database/sql"
	"net/http"
	"strconv"
	"time"
	
	"github.com/gin-gonic/gin"
)

type Post struct {
	ID        int       `json:"id"`
	Title     string    `json:"title"`
	Content   string    `json:"content"`
	UserID    int       `json:"user_id"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

func CreatePost(db *sql.DB) gin.HandlerFunc {
	return func(c *gin.Context) {
		var post Post
		
		// Bind JSON to post struct
		if err := c.ShouldBindJSON(&post); err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
			return
		}
		
		// Insert into database
		query := `
			INSERT INTO posts (title, content, user_id, created_at, updated_at) 
			VALUES ($1, $2, $3, NOW(), NOW()) 
			RETURNING id, created_at, updated_at
		`
		
		err := db.QueryRow(query, post.Title, post.Content, post.UserID).
			Scan(&post.ID, &post.CreatedAt, &post.UpdatedAt)
			
		if err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
			return
		}
		
		c.JSON(http.StatusCreated, post)
	}
}

func GetPost(db *sql.DB) gin.HandlerFunc {
	return func(c *gin.Context) {
		idStr := c.Param("id")
		id, err := strconv.Atoi(idStr)
		
		if err != nil {
			c.JSON(http.StatusBadRequest, gin.H{"error": "Invalid post ID"})
			return
		}
		
		var post Post
		query := `
			SELECT id, title, content, user_id, created_at, updated_at 
			FROM posts 
			WHERE id = $1
		`
		
		err = db.QueryRow(query, id).
			Scan(&post.ID, &post.Title, &post.Content, &post.UserID, &post.CreatedAt, &post.UpdatedAt)
			
		if err == sql.ErrNoRows {
			c.JSON(http.StatusNotFound, gin.H{"error": "Post not found"})
			return
		} else if err != nil {
			c.JSON(http.StatusInternalServerError, gin.H{"error": err.Error()})
			return
		}
		
		c.JSON(http.StatusOK, post)
	}
}

Best Practices for Database Migrations​

  1. Version Control: Always keep your migrations in your version control system
  2. One Change Per Migration: Each migration should do one specific change
  3. Always Test Migrations: Test both the up and down migrations
  4. Idempotent Migrations: Use IF NOT EXISTS and IF EXISTS clauses to make migrations repeatable
  5. Never Modify Existing Migrations: Once a migration is deployed, create a new one for further changes
  6. Include Rollback Logic: Always write down migrations for every change
  7. Use Transactions: Wrap complex migrations in transactions when possible
  8. Document Complex Migrations: Add comments for any non-obvious changes

Advanced Migration Scenarios​

Data Migrations​

Sometimes you need to not just change the schema, but also migrate data:

sql
-- migrations/000003_add_user_status.up.sql
BEGIN;

-- Add the new column with a default value
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- Update existing records with specific logic
UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL '6 months';

-- Remove the default constraint
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

COMMIT;

Handling Foreign Keys​

Always drop foreign keys before dropping tables:

sql
-- migrations/000004_drop_posts_table.up.sql
BEGIN;

-- Drop foreign key constraint first
ALTER TABLE comments DROP CONSTRAINT comments_post_id_fkey;

-- Now safe to drop the table
DROP TABLE posts;

COMMIT;

Summary​

Database migrations are essential for evolving your database schema in a controlled, version-controlled manner. In this guide, we've covered:

  1. What database migrations are and why they're important
  2. How to set up migrations using golang-migrate and GORM
  3. Implementing migrations in a Gin application
  4. Best practices for migration management
  5. Advanced migration scenarios like data migrations and foreign key handling

By implementing proper database migrations in your Gin application, you ensure your database evolves alongside your application code in a predictable and maintainable way.

Additional Resources​

Exercises​

  1. Create a migration that adds a "comments" table to the blog example, with foreign keys to both users and posts
  2. Implement a migration to add indices to commonly queried columns
  3. Write a data migration that populates a new column with values derived from existing data
  4. Create a CLI command for your Gin application that allows running migrations up, down, or to a specific version
  5. Extend the migration system to support multiple environments (development, staging, production)

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)