🛠️ DB MIGRATION FOR GOLANG SERVICES, WHY IT MATTERS?
DB Migration, why it matters?
Have you ever faced the situations when you deploy new update on production with updated database schemas, but got bugs after that and need to revert things.... that's when migration comes into place.
Database migration serves several key purposes:
- Schema Evolution: As applications evolve, their data models change. Migrations allow developers to systematically update the database schema to reflect these changes, ensuring that the database structure matches the application code.
- Version Control: Migrations provide a way to version the database schema, allowing teams to track changes over time. This versioning helps in understanding the evolution of the database and aids in collaboration among developers.
- Consistency Across Environments: Migrations ensure that the database schema is consistent across different environments (development, testing, production). This reduces the risk of discrepancies that can lead to bugs and integration issues.
- Rollback Capability: Many migration tools support rolling back changes, allowing developers to revert to a previous state of the database if a migration causes issues. This enhances stability during the development and deployment process.
- Automated Deployment: Migrations can be automated as part of the deployment process, ensuring that the necessary schema changes are applied to the database without manual intervention. This streamlines the release process and reduces human error.
Applying in golang projects
To create a comprehensive, production-grade setup for a Golang service using GORM with MySQL (To get a Quick Setup) that allows for easy migrations, updates, and rollbacks, you need to include migration tooling, handle database connection pooling, and ensure proper struct definitions. Here’s a complete example to guide you through the process:
Project Structure
/golang-service
|-- main.go
|-- database
| |-- migration.go
|-- models
| |-- user.go
|-- config
| |-- config.go
|-- migrations
| |-- ...
|-- go.mod
1. Database Configuration (config/config.go
)
package config
import (
"fmt"
"log"
"os"
"time"
"github.com/joho/godotenv"
"gorm.io/driver/mysql"
"gorm.io/gorm"
)
var DB *gorm.DB
func ConnectDB() {
err := godotenv.Load()
if err != nil {
log.Fatal("Error loading .env file")
}
// charset=utf8mb4: Sets the character set to utf8mb4, which supports all Unicode characters, including emojis.
// parseTime=True: Tells the driver to automatically parse DATE and DATETIME values into Go's time.Time type.
// loc=Local: Uses the local timezone of the server for time-related queries and storage.
dsn := fmt.Sprintf(
"%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local",
os.Getenv("DB_USER"),
os.Getenv("DB_PASS"),
os.Getenv("DB_HOST"),
os.Getenv("DB_PORT"),
os.Getenv("DB_NAME"),
)
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
if err != nil {
panic("failed to connect database")
}
sqlDB, err := db.DB()
if err != nil {
panic("failed to configure database connection")
}
// Set connection pool settings
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
// 1.sqlDB.SetMaxIdleConns(10)
// Sets the maximum number of idle (unused but open) connections in the connection pool.
// A value of 10 means up to 10 connections can remain idle, ready to be reused.
// 2. sqlDB.SetMaxOpenConns(100):
// Sets the maximum number of open (active or idle) connections that can be created to the database.
// A value of 100 limits the total number of connections, helping to prevent overloading the database.
// 3. sqlDB.SetConnMaxLifetime(time.Hour):
// Sets the maximum amount of time a connection can be reused before it’s closed.
// A value of time.Hour means that each connection will be kept for up to 1 hour, after which it will be discarded and a new connection will be created if needed.
DB = db
}
2. Database Migration (database/migration.go
)
package database
import (
"golang-service/models"
"golang-service/migrations"
"gorm.io/gorm"
)
func Migrate(db *gorm.DB) {
db.AutoMigrate(&models.User{})
// Apply additional custom migrations if needed
}
3. Models (models/user.go
)
package models
import "gorm.io/gorm"
type User struct {
gorm.Model
Name string `json:"name"`
}
4. Environment Configuration (.env
)
DB_USER=root
DB_PASS=yourpassword
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=yourdb
5. Main Entry Point (main.go
)
package main
import (
"golang-service/config"
"golang-service/database"
"golang-service/models"
"github.com/gin-gonic/gin"
"gorm.io/gorm"
)
func main() {
config.ConnectDB()
database.Migrate(config.DB)
r := gin.Default()
r.POST("/users", createUser)
r.GET("/users/:id", getUser)
r.Run(":8080")
}
func createUser(c *gin.Context) {
var user models.User
if err := c.ShouldBindJSON(&user); err != nil {
c.JSON(400, gin.H{"error": err.Error()})
return
}
if err := config.DB.Create(&user).Error; err != nil {
c.JSON(500, gin.H{"error": err.Error()})
return
}
c.JSON(201, user)
}
func getUser(c *gin.Context) {
id := c.Param("id")
var user models.User
if err := config.DB.First(&user, id).Error; err != nil {
c.JSON(404, gin.H{"error": "User not found"})
return
}
c.JSON(200, user)
}
6. Explanation:
- Database Config: Manages connection pooling for production-grade performance.
- Migration Files: (in
migrations
folder) Helps in versioning the database schema. - GORM Models: Maps database tables to Go structs.
- Database Migrations: (in
database
folder) Custom logic for altering tables over time, allowing for easy rollbacks. - Testing: You can create integration tests for this setup using
httptest
andtestify
.
7. Create First Migration
-
For production environments, we could use a migration library like golang-migrate to apply, rollback, or redo migrations.
Install golang-migrate:
go install -tags 'mysql' github.com/golang-migrate/migrate/v4/cmd/migrate@latest
-
Generate migrate files for
users
tablemigrate create -ext=sql -dir=./migrations -seq create_users_table
After running the command we'll get a pair of
.up.sql
(to update schema) anddown.sql
(for potential rollback later) . The number000001
is the auto generated index of migration./golang-service |-- migrations | |-- 000001_create_users_table.down.sql | |-- 000001_create_users_table.up.sql
Add relevant sql command to
.up
file , and.down
file.000001_create_users_table.up.sql
CREATE TABLE users ( id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at DATETIME, updated_at DATETIME, deleted_at DATETIME);
000001_create_users_table.down.sql
DROP TABLE IF EXISTS users;
Run the
up
migration and apply changes to the database with the following command (-verbose
flag to see more log details):migrate -path ./migrations -database "mysql://user:password@tcp(localhost:3306)/dbname" -verbose up
In case we got issue with migration we can use the following command to see the current migration version and its status:
migrate -path ./migrations -database "mysql://user:password@tcp(localhost:3306)/dbname" version
If we have a broken migration for some reasons we can consider to use the
force
(use carefully) command with the version number of the dirty migration. If the version is1
(could check it inmigrations
orschema_migrations
table), we would run:migrate -path ./migrations -database "mysql://user:password@tcp(localhost:3306)/dbname" force 1
8. Changing schemes
-
At some point in time, we might like to add new features and some of those might require chaning data schemes, for instance we'd like to add
email
field tousers
table. We'd do it as following.Make a new migration for adding
email
column tousers
tablemigrate create -ext=sql -dir=./migrations -seq add_email_to_users
Now we have a new pair of
.up.sql
and.down.sql
/golang-service |-- migrations | |-- 000001_create_users_table.down.sql | |-- 000001_create_users_table.up.sql | |-- 000002_add_email_to_users.down.sql | |-- 000002_add_email_to_users.up.sql
-
Adding following content to *_add_email_to_users.*.sql files
000002_add_email_to_users.up.sql
ALTER TABLE `users` ADD COLUMN `email` VARCHAR(255) UNIQUE;
000002_add_email_to_users.down.sql
ALTER TABLE `users` DROP COLUMN `email`;
Run the
up
migration command again to make update to the data schemasmigrate -path ./migrations -database "mysql://user:password@tcp(localhost:3306)/dbname" -verbose up
We'll also need to update the golang
users
struct (adding Email field) to keep it in sync with the new schemas..type User struct { gorm.Model Name string `json:"name"` Email string json:"email" gorm:"uniqueIndex" }
9. Rolling Back Migrations:
In case for some reasons we got bugs with new updated schemas, and we need to rollback, this case we'll use the down
command:
migrate -path ./migrations -database "mysql://user:password@tcp(localhost:3306)/dbname" down 1
Number 1
indicates that we want to rollback 1
migration.
Here we also need manually to update golang users
struct (remove the Email field) to reflect the data schema changes.
type User struct {
gorm.Model
Name string `json:"name"`
}
10. Use with Makefile
To simplify the process of migration and rolling back, we can add a Makefile
.
/golang-service
|-- ...
|-- Makefile
The content of Makefile
as following.
include .env
create_migration:
migrate create -ext=sql -dir=./migrations -seq create_new_migration
migrate_up:
migrate -path=./migrations -database "mysql://${DB_USER}:${DB_PASS}@tcp(${DB_HOST}:${DB_PORT})/${DB_NAME}" -verbose up 1
migrate_down:
migrate -path=./migrations -database "mysql://${DB_USER}:${DB_PASS}@tcp(${DB_HOST}:${DB_PORT})/${DB_NAME}" -verbose down 1
.PHONY: create_migration migrate_up migrate_down
Now we can simply run make migrate_up
or make migrate_down
on CLI to do the migration and the rollback.
11.Considerations:
- Data Loss During Rollback: Rolling back migrations that delete columns or tables may result in data loss, so always backup data before running a rollback.
- CI/CD Integration: Integrate the migration process into your CI/CD pipeline to automate schema changes during deployment.
- DB Backups: Schedule regular database backups to prevent data loss in case of migration errors.
About DB backups
Before rolling back a migration or making changes that could potentially affect your database, here are some key points to consider.
- Schema Changes: If the migration involved altering the schema (e.g., adding or removing columns, changing data types), rolling back to a previous migration can result in the loss of any data stored in those altered columns or tables.
- Data Removal: If the migration includes commands that delete data (like dropping tables or truncating tables), rolling back will execute the corresponding "down" migration, which could permanently remove that data.
- Transaction Handling: If your migration tool supports transactions, the rollback might be safer since changes are applied in a transaction. However, if you manually run SQL commands outside of transactions, there is a risk of losing data.
- Data Integrity: If you have modified data in a way that depends on the current schema, rolling back could leave your database in an inconsistent state.
So it’s crucial to back up your data. Here’s a brief guide:
- Database Dump:
- Use database-specific tools to create a full backup of your database. For MySQL, you can use:
This creates a file (mysqldump -u root -p dbname > backup_before_rollback.sql
backup_before_rollback.sql
) that contains all the data and schema of thedbname
database.
- Export Specific Tables:
- If you only need to back up certain tables, specify them in the mysqldump command:
mysqldump -u root -p golang_1 users > users_table_backup.sql
- Verify the Backup:
- Ensure that the backup file has been created and check its size or open it to ensure it contains the necessary data.
- Store Backups Securely:
- Keep a copy of the backup in a secure location, such as cloud storage or a separate server, to prevent data loss during the rollback process.
Backups on cloud
To back up your MySQL data when using Golang and deploying on AWS EKS, you can follow these steps:
- Use mysqldump for Database Backup:
- Create a
mysqldump
of your MySQL database using a Kubernetes cron job.
Store this in a persistent volume or an S3 bucket.mysqldump -h <mysql-host> -u <user> -p<password> <database_name> > backup.sql
- Automate with Kubernetes CronJob:
- Use a Kubernetes
CronJob
to automate the mysqldump process. - Example YAML
configuration:yaml
apiVersion: batch/v1 kind: CronJob metadata: name: mysql-backup spec: schedule: "0 2 * * *" # Runs every day at 2 AM jobTemplate: spec: template: spec: containers: - name: mysql-backup image: mysql:5.7 args: - /bin/sh - -c - "mysqldump -h <mysql-host> -u <user> -p<password> <database_name> | aws s3 cp - s3://<your-bucket-name>/backup-$(date +\\%F).sql" env: - name: AWS_ACCESS_KEY_ID value: "<your-access-key>" - name: AWS_SECRET_ACCESS_KEY value: "<your-secret-key>" restartPolicy: OnFailure
- Using AWS RDS Automated Backups (if using RDS):
- If your MySQL database is on AWS RDS, you can leverage RDS automated backups and snapshots.
- Set a backup retention period and take snapshots manually or automate snapshots using
Lambda functions
.
- Back Up Persistent Volumes (PV) with Velero:
- Use Velero, a backup tool for Kubernetes, to back up the persistent volume that holds MySQL data.
- Install Velero on your EKS cluster and configure it to back up to S3.
By using these methods, you can ensure your MySQL data is regularly backed up and securely stored.
If you found this helpful, let me know by leaving a 👍 or a comment!, or if you think this post could help someone, feel free to share it! Thank you very much! 😃
All Rights Reserved