Connecting Your Go API to a PostgreSQL Database

Alright, so we’ve got our Go API rolling, but it’s about time we gave it some long-term memory. This week, we’re connecting our API to PostgreSQL, so you can store all that precious data without losing it the second you shut down your app. Trust me, your users will thank you.

Why PostgreSQL?

PostgreSQL or “Postgres” for short, is the real deal when it comes to databases. Here’s why it’s the most popular DB:

1. Feature-Packed: Whether you want to store plain old text, JSON, or even complex geographical data, Postgres has got you covered. It’s also got full ACID compliance (read: it keeps your data consistent and safe) and enough fancy querying options to make any data nerd smile.

2. Open-Source and Free: That’s right—Postgres is totally free and open-source. Plus, it has an active community that’s constantly improving it, so you’ll never have to worry about it becoming outdated.

3. Scales Like a Pro: Whether you’re building a tiny app or a massive, data-chomping enterprise service, Postgres can handle it. It’s designed to scale, with parallel query execution and optimization magic to keep things running smoothly.

4. Built Like a Tank: With decades of development under its belt, Postgres is rock-solid. It gets regular updates, has a ton of security features, and is used in production by giants like Apple and Netflix.

Got all that? Cool, let’s hook it up to our Go API and start working some database magic!

Step 0: Setting Up PostgreSQL

If you don’t already have PostgreSQL installed, grab it here. Then let’s fire it up:

1. Connect to PostgreSQL:

   psql -U postgres

1. Create a database:

   CREATE DATABASE bookdb;

1. Set up a table for our books:

   \c bookdb;
   CREATE TABLE books (
       id SERIAL PRIMARY KEY,
       title VARCHAR(255) NOT NULL,
       author VARCHAR(255) NOT NULL
   );

Now you’ve got a fresh database ready to go. Time to get Go talking to it!

Step 1: Connect Go to PostgreSQL

We’re using the pgx library for this one. It’s fast, it’s lightweight, and it gets the job done.

go get github.com/jackc/pgx/v5

Open up your main.go file and add this code to set up a connection to the database:

var db *pgxpool.Pool

func connectDB() *pgxpool.Pool {
    url := "postgres://postgres:yourpassword@localhost:5432/bookdb"
    config, err := pgxpool.ParseConfig(url)
    if err != nil {
        log.Fatalf("Unable to parse DB config: %v\n", err)
    }

    dbpool, err := pgxpool.NewWithConfig(context.Background(), config)
    if err != nil {
        log.Fatalf("Unable to connect to database: %v\n", err)
    }

    return dbpool
}

Replace yourpassword with your PostgreSQL password. This function connects to our bookdb database and returns a connection pool, which basically means our app will have a bunch of reusable connections ready to go. Efficiency, baby! 😎

Step 2: Update the Main Function

Let’s make sure our database connection fires up when our server does:

func main() {
    db = connectDB()
    defer db.Close()

    // Initialize router and define routes here (as before)
}

Step 3: CRUD Operations – Bringing in the Data

Alright, let’s add some functions to fetch, create, and manage books in our database.

Fetch All Books

func getBooks(w http.ResponseWriter, r *http.Request) {
    rows, err := db.Query(context.Background(), "SELECT id, title, author FROM books")
    if err != nil {
        http.Error(w, "Database error", http.StatusInternalServerError)
        return
    }
    defer rows.Close()

    var books []Book
    for rows.Next() {
        var book Book
        err := rows.Scan(&book.ID, &book.Title, &book.Author)
        if err != nil {
            http.Error(w, "Error scanning row", http.StatusInternalServerError)
            return
        }
        books = append(books, book)
    }

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

Add a New Book

func createBook(w http.ResponseWriter, r *http.Request) {
    var book Book
    err := json.NewDecoder(r.Body).Decode(&book)
    if err != nil {
        http.Error(w, "Bad request", http.StatusBadRequest)
        return
    }

    _, err = db.Exec(context.Background(), "INSERT INTO books (title, author) VALUES ($1, $2)", book.Title, book.Author)
    if err != nil {
        http.Error(w, "Error inserting book", http.StatusInternalServerError)
        return
    }

    w.WriteHeader(http.StatusCreated)
    json.NewEncoder(w).Encode(book)
}

Step 4: Protecting the Routes with Middleware

We want to make sure only authenticated users can access our new database-powered endpoints. Use the authenticate middleware from Week 2, and you’re all set!

func main() {
    db = connectDB()
    defer db.Close()

    r := mux.NewRouter()

    r.HandleFunc("/login", login).Methods("POST")
    r.Handle("/books", authenticate(http.HandlerFunc(getBooks))).Methods("GET")
    r.Handle("/books", authenticate(http.HandlerFunc(createBook))).Methods("POST")

    fmt.Println("Server started on port :8000")
    log.Fatal(http.ListenAndServe(":8000", r))
}

Testing It Out

Let’s put this thing to the test:

1. Add a new book:

   curl -X POST http://localhost:8000/books -d '{"title": "1984", "author": "George Orwell"}' -H "Content-Type: application/json"

1. Fetch all books:

   curl http://localhost:8000/books

And boom! You’ve got a Go API with PostgreSQL, ready to handle some real data.

What’s Next?

Next time, we’ll make our API even slicker with some custom middleware for logging and error handling. Stay tuned for more!