Use Go to develop applications - Lindorm - Alibaba Cloud Documentation Center

This topic describes how to use the database/sql package provided by GO and SQL statements to develop applications based on LindormTable and provides examples.

Prerequisites

Go is installed. We recommend that you install Go 1.17 or later versions. For more information about how to install Go, see Download and install.
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.

Procedure

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
```

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 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 left-side navigation pane, select Database Connections > Wide Table Engine. On the Wide Table Engine tab, click Enable Public Endpoint. If you use a VPC to access the Lindorm instance, specify the LindormTable VPC endpoint for MySQL in the value of host. If you use the Internet to access the Lindorm instance, specify the LindormTable Internet endpoint for MySQL in the value of host.
port	The port used to access LindormTable by 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	// Specify the timeout period of the database connection. Unit: seconds.

Establish a connection and use LindormTable SQL to perform operations in LindormTable. The following code block provides an example on 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. 
db.SetMaxOpenConns(20)
// Specify the maximum number of idle connections. The default value is 2. 
db.SetMaxIdleConns(20)
// Specify the maximum idle period of connections. The default value is 0, which indicates that connections do not time out. 
db.SetConnMaxIdleTime(8 * time.Minute)
defer db.Close()

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

Sample code

The following code provides a complete example on how to connect to and use LindormTable by using Go:

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 by 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 = "10s"
)
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.
	db.SetMaxOpenConns(20)
	// Specify the maximum number of idle connections. The default value is 2.
	db.SetMaxIdleConns(20)
	// Specify the maximum idle period of connections. The default value is 0, which indicates that connections do not time out.
	db.SetConnMaxIdleTime(8 * time.Minute)
	defer db.Close()

	// Query all databases.
	{
		rows, err := db.Query("show databases")
		if err != nil {
			panic(err.Error())
		}
		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
		}
		_, 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: Query data by specifying parameters. 
	{
		stmt, err := db.Prepare("select * from user_test where id=?")
		if err != nil {
			fmt.Println("prepare error", err)
			return
		}
		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