Open in app

Sign in

Write

Sign in

Better practices for Go Database use

Mitch Dresdner
9 min readJun 9, 2020

After the basic getting started examples have you up and running, you wonder how to apply better practices to your projects.

Objectives

  • Use YAML to externalize your properties
  • Encode your secrets
  • Dependency inject your db handle into helpers
  • Separate concerns to promote ease of reuse

I come to the Go programming language from other languages and best practices learned along the way. When we go to Google to learn how to get started with something new we usually get just enough to get a solution up and running. The searches generally become more difficult when we want to harden a solution. It’s with that goal in mind that I set out to shed some light on the objectives above and hopefully learn some new tricks in the process.

There can be a lot of complex underlying technology that may need to be put in place for an author to properly explain their objectives. Because of that, the complexities may be mostly ignored or glossed over to focus on the main topic. In my post which follows I intend to do just that, to gloss over the complexity of configuring a Postgres database which we’ll use in these examples. We’ll also ignore the intricacies of cryptography, focusing instead on decoding our secrets.

With these caveats in place, feel free to use your own Postgres DB or install one demonstrated by Florin Pățan in his webcast Build a Microservice in Go with GoLand in an Hour. It was his webcast which inspired me it add some bells and whistles which a hardened application demands. He demonstrated a lot in an hour, but his project YAML for getting Postgress up and running in Docker or Kubernetes can get you a running Postgres database, if needed.

Given a running Postgres database, I wanted to add the connection properties to a YAML file. Externalizing properties allows you to create separate property files for different environments your application will run in. I’ve created a propety file for DEV, but you’ll likely need a different one for TEST and PROD as well. To work with YAML in my project, I chose the go-yaml package.

The ReadMe shows how to install the package, though at the time of this post the latest release is v3, not the suggested v2 install.

go get gopkg.in/yaml.v3

Here’s the project layout which i’m using, you can download the project from my GitHub repository here.

Project Layout

The files in the models folder will handle our connection to the Postgres database, loading some initial sample data and a query capability for returning the sample data.

The util folder contains code for reading the YAML file and decoding secrets.

The project reuses some concepts which may be familiar to you from my previous earlier article Zero Code REST, but this time we’ll have some real code and extend it in a future post to be REStful.

Lets take a look first at the properties file:

db:
  postgress:
    user: "goland"
    dbname: "goland"
    password: "![e22368dfc321a77dd097892778c2c9464d0512693f6e7b55159ccbc4d60b5357e20f]"
    host: "localhost"
    port: "30432"
    sslmode: "disable"

I’m using the same secret encoding scheme which MuleSoft uses in their secure properties plugin, if this is something of interest to you you can see more details in my wrapper using TornadoFx. For the security layer we’re leveraging Go’s built-in cypher tool chain. To encode the password i’m leveraging Astaxi’s advanced encryption/decryption example shown below.

package main

// https://astaxie.gitbooks.io/build-web-application-with-golang/en/09.6.html

import (
   "crypto/aes"
   "crypto/cipher"
   "crypto/rand"
   "errors"
   "fmt"
   "io"
   "log"
)

func main() {   text := []byte("goland")
   key := []byte("aaaaBBBB1234&^%$")

   ciphertext, err := encrypt(text, key)
   if err != nil {
      // TODO: Properly handle error
      log.Fatal(err)
   }
   fmt.Printf("%s => %x\n", text, ciphertext)

   plaintext, err := decrypt(ciphertext, key)
   if err != nil {
      // TODO: Properly handle error
      log.Fatal(err)
   }
   fmt.Printf("%x => %s\n", ciphertext, plaintext)
}

func encrypt(plaintext []byte, key []byte) ([]byte, error) {
   c, err := aes.NewCipher(key)
   if err != nil {
      return nil, err
   }

   gcm, err := cipher.NewGCM(c)
   if err != nil {
      return nil, err
   }

   nonce := make([]byte, gcm.NonceSize())
   if _, err = io.ReadFull(rand.Reader, nonce); err != nil {
      return nil, err
   }

   return gcm.Seal(nonce, nonce, plaintext, nil), nil
}

func decrypt(ciphertext []byte, key []byte) ([]byte, error) {
   c, err := aes.NewCipher(key)
   if err != nil {
      return nil, err
   }

   gcm, err := cipher.NewGCM(c)
   if err != nil {
      return nil, err
   }

   nonceSize := gcm.NonceSize()
   if len(ciphertext) < nonceSize {
      return nil, errors.New("ciphertext too short")
   }

   nonce, ciphertext := ciphertext[:nonceSize], ciphertext[nonceSize:]
   return gcm.Open(nil, nonce, ciphertext, nil)
}

