How to connect to different databases with GORM?

GORM supports multiple databases, including MySQL, PostgreSQL, SQLite, SQL Server, etc. Here are the methods and considerations for connecting to different databases.

Connecting to MySQL

Basic Connection

go
import (
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

dsn := "user:password@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})

Connection Parameters

• user: Database username
• password: Database password
• tcp(127.0.0.1:3306): Host and port
• dbname: Database name
• charset=utf8mb4: Character set
• parseTime=True: Parse time
• loc=Local: Timezone setting

Advanced Configuration

go
dsn := "user:password@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local&timeout=10s&readTimeout=30s&writeTimeout=30s"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
    NowFunc: func() time.Time {
        return time.Now().Local()
    },
})

Connecting to PostgreSQL

Basic Connection

go
import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

dsn := "host=localhost user=gorm password=gorm dbname=gorm port=9920 sslmode=disable TimeZone=Asia/Shanghai"
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

Connection Parameters

• host: Database host
• user: Database username
• password: Database password
• dbname: Database name
• port: Port number
• sslmode: SSL mode
• TimeZone: Timezone setting

Using URL Format

go
dsn := "postgres://gorm:gorm@localhost:9920/gorm?sslmode=disable&timezone=Asia/Shanghai"
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

Connecting to SQLite

Basic Connection

go
import (
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})

In-Memory Database

go
db, err := gorm.Open(sqlite.Open("file::memory:?cache=shared"), &gorm.Config{})

Connecting to SQL Server

Basic Connection

go
import (
    "gorm.io/driver/sqlserver"
    "gorm.io/gorm"
)

dsn := "sqlserver://gorm:LoremIpsum86@localhost:9930?database=gorm"
db, err := gorm.Open(sqlserver.Open(dsn), &gorm.Config{})

Connection Pool Configuration

Get Underlying SQL DB

go
sqlDB, err := db.DB()
if err != nil {
    panic("failed to get database connection")
}

Configure Connection Pool Parameters

go
// Set maximum number of idle connections in the connection pool
sqlDB.SetMaxIdleConns(10)

// Set maximum number of open database connections
sqlDB.SetMaxOpenConns(100)

// Set maximum time a connection can be reused
sqlDB.SetConnMaxLifetime(time.Hour)

// Set maximum idle time for connection
sqlDB.SetConnMaxIdleTime(10 * time.Minute)

GORM Configuration Options

Basic Configuration

go
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    // Skip default transaction
    SkipDefaultTransaction: true,
    
    // Disable foreign key constraints
    DisableForeignKeyConstraintWhenMigrating: true,
    
    // Ignore data errors
    IgnoreRelationshipsWhenMigrating: true,
})

Logger Configuration

go
import "gorm.io/gorm/logger"

db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
})

// Custom Logger
newLogger := logger.New(
    log.New(os.Stdout, "\r\n", log.LstdFlags),
    logger.Config{
        SlowThreshold:             time.Second, // Slow query threshold
        LogLevel:                  logger.Info, // Log level
        IgnoreRecordNotFoundError: true,        // Ignore record not found error
        Colorful:                  true,        // Colorful output
    },
)

db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
    Logger: newLogger,
})

Naming Strategy Configuration

go
import "gorm.io/gorm/schema"

db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    NamingStrategy: schema.NamingStrategy{
        SingularTable: true, // Use singular table names
        NoLowerCase:   true, // Don't convert to lowercase
    },
})

Database-Specific Configuration

MySQL Specific Configuration

go
import "gorm.io/driver/mysql"

db, err := gorm.Open(mysql.New(mysql.Config{
    DSN: dsn,
    DefaultStringSize: 256,       // Default string length
    DisableDatetimePrecision: true, // Disable datetime precision
    DontSupportRenameIndex: true,  // Don't support rename index
    DontSupportRenameColumn: true, // Don't support rename column
    SkipInitializeWithVersion: false, // Auto configure based on version
}), &gorm.Config{})

PostgreSQL Specific Configuration

go
import "gorm.io/driver/postgres"

db, err := gorm.Open(postgres.New(postgres.Config{
    DSN: dsn,
    PreferSimpleProtocol: false, // Disable prepared statement
}), &gorm.Config{})

Connection Management Best Practices

1. Use Connection Pool

go
sqlDB, _ := db.DB()
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)

2. Test Connection

go
sqlDB, _ := db.DB()
if err := sqlDB.Ping(); err != nil {
    panic("failed to connect to database")
}

3. Graceful Shutdown

go
sqlDB, _ := db.DB()
defer sqlDB.Close()

4. Connection Retry

go
func connectWithRetry(dsn string, maxRetries int) (*gorm.DB, error) {
    var db *gorm.DB
    var err error
    
    for i := 0; i < maxRetries; i++ {
        db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
        if err == nil {
            return db, nil
        }
        time.Sleep(time.Second * time.Duration(i+1))
    }
    
    return nil, err
}

Environment Variable Configuration

Using Environment Variables

go
import "os"

dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local",
    os.Getenv("DB_USER"),
    os.Getenv("DB_PASSWORD"),
    os.Getenv("DB_HOST"),
    os.Getenv("DB_PORT"),
    os.Getenv("DB_NAME"),
)

db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})

Multiple Database Connections

Connecting to Multiple Databases

go
// Primary database
primaryDB, err := gorm.Open(mysql.Open(primaryDSN), &gorm.Config{})

// Replica database
replicaDB, err := gorm.Open(mysql.Open(replicaDSN), &gorm.Config{})

// Use different databases
primaryDB.Create(&user)
replicaDB.First(&user, 1)

Notes

1. Connection Pool Size: Set connection pool size reasonably based on application concurrency
2. Timeout Settings: Set reasonable connection timeout and read/write timeout
3. Error Handling: Properly handle connection errors and query errors
4. Resource Release: Ensure database connection is closed when application exits
5. Security: Don't hardcode database password in code
6. Monitoring: Monitor connection pool usage, discover problems in time

Common Questions

Q: How to handle connection timeout?

A: Set timeout parameter in DSN, or use context to set timeout.

Q: How large should the connection pool be?

A: Adjust based on application concurrency and database server performance, usually MaxOpenConns set to 2-4 times CPU cores.

Q: How to switch databases?

A: Just need to change the corresponding driver and DSN, other code basically doesn't need modification.

Q: How to handle database connection leaks?

A: Use defer to ensure connection is closed, monitor connection pool status, set reasonable connection lifecycle.