Use Go-MySQL-Driver to connect to and operate on RDS for MySQL - ApsaraDB RDS

Go-MySQL-Driver is a third-party MySQL driver for Go that manages a connection pool between your application and the database. Use it when your application opens many short-lived connections to an RDS for MySQL instance, or when the total number of concurrent connections approaches the instance limit. The driver reduces connection overhead and lowers the main thread load on the instance.

This guide covers:

Adding Go-MySQL-Driver to your project and initializing the connection pool
Configuring key connection pool parameters
Running CRUD operations against your RDS instance

Note Go-MySQL-Driver supports MySQL and MariaDB databases. Only RDS instances running MySQL 5.7 or 8.0 are supported.

Prerequisites

Before you begin, ensure that you have:

Go 1.20 or later installed. See Download and install.
The IP address of your client added to the IP whitelist of your RDS instance. See Configure an IP whitelist.

Note If your application runs on an Elastic Compute Service (ECS) instance in the same region and virtual private cloud (VPC) as the RDS instance, you do not need to configure an IP whitelist.

Connect to an RDS instance

Step 1: Add the dependency

Add go-sql-driver to the go.mod file of your Go project:

require (
    github.com/go-sql-driver/mysql v1.8.1
)

Import the required packages in your .go file:

import (
    "database/sql"                       // General SQL interface
    "fmt"                                // Formatted input and output
    "os"                                 // Environment variable access
    "time"                               // Time-related operations

    "github.com/go-sql-driver/mysql"     // Go-MySQL-Driver
)

Step 2: Initialize the connection pool

Use the following code in your main function to configure and open a connection pool. Store your credentials in environment variables rather than hardcoding them.

cfg := mysql.NewConfig()
cfg.User   = os.Getenv("RDS_USER")    // RDS account username
cfg.Passwd = os.Getenv("RDS_PASSWD")  // RDS account password
cfg.Net    = "tcp"                    // Always TCP; do not change
cfg.Addr   = os.Getenv("RDS_ADDR")    // Endpoint and port, e.g. rm-2zefwjx1s8156******.mysql.rds.aliyuncs.com:3306
cfg.DBName = os.Getenv("RDS_DBNAME")  // Database name

conn, err := mysql.NewConnector(cfg)
if err != nil {
    panic(err.Error())
}

db := sql.OpenDB(conn)
defer func(db *sql.DB) {
    err := db.Close()
    if err != nil {
        fmt.Printf("Error closing database connection: %v\n", err)
    }
}(db)

// Connection pool settings
db.SetMaxOpenConns(20)                       // Maximum open connections
db.SetMaxIdleConns(2)                        // Maximum idle connections
db.SetConnMaxIdleTime(10 * time.Second)      // Maximum idle time per connection
db.SetConnMaxLifetime(80 * time.Second)      // Maximum lifetime per connection

To find your endpoint and port, see View and manage instance endpoints and ports.

Connection pool parameters

The following tables list the parameters you can pass to the mysql.Config struct and the sql.DB setter methods.

Important

Run a full feature and performance test before applying new parameter values in production.

Recommended configurations

Parameter	Method	Default	Recommended	Notes
`maxIdleTime`	`SetConnMaxIdleTime()`	0 (no timeout)	10–30 minutes	A value of 0 means connections never time out. Set based on your idle traffic patterns.
`maxLifetime`	`SetConnMaxLifetime()`	0 (unlimited)	1–8 hours	Prevents long-lived connections from accumulating.
`maxOpen`	`SetMaxOpenConns()`	0 (unlimited)	100	If `MaxIdleConns` exceeds the new `MaxOpenConns` value, `MaxIdleConns` is reduced automatically.
`maxIdleCount`	`SetMaxIdleConns()`	2	20	Keeps idle connections ready for request bursts. Set to 0 or below to close all idle connections. If `MaxOpenConns` is set and is lower than `MaxIdleConns`, the idle count is reduced to match.
`readTimeout`	`cfg.ReadTimeout`	0 (no limit)	10,000–60,000 ms	Use a `time.Duration` constant, e.g. `cfg.ReadTimeout = 30 * time.Second`. Do not pass a string such as `"30s"`.
`writeTimeout`	`cfg.WriteTimeout`	0 (no limit)	10,000–60,000 ms	Same format requirement as `readTimeout`.

