Lindorm:Use Go to develop applications

Prerequisites

• Go is installed. We recommend that you install Go 1.17 or later. For more information, see Go.

• The MySQL compatibility feature is enabled for the instance. For more information, see Enable the MySQL compatibility feature.

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

Considerations

• Lindorm SQL frontend access nodes use Server Load Balancer (SLB) to distribute client requests to various frontend nodes. To ensure client requests are evenly distributed to all frontend nodes,

  we recommend that you avoid setting a long connection keep-alive time. You can configure the SetConnMaxLifetime parameter to refresh connections periodically.

• In complex network environments, when gateway performance reaches a bottleneck, network links are long, or network jitter, retransmission rate, or packet loss rate is high, connections may be interrupted. We recommend that you ensure proper connection pool configuration and implement retry mechanisms in your business code when necessary.

• When the server is upgraded and restarted, connections may be temporarily interrupted. Even with a connection pool, your business may still detect exceptions. We recommend that you catch exceptions and implement retries.

• Adjust connection pool configurations as needed and ensure the configurations take effect. Make sure the number of connections in the connection pool meets your business needs to avoid waiting for connections due to insufficient connections, which can increase response time (RT). You can diagnose issues by printing the connection pool status (fmt.Printf("%+v\n", db.Stats())).

Procedure

1. In the go.mod file of your Go project, add the dependency of Go MySQL Driver.

   require github.com/go-sql-driver/mysql v1.7.1

2. Configure connection parameters.

   const ( 
       user = "user"
       password = "test"
       host = "ld-uf6k8yqb741t3****-proxy-sql-lindorm.lindorm.rds.aliyuncs.com"
       port = 33060
       database = "default"
       connectTimeout = "10s"
   )

   Parameters

   Parameter	Description
   user	If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see Manage users.
   password
   host	The LindormTable Endpoint For MySQL. For more information about how to obtain the endpoint, see View endpoints. Important If your application is deployed on an ECS instance, we recommend that you use a virtual private cloud (VPC) to connect to the Lindorm instance to ensure higher security and lower network latency. If your application is deployed on a local server and needs to connect to the Lindorm instance over the Internet, you can perform the following steps to enable the Internet endpoint for the instance in the Lindorm console: In the console, select Database Connections > Wide Table Engine, and on the Wide Table Engine tab, click Enable Public Endpoint. If you access the Lindorm instance over a VPC, set the host parameter to the MySQL-compatible VPC address. If you access the Lindorm instance over the internet, set the host parameter to the MySQL-compatible Internet address.
   port	The port used to access LindormTable using MySQL. The value of this parameter is fixed to 33060.
   database	The name of the database to which you want to connect. By default, your client is connected to a database named default.
   connectTimeout	The timeout period of the database connection. Unit: seconds (s).

3. Establish a connection and use LindormTable SQL to perform operations in LindormTable. The following code block provides an example of how to use LindormTable SQL to query all databases:

   url := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?timeout=%s", user, password, host, port, database, connectTimeout)
   db, err := sql.Open("mysql", url)
   if err != nil {
   	panic(err.Error())
   }
   //Specify the maximum number of connections that can be established. The default value is 0, which indicates that no limit applies to the maximum number of connections. Set this as needed to avoid waiting for connections due to insufficient connections.
   db.SetMaxOpenConns(20)
   //Specify the maximum number of idle connections. The default value is 2. We recommend setting SetMaxIdleConns equal to SetMaxOpenConns.
   db.SetMaxIdleConns(20)
   // Specify the maximum idle period of connections. The default value is 0, which indicates that connections do not time out. We recommend setting this to 8 minutes.
   db.SetConnMaxIdleTime(8 * time.Minute)
   // Specify the maximum lifetime of connections to avoid connection imbalance caused by long-term use of persistent connections. We recommend setting this to 30 minutes.
   db.SetConnMaxLifetime(30 * time.Minute)
   
   defer db.Close()
   
   // Query all databases
   {
   	rows, err := db.Query("show databases")
   	if err != nil {
   		panic(err.Error())
   	}
       defer rows.Close()
   	for rows.Next() {
   		var dbName string
   		err := rows.Scan(&dbName)
   		if err != nil {
   			panic(err.Error())
   		}
   		fmt.Println(dbName)
   	}
   }

Sample code

The complete example code is as follows:

