PostgreSQL Tutorial: Connect to PostgreSQL with Golang

August 8, 2024

Summary: This tutorial 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.

Table of Contents

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 some 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 tutorial 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

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)
}

Running test

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.