Skip to main content

Getting started with Go and React - PostgreSQL

Shenzhen, China

Creating an REST API backend in Go and connecting it to a React.js frontend.

I want to prototype a Go backend for a Weather Cam tool. The backend should hold all the information related to all cameras and serve them on different routes. The backend then needs to be connected to a React.js frontend that displays the JSON data that is being served as well as to allow to add / delete cameras.

Setting the Database​

Setup​

I am going to use the official Docker image to set up the SQL Database on a Debian Bullseye server:

docker run -d --rm \
    --name postgres \
    -e POSTGRES_PASSWORD=secretpassword \
    -p 5432:5432 \
    postgres:14

I can now access the container and connect to the Postgres CLI:

docker exec -ti -u postgres postgres psql

psql (14.0 (Debian 14.0-1.pgdg110+1))
Type "help" for help.

postgres=#

Create a Database for your Application​

CREATE DATABASE gocam;

Switch to using the new message_boards table instead of the default postgres:

\connect gocam;
You are now connected to database "gocam" as user "postgres".

Create Tables​

We build 3 models that for the data that our app should handle:

./models/models.go

  • Camera
  • CameraModel
  • Locations

We now need 3 tables in our database that can hold this data.

Camera​

CREATE TABLE camera (
    id INTEGER PRIMARY KEY UNIQUE GENERATED ALWAYS AS IDENTITY,
    name VARCHAR ( 25 ) UNIQUE NOT NULL,
    usr VARCHAR ( 25 ) NOT NULL,
    pass VARCHAR ( 25 ) NOT NULL,
    ip VARCHAR ( 50 ) UNIQUE NOT NULL,
    created TIMESTAMP NOT NULL,
    updated TIMESTAMP,
    rating INT,
    location VARCHAR ( 50 ) NOT NULL
);

Camera Model​

CREATE TABLE camera_model (
    id INT REFERENCES camera(id) ON DELETE CASCADE,
    model VARCHAR ( 25 ) NOT NULL,
    lense VARCHAR ( 25 ) NOT NULL,
    res VARCHAR ( 10 ) NOT NULL,
    created TIMESTAMP NOT NULL,
    updated TIMESTAMP
);

Locations​

CREATE TABLE camera_locations (
    id INT REFERENCES camera(id) ON DELETE CASCADE,
    location VARCHAR ( 50 ),
    cid VARCHAR ( 15 ),
    lid VARCHAR ( 15 ),
    created TIMESTAMP NOT NULL,
    updated TIMESTAMP
);

Adding Data​

Camera​

INSERT INTO camera
  (name, usr, pass, ip, created, updated, rating, location)
VALUES
  ('Mountain View', 'admin', 'instar', '192.168.2.10', NOW() - interval '123 days', NOW(), 3, 'HK'),
  ('Harbour East', 'admin', 'instar', '192.168.2.19', NOW() - interval '143 days', NOW(), 5, 'HK'),
  ('Harbour West', 'admin', 'instar', '192.168.2.24', NOW() - interval '156 days', NOW(), 3, 'HK'),
  ('Beachfront', 'admin', 'instar', '192.168.2.117', NOW() - interval '32 days', NOW(), 2, 'HK'),
  ('Downtown', 'admin', 'instar', '192.168.178.70', NOW() - interval '467 days', NOW(), 4, 'HK'),
  ('Central Park', 'admin', 'instar', '192.168.178.42', NOW() - interval '435 days', NOW(), 2, 'HK'),
  ('Terminal', 'admin', 'instar', '192.168.178.52', NOW() - interval '213 days', NOW(), 2, 'HK'),
  ('Skyline', 'admin', 'instar', '192.168.178.67', NOW() - interval '234 days', NOW(), 5, 'HK'),
  ('Plaza', 'admin', 'instar', '192.168.178.249', NOW() - interval '7 days', NOW(), 3, 'HK');

Camera Model​

INSERT INTO camera_model
  (id, model, lense, res, created, updated)
