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

Connection Pool Size: Set connection pool size reasonably based on application concurrency
Timeout Settings: Set reasonable connection timeout and read/write timeout
Error Handling: Properly handle connection errors and query errors
Resource Release: Ensure database connection is closed when application exits
Security: Don't hardcode database password in code
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.