Building a Web API with Go and PostgreSQL

Table of Contents

  1. Introduction
  2. Prerequisites
  3. Setting up PostgreSQL
  4. Installing the Required Packages
  5. Creating the API
  6. Testing the API
  7. Conclusion

Introduction

In this tutorial, we will build a simple Web API using the Go programming language and PostgreSQL as our database. By the end of this tutorial, you will be able to create a fully functional API that can interact with a PostgreSQL database.

Prerequisites

Before starting this tutorial, you should have a basic understanding of the Go programming language. It would also be helpful if you have some knowledge of SQL and RESTful APIs.

To follow along with this tutorial, you will need:

  • Go installed on your machine
  • PostgreSQL installed on your machine
  • An IDE or text editor of your choice

Setting up PostgreSQL

First, let’s set up PostgreSQL on our machine. If you haven’t already installed PostgreSQL, you can download it from the official website and follow the installation instructions for your operating system.

Once PostgreSQL is installed, open a terminal or command prompt and start the PostgreSQL service. You can do this by running the following command:

pg_ctl start

Next, we need to create a new database for our API. Run the following command to open the PostgreSQL command-line interface:

psql

In the PostgreSQL CLI, enter the following command to create a new database:

CREATE DATABASE api;

Exit the PostgreSQL CLI by typing \q and pressing Enter.

Installing the Required Packages

Next, we need to install the necessary packages to work with PostgreSQL in Go. Open a terminal or command prompt and run the following command:

go get github.com/lib/pq

This will download and install the pq package, which provides a driver for connecting to PostgreSQL.

Creating the API

Now that we have PostgreSQL set up and the required packages installed, let’s create the API. In your IDE or text editor, create a new file called main.go.

First, we need to import the necessary packages. Add the following import statements at the beginning of the file:

package main

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

	_ "github.com/lib/pq"
)

Next, let’s define a struct to represent our API’s data model. Add the following code below the import statements:

type Book struct {
	ID     int    `json:"id"`
	Title  string `json:"title"`
	Author string `json:"author"`
}

In the main function, we will establish a connection to the PostgreSQL database. Add the following code inside the main function:

func main() {
	// Establish a connection to the PostgreSQL database
	db, err := sql.Open("postgres", "postgres://username:password@localhost/api?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}

	defer db.Close()

	// Test the database connection
	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("Connected to the database")

	// Handle API routes
	http.HandleFunc("/books", getBooks)

	// Start the server
	log.Fatal(http.ListenAndServe(":8080", nil))
}

Replace username and password with your PostgreSQL credentials. The sslmode=disable parameter is used to disable SSL for development purposes.

In the getBooks function, we will query the database to retrieve all the books. Add the following code below the main function:

func getBooks(w http.ResponseWriter, r *http.Request) {
	// Query the database for all books
	rows, err := db.Query("SELECT * FROM books")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	defer rows.Close()

	// Iterate over the rows and create book objects
	books := []Book{}
	for rows.Next() {
		book := Book{}
		err := rows.Scan(&book.ID, &book.Title, &book.Author)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
			return
		}
		books = append(books, book)
	}

	// Convert books slice to JSON
	jsonBooks, err := json.Marshal(books)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	// Set the content type header
	w.Header().Set("Content-Type", "application/json")

	// Send the JSON response
	w.Write(jsonBooks)
}

Finally, let’s run the API and see it in action. In your terminal or command prompt, navigate to the directory where you saved the main.go file and run the following command:

go run main.go

If everything is set up correctly, you should see the message “Connected to the database” printed in the console.

Testing the API

To test the API, we can use tools like cURL or Postman. Open your preferred tool and make a GET request to http://localhost:8080/books. You should receive a JSON response containing all the books in the database.

To add more functionality to our API, we can implement additional endpoints like creating, updating, and deleting books. We can also add validation, authentication, and other features as needed.

Conclusion

In this tutorial, we have learned how to build a Web API with Go and PostgreSQL. We started by setting up PostgreSQL, installing the required packages, and then created a simple API that retrieves books from the database. We also discussed how to test the API using tools like cURL or Postman.

With this knowledge, you can now create your own APIs using Go and PostgreSQL, and further enhance them with additional features.

Remember, this is just a starting point, and there is much more you can do with Go and PostgreSQL. Keep exploring, learning, and building amazing applications!

I hope you find this tutorial helpful! Let me know if you have any questions or need further assistance. Good luck with your Go and PostgreSQL development journey!

Published:

Related Articles