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

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
• Project Setup
• Database Setup
• Project Structure
• Environment Configuration
• Database Connection
• User Model
• Request Handlers
• Main Application
• Testing the API
• Error Handling Best Practices
• Security Considerations
• Conclusion

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:

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

Install the required dependencies:

go get github.com/lib/pq
go get github.com/gorilla/mux
go get github.com/joho/godotenv
Code language: JavaScript (javascript)

Database Setup

Create a new PostgreSQL database and table for our API:

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
);
Code language: PHP (php)

Project Structure

Create the following directory structure:

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

Environment Configuration

Create a .env file:

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

Database Connection

Create database/connection.go:

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
}
Code language: JavaScript (javascript)

User Model

Create models/user.go:

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"`
}
Code language: JavaScript (javascript)

Request Handlers

Create handlers/users.go:

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)
}
Code language: JavaScript (javascript)

Main Application

Create main.go:

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))
}
Code language: JavaScript (javascript)

Testing the API

Run your API:

go run main.go
Code language: CSS (css)

Test endpoints using curl:

# Create a user
curl -X POST http://localhost:8080/users \
-H "Content-Type: application/json" \
-d '{"name":"John Doe","email":"[email protected]"}'\n
# Get all users
curl http://localhost:8080/users

# Get a specific user
curl http://localhost:8080/users/1
Code language: PHP (php)

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.