PostgreSQL and Golang Tutorial

Advanced How-tos Go

Richard Yen Principal Support Engineer

SUMMARY: This article covers how to use PostgreSQL together with the programming language Golang (Go). After showing how to get started with Go’s Object Relational Mapper, it offers an example of setting up a PostgreSQL database using Go.

1. The connector

2. Getting started

3. A simple example

4. Launch!

 

 

Golang (or simply “Go”) is a powerful C/C++-like programming language that has garnered a lot of interest since its inception in 2009. It’s widely used at Google, where it was created, and I’ve personally seen several software engineers electing to use Go over more traditional languages like C/C++ and Java because of its more intuitive syntax and features. For the most part, these developers have said that choosing Go was an excellent choice, and they probably won’t go back. And to continue their streak of good choices, they often back their application with PostgreSQL, the world’s most powerful open-source database. This article will discuss how to get Go-ing with PostgreSQL.

 

The connector

For this article, we will be using GORM, a very powerful Object Relational Mapper (ORM) written for Go, which ultimately connects to PostgreSQL with Go’s “lib/pq” module.  Using “lib/pq” is pretty straightforward (and probably too simple for us to write a full article about). For more information, visit https://godoc.org/github.com/lib/pq.

 

Getting started

We’re going to assume that you’ve got Go installed on your machine, since there are several ways to do it, and to cover every OS and method would be outside the scope of this article.  Once Go is installed, you’ll need to install GORM and some of its dependencies:

go get github.com/jinzhu/gorm

go get github.com/gorilla/mux

go get github.com/lib/pq

go get github.com/rs/cors

 

A simple example

While there are many frameworks out there for working with Go, most of them don’t get into the nitty-gritty details about separating models from routes and such. All that is up to the developer, and while you can certainly split them up into different files and directories, we’ll give a simple one-file example here.

 

First, you’ll need to import the GORM package:

package main



import (

  "encoding/json"

  "log"

  "net/http"



  "github.com/gorilla/mux"

  "github.com/jinzhu/gorm"

  "github.com/rs/cors"



  _ "github.com/jinzhu/gorm/dialects/postgres"

)

 

Next, you’ll need to define the Driver and Car tables as structs:

type Driver struct {

  gorm.Model

  Name     string

  License  string

  Cars     []Car

}



type Car struct {

  gorm.Model

  Year       int

  Make       string

  ModelName  string

  DriverID   int

}

 

Note that we have created a reference for a Car to have a driver/owner. GORM does not create foreign keys on the database side, though you can define a has-many relationship in the software (notice that a Driver can have multiple Cars in its model definition).

Next, we’ll tell Go to insert some data for us:

var db *gorm.DB

var err error



var (

    drivers = []Driver{

        {Name: "Jimmy Johnson", License: "ABC123"},

        {Name: "Howard Hills",  License: "XYZ789"},

        {Name: "Craig Colbin",  License: "DEF333"},

    }

    cars = []Car{

        {Year: 2000, Make: "Toyota", ModelName: "Tundra", DriverID: 1},

        {Year: 2001, Make: "Honda",  ModelName: "Accord", DriverID: 1},

        {Year: 2002, Make: "Nissan", ModelName: "Sentra", DriverID: 2},

        {Year: 2003, Make: "Ford",   ModelName: "F-150",  DriverID: 3},

    }

)

 

Then, you’ll need to tell Go how to handle incoming HTTP requests:

func main() {

  router := mux.NewRouter()



  db, err = gorm.Open( "postgres", "host=db port=5432 user=postgres dbname=postgres sslmode=disable password=postgres")



  if err != nil {

    panic("failed to connect database")

  }



  defer db.Close()



  db.AutoMigrate(&Driver{})

  db.AutoMigrate(&Car{})



  for index := range cars {

      db.Create(&cars[index])

  }



  for index := range drivers {

      db.Create(&drivers[index])

  }



  router.HandleFunc("/cars", GetCars).Methods("GET")

  router.HandleFunc("/cars/{id}", GetCar).Methods("GET")

  router.HandleFunc("/drivers/{id}", GetDriver).Methods("GET")

  router.HandleFunc("/cars/{id}", DeleteCar).Methods("DELETE")



  handler := cors.Default().Handler(router)

  

  log.Fatal(http.ListenAndServe(":8080", handler))

}

 

Finally, you’ll need to define how the app will handle the HTTP requests:

func GetCars(w http.ResponseWriter, r *http.Request) {

  var cars []Car

  db.Find(&cars)

  json.NewEncoder(w).Encode(&cars)

}



func GetCar(w http.ResponseWriter, r *http.Request) {

  params := mux.Vars(r)

  var car Car

  db.First(&car, params["id"])

  json.NewEncoder(w).Encode(&car)

}