If you’re using Florin’s database example, the password is Goland. It’s being encoded using AES-128 encryption using the 16 byte key.

text := []byte(“goland”)
key := []byte(“aaaaBBBB1234&^%$”)

This secret key will be passed to our application when it runs and used by our application to decode the secret in the YAML property file.

SECRET=aaaaBBBB1234&^%$

For performance reasons we should create our database connection once, around the time when the application is started. There are several other best practices which be adhered to when working with Databases on Go described in the Go Database/SQL Tutorial. For a better understanding on whether to use a Global to store the connection example there’s Alex Edwards Organizing Database Access.

We’ll be following the pattern for injecting the connection handle into the helper funcs. With the background out of the way let’s take a look at the code.

main.go

package main

import (
   "fmt"
   "github.com/mjd/winestore/models"
   "github.com/mjd/winestore/util"
   "log"
)

type Env struct {
   db models.Datastore
}

func main() {

   // 1 - Fetch database properties stored as YAML, decode secrets
   connStr, err := util.FetchYAML()
   if err != nil {
      log.Fatalf("error: %v", err)
   }

   // 2 - Connect to Postgres DB
   db, err := models.Connect(connStr)
   if err != nil {
      log.Panic(err)
   }

   // 3 - Save db handle for dependency injecting into supporting routines
   env := &Env{db}

   // 4 - Load some initial sample rows, truncates existing
   err = models.LoadDb(db)
   if err != nil {
      log.Fatalf("error: %v", err)
   }

   // 5 - Retrieve and display rows created
   env.getAllWine()

   err = models.Close(db)
}

func (env *Env) getAllWine() {

   wines, err := env.db.AllWines()
   if err != nil {
      log.Fatalf("error: %v", err)
   }

   for _, wine := range wines {
      fmt.Printf("%-25s %-69s Today Only $%4.2f\n", wine.Product, wine.Description, wine.Price)
   }
}

In main.go, we call FetchYaml(1) to read and decode the YAML properties. It returns a connection string which we’ll use to connect with the Postgres database using Connect(2). The Connect func returns a DB handle which we’ll save(3) for dependency injecting into the helper functions. We use the Load(4) func to populate some sample data. If any prior data exists the table is truncated. If you wish to preserve data, comment our the Load func after it’s called initially. Finally the getAllWine(5) func displays the sample data by running a query for all rows.

yaml-props.go

package util

import (
   "encoding/hex"
   "fmt"
   "gopkg.in/yaml.v3"
   "io/ioutil"
   "os"
   "strings"
)

type Config struct {
   Db struct {
      Pg map[string]string `yaml:"postgress"`
   }
}

func FetchYAML() (string, error) {

   dat, err := ioutil.ReadFile("./properties/dev-properties.yaml")
   if err != nil {
      return "", err
   }

   // Unmarshal YAML file into Config struct
   var config Config
   err = yaml.Unmarshal([]byte(dat), &config)
   if err != nil {
      return "", err
   }

   // Iterate over properties, decoding any secrets
   for k, v := range config.Db.Pg {
      // Extract and decode secrets which are wrapped in ![] (like Mule)
      if strings.HasPrefix(v, "![") && strings.HasSuffix(v, "]") {
         secret := strings.TrimRight(strings.TrimLeft(v, "!["), "]")
         // Convert hex representation of secret to uint8 byte array
         data, err := hex.DecodeString(secret)
         if err != nil {
            return "", err
         }
         decoded, e := decrypt(data, []byte(os.Getenv("SECRET")))
         if e != nil {
            return "", fmt.Errorf("error: %v", e)
         }

         // fmt.Printf("found %s\n", decoded)
         // Replace encoded map value with the decoded secret
         v = string(decoded)
         config.Db.Pg[k] = v
      }
      // fmt.Printf("key %s: %s\n", k, v)
   }

   // Create the database connection string, this one is for Postgres
   // ToDo: Create a general purpose function that handles other DB's
   return fmt.Sprintf("user=%s dbname=%s password=%s host=%s port=%s sslmode=disable",
      config.Db.Pg["user"], config.Db.Pg["dbname"], config.Db.Pg["password"],
      config.Db.Pg["host"], config.Db.Pg["port"]), err
}

The yaml-props.go func uses gopkg.in/yaml.v3 to do the heavy lifting, of unmarshaling out property file to a Config struct. The Postgres properties are represented as a Map, which we iterate over, replacing encoded secrets with plain text secrets. Finally, we compose a connection string used to Connect with Postgres.

pg-connect.go

package models

