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

Limits

Only RDS instances that run MySQL 5.7 or 8.0 can be connected by using Go-MySQL-Driver.

Preparations

• Go 1.20 or later is installed. For more information, see Download and install.

• The IP address of your client is added to the IP address whitelist of the RDS instance. For more information, see Configure an IP whitelist.

  Note

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

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

// Establish a database connection.
cfg := mysql.NewConfig()
cfg.User = "****" // Replace the value with the username of the account used to log on to your RDS instance.
cfg.Passwd = "****" // Replace the value with the password of the account.
cfg.Net = "tcp" // The connection type is TCP by default. You do not need to modify the value.
cfg.Addr = "rm-2zefwjx1s8156******.mysql.rds.aliyuncs.com:3306" // Replace the value with the endpoint and port number of your RDS instance.
cfg.DBName = "****" // Replace the value with the name of the RDS instance.

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)   // Specify the maximum number of open connections in the connection pool. You can modify the value based on your business requirements.
db.SetMaxIdleConns(2)    // Specify the maximum number of idle connections in the connection pool. You can modify the value based on your business requirements.
db.SetConnMaxIdleTime(10 * time.Second)   // Specify the maximum period of time that a connection in the connection pool can stay idle. You can modify the value based on your business requirements.
db.SetConnMaxLifetime(80 * time.Second)   // Specify the maximum period of time that a connection can be reused. You can modify the value based on your business requirements.

Perform operations on a database

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

Example

package main

import (
    // Import the database/sql package that provides a general SQL interface.
    "database/sql"
    // Import the fmt package that provides formatted input and output.
    "fmt"
    // Import Go-MySQL-Driver to connect and manage databases in RDS for MySQL.
    "github.com/go-sql-driver/mysql"
    // Import the time package to handle time-related operations.
    "time"
)

func main() {
    // Establish a database connection.
    cfg := mysql.NewConfig()
    cfg.User = "****"                                                   /*Replace the value with the username of the account used to log on to your RDS instance.*/
    cfg.Passwd = "****"                                                 /*Replace the value with the password of the account.*/
    cfg.Net = "tcp"                                                     /*The connection type is TCP by default. You do not need to modify the value.*/
    cfg.Addr = "rm-2ze1vw17v542q6b****.mysql.pre.rds.aliyuncs.com:3306" /*Replace the value with the endpoint and port number of your RDS instance.*/
    cfg.DBName = "****"                                                 /*Replace the value with the name of the database.*/
    cfg.Timeout = 3 * time.Second                                       /*Specify 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 the parameters related to the connection pool.
    db.SetMaxOpenConns(100)                 /*Specify the maximum number of open connections in the connection pool.*/
    db.SetMaxIdleConns(20)                  /*Specify the maximum number of idle connections in the connection pool.*/
    db.SetConnMaxIdleTime(10 * time.Minute) /*Specify the maximum period of time that a connection in the connection pool can stay idle.*/
    db.SetConnMaxLifetime(8 * time.Hour) /*Specify 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 the 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 the table by performing a parameterized query.
    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 the 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 to the table.
    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 to the table by performing a 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 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 the 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

• Use DBUtils to connect to an ApsaraDB RDS for MySQL instance.

• Use Druid to connect to an ApsaraDB RDS for MySQL instance.

• Configure the connection pooling feature.