Develop LindormTable applications using the Go database/sql library - Lindorm

Use Go's standard database/sql library to connect to LindormTable SQL and run CRUD operations — no additional client SDK required.

This procedure does not apply to LindormTable Serverless.

Prerequisites

Before you begin, ensure that you have:

Go V1.17 or later installed. See the official Go documentation for download and installation instructions.
Your client IP address added to the Lindorm whitelist.

How it works

LindormTable SQL is built on Apache Calcite, so the standard sql.Open pattern used with MySQL or PostgreSQL drivers does not apply here. Instead, use the Avatica connector (avatica.NewConnector) with sql.OpenDB to establish the connection.

Set up the Go driver

Step 1: Add the Avatica driver to your go.mod file.

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

Step 2: Import the driver in your .go file.

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

Step 3: Initialize the connection pool in the main function.

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 connection pool settings
db.SetConnMaxIdleTime(8 * time.Minute)
db.SetMaxOpenConns(20)
db.SetMaxIdleConns(2)

The following table describes the connection parameters.

Parameter	Required	Default	Description
`databaseUrl`	Yes	—	The LindormTable SQL endpoint. Format: `http://<endpoint>:30060`. To get your endpoint, see View the endpoints of LindormTable.
`user`	Yes	—	The username for LindormTable.
`password`	Yes	—	The password for LindormTable. To reset a forgotten password, see Change the password.
`database`	Yes	—	The name of the database to access.
`SetConnMaxIdleTime`	No	0 (no timeout)	The maximum idle time for connections.
`SetMaxOpenConns`	No	0 (no limit)	The maximum number of open connections in the pool.
`SetMaxIdleConns`	No	2	The maximum number of idle connections in the pool.

Run CRUD operations

All examples below use the db object initialized in the previous step.

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

Use direct execution for one-off writes, or a prepared statement for repeated writes with different values. Prepared statements are analyzed and optimized once and reused across executions, which improves performance for bulk or repeated writes.

Direct execution

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

Prepared statement (recommended for repeated writes)

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

Direct query

rows, err := db.Query("select * from user_test")
if err != nil {
    fmt.Println("query data error", err)
    return
}
defer rows.Close() // Always close rows to avoid connection leaks.
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)
}

Parameterized query (recommended for repeated queries)

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() // Always close rows to avoid connection leaks.
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
}

What's next

Review the complete working examples on GitHub: