What we will be building:
In this article, we will build a basic CRUD app that stores books on the PostgreSQL database.
Prerequisite:
golang installed on your system
Basic knowledge of Golang
How to use Postman for calling APIs
Setup the project
create a folder in your $GOPATH and name it go-gres
Then do
go mod init go-gres
Installing the packages
I will be using Vscode for this tutorial, so if you are doing the same, you can use `ctrl + shift + `` to open a new terminal to import the packages.
- The first package we will be installing will be the
postgres driverpackage.
go get github.com/lib/pq
- The
muxpackage for handling our routes.
go get -u github.com/gorilla/mux
- The
godotenvpackage of our environmental variables
go get github.com/joho/godotenv
How to Install Postgres DB
You can either use the online ElephantSql or download the pgAdmin windows app. For this tutorial, we will be using the pgAdmin app
After that, we are going to create our database through the SQL shell terminal.
CREATE DATABASE books
If you go to your pgAdmin app and you refresh the server, you should see it there.
Like so.
Click on the query tool in your query editor to create our table. Paste this there:
create table books(
id serial,
author text,
title text,
year int
)
After this, we are done with creating the database, and we can now focus on writing the code for it.
We will move on to creating our new file structure for the app. Create three folders and name them:
middleware
models
router
Then we will create the .env file to store our environmental variables and the .gitignore file to hide our .env file from git.
Under our models folder, we will create a folder called models.go. It will take the structure of what is stored in our database
package models
type Book struct {
ID int `json:"id"`
Title string `json:"title"`
Author string `json:"Author"`
Year int `json:"Year"`
}
Then in our middleware folder, we can create a file called handlers.go
In our handlers.go file, we will have our first function, LoadDb, which will be used to connect to load our environmental variables and connect to our database.
func LoadDb() *sql.DB{}
Before we start to load our database, we should define our environmental variables in our .env file.
APP_DB_USERNAME=postgres
APP_DB_PASSWORD=yourpassword
APP_DB_NAME=books
APP_DB_HOST=localhost
APP_DB_PORT=5432
Then we will have to load our .env file with the .env package we got earlier in our LoadDb func we will do
err := godotenv.Load(".env")
if err != nil {
log.Fatalf("error loading %s", err)
} else {
log.Println("env loaded")
}
After this we want to pass get our values from the env and use it to connect to our posgres database.
username := os.Getenv("APP_DB_USERNAME")
pass := os.Getenv("APP_DB_PASSWORD")
host := os.Getenv("APP_DB_HOST")
dbName := os.Getenv("APP_DB_NAME")
port := os.Getenv("APP_DB_PORT")
connects := fmt.Sprintf("host=%s port=%s user=%s "+
"password=%s dbname=%s sslmode=disable", host, port, username, pass, dbName)
db, err := sql.Open("postgres", connects)
if err != nil{
panic(err)
}
err = db.Ping()
if err != nil{
panic(err)
}
fmt.Println("Successfully connected!")
return db
Then our LoadDb function should look like this:
func LoadDb() *sql.DB {
err := godotenv.Load(".env")
if err != nil {
log.Fatalf("error loading %s", err)
} else {
log.Println("env loaded")
}
username := os.Getenv("APP_DB_USERNAME")
pass := os.Getenv("APP_DB_PASSWORD")
host := os.Getenv("APP_DB_HOST")
dbName := os.Getenv("APP_DB_NAME")
port := os.Getenv("APP_DB_PORT")
connects := fmt.Sprintf("host=%s port=%s user=%s "+
"password=%s dbname=%s sslmode=disable", host, port, username, pass, dbName)
db, err := sql.Open("postgres", connects)
if err != nil{
panic(err)
}
err = db.Ping()
if err != nil{
panic(err)
}
fmt.Println("Successfully connected!")
return db
}
After this, we are going to perform five primary operations on our DB which will be
- Get all books.
- Get a book by its ID.
- Create a book.
- Delete a book.
- Update book
We will start with the get all books
func GetBooks(w http.ResponseWriter, r *http.Request) {
var book models.Book
books := []models.Book{}
db := LoadDb()
statement := `select * from books`
rows, err := db.Query(statement)
if err != nil {
log.Println(fmt.Sprintf("error occurred doing this: %s", err))
}
for rows.Next() {
err := rows.Scan(&book.ID, &book.Author, &book.Title, &book.Year)
if err != nil {
log.Println(fmt.Sprintf("error occurred doing this: %s", err))
}
books = append(books, book)
}
json.NewEncoder(w).Encode(books)
defer db.Close()
defer rows.Close()
}
In this getbook function, we pass in our request and response params, and then we have an instance of the book model with var book models.Book then we created a slice which will take our books once we get it from the DB. Our SQL statement is used in getting all the books from the db then passing that into the rows. We used the rows.Scan(&book.ID, &book.Author, &book.Title, &book.Year) to map the response back to our book params. Then we appended them into the slice we created earlier with books = append(books, book). After that, we needed to pass the response out through the json.NewEncoder(w).Encode(books). Then we closed the connection to the rows and the DB.
Then we will proceed to implementing getting books by the id.
func GetBook(w http.ResponseWriter, r *http.Request) {
statement := `select * from books where id=$1`
db := LoadDb()
defer db.Close()
params := mux.Vars(r)
rows := db.QueryRow(statement, params["id"])
err := rows.Scan(&book.ID, &book.Author, &book.Title, &book.Year)
if err != nil {
log.Println(fmt.Sprintf("error occurred doing this: %s", err))
}
json.NewEncoder(w).Encode(book)
}
In this getbook function, we started by declaring our SQL statement to get the book we want. The $1 there stand for the variable we are yet to pass in. We load the DB and defer the close connection.
params := mux.Vars(r) is used to get the params in our route./book/{id}
This is an example of how our route will look. So the params is used to get the map of the id. Then we pass the statement and the parameter needed in our statement, which is the id of the book we want. Just like before, rows.Scan is used to connect the response back to our model. We then checked for errors and passed back the book response to the route.
Then we will move on to the add functionality.
func AddBook(w http.ResponseWriter, r *http.Request) {
db := LoadDb()
json.NewDecoder(r.Body).Decode(&book)
defer db.Close()
statement := `insert into books(title, author, year) values($1,$2,$3) returning id, author, title, year`
err := db.QueryRow(statement, book.Title, book.Author, book.Year).Scan(&book.ID, &book.Author, &book.Title, &book.Year)
if err != nil {
log.Println(fmt.Sprintf("error occurred doing this: %s", err))
}
json.NewEncoder(w).Encode(book)
}
In adding books to the DB, we started by loading the DB then we get the JSON input from the user with json.NewDecoder(r.Body).Decode(&book), then we defered our closing of the database connection.
We go on to declare our SQL statement, which inserts the book details to our DB and returns the values inserted. Then we check for errors and pass our response book our.
Then we move on to the updating book functionality.
func UpdateBook(w http.ResponseWriter, r *http.Request) {
params := mux.Vars(r)
db := LoadDb()
defer db.Close()
statement := `update books set author=$2, title=$3, year=$4 where id=$1`
_, err := db.Exec(statement, params["id"], book.Author, book.Title, book.Year)
if err != nil {
log.Println(fmt.Sprintf("error occurred doing this: %s", err))
}
json.NewEncoder(w).Encode("row updated")
}
In this update functionality, we start by getting the params from our route. Then opening the connection to our database, Then we deferred our close connection function. We then move on to our SQL statement to update the author, title, and year where the id is specified. We check for error and then inform the user the operation is successful.
Then unto the last functionality, which is the delete functionality.
func DeleteBook(w http.ResponseWriter, r *http.Request) {
db := LoadDb()
defer db.Close()
params := mux.Vars(r)
statement := `delete from books where id=$1 `
_, err := db.Exec(statement, params["id"])
if err != nil {
log.Println(fmt.Sprintf("error occurred doing this: %s", err))
} else {
json.NewEncoder(w).Encode("row deleted")
}
}
We start this by opening our database connection and deferring the close connection. We get the params from the route then we declare our SQL statement. We write the DB command and check for error, and then we pass the message to the user that it has been successful.
Creating our routes
After defining our functions to work for the creating, replacing, updating, and deleting of books in our database now, we can go on to create our route in our router folder, which we will create a route.go
For the first step, we import our package router, and then we import the middleware folder directory and the mux package. Like this:
package router
import (
"github.com/gorilla/mux"
"github.com/iyiola-dev/go-gres/middleware"
)
We will go on to the next step - creating the router function and defining each route for each function.
func Router() *mux.Router {
router := mux.NewRouter()
router.HandleFunc("/books", middleware.GetBooks).Methods("GET")
router.HandleFunc("/book/{id}", middleware.GetBook).Methods("GET")
router.HandleFunc("/book/update/{id}", middleware.UpdateBook).Methods("PUT")
router.HandleFunc("/book/add", middleware.AddBook).Methods("post")
router.HandleFunc("/book/delete/{id}", middleware.DeleteBook).Methods("DELETE")
return router
}
Starting our Route in our Main.go file
The aim here is to pass the route function to our main.go file, which will be our serving point.
package main
import (
"fmt"
"log"
"net/http"
"github.com/iyiola-dev/go-gres/router"
)
func main() {
route := router.Router()
log.Fatal(http.ListenAndServe(":8080", route))
fmt.Println("starting route at one port 8080")
}
Starting our server and testing on Postman
To start our server, we will go to the terminal and type go run main.go
Then to test our endpoints, you can choose to use Postman.
What has been learnt:
How to install postgres pgAdmin
How to connect postgres to go
how to create a basic crud
Thank you for taking your time to read this article. link to the GitHub github.com/iyiola-dev/go-gres