DEV Community

Ganesh Kumar
Ganesh Kumar

Posted on

Good Practices For Connecting Go Server To Postgres

Preparing a PG DB and Go backend

Lets Create Bookstore Database where we fetch book details

Creating a BookStore DB in PostgreSQL

I'll start with the installation

$ sudo apt install postgresql
$ sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

Logging into postgres

psql -h localhost -U postgres

Enter fullscreen mode Exit fullscreen mode

Postgres console will be activated

psql (14.11 (Ubuntu 14.11-0ubuntu0.22.04.1))
Type "help" for help.
postgres=# 
Enter fullscreen mode Exit fullscreen mode

Create DB name bookstore by this command

postgres=CREATE DATABASE bookstore;
CREATE DATABASE
Enter fullscreen mode Exit fullscreen mode

Check out the DB and create Table

postgres=# \c bookstore
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to the database "bookstore" as user "postgres".
Enter fullscreen mode Exit fullscreen mode

Create a Table in the bookstore DB

CREATE TABLE books (
    isbn char(14) NOT NULL,
    title varchar(255) NOT NULL,
    author varchar(255) NOT NULL,
    price decimal(5,2) NOT NULL
);

INSERT INTO books (isbn, title, author, price) VALUES
('9780099470464', 'A House for Mr. Biswas', 'V. S. Naipaul', 8.99),
('9780143037346', 'Miss New India', 'Bharati Mukherjee', 9.99),
('9781784782781', 'The Lives of Others', 'Neel Mukherjee', 11.99),

ALTER TABLE books ADD PRIMARY KEY (isbn);
Enter fullscreen mode Exit fullscreen mode

Then Verify whether it is created

bookstore-# \dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | books | table | postgres
(1 row)
Enter fullscreen mode Exit fullscreen mode

Setting up Go backend

$ mkdir bookstore && cd bookstore
$ mkdir models
$ touch main.go models/models.go
$ go mod init go.backend
go: creating new go.mod: module go.backend
Enter fullscreen mode Exit fullscreen mode

File Structure of example :

bookstore/
├── go.mod
├── go.sum
├── main.go
└── models
    └── models.go
Enter fullscreen mode Exit fullscreen mode

Set a Global DB Instance

This method simplifies accessing the database connection across the application.

It initializes the database connection, sets up an HTTP server, and listens for incoming requests.

In the context of our bookstore application, the code would look something like this

// models/models.go
package models

import (
    "database/sql"
)
Enter fullscreen mode Exit fullscreen mode

Create an exported global variable to hold the database connection pool.

// models/models.go
var DB *sql.DB

type Book struct {
    Isbn   string
    Title  string
    Author string
    Price  float32
}
Enter fullscreen mode Exit fullscreen mode

AllBooks() returns a slice of all books in the books table.

// models/models.go
func AllBooks() ([]Book, error) {
    // Note that we are calling Query() on the global variable.
    rows, err := DB.Query("SELECT * FROM books")
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var bks []Book

    for rows.Next() {
        var bk Book

        err := rows.Scan(&bk.Isbn, &bk.Title, &bk.Author, &bk.Price)
        if err != nil {
            return nil, err
        }

        bks = append(bks, bk)
    }
    if err = rows.Err(); err != nil {
        return nil, err
    }

    return bks, nil
}              
Enter fullscreen mode Exit fullscreen mode
// main.go
package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"

    "go.backend/models"

    _ "github.com/lib/pq"
)

Enter fullscreen mode Exit fullscreen mode

Install dependencies

$ go get github.com/lib/pq
Enter fullscreen mode Exit fullscreen mode

Initialize the sql.DB connection pool and assign it to the models.DB

func main() {
    var err error
    // global variable.
    models.DB, err = sql.Open("postgres", "postgres://user:pass@localhost/bookstore")
    if err != nil {
        log.Fatal(err)
    }

    http.HandleFunc("/books", booksIndex)
    http.ListenAndServe(":3000", nil)
}
Enter fullscreen mode Exit fullscreen mode

When I make a request books

$ curl localhost:3000/books
978-1503261969, Emma, Jayne Austen, £9.44
978-1505255607, The Time Machine, H. G. Wells, £5.99
978-1503379640, The Prince, Niccolò Machiavelli, £6.99
Enter fullscreen mode Exit fullscreen mode

Remember global variable for the database connection is suitable when:

  • Your project is simple and small, so tracking global variables isn't hard.
  • Your code handling web requests is split across different folders, but all database actions stay in one folder.
  • You don't need to pretend the database isn't there for testing.

Global variable with an InitDB function

A variation on the 'global variable' approach that I sometimes see uses an initialization function to set up the connection pool, like so:

  • All database stuff is in one place.
  • The global database variable is hidden from other parts of the program, so it can't be changed by mistake.
  • During testing, you can easily set up a test database connection using a special function.
// models/models.go
package models

import (
    "database/sql"

    _ "github.com/lib/pq"
)
Enter fullscreen mode Exit fullscreen mode

Initialize the sql.DB connection pool and assign it to the models.DB

// This time the global variable is unexported.
var db *sql.DB

// InitDB sets up setting up the connection pool global variable.
func InitDB(dataSourceName string) error {
    var err error

    db, err = sql.Open("postgres", dataSourceName)
    if err != nil {
        return err
    }

    return db.Ping()
}
Enter fullscreen mode Exit fullscreen mode

Continue Reading

Top comments (0)