Exasol Go SQL Driver

This repository contains a Go library for connection to the Exasol database.

This library uses the standard Golang SQL driver interface for easy use.

Prerequisites

To use the Exasol Go Driver you need an Exasol database in the latest 7.1 or 8 version. Older versions might work but are not supported.

Usage

Create Connection

With Exasol Config

We recommend using a provided builder to build a connection string:

package main

import (
    "database/sql"
    "github.com/exasol/exasol-driver-go"
)

func main() {
    database, err := sql.Open("exasol", exasol.NewConfig("<username>", "<password>").
                                              Port(<port>).
                                              Host("<host>").
                                              String())
    // ...
}

If you want to login via OpenID tokens use exasol.NewConfigWithRefreshToken("token") or exasol.NewConfigWithAccessToken("token"). See the documentation about how to configure OpenID authentication in Exasol. OpenID authentication is only supported with Exasol 7.1.x and later.

With Exasol DSN

There is also a way to build the connection string without the builder:

package main

import (
    "database/sql"
    _ "github.com/exasol/exasol-driver-go"
)

func main() {
    database, err := sql.Open("exasol",
            "exa:<host>:<port>;user=<username>;password=<password>")
    // ...
}

Execute Statement

result, err := exasol.Exec(`
    INSERT INTO CUSTOMERS
    (NAME, CITY)
    VALUES('Bob', 'Berlin');`)

Query Statement

rows, err := exasol.Query("SELECT * FROM CUSTOMERS")

Use Prepared Statements

preparedStatement, err := exasol.Prepare(`
    INSERT INTO CUSTOMERS
    (NAME, CITY)
    VALUES(?, ?)`)
result, err = preparedStatement.Exec("Bob", "Berlin")

preparedStatement, err := exasol.Prepare("SELECT * FROM CUSTOMERS WHERE NAME = ?")
rows, err := preparedStatement.Query("Bob")

Transaction Commit and Rollback

To control a transaction state manually, you would need to disable autocommit (enabled by default):

database, err := sql.Open("exasol",
                "exa:<host>:<port>;user=<username>;password=<password>;autocommit=0")
// or
database, err := sql.Open("exasol", exasol.NewConfig("<username>", "<password>")
                                          .Port(<port>)
                                          .Host("<host>")
                                          .Autocommit(false)
                                          .String())

After that you can begin a transaction:

transaction, err := exasol.Begin()
result, err := transaction.Exec( ... )
result2, err := transaction.Exec( ... )

To commit a transaction use Commit():

err = transaction.Commit()

To rollback a transaction use Rollback():

err = transaction.Rollback()

Import local CSV files

Use the sql driver to load data into your Exasol Database.

!! Limitation !!

Only import of CSV files is supported at the moment.

result, err := exasol.Exec(`
IMPORT INTO CUSTOMERS FROM LOCAL CSV FILE './testData/data.csv' FILE './testData/data_part2.csv' 
 COLUMN SEPARATOR = ';' 
 ENCODING = 'UTF-8' 
 ROW SEPARATOR = 'LF'
`)

Connection String

The golang Driver uses the following URL structure for Exasol:

exa:<host>[,<host_1>]...[,<host_n>]:<port>[;<prop_1>=<value_1>]...[;<prop_n>=<value_n>]

Host-Range-Syntax is supported (e.g. exasol1..3). A range like exasol1..exasol3 is not valid.

Supported Driver Properties

Property	Value	Default	Description
`autocommit`	0=off, 1=on	`1`	Switch autocommit on or off.
`clientname`	string	`Go client`	Tell the server the application name.
`clientversion`	string		Tell the server the version of the application.
`compression`	0=off, 1=on	`0`	Switch data compression on or off.
`encryption`	0=off, 1=on	`1`	Switch automatic encryption on or off.
`validateservercertificate`	0=off, 1=on	`1`	TLS certificate verification. Disable it if you want to use a self-signed or invalid certificate (server side).
`certificatefingerprint`	string		Expected fingerprint of the server's TLS certificate. See below for details.
`fetchsize`	numeric, >0	`128*1024`	Amount of data in kB which should be obtained by Exasol during a fetch. The application can run out of memory if the value is too high.
`password`	string		Exasol password.
`resultsetmaxrows`	numeric		Set the max amount of rows in the result set.
`schema`	string		Exasol schema name.
`user`	string		Exasol username.

Configuring TLS

We recommend to always enable TLS encryption. This is on by default, but you can enable it explicitly via driver property encryption=1 or config.Encryption(true).

There are two driver properties that control how TLS certificates are verified: validateservercertificate and certificatefingerprint. You have these three options depending on your setup:

With validateservercertificate=1 (or config.ValidateServerCertificate(true)) the driver will return an error for any TLS errors (e.g. unknown certificate or invalid hostname).

Use this when the database has a CA-signed certificate. This is the default behavior.
With validateservercertificate=1;certificatefingerprint=<fingerprint> (or config.ValidateServerCertificate(true).CertificateFingerprint("<fingerprint>")) you can specify the fingerprint (i.e. the SHA256 checksum) of the server's certificate.

This is useful when the database has a self-signed certificate with invalid hostname but you still want to verify connecting to the corrrect host.

Note: You can find the fingerprint by first specifiying an invalid fingerprint and connecting to the database. The error will contain the actual fingerprint.
With validateservercertificate=0 (or config.ValidateServerCertificate(false)) the driver will ignore any TLS certificate errors.

Use this if the server uses a self-signed certificate and you don't know the fingerprint. This is not recommended.

Information for Users

Information for Developers

Developer guide

sabitor/exasol-driver-go