VALUES
  ( 1, 'IN-9020', '4.2mm', '1080p', NOW() - interval '53 days', NOW() ),
  ( 2, 'IN-9008', '4.2mm', '1080p', NOW() - interval '84 days', NOW() ),
  ( 3, 'IN-9008', '4.2mm', '1080p', NOW() - interval '89 days', NOW() ),
  ( 4, 'IN-9408', '4.2mm', '1440p', NOW() - interval '97 days', NOW() ),
  ( 5, 'IN-9020', '4.2mm', '1080p', NOW() - interval '69 days', NOW() ),
  ( 6, 'IN-9008', '4.2mm', '1080p', NOW() - interval '76 days', NOW() ),
  ( 7, 'IN-9408', '4.2mm', '1440p', NOW() - interval '78 days', NOW() ),
  ( 8, 'IN-9020', '4.2mm', '1080p', NOW() - interval '56 days', NOW() ),
  ( 9, 'IN-9408', '4.2mm', '1440p', NOW() - interval '87 days', NOW() );

Locations​

INSERT INTO camera_locations
  (id, location, cid, lid, created, updated)
VALUES
  ( 1, 'Mountain View', 'INSTAR-0001', 'HK-0001', NOW() - interval '32 days', NOW() ),
  ( 2, 'Harbour East', 'INSTAR-0002', 'HK-0001', NOW() - interval '23 days', NOW() ),
  ( 3, 'Harbour West', 'INSTAR-0003', 'HK-0001', NOW() - interval '43 days', NOW() ),
  ( 4, 'Beachfront', 'INSTAR-0001', 'HK-0002', NOW() - interval '12 days', NOW() ),
  ( 5, 'Downtown', 'INSTAR-0005', 'HK-0003', NOW() - interval '54 days', NOW() ),
  ( 6, 'Central Park', 'INSTAR-0001', 'HK-0003', NOW() - interval '213 days', NOW() ),
  ( 7, 'Terminal', 'INSTAR-0002', 'HK-0003', NOW() - interval '33 days', NOW() ),
  ( 8, 'Skyline', 'INSTAR-0003', 'HK-0003', NOW() - interval '65 days', NOW() ),
  ( 9, 'Plaza', 'INSTAR-0004', 'HK-0003', NOW() - interval '45 days', NOW() );

Connecting your Application​

Postgres Driver​

First we need to install a driver that allows us to establish a connection with our database:

go get -u github.com/lib/pq

Connecting​

And import the following packages to main.go:

import (
	"context"
	"database/sql"
	...

	_ "github.com/lib/pq"
)

Now open a connection with - add your own Postgres connection string pgURL:

./src/api/main.go

// PG Database
const pgURL = "postgres://postgres:secretpassword@192.168.2.111/gocam?sslmode=disable"


func main() {
  ...
	flag.StringVar(&cfg.db.dsn, "dsn", pgURL, "Postgres connection string")
  ...
	// Call database and crash if not successful
	db, err := connectDB(cfg)
	if err !=nil {
		logger.Fatal(err)
	}
	defer db.Close()
  ...
}


func connectDB(cfg config) (*sql.DB, error) {
	db, err := sql.Open("postgres", cfg.db.dsn)
	if err != nil {
		return nil, err
	}

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()

	err = db.PingContext(ctx)
	if err != nil {
		return nil, err
	}

	return db, nil
}

Add Database to Route Handler​

Create the database model:

./models/models.go

// Database wrapper
type Models struct {
	DB DBModel
}
// Return models with db pool
func NewModels(db *sql.DB) Models {
	return Models {
		DB: DBModel{DB: db},
	}
}

And the database type:

./models/gocamDB.go

package models

import "database/sql"

type DBModel struct {
	DB *sql.DB
}
// Return one camera or error
func(m *DBModel) Get(id int) (*Camera, error) {
	return nil, nil
}
// Return all cameras or error
func(m *DBModel) All(id int) ([]*Camera, error) {
	return nil, nil
}

Now I want to fill those methods with SQL queries that either fetch one specific camera (identified by ID) or a collection of all cameras. The SQL queries for this are:

SELECT id, name, usr, pass, ip, updated, rating, location FROM camera WHERE id = 1;

 id |     name      |  usr  |  pass  |      ip      |          updated           | rating | location
----+---------------+-------+--------+--------------+----------------------------+--------+----------
  1 | Mountain View | admin | instar | 192.168.2.10 | 2021-10-15 03:46:36.603446 |      3 | HK
