Working with Databases in Go: PostgreSQL and MongoDB

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 a Perfect Pair

Go (Golang) is a compiled, statically typed language created for high performance and ease of development. One of Go's key advantages is its rich standard library and ecosystem of drivers for working with databases. In this article, we will break down how to connect and perform operations with two of the most popular database management systems (DBMS): the relational PostgreSQL and the document-oriented MongoDB.

We will cover driver installation, writing CRUD operations (Create, Read, Update, Delete), and best practices for each type of database in the context of Go applications.

1. Working with PostgreSQL in Go

PostgreSQL is a powerful open-source object-relational DBMS. There are several drivers for working with it in Go, but the most popular are lib/pq and pgx. pgx is more modern and performant, so we will use it.

1.1. Installing the Driver

Install the pgx package using go get:

go get github.com/jackc/pgx/v5

1.2. Connecting to the Database

Let's create a main.go file and establish a connection to PostgreSQL. Note the use of context (context.Background()) — this is a standard pattern in Go for managing the lifecycle of requests.

package main

import (    "context"    "fmt"    "log"

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

func main() {    // Connection string: postgres://username:password@host:port/dbname    connString := "postgres://user:password@localhost:5432/mydb"        // Create a connection pool (recommended for production)    pool, err := pgxpool.New(context.Background(), connString)    if err != nil {        log.Fatal("Failed to create connection pool:", err)    }    defer pool.Close()        // Check the connection    err = pool.Ping(context.Background())    if err != nil {        log.Fatal("Failed to connect to the database:", err)    }    fmt.Println("Successfully connected to PostgreSQL!")}

1.3. Executing SQL Queries (CRUD)

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

Creating a Table (CREATE)

// Executing a DDL query_, err = pool.Exec(context.Background(), `    CREATE TABLE IF NOT EXISTS users (        id SERIAL PRIMARY KEY,        name VARCHAR(100) NOT NULL,        email VARCHAR(100) UNIQUE NOT NULL,        age INT    )`)if err != nil {    log.Fatal("Error creating table:", err)}

Inserting Data (INSERT)

// Insert with returning IDvar userID interr = pool.QueryRow(context.Background(),    "INSERT INTO users (name, email, age) VALUES ($1, $2, $3) RETURNING id",    "Alexey", "alex@example.com", 30).Scan(&userID)if err != nil {    log.Fatal("Error inserting:", err)}fmt.Printf("Added user with ID: %d", userID)

Reading Data (SELECT)

// Getting a single usertype User struct {    ID    int    Name  string    Email string    Age   int}

var user Usererr = pool.QueryRow(context.Background(),    "SELECT id, name, email, age FROM users WHERE id = $1", 1).    Scan(&user.ID, &user.Name, &user.Email, &user.Age)if err != nil {    log.Fatal("User not found:", err)}fmt.Printf("Found: %+v", user)

Updating and Deleting (UPDATE/DELETE)

// Update_, err = pool.Exec(context.Background(),    "UPDATE users SET age = $1 WHERE id = $2", 31, 1)

// Delete_, err = pool.Exec(context.Background(),    "DELETE FROM users WHERE id = $1", 1)