Optional configurations

Parameter	Method	Default	Recommended	Notes
`timeout`	`cfg.Timeout`	OS default	3,000 ms	Connection establishment timeout. Keep in the 1–10 s range; adjust based on network latency. Use a `time.Duration` constant.

Perform CRUD operations

The following examples use a userinfo table with this schema:

CREATE TABLE IF NOT EXISTS userinfo (
    uid        INT AUTO_INCREMENT,
    username   VARCHAR(20) NOT NULL DEFAULT '',
    departname VARCHAR(20) NOT NULL DEFAULT '',
    created    VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY (uid)
);

Create a table

_, err = db.Exec(`CREATE TABLE IF NOT EXISTS userinfo (
    uid        INT AUTO_INCREMENT,
    username   VARCHAR(20) NOT NULL DEFAULT '',
    departname VARCHAR(20) NOT NULL DEFAULT '',
    created    VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY (uid)
)`)
if err != nil {
    fmt.Println("create table error:", err)
    return
}

Insert data

Direct insert

stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
if err != nil {
    panic(err)
}
res, err := stmt.Exec("James", "Research", "2016-06-17")
if err != nil {
    panic(err)
}
id, _ := res.LastInsertId()
fmt.Println(id)

Parameterized insert

result, err := db.Exec(
    "INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)",
    "Linda", "Testing", "2016-06-21",
)
if err != nil {
    panic(err)
}
ids, _ := result.LastInsertId()
fmt.Println(ids)

Update data

stmt, err := db.Prepare("UPDATE userinfo SET username=?, departname=?, created=? WHERE username=?")
if err != nil {
    fmt.Println("prepare update error:", err)
    return
}
res, err := stmt.Exec("Robert", "Sales", "2024-09-23", "Linda")
if err != nil {
    fmt.Println(err)
}
rowCnt, _ := res.RowsAffected()
fmt.Println(rowCnt)

Query data

Direct query

rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?", "Robert")
if err != nil {
    panic(err)
}
defer rows.Close()

for rows.Next() {
    var username, departname, created string
    if err := rows.Scan(&username, &departname, &created); err != nil {
        fmt.Println(err)
    }
    fmt.Println("username:", username, "departname:", departname, "created:", created)
}

Parameterized query

stmt, err := db.Prepare("SELECT username,departname,created FROM userinfo WHERE username=?")
if err != nil {
    fmt.Println("prepare error:", err)
    return
}
rows, err := stmt.Query("Robert")
if err != nil {
    fmt.Println("query error:", err)
    return
}
defer rows.Close()

for rows.Next() {
    var username, departname, created string
    if err := rows.Scan(&username, &departname, &created); err != nil {
        fmt.Println(err)
    }
    fmt.Println("username:", username, "departname:", departname, "created:", created)
}

Delete data

stmt, err := db.Prepare("DELETE FROM userinfo WHERE username=?")
if err != nil {
    panic(err)
}
res, err := stmt.Exec("James")
if err != nil {
    panic(err)
}
rowAffect, _ := res.RowsAffected()
fmt.Println("Rows deleted:", rowAffect)

Complete example

The following is a self-contained example that puts together connection setup and all CRUD operations.

package main

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

    "github.com/go-sql-driver/mysql"
)