SELECT id, name, usr, pass, ip, updated, rating, location FROM camera;

 id |     name      |  usr  |  pass  |       ip        |          updated           | rating | location
----+---------------+-------+--------+-----------------+----------------------------+--------+----------
  1 | Mountain View | admin | instar | 192.168.2.10    | 2021-10-15 03:46:36.603446 |      3 | HK
  2 | Harbour East  | admin | instar | 192.168.2.19    | 2021-10-15 03:46:36.603446 |      5 | HK
  3 | Harbour West  | admin | instar | 192.168.2.24    | 2021-10-15 03:46:36.603446 |      3 | HK
  4 | Beachfront    | admin | instar | 192.168.2.117   | 2021-10-15 03:46:36.603446 |      2 | HK
  5 | Downtown      | admin | instar | 192.168.178.70  | 2021-10-15 03:46:36.603446 |      4 | HK
  6 | Central Park  | admin | instar | 192.168.178.42  | 2021-10-15 03:46:36.603446 |      2 | HK
  7 | Terminal      | admin | instar | 192.168.178.52  | 2021-10-15 03:46:36.603446 |      2 | HK
  8 | Skyline       | admin | instar | 192.168.178.67  | 2021-10-15 03:46:36.603446 |      5 | HK
  9 | Plaza         | admin | instar | 192.168.178.249 | 2021-10-15 03:46:36.603446 |      3 | HK

Query Single Camera​

The function for a single camera looks like this:

./models/gocamDB.go

// Return one camera or error
func(m *DBModel) Get(id int) (*Camera, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()
	// SQL query to get one camera by id
	query := `SELECT "id", "name", "usr", "pass", "ip", "updated", "created", "rating" FROM camera WHERE "id"= $1`
	// Expect a single row in return from Receiver
	row := m.DB.QueryRowContext(ctx, query, id)
	// Create variable of type camera
	var camera Camera
	// And add the row that was returned from receiver to it
	err := row.Scan(
		&camera.ID,
		&camera.Name,
		&camera.Username,
		&camera.Password,
		&camera.Address,
		&camera.Created,
		&camera.Updated,
		&camera.Rating,
	)
	if err != nil {
		return nil, err
	}

	return &camera, nil
}

Now we have a function to connect to Postgres and fetch data from our database tables. Now we need to add a function to our Receiver that provide our route handlers with access to this data.

./src/api/main.go

...
type application struct {
	config config
	logger *log.Logger
	models models.Models
}
...
func main() {
  ...
	app := &application {
		config: cfg,
		logger: logger,
		models: models.NewModels(db),
	}
  ...
}

And now all we need is to replace our placeholder dummy data from the camera route handler with an call to this function:

./models/gocamDB.go

// Hardcode a camera to get started
// camera := models.Camera {
// 	ID: id,
// 	Name: "Mountain View",
// 	Username: "admin",
// 	Password: "instar",
// 	Address: "192.168.2.19",
// 	Rating: 5,
// 	Location: "Hongkong",
// 	Created: time.Date(2021, 9, 22, 0, 0, 0, 0, time.Local),
// 	Updated: time.Now(),
// }
camera, err := app.models.DB.Get(id)

And we now successfully retrieve our data from Postgres:

curl http://localhost:4000/v1/camera/6

{"camera":{"id":6,"name":"Central Park","usr":"admin","pass":"instar","ip":"192.168.178.42","installed":"2021-10-16T08:16:15.363764Z","inspected":"2020-08-07T08:16:15.363764Z","rating":2}}

Add Location to Camera Query​

./src/api/cameraHandler.go

// Get camera location
	query = `SELECT
				camera_locations.id,
				camera_locations.location,
				camera_locations.cid,
				camera_locations.lid,
				camera_model.model,
				camera_model.lense,
				camera_model.res
			FROM
				camera_locations
			INNER JOIN
				camera_model
			ON
				camera_locations.id = camera_model.id
			WHERE
				camera_locations.id=$1;`

	rows, _ := m.DB.QueryContext(ctx, query, id)
	defer rows.Close()

	var locations []Location
	for rows.Next() {
		var loc Location
		err := rows.Scan(
			&loc.ID,
			&loc.LocationName,
			&loc.CameraID,
			&loc.LocationID,
			// The following are of type CameraModel not Location
			// I now added them to the Location type in models.go
			// Not sure how to mix up types here. #TODO
			&loc.Model,
			&loc.Lense,
			&loc.Resolution,
		)
		if err != nil {
			return nil, err
		}
		locations = append(locations, loc)
	}

	camera.Location = locations

