In this article, we’ll expand the CRUD REST API build in one of the recent articles to store the created resources in a PostgreSQL database rather than in memory. For those who did not follow along here is the link to the last post.
- Setup PostgreSQL
- ORM or plain SQL
- Initializing the database connection
- Implement CRUD database access with HTTP handlers
>>> Previous post of the series: CRUD REST API with gorilla/mux
>>> Next post of the series: Go REST API – Sort, Page, Filter
>>> Initial Code on Github
Setup PostgreSQL
We’ll start with setting up a PostgreSQL database. There are many ways to do this. I decided to use Docker, as most developers would already have it installed. If you’re not one of them follow along with the official guide to installing docker on your system.
After you installed Docker you can start PostgreSQL using folowing command in a terminal:
docker run --name psql --rm -d -p 5432:5432 -e POSTGRES_PASSWORD=1234 -e POSTGRES_DB=userdb postgres:9.6.19
Let’s decompose this command:
docker run postgres:9.6.19
This will run a container using the postgres:9.6.19 image from Dockerhub.
--name psql
This labels the container with the name “psql”.
--rm
The –rm flag makes sure the container is removed from your system after it exits.
-d
Will execute the container detached from your current terminal session.
-p 5432:5432
This will map the container port 5432, which is the default port of PostgresSQL, to the port 5432 on your host system.
-e POSTGRES_PASSWORD=1234 -e POSTGRES_DB=userdb
These are environment variables that will be used by the container to create an initial database called userdb and set the password for the admin user of that database.
Make sure you don’t use this password in production. It might seem secure but it’s not.
Now that we’ve started the container let’s see if everything went right:
docker ps
Your output should look like this:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 078251d7f40f postgres:9.6.19 "docker-entrypoint.s…" 4 minutes ago Up 4 minutes 0.0.0.0:5432->5432/tcp psql
Great the PostgreSQL database is running, let’s expand the REST API to use it.
ORM or plain SQL
There are two common ways of working with SQL databases when using Go: using an Object Relation Mapper (ORM) or using the build-in “sql” package. An Object Relation Mapper is a library that allows you to work with a SQL database without much knowledge about the query language SQL. It provides an API that automatically generates SQL code for common DB actions like creating tables and storing, reading, updating, or deleting rows. So you can focus on your programming language and domain objects and leave the boilerplate SQL code to the library. But using an ORM has also some disadvantages. With the abstraction of an ORM, it gets harder to optimize query performance and you have to learn how to use the ORM library, which might be an overhead for you if you’re already very comfortable with working in plain SQL. With the “sql” package, on the other hand, you would have to write all SQL queries to the database by yourself. This topic is highly controversial in the community and there is no one size fits all solution.
In general, ORMs are great, if you’re not familiar with SQL, especially for simple CRUD APIs. That’s why I decided to use the ORM approach for this application. But keep in mind, when working on a project that has very high database performance requirements, working with plain SQL would be the better choice.
Here’s a good tutorial to lead you into the right direction, when you’d like to use the “sql” package instead.
Initializing the database connection
We decided to use an ORM for our application. The package we’re going to use is called gorm. It is the most popular Object Relational Mapper for Go. We can add gorm to our dependencies by running following commands in our project root directory:
go get -u gorm.io/gorm go get -u gorm.io/driver/postgres
Notice: For gorm to work we need a specialized database driver package for the SQL database we’re using, which in this case is “postgres”
After we installed the gorm package, we add the code to initialize a database connection in a function called initDB(). We open the database connection using the Postgres driver. Then we let gorm initialize the user table by calling db.AutoMigrate with an empty user reference.
main.go
...
func initDB() (*gorm.DB, error) {
dataSourceName := "host=localhost user=postgres password=1234 dbname=userdb port=5432"
db, err := gorm.Open(postgres.Open(dataSourceName), &gorm.Config{})
db.AutoMigrate(&User{})
return db, err
}
...
Despite gorm being compatible with many SQL databases, the initialization of the connection is different for each of them. If you’re not using PostgreSQL there’s a good chance that initializing gorm for the SQL database of your choice is document here.
Now we initialize the database at the start of our main function, store the gorm.DB reference in a local variable and print the error in case something goes wrong:
main.go
...
func main() {
db, err := initDB()
if err != nil {
fmt.Printf("Error initializing DB: %v\n", err)
}
r := mux.NewRouter()
usersR := r.PathPrefix("/users").Subrouter()
usersR.Path("").Methods(http.MethodGet).HandlerFunc(getAllUsers)
usersR.Path("").Methods(http.MethodPost).HandlerFunc(createUser)
usersR.Path("/{id}").Methods(http.MethodGet).HandlerFunc(getUserByID)
usersR.Path("/{id}").Methods(http.MethodPut).HandlerFunc(updateUser)
usersR.Path("/{id}").Methods(http.MethodDelete).HandlerFunc(deleteUser)
fmt.Println("Start listening")
fmt.Println(http.ListenAndServe(":8080", r))
}
...
The database would now get initialized. But currently, the code does not compile. We’re not able to use the gorm.DB reference in our HTTP handler functions, and thus it’s an unused variable. We need a way to pass the reference to our handler function. In a previous post on How to pass arguments to HTTP Handlers in GO, I documented multiple possible ways to do that. We’re using the struct method for our service.
So we create a struct called userHandler, that has a field db of type *gorm.DB to hold our database reference. Then we change all handler functions to be methods provided by the userHandler struct. After that the code for our HTTP handler functions (or now methods) should look like this:
main.go
...
type userHandler struct {
db *gorm.DB
}
func (uh userHandler) getAllUsers(w http.ResponseWriter, r *http.Request) {
w.Header().Add("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(users); err != nil {
fmt.Println(err)
w.WriteHeader(http.StatusInternalServerError)
}
}
func (uh userHandler) getUserByID(w http.ResponseWriter, r *http.Request) {
id := mux.Vars(r)["id"]
index := indexByID(users, id)
if index < 0 {
w.WriteHeader(http.StatusNotFound)
return
}
w.Header().Add("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(users[index]); err != nil {
fmt.Println(err)
w.WriteHeader(http.StatusInternalServerError)
}
}
func (uh userHandler) updateUser(w http.ResponseWriter, r *http.Request) {
id := mux.Vars(r)["id"]
index := indexByID(users, id)
if index < 0 {
w.WriteHeader(http.StatusNotFound)
return
}
u := User{}
if err := json.NewDecoder(r.Body).Decode(&u); err != nil {
w.WriteHeader(http.StatusBadRequest)
return
}
users[index] = u
w.Header().Add("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(&u); err != nil {
fmt.Println(err)
w.WriteHeader(http.StatusInternalServerError)
}
}
func (uh userHandler) deleteUser(w http.ResponseWriter, r *http.Request) {
id := mux.Vars(r)["id"]
index := indexByID(users, id)
if index < 0 {
w.WriteHeader(http.StatusNotFound)
return
}
users = append(users[:index], users[index+1:]...)
w.WriteHeader(http.StatusOK)
}
func (uh userHandler) createUser(w http.ResponseWriter, r *http.Request) {
u := User{}
if err := json.NewDecoder(r.Body).Decode(&u); err != nil {
w.WriteHeader(http.StatusBadRequest)
return
}
users = append(users, u)
response, err := json.Marshal(&u)
if err != nil {
fmt.Println(err)
w.WriteHeader(http.StatusInternalServerError)
return
}
w.Header().Add("Content-Type", "application/json")
w.WriteHeader(http.StatusCreated)
w.Write(response)
}
...
In our main function, we now create a userHandler instance and assign our initialized gorm.DB reference to its db field. Additionally, we change the registration of the routes to use the methods of userHandler instead of the previously defined HTTP handler functions:
main.go
...
func main() {
db, err := initDB()
if err != nil {
fmt.Printf("Error initializing DB: %v\n", err)
}
uh := userHandler{db: db}
r := mux.NewRouter()
usersR := r.PathPrefix("/users").Subrouter()
usersR.Path("").Methods(http.MethodGet).HandlerFunc(uh.getAllUsers)
usersR.Path("").Methods(http.MethodPost).HandlerFunc(uh.createUser)
usersR.Path("/{id}").Methods(http.MethodGet).HandlerFunc(uh.getUserByID)
usersR.Path("/{id}").Methods(http.MethodPut).HandlerFunc(uh.updateUser)
usersR.Path("/{id}").Methods(http.MethodDelete).HandlerFunc(uh.deleteUser)
fmt.Println("Start listening")
fmt.Println(http.ListenAndServe(":8080", r))
}
...
Great, our handlers can finally use the database reference to interact with the database. Let’s see how we can change our handler implementation to use the database instead of the in-memory user storage.
Implement CRUD database access with HTTP handlers
At first, we delete the global users slice we used to store the created users locally. Then we refactore all methods of the userHandler struct one by one.
Get all entries
The getAllUsers method now needs to read all user entries from the database before returning a slice of users encoded as JSON. To do this we use the Find method of the gorm.DB reference and provide the reference of an empty array as input. The Find method will populate the array and return a result object. For every DB method, we call we have to test the result for an Error and handle it appropriately.
main.go
...
func (uh userHandler) getAllUsers(w http.ResponseWriter, r *http.Request) {
users := []User{}
if err := uh.db.Find(&users).Error; err != nil {
fmt.Println(err)
http.Error(w, "Error on DB find for all users", http.StatusInternalServerError)
return
}
w.Header().Add("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(users); err != nil {
fmt.Println(err)
w.WriteHeader(http.StatusInternalServerError)
}
}
...
Get by ID
For the getUserByID method we remove the call to the indexByID helper function, that was used to find users in our in-memory storage implementation. This will from now on be handled by the database. We use the First method with the reference to a user object, that has its ID set to the ID referenced in the request URL. Gorm will automatically create a select query for the database which includes a filter with the given ID. Again we test for errors, this time we add another case to test if the error for the database call was an ErrRecordNotFoun. In this case, we return http.StatusNotFound instead of an internal server error. If everything went fine we output the user object as JSON.
main.go
...
func (uh userHandler) getUserByID(w http.ResponseWriter, r *http.Request) {
id := mux.Vars(r)["id"]
user := User{ID: id}
if err := uh.db.First(&user).Error; err != nil {
if err == gorm.ErrRecordNotFound {
http.Error(w, "User not found", http.StatusNotFound)
return
}
fmt.Println(err)
http.Error(w, fmt.Sprintf("Error on DB find for user with id: %s", id), http.StatusInternalServerError)
return
}
w.Header().Add("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(user); err != nil {
fmt.Println(err)
http.Error(w, "Error decoding response object", http.StatusInternalServerError)
}
}
...
Create entry
For the createUser method, all we have to do is to replace the append call to the in-memory slice with a call to the Create method of the gorm.DB reference, and again test for Errors:
main.go
...
func (uh userHandler) createUser(w http.ResponseWriter, r *http.Request) {
u := User{}
if err := json.NewDecoder(r.Body).Decode(&u); err != nil {
w.WriteHeader(http.StatusBadRequest)
return
}
if err := uh.db.Create(&u).Error; err != nil {
http.Error(w, "Error creating user", http.StatusInternalServerError)
return
}
response, err := json.Marshal(&u)
if err != nil {
fmt.Println(err)
http.Error(w, "Error encoding response object", http.StatusInternalServerError)
return
}
w.Header().Add("Content-Type", "application/json")
w.WriteHeader(http.StatusCreated)
w.Write(response)
}
...
Update by ID
For the updateUser method, the code looks similar to the getUserByID method. First, we try to find the user in the database instead of using the in-memory slice. If that worked we update the user in the database using the Save method of the gorm.DB object:
main.go
...
func (uh userHandler) updateUser(w http.ResponseWriter, r *http.Request) {
id := mux.Vars(r)["id"]
user := User{ID: id}
if err := uh.db.First(&user).Error; err != nil {
if err == gorm.ErrRecordNotFound {
http.Error(w, "User not found", http.StatusNotFound)
return
}
fmt.Println(err)
http.Error(w, fmt.Sprintf("Error on DB find for user with id: %s", id), http.StatusInternalServerError)
return
}
u := User{}
if err := json.NewDecoder(r.Body).Decode(&u); err != nil {
fmt.Println(err)
http.Error(w, "Error deconding request body", http.StatusBadRequest)
return
}
if err := uh.db.Save(&user).Error; err != nil {
fmt.Println(err)
http.Error(w, "Error saving user", http.StatusInternalServerError)
return
}
w.Header().Add("Content-Type", "application/json")
if err := json.NewEncoder(w).Encode(&u); err != nil {
fmt.Println(err)
w.WriteHeader(http.StatusInternalServerError)
}
}
...
Delete by ID
Deleting a database entry works just like updating it. The only difference is we call the Delete, instead of the Save method:
...
func (uh userHandler) deleteUser(w http.ResponseWriter, r *http.Request) {
id := mux.Vars(r)["id"]
user := User{ID: id}
if err := uh.db.First(&user).Error; err != nil {
if err == gorm.ErrRecordNotFound {
http.Error(w, "User not found", http.StatusNotFound)
return
}
fmt.Println(err)
http.Error(w, fmt.Sprintf("Error on DB find for user with id: %s", id), http.StatusInternalServerError)
return
}
if err := uh.db.Delete(&user).Error; err != nil {
fmt.Println(err)
http.Error(w, "Error deleting user", http.StatusInternalServerError)
return
}
w.WriteHeader(http.StatusOK)
}
...
Conclusion
That’s it we implemented all our CRUD REST API handler methods to store our users in a PostgreSQL Database. If you want to test your code make sure to start your PostgreSQL database and test the API calls with a REST API client of your choice. If you don’t know where to start looking at the first blog post of the series, where I describe how to use the REST API with the command line tool “curl”.
>>> Previous post of the series: CRUD REST API with gorilla/mux
>>> Next post of the series: Go REST API – Sort, Page, Filter
>>> Full Code on Github