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

Limits

Only RDS instances running MySQL 5.7 or 8.0 are supported.

Preparations

• Download and install the Go environment. We recommend version 1.20 or later.

• Add your client's IP address to the RDS instance's whitelist. For more information, see Configure a whitelist.

  Note

  If your application is deployed on an ECS instance in the same region and VPC as the RDS instance, you do not need to configure a whitelist.

Use Go-MySQL-Driver to connect to an RDS instance

// Create a database connection
cfg := mysql.NewConfig()
cfg.User = "****"       // You need to manually replace this with the actual database username.
cfg.Passwd = "****"     // You need to manually replace this with the actual database password.
cfg.Net = "tcp"         // The connection type is TCP. You do not need to manually modify this.
cfg.Addr = "rm-2zefwjx1s8156******.mysql.rds.aliyuncs.com:3306" // You need to manually replace this with the actual MySQL database connection address and port number.
cfg.DBName = "****"     // You need to manually replace this with the actual 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)
// Configure the parameters related to the connection pool.
db.SetMaxOpenConns(20)                 // Configure the maximum number of open connections in the connection pool. You can manually adjust this based on your actual needs.
db.SetMaxIdleConns(2)                  // Configure the maximum number of idle connections in the connection pool. You can manually adjust this based on your actual needs.
db.SetConnMaxIdleTime(10 * time.Second) // Configure the maximum idle period of connections in the connection pool. You can manually adjust this based on your actual needs.
db.SetConnMaxLifetime(80 * time.Second) // Configure the maximum period of time that a connection can be reused. You can manually adjust this based on your actual needs.

Perform operations on databases

_, 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
}

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

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

stmtUpdate, err := db.Prepare("UPDATE userinfo SET username=?, departname=?, created=? WHERE username=?")
if err != nil {
    fmt.Println("Prepare update statement 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)

rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?", "Robert")
if err != nil {
    panic(err)
}
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)
}
defer func(rows *sql.Rows) {
 err := rows.Close()
 if err != nil {
     fmt.Println(err)
 }
}(rows)

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 data error", err)
    return
}
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)
}
defer func(rows *sql.Rows) {
 err := rows.Close()
 if err != nil {
     fmt.Println(err)
 }
}(rowData)

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

Complete example

package main

import (
    // Import the database/sql package of the Go programming language. This package provides a general SQL interface.
    "database/sql"
    // Import the fmt package. This package is used for formatting input and output in the standard library of the Go programming language.
    "fmt"
    // This driver can be used to connect to and operate MySQL databases in the Go programming language.
    "github.com/go-sql-driver/mysql"
    // Import the time package of the Go programming language. This package is used to handle time-related operations.
    "time"
)

func main() {
    // Create a database connection
    cfg := mysql.NewConfig()
    cfg.User = "****"                                                   /* Username */
    cfg.Passwd = "****"                                                 /* Password */
    cfg.Net = "tcp"                                                     /* Connection type */
    cfg.Addr = "rm-2ze1vw17v542q6b****.mysql.pre.rds.aliyuncs.com:3306" /* Connection address */
    cfg.DBName = "****"                                                 /* Database name */
    cfg.Timeout = 3 * time.Second                                       /* The timeout period for establishing a connection to the database. You can specify the value in milliseconds (ms), seconds (s), or minutes (m) based on your business requirements. */
    cfg.ReadTimeout = 60 * time.Second                                  /* The timeout period for I/O reads. You can specify the value in milliseconds (ms), seconds (s), or minutes (m) based on your business requirements. */
    cfg.WriteTimeout = 60 * time.Second                                 /* The timeout period for I/O writes. You can specify the value in milliseconds (ms), seconds (s), or minutes (m) based on your business requirements. */

    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)

    // Configure parameters related to the connection pool.
    db.SetMaxOpenConns(100)                 /* Configure the maximum number of open connections in the connection pool. */
    db.SetMaxIdleConns(20)                  /* Configure the maximum number of idle connections in the connection pool. */
    db.SetConnMaxIdleTime(10 * time.Minute) /* Configure the maximum idle period of connections in the connection pool. */
    db.SetConnMaxLifetime(8 * time.Hour)    /* Configure the maximum period of time that a connection can be reused. */

    // Create a table named userinfo
    _, 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
    }

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

    // Write data to a table by binding parameters
    result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)", "Linda", "Testing", "2016-06-21")
    if err != nil {
        panic(err)
    }
    ids, err := result.LastInsertId()
    fmt.Println(ids)

    // Update data in a table
    stmtUpdate, err := db.Prepare("UPDATE userinfo SET username=?, departname=?, created=? WHERE username=?")
    if err != nil {
        fmt.Println("Prepare update statement 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)

    // Directly query data
    rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?", "Robert")
    if err != nil {
        panic(err)
    }
    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)
    }
    defer func(rows *sql.Rows) {
        err := rows.Close()
        if err != nil {
            fmt.Println(err)
        }
    }(rows)

    // Query data by binding parameters
    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 data error", err)
        return
    }
    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)
    }
    defer func(rows *sql.Rows) {
        err := rows.Close()
        if err != nil {
            fmt.Println(err)
        }
    }(rowData)

    // Delete data from a table
    delStmt, _ := db.Prepare("DELETE FROM userinfo WHERE username=?")
    resultDel, err := delStmt.Exec("James")
    if err != nil {
        panic(err)
    }
    rowAffect, _ := resultDel.RowsAffected()
    fmt.Println("Data deletion completed.", rowAffect)
}

References

• Tutorials: Use the Python Connection Pool DBUtils to Connect to an RDS MySQL Database

• Tutorials: Use the Java Connection Pool Druid to Connect to an RDS MySQL Database

• The RDS MySQL Database Proxy's Connection Pool Feature: Configure an RDS MySQL Connection Pool