Working with Databases in Go: PostgreSQL and MongoDB in Practice

Online Python Trainer for Beginners

Learn Python easily without overwhelming theory. Solve practical tasks with automatic checking, get hints in Russian, and write code directly in your browser — no installation required.

Start Course

Introduction: Why Go and Databases Are the Perfect Pair

The Go programming language (Golang) has gained popularity due to its high performance, built-in concurrency support, and ease of deployment. However, the true power of Go is revealed when working with databases. Whether it's relational PostgreSQL or document-oriented MongoDB, Go provides elegant tools for efficient data interaction.

In this article, we will cover the practical aspects of connecting, performing CRUD operations, and optimizing queries for PostgreSQL and MongoDB in Go. You will learn how to use standard packages and popular drivers to write reliable and fast code.

1. Working with PostgreSQL in Go

To work with PostgreSQL in Go, the standard database/sql package is used in conjunction with the pgx or lib/pq driver. pgx is recommended due to its better performance and support for advanced PostgreSQL features.

1.1. Installation and Connection

Install the driver via go modules:

go get github.com/jackc/pgx/v5

Example of connecting to a database:

package main

import (    "context"    "fmt"    "github.com/jackc/pgx/v5/pgxpool"    "log")

func main() {    dsn := "postgres://user:password@localhost:5432/mydb?sslmode=disable"    pool, err := pgxpool.New(context.Background(), dsn)    if err != nil {        log.Fatal("Unable to connect to database:", err)    }    defer pool.Close()

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

Tip: Use a connection pool (pgxpool) for production environments — this reduces the overhead of establishing new connections.

1.2. CRUD Operations

Let's look at basic operations using the users table as an example:

// CREATEfunc createUser(pool *pgxpool.Pool, name string, email string) (int, error) {    var id int    err := pool.QueryRow(context.Background(),        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",        name, email).Scan(&id)    if err != nil {        return 0, err    }    return id, nil}

// READfunc getUser(pool *pgxpool.Pool, id int) (string, string, error) {    var name, email string    err := pool.QueryRow(context.Background(),        "SELECT name, email FROM users WHERE id = $1", id).Scan(&name, &email)    if err != nil {        return "", "", err    }    return name, email, nil}

// UPDATEfunc updateUserEmail(pool *pgxpool.Pool, id int, newEmail string) error {    _, err := pool.Exec(context.Background(),        "UPDATE users SET email = $1 WHERE id = $2", newEmail, id)    return err}

// DELETEfunc deleteUser(pool *pgxpool.Pool, id int) error {    _, err := pool.Exec(context.Background(),        "DELETE FROM users WHERE id = $1", id)    return err}

Note the use of parameterized queries with $1, $2 — this protects against SQL injection.

1.3. Transactions

PostgreSQL supports transactions, which are critical for data integrity:

func transferMoney(pool *pgxpool.Pool, fromID, toID int, amount float64) error {    tx, err := pool.Begin(context.Background())    if err != nil {        return err    }    defer tx.Rollback(context.Background()) // rollback on error

    _, err = tx.Exec(context.Background(),        "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID)    if err != nil {        return err    }

    _, err = tx.Exec(context.Background(),        "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID)    if err != nil {        return err    }

    return tx.Commit(context.Background())}