Go Drivers

• YugabyteDB PGX Driver
• PGX Driver
• PQ Driver

The PQ driver is a popular driver for PostgreSQL which can used for connecting to YugabyteDB YSQL as well.

This driver allows Go programmers to connect to YugabyteDB database to execute DMLs and DDLs using the standard database/sql package.

Fundamentals of PQ 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.

Let us break down the quick start example and understand how to perform the common tasks required for Go App development using the PQ driver.

Import the driver package

Import the PQ driver package by adding the following import statement in your Go code.

import (
  _ "github.com/lib/pq"
)

Connect to YugabyteDB database

Go Apps can connect to the YugabyteDB database using the sql.Open() function. All the functions or structs required for working with YugabyteDB database are part of sql package.

Use the sql.Open() function for getting connection object for the YugabyteDB database which can be used for performing DDLs and DMLs against the database.

The connection details can be specified either as string params or via an url in the format given below:

postgresql://username:password@hostname:port/database

Code snippet for connecting to YugabyteDB:

psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s",
                        host, port, user, password, dbname)
// Other connection configs are read from the standard environment variables:
// PGSSLMODE, PGSSLROOTCERT, and so on.
db, err := sql.Open("postgres", psqlInfo)
defer db.Close()
if err != nil {
    log.Fatal(err)
}

Create table

Execute an SQL statement like the DDL CREATE TABLE ... using the Exec() function on the db 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)`;
if _, err := db.Exec(createStmt); err != nil {
    log.Fatal(err)
}

The db.Exec() function also returns an error object which, if not nil, needs to 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 db.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')";
if _, err := db.Exec(insertStmt); err != nil {
    log.Fatal(err)
}

Query data

To query data from YugabyteDB tables, execute the SELECT statement using the function Query() on db instance.

Query results are returned as rows which can be iterated using rows.next() method. Use rows.Scan() for reading the data.

The SELECT DML statement:

SELECT * from employee;

Code snippet:

var name string
var age int
var language string
rows, err := db.Query(`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)
}

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.

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 PQ driver provides db.Begin() function to start a transaction. Another function conn.BeginEx() can create a transaction with a specified isolation level.`

tx, err := db.Begin()
if err != nil {
  log.Fatal(err)
}

...

_, err = stmt.Exec()
if err != nil {
  log.Fatal(err)
}

err = stmt.Close()
if err != nil {
  log.Fatal(err)
}

err = txn.Commit()
if err != nil {
  log.Fatal(err)
}