Connecting to PostgreSQL with Go using PGX

PostgreSQL has emerged as a reliable, feature-rich database used extensively across various industries. On the other hand, Go (Golang), known for its simplicity, efficiency, and strong standard libraries, has become increasingly preferred for modern backend development. In this article we will discuss PGX - a powerful, lightweight, and easy-to-use driver for accessing PostgreSQL databases in Go. With native support for prepared statements, streaming results, transactions, and parameterized queries, PGX enables developers to achieve maximum productivity and performance during PostgreSQL interaction within Go projects. We will dive into utilizing PGX for PostgreSQL operations in Go by taking a simple example of a bookstore database.

In this blog, we'll cover the basics of PGX. We'll learn about connecting to databases, doing basic operations like adding and retrieving data. In the next blog from this series, we'll go deeper and talk about more advanced stuff in PGX like PGXPOOL.

Application Introduction

Pre-requisites

This article assumes that, the following are satisfied.

1. Golang setup: Golang (v 1.21.6 or above) has been set up on your machine. To check the presence of GO, run the following command in your terminal or command prompt.
   

go --version

2. PostgreSQL setup: PostgreSQL (v 16 or above) has been set up on your machine.

3. Familiarity with GO and PostgreSQL

Before proceeding, create a "bookstore" database and a "books" table with the given schema. Execute the below commands to create the table.

CREATE TABLE books(
  id SERIAL PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  author VARCHAR(100) NOT NULL,
  quantity INTEGER NOT NULL,
);

The application we are going to build is a simple book store. Now let's start writing some code.

PostgreSQL Connection Setup

Create a main.go file and fill it up with the following code.

Importing required libraries

package main
import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5"
)

Book: A struct representing the structure of a book

type Book struct {
	ID       int
	Title    string
	Author   string
	Quantity int
}

BookStore: An interface defining the CRUD operations for interacting with books in the database

type BookStore interface {
	InsertBookIntoDatabase(Book) error
	GetBookDetailsByID(int) (*Book, error)
	GetAllBookDetails(int) (*[]Book, error)
	UpdateBookDetails(int, Book) error
	DeleteBookFromDatabase(int) error
}

NewPostgres: A function to establish a connection to the PostgreSQL database using PGX

This function takes in the DSN details and returns a connection object to the database.

func NewPostgres(username, password, host, port, dbName string) (*pgx.Conn, error) {
	connStr := fmt.Sprintf("postgres://%s:%s@%s:%s/%s", username, password, host, port, dbName)
	conn, err := pgx.Connect(context.Background(), connString)
	if err!=nil{
		return nil,err
	}
	return conn, nil
}

From the main function, call the NewPostgres to establish a new connection.

func main() {
	conn, err := NewPostgres("postgres", "postgres", "localhost", "5432", "bookstore")
	if err != nil {
		log.Fatalf("Error : %v", err)
	}
	defer conn.Close(context.Background())
}

Furthermore, let's perform CRUD operations using PGX.

CRUD Operations

Inserting Data: InsertBookIntoDatabase

This function is responsible for inserting a new book record into the database. It constructs an SQL query with named parameters for the book details (title, author, quantity) and executes it using PGX's .Exec function.

// InsertBookIntoDatabase inserts a new book record into the database.
func InsertBookIntoDatabase(conn *pgx.Conn, bookDetails Book) error {
    // Define the SQL query to insert a new book record.
    query := `
        INSERT INTO books (title, author, quantity) VALUES (@title, @author, @quantity)
    `
    // Define the named arguments for the query.
    args := pgx.NamedArgs{
        "title":    bookDetails.Title,
        "author":   bookDetails.Author,
        "quantity": bookDetails.Quantity,
    }
    // Execute the query with named arguments to insert the book details into the database.
    _, err := conn.Exec(context.Background(), query, args)
    if err != nil {
        log.Println("Error Inserting Book Details")
        return err
    }
    return nil
}

Querying Data

Query Single row: GetBookDetailsByID

This function retrieves the details of a book from the database based on its ID. It constructs a SQL query with a named parameter for the book ID and executes it using PGX's .QueryRow function. The retrieved data is then scanned into a Book struct.

