Go and PostgreSQL: A Complete Integration Guide

Why Go and PostgreSQL Are the Perfect Pair?

In the world of backend development, Go (or Golang) and PostgreSQL have proven themselves as a powerful tandem for building high-load and reliable systems. Go gives us incredible performance, simplicity, and excellent concurrency support, while PostgreSQL is an industrial-grade database with rich functionality, reliability, and extensibility. By combining them, you get a stack capable of handling startup loads and enterprise project requirements.

In this article, we won't just skim through the syntax. We will create a comprehensive practical guide: from installing the driver to optimizing the connection pool and working with transactions. You will learn how to avoid common mistakes and write efficient Go code for working with PostgreSQL. Let's go!

Getting Started: Choosing a Driver and Connecting

The first thing to do is install the driver. In the Go world, there are two main candidates:

\n
• lib/pq — classic, simple, but no longer actively maintained.
\n
• pgx — modern, fast, actively developed, supports more PostgreSQL features (e.g., COPY, LISTEN/NOTIFY, arrays). I recommend this one.
\n

Installing pgx:

go get github.com/jackc/pgx/v5

Now let's write the code to connect to the database. Create a file main.go:

package main\n\nimport (\n    "context"\n    "fmt"\n    "log"\n\n    "github.com/jackc/pgx/v5/pgxpool"\n)\n\nfunc main() {\n    // Connection URL: user, password, host, port, database name\n    dsn := "postgres://myuser:mypassword@localhost:5432/mydb?sslmode=disable"\n\n    // Create a connection pool\n    pool, err := pgxpool.New(context.Background(), dsn)\n    if err != nil {\n        log.Fatalf("Failed to create pool: %v", err)\n    }\n    defer pool.Close()\n\n    // Check the connection\n    err = pool.Ping(context.Background())\n    if err != nil {\n        log.Fatalf("Failed to connect: %v", err)\n    }\n\n    fmt.Println("Successfully connected to PostgreSQL!")\n}\n

Note: we use pgxpool, not a simple connection. Connection pool is a must-have for production. It allows reusing connections, avoiding the overhead of creating and closing them.

CRUD Operations: Create, Read, Update, and Delete

Now let's implement classic operations with the users table. Suppose we have a struct:

type User struct {\n    ID   int\n    Name string\n    Age  int\n}\n

Creating a Table (Create)

It's better to run migrations with separate tools, but for the example I'll show how to execute raw SQL:

func createTable(ctx context.Context, pool *pgxpool.Pool) error {\n    query := `\n        CREATE TABLE IF NOT EXISTS users (\n            id SERIAL PRIMARY KEY,\n            name TEXT NOT NULL,\n            age INT NOT NULL\n        );\n    `\n    _, err := pool.Exec(ctx, query)\n    return err\n}\n

Inserting a Record (Insert)

Use QueryRow to return the generated ID:

func createUser(ctx context.Context, pool *pgxpool.Pool, user *User) error {\n    query := `INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id`\n    err := pool.QueryRow(ctx, query, user.Name, user.Age).Scan(&user.ID)\n    return err\n}\n

Reading a Record (Select)

func getUserByID(ctx context.Context, pool *pgxpool.Pool, id int) (*User, error) {\n    query := `SELECT id, name, age FROM users WHERE id = $1`\n    user := &User{}\n    err := pool.QueryRow(ctx, query, id).Scan(&user.ID, &user.Name, &user.Age)\n    if err != nil {\n        return nil, err\n    }\n    return user, nil\n}\n

Updating and Deleting (Update