Connecting to CrateDB from Go

This article describes how to connect to CrateDB from Go using the pgx PostgreSQL driver.

Prerequisites

To connect to CrateDB, we first install the main pgx package as well as its connection pooling package:

$ go get github.com/jackc/pgx/v4
$ go get github.com/jackc/pgx/v4/pgxpool

Connecting to CrateDB

Below you will find a snippet utilizing pgx to create a pool of 10 connections to CrateDB. A simple test query gets run agains a system table to ensure data can be retrieved:

package main

import (
	"context"
	"fmt"
	"os"
	"github.com/jackc/pgx/v4"
	"github.com/jackc/pgx/v4/pgxpool"
)

func main() {
	ctx := context.Background()
	// a regular PostgreSQL-style connection URL with a pool of 10 connections
	config, err := pgxpool.ParseConfig("postgresql://crate@localhost:5432/doc?pool_max_conns=10")
	dbpool, err := pgxpool.ConnectConfig(ctx, config)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer dbpool.Close()

	var id string
	var hostname string
	err = dbpool.QueryRow(ctx, "SELECT id, hostname FROM sys.nodes LIMIT 1").Scan(&id, &hostname)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(id, hostname)
}

Ingesting into CrateDB

Several strategies are available to achieve high performance for ingesting data into CrateDB. pgx supports prepared statements for bulk ingests, which the following example makes use of:

package main

import (
	"context"
	"fmt"
	"os"
	"github.com/jackc/pgx/v4"
	"github.com/jackc/pgx/v4/pgxpool"
)

func main() {
	ctx := context.Background()
	// a regular PostgreSQL-style connection URL with a pool of 10 connections
	config, err := pgxpool.ParseConfig("postgresql://crate@localhost:5432/doc?pool_max_conns=10")
	dbpool, err := pgxpool.ConnectConfig(ctx, config)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer dbpool.Close()

	// simple test table, created via CREATE TABLE doc.my_test_table(numeric_value INTEGER);
	stmt := `INSERT INTO doc.my_test_table (numeric_value) VALUES ($1);`

	// create a new batch
	batch := &pgx.Batch{}

	// fill the batch with 200 sample values
	for i := 0; i < 200; i++ {
		batch.Queue(stmt, i)
	}

	// send the batch
	br := dbpool.SendBatch(ctx, batch)

	// execute the batch
	_, err = br.Exec()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to execute batch %v\n", err)
		os.Exit(1)
	}

	// close the batch
	err = br.Close()
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to closer batch %v\n", err)
		os.Exit(1)
	}
}
2 Likes