databend-go

Golang driver for databend cloud

Installation

go get github.com/datafuselabs/databend-go

Key features

• Supports native Databend HTTP client-server protocol
• Compatibility with database/sql

Examples

Connecting

Connection can be achieved either via a DSN string with the format https://user:password@host/database?<query_option>=<value> and sql/Open method such as https://username:password@tenant--warehousename.ch.datafusecloud.com/test.

import (
"database/sql"
_ "github.com/datafuselabs/databend-go"
)

func ConnectDSN() error {
dsn, cfg, err := getDSN()
if err != nil {
log.Fatalf("failed to create DSN from Config: %v, err: %v", cfg, err)
}
conn, err := sql.Open("databend", dsn)
if err != nil {
return err
}
return conn.Ping()
}

Connection Settings

If you are using the databend cloud you can get the connection settings using the following way.

• host - the connect host such as tenant--warehousename.ch.datafusecloud.com that you can get from databend cloud as follows:

• username/password - auth credentials that you can get from databend cloud connect page as above

• database - select the current default database

Execution

Once a connection has been obtained, users can issue sql statements for execution via the Exec method.

    dsn, cfg, err := getDSN()
if err != nil {
log.Fatalf("failed to create DSN from Config: %v, err: %v", cfg, err)
}
conn, err := sql.Open("databend", dsn)
if err != nil {
fmt.Println(err)
}
conn.Exec(`DROP TABLE IF EXISTS data`)
_, err = conn.Exec(`
    CREATE TABLE IF NOT EXISTS  data(
        Col1 TINYINT,
        Col2 VARCHAR
    )`)
if err != nil {
fmt.Println(err)
}
_, err = conn.Exec("INSERT INTO data VALUES (1, 'test-1')")

Batch Insert

If the create table SQL is CREATE TABLE test ( i64 Int64, u64 UInt64, f64 Float64, s String, s2 String, a16 Array(Int16), a8 Array(UInt8), d Date, t DateTime) you can use the next code to batch insert data:

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/datafuselabs/databend-go"
)

func main() {
	conn, err := sql.Open("databend", "http://databend:databend@localhost:8000/default?sslmode=disable")
	tx, err := conn.Begin()
	if err != nil {
		fmt.Println(err)
	}
	batch, err := tx.Prepare(fmt.Sprintf("INSERT INTO %s VALUES", "test"))
	for i := 0; i < 10; i++ {
		_, err = batch.Exec(
			"1234",
			"2345",
			"3.1415",
			"test",
			"test2",
			"[4, 5, 6]",
			"[1, 2, 3]",
			"2021-01-01",
			"2021-01-01 00:00:00",
		)
	}
	err = tx.Commit()
}

Querying Row/s

Querying a single row can be achieved using the QueryRow method. This returns a *sql.Row, on which Scan can be invoked with pointers to variables into which the columns should be marshaled.

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/datafuselabs/databend-go"
)

func main() {
	// create table data (col1 uint8, col2 string);
	// insert into data values(1,'col2');
	conn, err := sql.Open("databend", "http://databend:databend@localhost:8000/default?sslmode=disable")
	if err != nil {
		fmt.Println(err)
	}
	row := conn.QueryRow("SELECT * FROM data")
	var (
		col1 uint8
		col2 string
	)
	if err := row.Scan(&col1, &col2); err != nil {
		fmt.Println(err)
	}
	fmt.Println(col2)
}

Iterating multiple rows requires the Query method. This returns a *sql.Rows struct on which Next can be invoked to iterate through the rows. QueryContext equivalent allows passing of a context.

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/datafuselabs/databend-go"
)

func main() {
	// create table data (col1 uint8, col2 string);
	// insert into data values(1,'col2');
	conn, err := sql.Open("databend", "http://databend:databend@localhost:8000/default?sslmode=disable")
	if err != nil {
		fmt.Println(err)
	}
	row, err := conn.Query("SELECT * FROM data")
	var (
		col1 uint8
		col2 string
	)
	for row.Next() {
		if err := row.Scan(&col1, &col2); err != nil {
			fmt.Println(err)
		}
		fmt.Println(col2)
	}
}

Type Mapping

The following table outlines the mapping between Databend types and Go types:

Databend Type	Go Type
TINYINT	int8
SMALLINT	int16
INT	int32
BIGINT	int64
TINYINT UNSIGNED	uint8
SMALLINT UNSIGNED	uint16
INT UNSIGNED	uint32
BIGINT UNSIGNED	uint64
Float32	float32
Float64	float64
Bitmap	string
Decimal	decimal.Decimal
String	string
Date	time.Time
DateTime	time.Time
Array(T)	string
Tuple(T1, T2, ...)	string
Variant	string

Compatibility

• If databend version >= v0.9.0 or later, you need to use databend-go version >= v0.3.0.
• If databend version < 1.2.371, you need to use databend-go version < 0.5.7 and if your databend version >= 1.2.371, you need the databend-go version >=0.5.7. Because from 1.2.371, databend support transaction and databend-go has some brake changes.