Working with Databases in Go: PostgreSQL and MongoDB in Practice

Working with Databases in Go: PostgreSQL and MongoDB

Go (Golang) is a modern compiled language that is ideal for building high-load backend services. One of the key tasks of any backend is working with databases. In this article, we will detail how to connect and perform CRUD operations with two of the most popular DBMS: relational PostgreSQL and document-oriented MongoDB. You will get ready-made code examples, best practices, and tips for query optimization.

1. Environment Setup and Driver Installation

Before starting, make sure you have Go version 1.19 or higher installed. To work with databases, we will need two main packages:

• database/sql — Go's standard package for working with SQL databases (common interface);
• lib/pq — driver for PostgreSQL;
• go.mongodb.org/mongo-driver — official MongoDB driver.

Installing drivers via go get:

go get github.com/lib/pqgo get go.mongodb.org/mongo-driver/mongo

Create a new module for your project:

mkdir go-db-examplecd go-db-examplego mod init go-db-example

2. Working with PostgreSQL in Go

PostgreSQL is a powerful object-relational DBMS. In Go, working with it is done through the database/sql interface.

2.1 Connecting to the Database

Let's create a main.go file and write a connection to a local Postgres database:

package main

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

    _ "github.com/lib/pq")

func main() {    // Connection string: user password dbname sslmode    connStr := "user=postgres password=mysecretpassword dbname=testdb sslmode=disable"    db, err := sql.Open("postgres", connStr)    if err != nil {        log.Fatal("Connection error:", err)    }    defer db.Close()

    // Check the connection    err = db.Ping()    if err != nil {        log.Fatal("Failed to connect to database:", err)    }    fmt.Println("Successfully connected to PostgreSQL!")}

2.2 Creating a Table and Inserting Data

After connecting, let's create a users table and insert several records:

// Creating a tablecreateTable := `CREATE TABLE IF NOT EXISTS users (    id SERIAL PRIMARY KEY,    name VARCHAR(100) NOT NULL,    email VARCHAR(100) UNIQUE NOT NULL,    age INT);`

_, err = db.Exec(createTable)if err != nil {    log.Fatal("Error creating table:", err)}

// Inserting datainsert := `INSERT INTO users (name, email, age) VALUES ($1, $2, $3)`_, err = db.Exec(insert, "Anna Petrova", "anna@example.com", 28)if err != nil {    log.Fatal("Insert error:", err)}fmt.Println("Data added")

2.3 Reading Data (SELECT)

Use QueryRow for a single record and Query for multiple:

// Getting a single recordvar id intvar name, email stringvar age sql.NullInt64

row := db.QueryRow("SELECT id, name, email, age FROM users WHERE email = $1", "anna@example.com")err = row.Scan(&id, &name, &email, &age)if err != nil {    log.Fatal("Scan error:", err)}fmt.Printf("ID: %d, Name: %s, Email: %s, Age: %v", id, name, email, age)

// Getting all recordsrows, err := db.Query("SELECT id, name, email FROM users ORDER BY id")if err != nil {    log.Fatal("Query error:", err)}defer rows.Close()

for rows.Next() {    err := rows.Scan(&id, &name, &email)    if err != nil {        log.Fatal("Error reading row:", err)    }    fmt.Printf("User: %d | %s | %s", id, name, email)}

Important: Always handle sql.NullInt64 for nullable fields to avoid panics on NULL values.

3. Working with MongoDB