Go Drivers

• PGX Driver
• PQ Driver

The PGX driver is one of the most popular and actively maintained drivers for PostgreSQL.

This 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.

CRUD Operations with PGX driver

Learn how to establish a connection to YugabyteDB database and begin simple CRUD operations using the steps in the Build an application page under the Quick start section.

The following sections break down the quick start example to demonstrate how to perform common tasks required for Go application development using the PGX driver.

Import the driver package

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

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

Connect to YugabyteDB database

Go applications can connect to YugabyteDB using the pgx.Connect() function. The pgx package includes all the common functions or structs required for working with YugabyteDB.

Use the pgx.Connect() method to create the connection object, for performing DDLs and DMLs against the database.

PGX Connection url is in the format given below:

postgresql://username:[email protected]:port/database

Code snippet for connecting to YugabyteDB:

url := fmt.Sprintf("postgres://%s:%[email protected]%s:%d/%s",
                    user, password, host, port, dbname)
conn, err := pgx.Connect(context.Background(), url)

Create table

Execute an SQL statement like the DDL CREATE TABLE ... using the Exec() function on the conn instance.

The CREATE DDL statement:

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

Code snippet:

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.

The INSERT DML statement:

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

Code snippet:

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)
}

The pgx driver automatically prepares and caches statements by default, so that the developer does not have to.

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 PGX driver also provides pool APIs via its pgxpool package. One can import it as below.

import (
  "github.com/jackc/pgx/v4/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"))

One can also provide configuration for the pool as

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)

For more details, see pgxpool package doc

Configure SSL/TLS

In order 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.

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 conn.Begin() function to start a transaction. Another function conn.BeginEx() 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
}