Building a RESTful API with Go and PostgreSQL: A Complete Guide

building-restful-api

Building robust APIs is a crucial skill for modern developers. While there are many frameworks and languages to choose from, Go’s simplicity and performance make it an excellent choice for API development. In this guide, we’ll create a complete RESTful API using Go and PostgreSQL.

This guide expands on our previous tutorial “Building RESTful APIs with Go” by adding database integration and more advanced features.

Table of Contents

Prerequisites

  • Go installed on your system
  • Basic understanding of Go syntax
  • PostgreSQL installed and running
  • Basic understanding of REST principles

Project Setup

First, create a new directory for your project and initialize it:

Terminalbash
mkdir go-rest-api
cd go-rest-api
go mod init go-rest-api

Install the required dependencies:

Terminalbash
go get github.com/lib/pq
go get github.com/gorilla/mux
go get github.com/joho/godotenv

Database Setup

Create a new PostgreSQL database and table for our API:

Snippettext
CREATE DATABASE goapi;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Project Structure

Create the following directory structure:

Snippettext
go-rest-api/
├── main.go
├── .env
├── models/
│   └── user.go
├── handlers/
│   └── users.go
└── database/
    └── connection.go

Environment Configuration

Create a .env file:

Snippettext
DB_HOST=localhost
DB_PORT=5432
DB_USER=postgres
DB_PASSWORD=yourpassword
DB_NAME=goapi
API_PORT=8080

Database Connection

Create database/connection.go:

Snippettext
package database

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

    _ "github.com/lib/pq"
)

func Connect() *sql.DB {
    connStr := fmt.Sprintf(
        "host=%s port=%s user=%s password=%s dbname=%s sslmode=disable",
        os.Getenv("DB_HOST"),
        os.Getenv("DB_PORT"),
        os.Getenv("DB_USER"),
        os.Getenv("DB_PASSWORD"),
        os.Getenv("DB_NAME"),
    )

    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    return db
}

User Model

Create models/user.go:

Snippettext
package models

import "time"

type User struct {
    ID        int       `json:"id"`
    Name      string    `json:"name"`
    Email     string    `json:"email"`
    CreatedAt time.Time `json:"created_at"`
}

Request Handlers

Create handlers/users.go:

Snippettext
package handlers

import (
    "database/sql"
    "encoding/json"
    "net/http"
    "strconv"

    "github.com/gorilla/mux"
    "go-rest-api/models"
)

type UserHandler struct {
    DB *sql.DB
}

func (h *UserHandler) GetUsers(w http.ResponseWriter, r *http.Request) {
    rows, err := h.DB.Query("SELECT id, name, email, created_at FROM users")
    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }
    defer rows.Close()

    users := []models.User{}
    for rows.Next() {
        var user models.User
        err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)
        if err != nil {
            http.Error(w, err.Error(), http.StatusInternalServerError)
            return
        }
        users = append(users, user)
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(users)
}

func (h *UserHandler) CreateUser(w http.ResponseWriter, r *http.Request) {
    var user models.User
    err := json.NewDecoder(r.Body).Decode(&user)
    if err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    err = h.DB.QueryRow(
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at",
        user.Name,
        user.Email,
    ).Scan(&user.ID, &user.CreatedAt)

    if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(http.StatusCreated)
    json.NewEncoder(w).Encode(user)
}

func (h *UserHandler) GetUser(w http.ResponseWriter, r *http.Request) {
    params := mux.Vars(r)
    id, err := strconv.Atoi(params["id"])
    if err != nil {
        http.Error(w, "Invalid user ID", http.StatusBadRequest)
        return
    }

    var user models.User
    err = h.DB.QueryRow(
        "SELECT id, name, email, created_at FROM users WHERE id = $1",
        id,
    ).Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)

    if err == sql.ErrNoRows {
        http.Error(w, "User not found", http.StatusNotFound)
        return
    } else if err != nil {
        http.Error(w, err.Error(), http.StatusInternalServerError)
        return
    }

    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(user)
}

Main Application

Create main.go:

Snippettext
package main

import (
    "log"
    "net/http"
    "os"

    "github.com/gorilla/mux"
    "github.com/joho/godotenv"
    "go-rest-api/database"
    "go-rest-api/handlers"
)

func main() {
    err := godotenv.Load()
    if err != nil {
        log.Fatal("Error loading .env file")
    }

    db := database.Connect()
    defer db.Close()

    userHandler := &handlers.UserHandler{DB: db}

    r := mux.NewRouter()
    r.HandleFunc("/users", userHandler.GetUsers).Methods("GET")
    r.HandleFunc("/users", userHandler.CreateUser).Methods("POST")
    r.HandleFunc("/users/{id}", userHandler.GetUser).Methods("GET")

    port := os.Getenv("API_PORT")
    log.Printf("Server starting on port %s", port)
    log.Fatal(http.ListenAndServe(":"+ port, r))
}

Testing the API

Run your API:

Terminalbash
go run main.go

Test endpoints using curl:

Snippettext
# Create a user
curl -X POST http://localhost:8080/users \
-H "Content-Type: application/json" \
-d '{"name":"John Doe","email":"john@example.com"}'\n
# Get all users
curl http://localhost:8080/users

# Get a specific user
curl http://localhost:8080/users/1

Error Handling Best Practices

When building production APIs, proper error handling is crucial. Here are some best practices we’ve implemented:

  1. Using appropriate HTTP status codes
  2. Returning meaningful error messages
  3. Proper validation of input data
  4. Database error handling

Security Considerations

To make your API production-ready, consider implementing:

  1. Input validation
  2. Rate limiting
  3. Authentication/Authorization
  4. CORS policies
  5. Prepared statements for SQL queries

Conclusion

You’ve now built a complete RESTful API with Go and PostgreSQL. This foundation can be extended with additional features like authentication, validation, and more complex database operations. The combination of Go’s performance and PostgreSQL’s reliability makes this stack excellent for production applications.

To further enhance your Go API development skills, consider exploring our other tutorials on Go interfaces, error handling, and concurrency patterns. These concepts will help you build more robust and maintainable APIs.

whoami

Stefan founded Ceos3c in 2016 and has spent a decade teaching cybersecurity, Linux, and web development through hands-on tutorials. He builds and ships real products while sharing his journey: ProofSwap.me, XPMetric.com, and EasyEarningsTracker.com.

More about me