func GetDriver(w http.ResponseWriter, r *http.Request) {

  params := mux.Vars(r)

  var driver Driver

  var cars   []Car

  db.First(&driver, params["id"])

  db.Model(&driver).Related(&cars)

  driver.Cars = cars

  json.NewEncoder(w).Encode(&driver)

}



func DeleteCar(w http.ResponseWriter, r *http.Request) {

  params := mux.Vars(r)

  var car Car

  db.First(&car, params["id"])

  db.Delete(&car)



  var cars []Car

  db.Find(&cars)

  json.NewEncoder(w).Encode(&cars)

}

 

Launch!

You should now have in one file all the code necessary to run—you’ve basically got a Model-View-Controller to manage your Cars and Drivers. Let’s give it a try! In your terminal, call “go run gorm_demo.go”. This will launch a webserver that listens on the port specified (“:8080” in our example). You’ll need to now open either a browser and navigate to “localhost:8080”, or just use “curl” to load the pages (since we never really defined any HTML templates):

$ curl localhost:8080/cars

[{"ID":1,"CreatedAt":"2019-12-20T23:38:26.176961Z","UpdatedAt":"2019-12-20T23:38:26.176961Z","DeletedAt":null,"Year":2000,"Make":"Toyota","ModelName":"Tundra","DriverID":1},{"ID":2,"CreatedAt":"2019-12-20T23:38:26.178927Z","UpdatedAt":"2019-12-20T23:38:26.178927Z","DeletedAt":null,"Year":2001,"Make":"Honda","ModelName":"Accord","DriverID":1},{"ID":3,"CreatedAt":"2019-12-20T23:38:26.179947Z","UpdatedAt":"2019-12-20T23:38:26.179947Z","DeletedAt":null,"Year":2002,"Make":"Nissan","ModelName":"Sentra","DriverID":2},{"ID":4,"CreatedAt":"2019-12-20T23:38:26.180939Z","UpdatedAt":"2019-12-20T23:38:26.180939Z","DeletedAt":null,"Year":2003,"Make":"Ford","ModelName":"F-150","DriverID":3}]

 

Note that the JSON comes out unformatted. If you want to pretty-print it (without having to refactor the Go code), the easiest way to do so is by piping it to “python -m json.tool”. In the interest of length, I’ve only used that method in the final example.

Since we basically created a REST API, we can retrieve Cars by ID:

$ curl localhost:8080/cars/2

{"ID":2,"CreatedAt":"2019-12-20T23:38:26.178927Z","UpdatedAt":"2019-12-20T23:38:26.178927Z","DeletedAt":null,"Year":2001,"Make":"Honda","ModelName":"Accord","DriverID":1}

 

We can also delete Cars:

$ curl -X DELETE localhost:8080/cars/2

[{"ID":1,"CreatedAt":"2019-12-20T23:38:26.176961Z","UpdatedAt":"2019-12-20T23:38:26.176961Z","DeletedAt":null,"Year":2000,"Make":"Toyota","ModelName":"Tundra","DriverID":1},{"ID":3,"CreatedAt":"2019-12-20T23:38:26.179947Z","UpdatedAt":"2019-12-20T23:38:26.179947Z","DeletedAt":null,"Year":2002,"Make":"Nissan","ModelName":"Sentra","DriverID":2},{"ID":4,"CreatedAt":"2019-12-20T23:38:26.180939Z","UpdatedAt":"2019-12-20T23:38:26.180939Z","DeletedAt":null,"Year":2003,"Make":"Ford","ModelName":"F-150","DriverID":3}]

 

Finally, if you recall, we defined a one-to-many relationship between Drivers and Cars, so if we fetch a Driver, we can see its related Cars (and pretty-printed with python’s “json.tool” module):

$ curl localhost:8080/drivers/1 | python -m json.tool

{

    "ID": 1,

    "CreatedAt": "2019-12-20T23:38:26.181909Z",

    "UpdatedAt": "2019-12-20T23:38:26.181909Z",

    "DeletedAt": null,

    "Name": "Jimmy Johnson",

    "License": "ABC123",

    "Cars": [

        {

            "ID": 1,

            "CreatedAt": "2019-12-20T23:38:26.176961Z",

            "UpdatedAt": "2019-12-20T23:38:26.176961Z",

            "DeletedAt": null,

            "Year": 2000,

            "Make": "Toyota",

            "ModelName": "Tundra",

            "DriverID": 1

        }

    ]

}

 

There you have it! A simple and working example of how to use PostgreSQL with Go’s ORM.  For more information about the features of GORM, you may want to check out their documentation.

Happy coding!

 

Richard YenPrincipal Support Engineer