Database/SQL API

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/language-clients/go/database-sql-api.md)

Full code examples for the standard API can be found [here](https://github.com/ClickHouse/clickhouse-go/tree/main/examples/std).

For connection configuration, see [Configuration](/docs/integrations/language-clients/go/configuration).
For supported data types and Go type mappings, see [Data Types](/docs/integrations/language-clients/go/data-types).

The `database/sql` or "standard" API allows you to use the client in scenarios where application code should be agnostic of the underlying databases by conforming to a standard interface. This comes at some expense - additional layers of abstraction and indirection and primitives which aren't necessarily aligned with ClickHouse. These costs are, however, typically acceptable in scenarios where tooling needs to connect to multiple databases.

Additionally, this client supports using HTTP as the transport layer - data will still be encoded in the native format for optimal performance.

## Connecting[​](#connecting "Direct link to Connecting")

Connection can be achieved either via a DSN string with the format `clickhouse://<host>:<port>?<query_option>=<value>` and `Open` method or via the `clickhouse.OpenDB` method. The latter isn't part of the `database/sql` specification but returns a `sql.DB` instance. This method provides functionality such as profiling, for which there are no obvious means of exposing through the `database/sql` specification.

```
func Connect() error {
        env, err := GetStdTestEnvironment()
        if err != nil {
                return err
        }
        conn := clickhouse.OpenDB(&clickhouse.Options{
                Addr: []string{fmt.Sprintf("%s:%d", env.Host, env.Port)},
                Auth: clickhouse.Auth{
                        Database: env.Database,
                        Username: env.Username,
                        Password: env.Password,
                },
        })
        return conn.Ping()
}

func ConnectDSN() error {
        env, err := GetStdTestEnvironment()
        if err != nil {
                return err
        }
        conn, err := sql.Open("clickhouse", fmt.Sprintf("clickhouse://%s:%d?username=%s&password=%s", env.Host, env.Port, env.Username, env.Password))
        if err != nil {
                return err
        }
        return conn.Ping()
}
```

[Full Example](https://github.com/ClickHouse/clickhouse-go/blob/main/examples/std/connect.go)

**For all subsequent examples, unless explicitly shown, we assume the use of the ClickHouse `conn` variable has been created and is available.**

### Connection settings[​](#connection-settings "Direct link to Connection settings")

Most configuration options are shared with the ClickHouse API. See [Configuration](/docs/integrations/language-clients/go/configuration) for shared settings. The following SQL-specific DSN parameters are available:

- `hosts` - comma-separated list of single address hosts for load-balancing and failover - see [Connecting to Multiple Nodes](/docs/integrations/language-clients/go/configuration#connecting-to-multiple-nodes).
- `username/password` - auth credentials - see [Authentication](/docs/integrations/language-clients/go/configuration#authentication)
- `database` - select the current default database
- `dial_timeout` - a duration string is a possibly signed sequence of decimal numbers, each with optional fraction and a unit suffix such as `300ms`, `1s`. Valid time units are `ms`, `s`, `m`.
- `connection_open_strategy` - `random/in_order` (default `random`) - see [Connecting to Multiple Nodes](/docs/integrations/language-clients/go/configuration#connecting-to-multiple-nodes)
  - `round_robin` - choose a round-robin server from the set
  - `in_order` - first live server is chosen in specified order
- `debug` - enable debug output (boolean value)
- `compress` - specify the compression algorithm - `none` (default), `zstd`, `lz4`, `gzip`, `deflate`, `br`. If set to `true`, `lz4` will be used. Only `lz4` and `zstd` are supported for native communication.
- `compress_level` - Level of compression (default is `0`). See Compression. This is algorithm specific:
  - `gzip` - `-2` (Best Speed) to `9` (Best Compression)
  - `deflate` - `-2` (Best Speed) to `9` (Best Compression)
  - `br` - `0` (Best Speed) to `11` (Best Compression)
  - `zstd`, `lz4` - ignored
- `secure` - establish secure SSL connection (default is `false`)
- `skip_verify` - skip certificate verification (default is `false`)
- `block_buffer_size` - allows you to control the block buffer size. See [`BlockBufferSize`](/docs/integrations/language-clients/go/configuration#connection-settings). (default is `2`)

```
func ConnectSettings() error {
        env, err := GetStdTestEnvironment()
        if err != nil {
                return err
        }
        conn, err := sql.Open("clickhouse", fmt.Sprintf("clickhouse://127.0.0.1:9001,127.0.0.1:9002,%s:%d/%s?username=%s&password=%s&dial_timeout=10s&connection_open_strategy=round_robin&debug=true&compress=lz4", env.Host, env.Port, env.Database, env.Username, env.Password))
        if err != nil {
                return err
        }
        return conn.Ping()
}
```

[Full Example](https://github.com/ClickHouse/clickhouse-go/blob/main/examples/std/connect_settings.go)

### Connecting over HTTP[​](#connecting-over-http "Direct link to Connecting over HTTP")

By default, connections are established over the native protocol. For users needing HTTP, this can be enabled by either modifying the DSN to include the HTTP protocol or by specifying the Protocol in the connection options.

```
func ConnectHTTP() error {
        env, err := GetStdTestEnvironment()
        if err != nil {
                return err
        }
        conn := clickhouse.OpenDB(&clickhouse.Options{
                Addr: []string{fmt.Sprintf("%s:%d", env.Host, env.HttpPort)},
                Auth: clickhouse.Auth{
                        Database: env.Database,
                        Username: env.Username,
                        Password: env.Password,
                },
                Protocol: clickhouse.HTTP,
        })
        return conn.Ping()
}

func ConnectDSNHTTP() error {
        env, err := GetStdTestEnvironment()
        if err != nil {
                return err
        }
        conn, err := sql.Open("clickhouse", fmt.Sprintf("http://%s:%d?username=%s&password=%s", env.Host, env.HttpPort, env.Username, env.Password))
        if err != nil {
                return err
        }
        return conn.Ping()
}
```

[Full Example](https://github.com/ClickHouse/clickhouse-go/blob/main/examples/std/connect_http.go)

### Sessions[​](#sessions "Direct link to Sessions")

HTTP only

Sessions are only needed when using the HTTP transport. Native TCP connections have a built-in session automatically.

When using HTTP, pass a `session_id` as a setting to enable session-bound features such as temporary tables.

```
conn := clickhouse.OpenDB(&clickhouse.Options{
    Addr: []string{fmt.Sprintf("%s:%d", env.Host, env.HttpPort)},
    Auth: clickhouse.Auth{
        Database: env.Database,
        Username: env.Username,
        Password: env.Password,
    },
    Protocol: clickhouse.HTTP,
    Settings: clickhouse.Settings{
        "session_id": uuid.NewString(),
    },
})
if _, err := conn.Exec(`DROP TABLE IF EXISTS example`); err != nil {
    return err
}
_, err = conn.Exec(`
    CREATE TEMPORARY TABLE IF NOT EXISTS example (
            Col1 UInt8
    )
`)
if err != nil {
    return err
}
scope, err := conn.Begin()
if err != nil {
    return err
}
batch, err := scope.Prepare("INSERT INTO example")
if err != nil {
    return err
}
for i := 0; i < 10; i++ {
    _, err := batch.Exec(
        uint8(i),
    )
    if err != nil {
        return err
    }
}
rows, err := conn.Query("SELECT * FROM example")
if err != nil {
    return err
}
defer rows.Close()

var (
    col1 uint8
)
for rows.Next() {
    if err := rows.Scan(&col1); err != nil {
        return err
    }
    fmt.Printf("row: col1=%d\n", col1)
}