Search⌘ K
AI Features
Home
Courses
Go for DevOps

Accessing SQL Databases

Explore how to use Go to access SQL databases, focusing on Postgres integration. Learn to connect, query, write data, and manage transactions using both standard and Postgres-specific libraries. Understand handling null values, optimizing queries with prepared statements, and designing storage abstractions to enable flexible and testable DevOps applications.

We'll cover the following...

DevOps engineers commonly have a need to access data stored in database systems. SQL is a standard for communicating with database systems that a DevOps engineer will encounter in their day-to-day lives.

Go provides a standard library for interacting with SQL-based systems called database/sql. The interfaces provided by that package, with the addition of a database driver, allow a user to work with several different SQL databases. In this lesson, we’ll look at how we can access a Postgres database to perform basic SQL operations using Go.

Connecting to a Postgres database

To connect to a Postgres database will require using a database driver for Postgres. The currently recommended third-party package is github.com/jackc/pgx. This package implements a SQL driver for database/sql and provides its own methods/types for Postgres-specific features.

The choice to use database/sql or Postgres-specific types will depend on whether we need to ensure compatibility between different databases. Using database/sql allows us to write functions that work on any SQL database, while using Postgres-specific features removes compatibility and makes migration to another database more difficult. We'll discuss how to perform our examples using both methods. Here is how to connect using a standard SQL package without extra Postgres features:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   SALARY         REAL
);

INSERT INTO COMPANY (id, name, age, salary) VALUES (100, 'John', 25, 50000);

SELECT * FROM COMPANY;
Connecting to a sample database

Here, we open a connection to Postgres using the pgx driver that will be registered when we import the following package:

Go (1.18.2)
_ "github.com/jackc/pgx/v4/stdlib"

This is an anonymous import, meaning we are not using stdlib directly. This is done when we want a side effect, such as when registering a driver with the database/sql package.

The Open() call doesn't test our connection. We'll see conn.PingContext() to test that we'll be able to make calls to the database. When we want to use pgx-specific types for Postgres, the setup is slightly different, starting with a different package import:

Go (1.18.2)
"github.com/jackc/pgx/v4/pgxpool"

To create that connection, use the following code:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   SALARY         REAL
);

INSERT INTO COMPANY (id, name, age, salary) VALUES (100, 'John', 25, 50000);

SELECT * FROM COMPANY;
Creating connection with pgxpool

This uses a connection pool to connect to the database for performance. We'll notice that we don't have a PingContext() call, as the native connection tests the connection as part of Connect(). Now that we know how to connect to Postgres, let's look at how we can make queries.

Querying a Postgres database

Let's consider making a call to our SQL database to fetch some information about a user that is held in a table. 

CREATE TABLE users (
    id serial PRIMARY KEY,
    displayname VARCHAR (355) UNIQUE NOT NULL,
    usr VARCHAR (50) NOT NULL
);


INSERT INTO users (id, displayname, usr) VALUES (1, 'John', 'ruh');
Fetching user information

This example does the following:

  • Lines 18–22: Creates UserRec to store SQL data for a user.

  • Line 26: Creates a query statement called query. ...