// GetBookDetailsByID retrieves the details of a book by its ID from the database.
func GetBookDetailsByID(conn *pgx.Conn, bookID int) (*Book, error) {
    // Define the SQL query to select a book by its ID.
    query := `
        SELECT * FROM books WHERE id = @bookID
    `
    // Define the named argument for the book ID.
    args := pgx.NamedArgs{
        "bookID": bookID,
    }
    // Execute the query with named arguments to fetch the book details from the database.
    row := conn.QueryRow(context.Background(), query, args)

    var book Book
    // Scan the retrieved row into the book struct.
    err := row.Scan(&book.ID, &book.Title, &book.Author, &book.Quantity)
    if err != nil {
        log.Printf("Error Fetching Book Details")
        return nil, err
    }
    return &book, nil
}

Query multiple rows: GetAllBookDetails

This function retrieves details of all books from the database. It constructs an SQL query to select all books and executes it using PGX's .Query function. The retrieved data is then scanned into a slice of Book structs.

// GetAllBookDetails retrieves details of all books from the database.
func GetAllBookDetails(conn *pgx.Conn) (*[]Book, error) {
    // Define the SQL query to select all books.
    query := `
        SELECT * FROM BOOKS
    `
    // Execute the query to fetch all book details from the database.
    rows, err := conn.Query(context.Background(), query)
    if err != nil {
        log.Printf("Error Querying the Table")
        return nil, err
    }
    defer rows.Close()

    var books []Book
    // Iterate over the retrieved rows and scan each row into a Book struct.
    for rows.Next() {
        var book Book
        err := rows.Scan(&book.ID, &book.Title, &book.Author, &book.Quantity)
        if err != nil {
            log.Printf("Error Fetching Book Details")
            return &books, err
        }
        books = append(books, book)
    }
    return &books, nil
}

However in PGX v5 we can make use of pgx.CollectRows instead of iterating over the rows and manually scanning every record into the struct using for rows.Next().

books, err := pgx.CollectRows(rows, pgx.RowToStructByName[Book])

Updating Data: UpdateBookDetails

This function updates the details of a book in the database. It constructs an SQL query to update the book's details and executes it using PGX's .Exec function with named arguments for the new book details.

// UpdateBookDetails updates the details of a book in the database.
func UpdateBookDetails(conn *pgx.Conn, bookID int, bookDetails Book) error {
    // Define the SQL query to update a book's details by its ID.
    query := `
        UPDATE books
        SET title = @title, author = @author, quantity = @quantity
        WHERE id = @id
    `
    // Define the named arguments for the query.
    args := pgx.NamedArgs{
        "id":       bookDetails.ID,
        "title":    bookDetails.Title,
        "author":   bookDetails.Author,
        "quantity": bookDetails.Quantity,
    }
    // Execute the query with named arguments to update the book details in the database.
    _, err := conn.Exec(context.Background(), query, args)
    if err != nil {
        log.Println("Error Updating Book Details")
        return err
    }
    return nil
}

Deleting Data: DeleteBookFromDatabase

This function deletes a book record from the database based on its ID. It constructs an SQL query to delete the book and executes it using PGX's .Exec function with a named argument for the book ID.

// DeleteBookFromDatabase deletes a book record from the database by its ID.
func DeleteBookFromDatabase(conn *pgx.Conn, bookID int) error {
    // Define the SQL query to delete a book by its ID.
    query := `
        DELETE FROM books WHERE id = @id
    `
    // Define the named argument for the book ID.
    args := pgx.NamedArgs{
        "id": bookID,
    }
    // Execute the query with named arguments to delete the book from the database.
    _, err := conn.Exec(context.Background(), query, args)
    if err != nil {
        log.Println("Error Deleting Book")
        return err
    }
    return nil
}

Conclusion

We've covered the basics of PGX v5, from establishing a PostgreSQL connection to performing CRUD operations. Explore the PGX v5 package for more useful functions. This article provides a foundation for efficient and maintainable solutions using PostgreSQL and Go. In the next article of this series, we will deep dive into using PGXPOOL and explore advanced topics within the PGX family.

Seeking specialized PostgreSQL support or Database Migrations assistance ? Get experts advice for effective data management, query tuning, database migrations and database optimization. Click here for personalized expertise to elevate your database performance and meet your unique business needs.

I hope you have enjoyed reading this article and now have a good knowledge of PGX.

Subscribe to our Newsletters and Stay tuned for more interesting topics.