Go for DevOps/

...

/

Accessing SQL Databases

Accessing SQL Databases

Examine how to connect, access, and edit SQL and Postgres databases in Go.

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:

Press + to interact
_ "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:

Press + to interact
"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 ...