Update the Camera Type to display the location content:

./models/models.go

// BEFORE
Location			[]Location	`json:"-"`
// AFTER
Location			[]Location	`json:"location"`

The response from Postgres now also includes the location block:

curl http://localhost:4000/v1/camera/6

{
    "camera": {
        "id": 6,
        "name": "Central Park",
        "usr": "admin",
        "pass": "instar",
        "ip": "192.168.178.42",
        "installed": "2021-10-17T11:06:37.946572Z",
        "inspected": "2020-08-08T11:06:37.946572Z",
        "rating": 2,
        "location": [
            {
                "location": "Central Park",
                "cid": "INSTAR-0001",
                "lid": "HK-0003",
                "model": "IN-9008",
                "lense": "4.2mm",
                "res": "1080p"
            }
        ]
    }
}

Query All Cameras​

./models/gocamDB.go

This is mostly duplicated code - compared to the get-one-camera function and should be cleaned up a bit:

// Return all cameras
func(m *DBModel) All() ([]*Camera, error) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()

	// SQL query to get all cameras
	query := `SELECT
				"id", "name", "usr", "pass", "ip", "updated", "created", "rating"
			FROM camera ORDER BY created ASC`

	rows, err := m.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	var cameras []*Camera

	for rows.Next(){
		var camera Camera
		err := rows.Scan(
			&camera.ID,
			&camera.Name,
			&camera.Username,
			&camera.Password,
			&camera.Address,
			&camera.Created,
			&camera.Updated,
			&camera.Rating,
		)
		if err != nil{
			return nil, err
		}

		// Get camera location
		query = `SELECT
					camera_locations.id,
					camera_locations.location,
					camera_locations.cid,
					camera_locations.lid,
					camera_model.model,
					camera_model.lense,
					camera_model.res
				FROM
					camera_locations
				INNER JOIN
					camera_model
				ON
					camera_locations.id = camera_model.id
				WHERE
					camera_locations.id=$1;`

		rows, _ := m.DB.QueryContext(ctx, query, camera.ID)

		var locations []Location
		for rows.Next() {
		var loc Location
		err := rows.Scan(
			&loc.ID,
			&loc.LocationName,
			&loc.CameraID,
			&loc.LocationID,
			// The following are of type CameraModel not Location
			// I now added them to the Location type in models.go
			// Not sure how to mix up types here.
			&loc.Model,
			&loc.Lense,
			&loc.Resolution,
		)
		if err != nil {
			return nil, err
		}
		locations = append(locations, loc)
		}
		defer rows.Close()

		camera.Location = locations
		cameras = append(cameras, &camera)
	}

	return cameras, nil
}

We now need to prepare our route handler to use this function:

./src/api/cameraHandler.go

func (app *application) getAllCameras(w http.ResponseWriter, r *http.Request) {
	cameras, err := app.models.DB.All()
	if err != nil {
		app.errorJSON(w, err)
		return
	}

	err = app.writeJSON(w, http.StatusOK, cameras, "cameras")
	if err != nil {
		app.errorJSON(w, err)
		return
	}
}

We now have a response from Postgres on our /cameras route:

curl http://localhost:4000/v1/cameras

{
    "cameras": [
        {
            "id": 5,
            "name": "Downtown",
            "usr": "admin",
            "pass": "instar",
            "ip": "192.168.178.70",
            "install": "2021-10-17T11:06:37.946572Z",
            "inspect": "2020-07-07T11:06:37.946572Z",
            "rating": 4,
            "location": [
                {
                    "location": "Downtown",
                    "cid": "INSTAR-0005",
                    "lid": "HK-0003",
                    "model": "IN-9020",
                    "lense": "4.2mm",
                    "res": "1080p"
                }
            ]
        },
...