package main
import (
    "database/sql"
    "fmt"
    "time"
    _ "github.com/go-sql-driver/mysql"
)
const (
    //Set user to the username used to access LindormTable
    user = "user"
    //Set password to the password used to access LindormTable
    password = "test"
    //Set host to the LindormTable endpoint for MySQL
    host = "ld-uf6k8yqb741t3****-proxy-sql-lindorm-public.lindorm.rds.aliyuncs.com"
    //Specify the port used to access LindormTable using MySQL. The port number is fixed to 33060
    port = 33060
    //Set database to the name of the database that you want to connect
    database = "default"
    //Specify the timeout period of the database connection
    connectTimeout = "20s"
)
func main() {
	//Establish the database connection
	url := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?timeout=%s", user, password, host, port, database, connectTimeout)
	db, err := sql.Open("mysql", url)
	if err != nil {
		panic(err.Error())
	}
    
    //Specify the maximum number of connections that can be established. The default value is 0, which indicates that no limit applies to the maximum number of connections. Set this as needed to avoid waiting for connections due to insufficient connections.
    db.SetMaxOpenConns(20)
    //Specify the maximum number of idle connections. The default value is 2. We recommend setting SetMaxIdleConns equal to SetMaxOpenConns.
    db.SetMaxIdleConns(20)
    // Specify the maximum idle period of connections. The default value is 0, which indicates that connections do not time out. We recommend setting this to 8 minutes.
    db.SetConnMaxIdleTime(8 * time.Minute)
    // Specify the maximum lifetime of connections to avoid connection imbalance caused by long-term use of persistent connections. We recommend setting this to 30 minutes.
    db.SetConnMaxLifetime(30 * time.Minute)
    
	defer db.Close()

	//Periodically print connection pool status for diagnosing whether there are enough connections, if there are connections waiting, and how many connections have been closed
	go func() {
		for {
			fmt.Printf("%+v\n", db.Stats())
			time.Sleep(10 * time.Second)
		}
	}()

    
	//Query all databases
	{
		rows, err := db.Query("show databases")
		if err != nil {
			panic(err.Error())
		}
        defer rows.Close()
		for rows.Next() {
			var dbName string
			err := rows.Scan(&dbName)
			if err != nil {
				panic(err.Error())
			}
			fmt.Println(dbName)
		}
	}

	//Create a table
	{
		_, err := db.Exec("create table if not exists user_test(id int, name varchar,age int, primary key(id))")
		if err != nil {
			fmt.Println("create table error ", err)
			return
		}
	}

	//Write data to the table

	//Method 1: Directly write data to the table
	{
		_, err = db.Exec("upsert into user_test(id,name,age) values(1,'zhangsan',17)")
		if err != nil {
			fmt.Println("insert data error", err)
			return
		}
	}

	//Method 2: Write data to the table by specifying parameters
	{
		stmt, err := db.Prepare("upsert into user_test(id,name,age) values(?,?,?)")
		if err != nil {
			fmt.Println("prepare error", err)
			return
		}
        defer stmt.Close() 
		_, err = stmt.Exec(2, "lisi", 18)
		if err != nil {
			fmt.Println("upsert error", err)
			return
		}
	}

	//Query data in the table

	//Method 1: Directly query data

	{
		rows, err := db.Query("select * from user_test")
		if err != nil {
			fmt.Println("query data error", err)
			return
		}
		defer rows.Close()
		var id int
		var name string
		var age int
		for rows.Next() {
			err = rows.Scan(&id, &name, &age)
			if err != nil {
				fmt.Println("scan data error", err)
				return
			}
			fmt.Println("id:", id, "name:", name, "age:", age)
		}
	}

	// Method 2: Execute parameterized query by specifying parameters
	{
		stmt, err := db.Prepare("select * from user_test where id=?")
		if err != nil {
			fmt.Println("prepare error", err)
			return
		}
        defer stmt.Close()
		rows, err := stmt.Query(1)
		if err != nil {
			fmt.Println("query data error", err)
			return
		}
		defer rows.Close()
		var id int
		var name string
		var age int
		for rows.Next() {
			err = rows.Scan(&id, &name, &age)
			if err != nil {
				fmt.Println("scan data error", err)
				return
			}
			fmt.Println("id:", id, "name:", name, "age:", age)
		}
	}

	//Delete data from the table
	{
		_, err = db.Exec("delete from user_test where id=1")
		if err != nil {
			fmt.Println("delete data error", err)
			return
		}
	}
}

If the current instance contains only a database named default, the following result is returned:

default
information_schema
id: 1 name: zhangsan age: 17
id: 2 name: lisi age: 18
id: 1 name: zhangsan age: 17