Guide to Using PostgreSQL with Go: Installation and Examples

Connecting to a PostgreSQL database in Go is a common requirement for many applications. Go provides robust support for interacting with databases, including PostgreSQL, through libraries such as database/sql in combination with a PostgreSQL driver like pgx or lib/pq. Below is a guide for setting up and using PostgreSQL in a Go application.

Setting Up

1. Install PostgreSQL Driver: You can use pgx (recommended for its performance and features) or lib/pq. Here, we’ll use pgx.

go get github.com/jackc/pgx/v4/pgxpool

2. Import Required Packages:

package main

import (
    "context"
    "fmt"
    "log"

    "github.com/jackc/pgx/v4/pgxpool"
)

Connecting to PostgreSQL

Here's a basic example of how to connect to a PostgreSQL database using pgx:

package main

import (
    "context"
    "fmt"
    "log"
    "os"

    "github.com/jackc/pgx/v4/pgxpool"
)

func main() {
    // Database connection string (adjust as needed)
    connString := "postgres://username:password@localhost:5432/mydb"

    // Create a connection pool
    dbpool, err := pgxpool.Connect(context.Background(), connString)
    if err != nil {
        log.Fatalf("Unable to connect to database: %v\n", err)
    }
    defer dbpool.Close()

    // Check if the connection works
    var greeting string
    err = dbpool.QueryRow(context.Background(), "SELECT 'Hello, world!'").Scan(&greeting)
    if err != nil {
        log.Fatalf("QueryRow failed: %v\n", err)
    }

    fmt.Println(greeting)
}

Executing Queries

Creating Tables

func createTable(dbpool *pgxpool.Pool) {
    createTableSQL := `
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        age INT NOT NULL
    );`
    _, err := dbpool.Exec(context.Background(), createTableSQL)
    if err != nil {
        log.Fatalf("Failed to create table: %v\n", err)
    }
}

Inserting Data

func insertUser(dbpool *pgxpool.Pool, name string, age int) {
    insertSQL := `INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id`
    var id int
    err := dbpool.QueryRow(context.Background(), insertSQL, name, age).Scan(&id)
    if err != nil {
        log.Fatalf("Failed to insert user: %v\n", err)
    }
    fmt.Printf("Inserted user with ID %d\n", id)
}

Querying Data

func getUsers(dbpool *pgxpool.Pool) {
    rows, err := dbpool.Query(context.Background(), `SELECT id, name, age FROM users`)
    if err != nil {
        log.Fatalf("Failed to execute query: %v\n", err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        var age int
        err = rows.Scan(&id, &name, &age)
        if err != nil {
            log.Fatalf("Failed to scan row: %v\n", err)
        }
        fmt.Printf("ID: %d, Name: %s, Age: %d\n", id, name, age)
    }
    if rows.Err() != nil {
        log.Fatalf("Row iteration error: %v\n", rows.Err())
    }
}

Example Full Program

Combining all the above components into a complete program:

package main

import (
    "context"
    "fmt"
    "log"

    "github.com/jackc/pgx/v4/pgxpool"
)

func main() {
    // Database connection string (adjust as needed)
    connString := "postgres://username:password@localhost:5432/mydb"

    // Create a connection pool
    dbpool, err := pgxpool.Connect(context.Background(), connString)
    if err != nil {
        log.Fatalf("Unable to connect to database: %v\n", err)
    }
    defer dbpool.Close()

    // Create table
    createTable(dbpool)

    // Insert users
    insertUser(dbpool, "Alice", 30)
    insertUser(dbpool, "Bob", 25)

    // Get and print users
    getUsers(dbpool)
}

func createTable(dbpool *pgxpool.Pool) {
    createTableSQL := `
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        age INT NOT NULL
    );`
    _, err := dbpool.Exec(context.Background(), createTableSQL)
    if err != nil {
        log.Fatalf("Failed to create table: %v\n", err)
    }
}

func insertUser(dbpool *pgxpool.Pool, name string, age int) {
    insertSQL := `INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id`
    var id int
    err := dbpool.QueryRow(context.Background(), insertSQL, name, age).Scan(&id)
    if err != nil {
        log.Fatalf("Failed to insert user: %v\n", err)
    }
    fmt.Printf("Inserted user with ID %d\n", id)
}

func getUsers(dbpool *pgxpool.Pool) {
    rows, err := dbpool.Query(context.Background(), `SELECT id, name, age FROM users`)
    if err != nil {
        log.Fatalf("Failed to execute query: %v\n", err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        var age int
        err = rows.Scan(&id, &name, &age)
        if err != nil {
            log.Fatalf("Failed to scan row: %v\n", err)
        }
        fmt.Printf("ID: %d, Name: %s, Age: %d\n", id, name, age)
    }
    if rows.Err() != nil {
        log.Fatalf("Row iteration error: %v\n", rows.Err())
    }
}

Conclusion

This guide provides a basic setup for interacting with a PostgreSQL database in Go using the pgx package. For more advanced use cases, consider leveraging additional features of pgx, such as connection pooling, prepared statements, and transaction management. The examples above should provide a solid foundation for building more complex database interactions in your Go applications.