func main() {
    cfg := mysql.NewConfig()
    cfg.User     = os.Getenv("RDS_USER")    // RDS account username
    cfg.Passwd   = os.Getenv("RDS_PASSWD")  // RDS account password
    cfg.Net      = "tcp"                    // Always TCP
    cfg.Addr     = os.Getenv("RDS_ADDR")    // Endpoint and port, e.g. rm-2ze1vw17v542q6b****.mysql.pre.rds.aliyuncs.com:3306
    cfg.DBName   = os.Getenv("RDS_DBNAME")  // Database name
    cfg.Timeout      = 3 * time.Second       // Connection timeout
    cfg.ReadTimeout  = 60 * time.Second      // I/O read timeout
    cfg.WriteTimeout = 60 * time.Second      // I/O write timeout

    conn, err := mysql.NewConnector(cfg)
    if err != nil {
        panic(err.Error())
    }

    db := sql.OpenDB(conn)
    defer func(db *sql.DB) {
        if err := db.Close(); err != nil {
            fmt.Printf("Error closing database connection: %v\n", err)
        }
    }(db)

    db.SetMaxOpenConns(100)
    db.SetMaxIdleConns(20)
    db.SetConnMaxIdleTime(10 * time.Minute)
    db.SetConnMaxLifetime(8 * time.Hour)

    // Create table
    _, err = db.Exec(`CREATE TABLE IF NOT EXISTS userinfo (
        uid        INT AUTO_INCREMENT,
        username   VARCHAR(20) NOT NULL DEFAULT '',
        departname VARCHAR(20) NOT NULL DEFAULT '',
        created    VARCHAR(10) NOT NULL DEFAULT '',
        PRIMARY KEY (uid)
    )`)
    if err != nil {
        fmt.Println("create table error:", err)
        return
    }

    // Direct insert
    stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
    if err != nil {
        panic(err)
    }
    res, err := stmt.Exec("James", "Research", "2016-06-17")
    if err != nil {
        panic(err)
    }
    id, _ := res.LastInsertId()
    fmt.Println(id)

    // Parameterized insert
    result, err := db.Exec(
        "INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)",
        "Linda", "Testing", "2016-06-21",
    )
    if err != nil {
        panic(err)
    }
    ids, _ := result.LastInsertId()
    fmt.Println(ids)

    // Update
    stmtUpdate, err := db.Prepare("UPDATE userinfo SET username=?, departname=?, created=? WHERE username=?")
    if err != nil {
        fmt.Println("prepare update error:", err)
        return
    }
    resUpdate, err := stmtUpdate.Exec("Robert", "Sales", "2024-09-23", "Linda")
    if err != nil {
        fmt.Println(err)
    }
    rowCnt, _ := resUpdate.RowsAffected()
    fmt.Println(rowCnt)

    // Direct query
    rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?", "Robert")
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var username, departname, created string
        if err := rows.Scan(&username, &departname, &created); err != nil {
            fmt.Println(err)
        }
        fmt.Println("username:", username, "departname:", departname, "created:", created)
    }

    // Parameterized query
    stmtQuery, err := db.Prepare("SELECT username,departname,created FROM userinfo WHERE username=?")
    if err != nil {
        fmt.Println("prepare error:", err)
        return
    }
    rowData, err := stmtQuery.Query("Robert")
    if err != nil {
        fmt.Println("query error:", err)
        return
    }
    defer rowData.Close()
    for rowData.Next() {
        var username, departname, created string
        if err := rowData.Scan(&username, &departname, &created); err != nil {
            fmt.Println(err)
        }
        fmt.Println("username:", username, "departname:", departname, "created:", created)
    }

    // Delete
    delStmt, err := db.Prepare("DELETE FROM userinfo WHERE username=?")
    if err != nil {
        panic(err)
    }
    resultDel, err := delStmt.Exec("James")
    if err != nil {
        panic(err)
    }
    rowAffect, _ := resultDel.RowsAffected()
    fmt.Println("Rows deleted:", rowAffect)
}

ApsaraDB RDS:Use Go-MySQL-Driver to connect to an ApsaraDB RDS for MySQL instance