Go Drivers

• YugabyteDB PGX Driver
• PGX Driver
• PQ Driver

YugabyteDB PGX driver is a Go driver for YSQL based on PGX driver.

Although the upstream PGX driver works with YugabyteDB, the Yugabyte driver enhances YugabyteDB by eliminating the need for external load balancers. The driver has the following features:

• It is cluster-aware, which eliminates the need for an external load balancer.

  The driver package includes a class that uses one initial contact point for the YugabyteDB cluster as a means of discovering all the nodes and, if required, refreshing the list of live endpoints with every new connection attempt. The refresh is triggered if stale information (older than 5 minutes) is discovered.

• It is topology-aware, which is essential for geographically-distributed applications.

  The driver uses servers that are part of a set of geo-locations specified by topology keys.

Load balancing

The YugabyteDB PGX driver has the following load balancing features:

Uniform load balancing

In this mode, the driver makes the best effort to uniformly distribute the connections to each YugabyteDB server. For example, if a client application creates 100 connections to a YugabyteDB cluster consisting of 10 servers, then the driver creates 10 connections to each server. If the number of connections are not exactly divisible by the number of servers, then a few may have 1 less or 1 more connection than the others. This is the client view of the load, so the servers may not be well balanced if other client applications are not using the Yugabyte JDBC driver.

Topology-aware load balancing

Because YugabyteDB clusters can have servers in different regions and availability zones, the YugabyteDB PGX driver is topology-aware, and can be configured to create connections only on servers that are in specific regions and zones. This is beneficial for client applications that need to connect to the geographically nearest regions and availability zone for lower latency; the driver tries to uniformly load only those servers that belong to the specified regions and zone.

Quick start

Learn how to establish a connection to YugabyteDB database and begin CRUD operations using the steps from Build a Go application.

This page provides details for getting started with YugabyteDB PGX driver for connecting to YugabyteDB YSQL API.

Import the driver package

You can import the YugabyteDB PGX driver package by adding the following import statement in your Go code.

import (
  "github.com/yugabyte/pgx/v4"
)

Optionally, you can choose to import the pgxpool package instead. Refer to Using pgxpool API to learn more.

Fundamentals

Learn how to perform common tasks required for Go application development using the YugabyteDB PGX driver.

Use the driver

After setting up the driver, implement the Go client application that uses the driver to connect to your YugabyteDB cluster and run a query on the sample data.

The YugabyteDB PGX driver allows Go programmers to connect to YugabyteDB to execute DMLs and DDLs using the PGX APIs. It also supports the standard database/sql package.

Use the pgx.Connect() method or pgxpool.Connect() method to create a connection object for the YugabyteDB database. This can be used to perform DDLs and DMLs against the database.

The following table describes the connection parameters required to connect to the YugabyteDB database:

Load balancing connection properties

The following connection properties need to be added to enable load balancing:

• load-balance - enable cluster-aware load balancing by setting this property to true; disabled by default.
• topology-keys - provide comma-separated geo-location values to enable topology-aware load balancing. Geo-locations can be provided as cloud.region.zone.

To use the driver, do the following:

• Pass new connection properties for load balancing in the connection URL or properties pool.

  To enable uniform load balancing across all servers, you set the load-balance property to true in the URL, as per the following example:

  baseUrl := fmt.Sprintf("postgres://%s:%s@%s:%d/%s",
                    user, password, host, port, dbname)
  url := fmt.Sprintf("%s?load_balance=true", baseUrl)
  conn, err := pgx.Connect(context.Background(), url)
  

  To specify topology keys, you set the topology-keys property to comma separated values, as per the following example:

  baseUrl := fmt.Sprintf("postgres://%s:%s@%s:%d/%s",
                    user, password, host, port, dbname)
  url = fmt.Sprintf("%s?load_balance=true&topology_keys=cloud1.datacenter1.rack1", baseUrl)
  conn, err := pgx.Connect(context.Background(), url)
  

Create table

Tables can be created in YugabyteDB by passing the CREATE TABLE DDL statement to the Exec() function on the conninstance.

CREATE TABLE employee (id int PRIMARY KEY, name varchar, age int, language varchar)

var createStmt = 'CREATE TABLE employee (id int PRIMARY KEY,
                  name varchar, age int, language varchar)';