import (
   "database/sql"
   _ "github.com/lib/pq"
)

type Datastore interface {
   AllWines() ([]*Wine, error)
}

type DB struct {
   *sql.DB
}

func Connect(dataSourceName string) (*DB, error) {
   db, err := sql.Open("postgres", dataSourceName)
   if err != nil {
      return nil, err
   }

   if err = db.Ping(); err != nil {
      return nil, err
   }

   return &DB{db}, nil
   //return db, nil
}

func Close(db *DB) error {
   err := db.Close()
   return err
}

pg-connect is simple enough, it take the dataSourceName constructed earlier by FetchYAML and connect to Postgres. Database connections are done lazily, the Ping func forces a connection to be added to the pool.

load-wine-db

package models

import "fmt"

func LoadDb(db *DB) error {

   // Some sample data to add to our DB
   wines := []Wine{
      {
         Product:     "SOMMELIER SELECT",
         Description: "Old vine Cabernet Sauvignon",
         Price:       159.99,
      },
      {
         Product:     "MASTER VINTNER",
         Description: "Pinot Noir captures luscious aromas",
         Price:       89.99,
      },
      {
         Product:     "WINEMAKER'S RESERVE",
         Description: "Merlot featuring complex flavors of cherry",
         Price:       84.99,
      },
      {
         Product:     "ITALIAN SANGIOVESE",
         Description: "Sangiovese grape is famous for its dry, bright cherry character",
         Price:       147.99,
      },
   }

   // Check postgres to see if the table already exists
   var checkDatabase string
   err := db.QueryRow("SELECT to_regclass('public.winetbl')").Scan(&checkDatabase)
   if err != nil {
      return err
   }

   // Create table if DNE
   if checkDatabase == "" {
      createSQL := "CREATE TABLE public.winetbl (id SERIAL PRIMARY KEY,product character varying,pdesc character varying,price decimal);"
      if _, err = db.Query(createSQL); err != nil {
         panic(err)
      }
      fmt.Println("Wine Database Created")
   }

   // SQL prepared statement to insert wine data
   statement := "INSERT INTO winetbl(product, pdesc, price) VALUES($1, $2, $3)"

   // Create prepared statement for inserts
   stmt, err := db.Prepare(statement)
   if err != nil {
      return err
   }
   defer stmt.Close()

   // Start with a clean slate
   if _, err = db.Exec(`TRUNCATE TABLE winetbl`); err != nil {
      return err
   }

   // Insert static entries into database
   for idx := 0; idx < len(wines); idx++ {
      w := wines[idx]
      _, err := stmt.Exec(w.Product, w.Description, w.Price)
      if err != nil {
         return err
      }
   }

   if err = stmt.Close(); err != nil {
      return err
   }

   return nil
}

I added the Loader as a separate function, so it can be easily discarded once you have some initial data loaded. After an initial load it’s served it’s purpose.

wines.go

package models

import "fmt"

type Wine struct {
   Product string
   Description string
   Price float32
}

func (db *DB) AllWines() ([]*Wine, error) {
   // Query and display persisted posted
   rows, err := db.Query("SELECT product, pdesc, price from winetbl")
   if err != nil {
      return nil, err
   }

   err = fmt.Errorf("Query returns no data!")
   if rows == nil {
      return nil, err
   }

   wines := make([]*Wine, 0)

   fmt.Println("--- Wine Collection")
   defer  rows.Close()
   for rows.Next(){

      wine := new(Wine)

      err = rows.Scan(&wine.Product, &wine.Description, &wine.Price)
      if err != nil {
         fmt.Print(err)
      }

      wines = append(wines, wine)
   }

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

   return wines, nil
}

The wines.go file will be enriched to abstract the data model from our application. It currently contains a func AllWines() which runs a Postgres query for all. When I integrate this code into a REST application i’ll be building out the other CRUD operations.

Func getAllWine in main.go uses AllWines() to display the results.

The pattern we followed of separating our concerns allowed us to easily pull in pre built patterns and code fragments from Astaxie and Alex Edwards with a minimal amount of effort. As for securing your secrets, keep your secret key private and exercise caution in where your encrypted secrets are stored. If they’re in a public repository they may be vulnerable to offline brute force password attacks.

I hope you enjoyed learning these best practices and find them useful in your daily practices!

Go
Microservices
Devsecops

--

--

Mitch Dresdner

Written by Mitch Dresdner

20 Followers

is a techie enthusiast, who’s interests range from: building scalable secure systems to creating that favorite fermented beverage. He’s also an outdoors cat.

Help

Status

About

Careers

Blog

Privacy

Terms

Text to speech

Teams