Working with Databases in Go: PostgreSQL and MongoDB for Beginners

Introduction: Why Go and Databases Are a Perfect Match

Go (Golang) is a modern compiled language created by Google. Its main advantages are high performance, simplicity, and built-in concurrency support. When it comes to working with databases, Go offers powerful standard libraries and popular third-party drivers for any DBMS.

In this article, we will cover how to connect and perform basic operations with two of the most popular databases: relational PostgreSQL and document-oriented MongoDB. The material is intended for beginner developers who are already familiar with the basics of Go syntax.

1. Environment Setup and Driver Installation

Before writing code, make sure you have Go installed (version 1.21+). To work with databases, we will need drivers. Install them using the commands:

go get github.com/lib/pq          // for PostgreSQL\ngo get go.mongodb.org/mongo-driver // for MongoDB

Also, ensure that the corresponding database servers are running on your machine (locally or via Docker).

1.1. Project Structure

Create a file main.go. All logic will be concentrated in it for simplicity. In real projects, you should separate code into packages.

2. Working with PostgreSQL in Go

PostgreSQL is a powerful relational DBMS. To work with it in Go, we use the database/sql package and the lib/pq driver.

2.1. Connecting to the Database

Let's create a connection function. The connection string (DSN) contains the host, port, username, and database name.

package main\n\nimport (\n    "database/sql"\n    "fmt"\n    "log"\n\n    _ "github.com/lib/pq"\n)\n\nfunc main() {\n    connStr := "host=localhost port=5432 user=postgres password=secret dbname=testdb sslmode=disable"\n    db, err := sql.Open("postgres", connStr)\n    if err != nil {\n        log.Fatal("Connection error:", err)\n    }\n    defer db.Close()\n\n    // Check the connection\n    err = db.Ping()\n    if err != nil {\n        log.Fatal("Failed to establish connection:", err)\n    }\n    fmt.Println("Successfully connected to PostgreSQL!")\n}

Important: sql.Open does not create an actual connection, only a pool. The actual connection occurs on the first request or when calling Ping().

2.2. Creating a Table and Inserting Data

Now let's create a users table and add a few records.

// Creating a table\ncreateTable := `\nCREATE TABLE IF NOT EXISTS users (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(100) NOT NULL,\n    email VARCHAR(100) UNIQUE NOT NULL,\n    age INT\n);`\n_, err = db.Exec(createTable)\nif err != nil {\n    log.Fatal("Error creating table:", err)\n}\n\n// Inserting data\ninsert := `INSERT INTO users (name, email, age) VALUES ($1, $2, $3)`\n_, err = db.Exec(insert, "Ivan Petrov", "ivan@example.com", 30)\nif err != nil {\n    log.Fatal("Insert error:", err)\n}\nfmt.Println("Data added")

Note the use of placeholders $1, $2 — this is protection against SQL injection.

2.3. Reading and Updating Data

Let's execute a query to select data and update the user's age.

// Reading data\nrows, err := db.Query("SELECT id, name, email, age FROM users")\nif err != nil {\n    log.Fatal(err)\n}\ndefer rows.Close()\n\nfor rows.Next() {\n    var id int\n    var name, email string\n    var age int\n    err := rows.Scan(&id, &name, &email, &age)\n    if err != nil {\n        log.Fatal(err)\n    }\n    fmt.Printf("ID: %d, Name: %s, Email: %s, Age: %d\\n", id, name, email, age)\n}\n\n// Update\n_, err = db.Exec("UPDATE users SET age = $1 WHERE name = $2", 31, "Ivan Petrov")\nif err != nil {\n    log.Fatal(err)\n}\nfmt.Println("Age updated")

3. Working with MongoDB