Lindorm:Use the APIs provided by the database/sql library of Go to develop applications

Prerequisites

• Go V1.17 or later is installed. For more information about how to download and install Go, see official documentation.

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

Limits

The procedure described in this topic is not applicable to LindormTable Serverless.

Procedure

1. Add the following dependencies to the go.mod file in your Go project:

   require github.com/apache/calcite-avatica-go/v5 v5.0.0
   replace github.com/apache/calcite-avatica-go/v5 => github.com/aliyun/alibabacloud-lindorm-go-sql-driver/v5 v5.0.6

2. Add the dependency on the database driver to the .go file. The following code block provides an example:

   import (
       avatica "github.com/apache/calcite-avatica-go/v5"
   )

3. Initialize the connection pool and configure related parameters in the main method of the .go file. The following code provides an example:

   databaseUrl := "http://localhost:30060" 
   
   conn := avatica.NewConnector(databaseUrl).(*avatica.Connector)
   conn.Info = map[string]string{
       "user":     "sql",     
       "password": "test",   
       "database": "default", 
   }
   db := sql.OpenDB(conn)
   // Configure parameters related to the connection pool.
   db.SetConnMaxIdleTime(8 * time.Minute)
   db.SetMaxOpenConns(20)
   db.SetMaxIdleConns(2)

   The following table describes the parameters that you can configure for the connection pool.

   Parameter	Required	Description
   databaseUrl	Yes	The endpoint that is used to connect to LindormTable SQL. Example: http://ld-bp12pc23yfb38****-proxy-lindorm.lindorm.rds.aliyuncs.com:30060. For more information about how to obtain the endpoint, see View the endpoints of LindormTable.
   user	Yes	The username that is used to connect to LindormTable.
   password	Yes	The password that is used to connect to LindormTable. If you forget your password, you can change the password in the cluster management system of LindormTable. For more information, see Change the password of a user.
   database	Yes	The name of the database that you want to access.
   SetConnMaxIdleTime	No	The maximum idle time period for connections. Default value: 0. The default value indicates that connections to LindormTable never time out.
   SetMaxOpenConns	No	The maximum number of connections in the connection pool. Default value: 0. The default value indicates that the number of connections in the connection pool is not limited.
   SetMaxIdleConns	No	The maximum number of idle connections in the connection pool. Default value: 2.

4. Perform operations in LindormTable, such as creating tables, writing data to tables, querying data, and deleting data. The following examples show how to perform operations in LindormTable:

   • 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 a table.

     • Method 1: Directly write data to a 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 a 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(1, "zhangsan", 17)
       if err != nil {
           fmt.Println("upsert error", err)
           return
       }

   • Query data.

     • 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.

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

   Note

   • In business scenarios where similar statements are executed to write or query data, we recommend that you use an stmt object initialized by the Prepare method to write or query data. In this case, same statements are analyzed, rewritten, and optimized in a batch. This way, the performance of the statements is improved.

   • For the complete sample code, visit Sample code and Write and query data by specifying parameters.