_, err = conn.Exec(context.Background(), createStmt)
if err != nil {
  fmt.Fprintf(os.Stderr, "Exec for create table failed: %v\n", err)
}

The conn.Exec() function also returns an error object which, if not nil, needs to be handled in your code.

Read more on designing Database schemas and tables.

Read and write data

Insert data

To write data into YugabyteDB, execute the INSERT statement using the same conn.Exec() function.

INSERT INTO employee(id, name, age, language) VALUES (1, 'John', 35, 'Go')

var insertStmt string = "INSERT INTO employee(id, name, age, language)" +
                        " VALUES (1, 'John', 35, 'Go')";
_, err = conn.Exec(context.Background(), insertStmt)
if err != nil {
  fmt.Fprintf(os.Stderr, "Exec for create table failed: %v\n", err)
}

By default, the YugabyteDB PGX driver automatically prepares and caches statements.

Query data

To query data from YugabyteDB tables, execute the SELECT statement using the function conn.Query(). Query results are returned in pgx.Rows which can be iterated using pgx.Rows.next() method. Then read the data using pgx.rows.Scan().

The SELECT DML statement:

SELECT * from employee;

Code snippet:

var name string
var age int
var language string

rows, err := conn.Query(context.Background(), "SELECT name, age, language FROM employee WHERE id = 1")
if err != nil {
  log.Fatal(err)
}
defer rows.Close()

fmt.Printf("Query for id=1 returned: ");
for rows.Next() {
  err := rows.Scan(&name, &age, &language)
  if err != nil {
    log.Fatal(err)
  }
  fmt.Printf("Row[%s, %d, %s]\n", name, age, language)
}

err = rows.Err()
if err != nil {
  log.Fatal(err)
}

Using pgxpool API

The YugabyteDB PGX driver also provides pool APIs via the pgxpool package. You can import it as follows:

import (
  "github.com/yugabyte/pgx/tree/master/pgxpool"
)

Establishing a connection

The primary way of establishing a connection is with pgxpool.Connect().

pool, err := pgxpool.Connect(context.Background(), os.Getenv("DATABASE_URL"))

You can also provide configuration for the pool as follows:

config, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
    // ...
}
config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
    // do something with every new connection
}

pool, err := pgxpool.ConnectConfig(context.Background(), config)

You can either Acquire a connection from pool and execute queries on it, or use Query API to directly execute SQLs on the pool.

conn, err := pool.Acquire(context.Background())
if err != nil {
	fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
	os.Exit(1)
}
defer conn.Release()

var createStmt = `CREATE TABLE employee (id int PRIMARY KEY,
                  name varchar, age int, language varchar)`
_, err = conn.Exec(context.Background(), createStmt)

// ...

rows, err := pool.Query(context.Background(), "SELECT name, age, language FROM employee WHERE id = 1")

For more details, see the pgxpool package documentation.

Configure SSL/TLS

To build a Go application that communicates securely over SSL with YugabyteDB database, you need the root certificate (ca.crt) of the YugabyteDB Cluster. To generate these certificates and install them while launching the cluster, follow the instructions in Create server certificates.

Because a YugabyteDB Managed cluster is always configured with SSL/TLS, you don't have to generate any certificate but only set the client-side SSL configuration. To fetch your root certificate, refer to CA certificate. For a YugabyteDB Managed cluster, or a YugabyteDB cluster with SSL/TLS enabled, set the SSL-related environment variables as below at the client side.

$ export PGSSLMODE=verify-ca
$ export PGSSLROOTCERT=~/root.crt  # Here, the CA certificate file is downloaded as `root.crt` under home directory. Modify your path accordingly.

SSL modes

Transaction and isolation levels

YugabyteDB supports transactions for inserting and querying data from the tables. YugabyteDB supports different isolation levels for maintaining strong consistency for concurrent data access.

The PGX driver provides the conn.Begin() function to start a transaction. The conn.BeginEx() function can create a transaction with a specified isolation level.

tx, err := conn.Begin()
if err != nil {
    return err
}
defer tx.Rollback()

_, err = tx.Exec("insert into employee(id, name, age, language) values (1, 'John', 35, 'Go')")
if err != nil {
    return err
}

err = tx.Commit()
if err != nil {
    return err
}