Introduction

Go (Golang) provides excellent support for working with PostgreSQL through the database/sql package and the pq driver. This tutorial covers everything you need to connect to PostgreSQL, perform CRUD operations, and handle transactions safely.

Installing the pq Driver

First, install the PostgreSQL driver for Go:

go get github.com/lib/pq

The pq driver implements Go's database/sql interface, so you'll primarily work with the standard library's database package.

Connecting to PostgreSQL

Here's how to establish a connection to your PostgreSQL database:

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

func main() {
    connStr := "host=localhost port=5432 user=myuser password=mypassword dbname=mydb sslmode=disable"

    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal("Failed to open database:", err)
    }
    defer db.Close()

    // Verify the connection
    err = db.Ping()
    if err != nil {
        log.Fatal("Failed to connect:", err)
    }

    fmt.Println("Connected to PostgreSQL!")
}

Connection String Formats:

You can also use a URL format:

connStr := "postgres://user:password@localhost:5432/dbname?sslmode=disable"

Basic CRUD Operations

Creating a Table

func createTable(db *sql.DB) error {
    query := `
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )`

    _, err := db.Exec(query)
    return err
}

Insert (Create)

func insertUser(db *sql.DB, name, email string) (int, error) {
    var id int
    query := `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id`

    err := db.QueryRow(query, name, email).Scan(&id)
    if err != nil {
        return 0, err
    }
    return id, nil
}

Note: PostgreSQL uses $1, $2, $3 for parameter placeholders, not ? like MySQL.

Select (Read)

Single row:

func getUserByID(db *sql.DB, id int) (string, string, error) {
    var name, email string
    query := `SELECT name, email FROM users WHERE id = $1`

    err := db.QueryRow(query, id).Scan(&name, &email)
    if err == sql.ErrNoRows {
        return "", "", fmt.Errorf("user %d not found", id)
    }
    return name, email, err
}

Multiple rows:

func getAllUsers(db *sql.DB) error {
    rows, err := db.Query("SELECT id, name, email FROM users")
    if err != nil {
        return err
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name, email string
        if err := rows.Scan(&id, &name, &email); err != nil {
            return err
        }
        fmt.Printf("ID: %d, Name: %s, Email: %s\n", id, name, email)
    }
    return rows.Err()
}

Update

func updateUserEmail(db *sql.DB, id int, newEmail string) error {
    query := `UPDATE users SET email = $1 WHERE id = $2`

    result, err := db.Exec(query, newEmail, id)
    if err != nil {
        return err
    }

    rowsAffected, _ := result.RowsAffected()
    if rowsAffected == 0 {
        return fmt.Errorf("user %d not found", id)
    }
    return nil
}

Delete

func deleteUser(db *sql.DB, id int) error {
    query := `DELETE FROM users WHERE id = $1`

    result, err := db.Exec(query, id)
    if err != nil {
        return err
    }

    rowsAffected, _ := result.RowsAffected()
    if rowsAffected == 0 {
        return fmt.Errorf("user %d not found", id)
    }
    return nil
}

Prepared Statements

Prepared statements improve performance for repeated queries and protect against SQL injection:

func insertMultipleUsers(db *sql.DB, users []User) error {
    stmt, err := db.Prepare("INSERT INTO users (name, email) VALUES ($1, $2)")
    if err != nil {
        return err
    }
    defer stmt.Close()

    for _, user := range users {
        _, err := stmt.Exec(user.Name, user.Email)
        if err != nil {
            return err
        }
    }
    return nil
}

Transactions

Use transactions when you need multiple operations to succeed or fail together:

func transferCredits(db *sql.DB, fromID, toID, amount int) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    // Rollback if we return early due to error
    defer tx.Rollback()

    // Deduct from sender
    _, err = tx.Exec("UPDATE accounts SET credits = credits - $1 WHERE id = $2", amount, fromID)
    if err != nil {
        return err
    }

    // Add to receiver
    _, err = tx.Exec("UPDATE accounts SET credits = credits + $1 WHERE id = $2", amount, toID)
    if err != nil {
        return err
    }

    // Commit the transaction
    return tx.Commit()
}

Error Handling

Always handle database errors properly. Common patterns:

import "github.com/lib/pq"

func insertUser(db *sql.DB, name, email string) error {
    _, err := db.Exec("INSERT INTO users (name, email) VALUES ($1, $2)", name, email)
    if err != nil {
        // Check for unique constraint violation
        if pqErr, ok := err.(*pq.Error); ok {
            if pqErr.Code == "23505" { // unique_violation
                return fmt.Errorf("email already exists")
            }
        }
        return err
    }
    return nil
}

Connection Pool Settings

Configure the connection pool for production:

db.SetMaxOpenConns(25)                 // Maximum open connections
db.SetMaxIdleConns(5)                  // Maximum idle connections
db.SetConnMaxLifetime(5 * time.Minute) // Connection max lifetime

Conclusion

You now have a solid foundation for working with PostgreSQL in Go. The database/sql package combined with the pq driver provides a clean, efficient interface for database operations. Remember to always use parameterized queries to prevent SQL injection, handle errors appropriately, and use transactions for operations that must be atomic.

For more advanced use cases, consider ORMs like GORM or query builders like sqlx that provide additional features while still supporting PostgreSQL.