This tutorial demonstrates how to ‘auto migrate’ the DB and how to store and retrieve data  from JSONB field in postgreSQL database using GoLang.

Auto migrating the DB

In the context of Go programming language and GORM (Go Object Relational Mapping) library, automigration is a feature that automatically creates or updates database tables based on the Go struct definitions.

For the purpose of this example, we will create a table with id, username, email and meta data field. The meta data will be a JSONB field. We could use JSON as well but JSONB is stored in binary format, and although insert operations are slower searching is faster.

In general it is recommended to always use JSONB unless we have a real good reason to use JSON. For example JSON preserves formatting and allows for duplicate keys.

But before we could use JSONB with GORM we have to install the package

go get github.com/jackc/pgtype

Create a connection to PostgreSQL database.

Gorm supports different databases but here will do this exercise with PostgreSQL only.

func connectToPostgreSQL() (*gorm.DB, error) {
    dsn := "user=toninichev dbname=tests host=localhost port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        return nil, err
    }

    return db, nil
}

Define the structure that will be used to create the the Users table

type User struct {
    ID       uint   `gorm:"primaryKey"`
    Username string `gorm:"unique"`
    Email    string
    Age      int
    MetaData pgtype.JSONB `gorm:"type:jsonb" json:"fieldnameofjsonb"`
}

 

Now let’s create two helper functions that will create a new user:

func createuserWithMetaData(db *gorm.DB, username string, email string, metaData string) User {
    jsonData := pgtype.JSONB{}
    err := jsonData.Set([]byte(metaData))
    if err != nil {
        log.Fatal(err)
    }
    // Create a user
    newUser := User{Username: username, Email: email, Age: 36, MetaData: jsonData}
    err = createUser(db, &newUser)
    if err != nil {
        log.Fatal(err)
    }
    return newUser
}

func createUser(db *gorm.DB, user *User) error {
    result := db.Create(user)
    if result.Error != nil {
        return result.Error
    }
    return nil
}

And let’s put it all together:

The entire code

package main

import (
    "log"

    "github.com/jackc/pgtype"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type User struct {
    ID       uint   `gorm:"primaryKey"`
    Username string `gorm:"unique"`
    Email    string
    Age      int
    MetaData pgtype.JSONB `gorm:"type:jsonb" json:"fieldnameofjsonb"`
}

func connectToPostgreSQL() (*gorm.DB, error) {
    dsn := "user=toninichev dbname=tests host=localhost port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        return nil, err
    }

    return db, nil
}

func createuserWithMetaData(db *gorm.DB, username string, email string, metaData string) User {
    jsonData := pgtype.JSONB{}
    err := jsonData.Set([]byte(metaData))
    if err != nil {
        log.Fatal(err)
    }
    // Create a user
    newUser := User{Username: username, Email: email, Age: 36, MetaData: jsonData}
    err = createUser(db, &newUser)
    if err != nil {
        log.Fatal(err)
    }
    return newUser
}

func createUser(db *gorm.DB, user *User) error {
    result := db.Create(user)
    if result.Error != nil {
        return result.Error
    }
    return nil
}

func getUserByID(db *gorm.DB, userID uint) (*User, error) {
    var user User
    result := db.First(&user, userID)
    if result.Error != nil {
        return nil, result.Error
    }
    return &user, nil
}

func updateUser(db *gorm.DB, user *User) error {
    result := db.Save(user)
    if result.Error != nil {
        return result.Error
    }
    return nil
}

func deleteUser(db *gorm.DB, user *User) error {
    result := db.Delete(user)
    if result.Error != nil {
        return result.Error
    }
    return nil
}

func autoMigrateDB(db *gorm.DB) {
    // Perform database migration
    err := db.AutoMigrate(&User{})
    if err != nil {
        log.Fatal(err)
    }
}

func main() {

    db := func() *gorm.DB {
        db, err := connectToPostgreSQL()
        if err != nil {
            log.Fatal(err)
        }
        return db
    }()

    autoMigrateDB(db)

    //CRUD operations

    func() {
        newUser := createuserWithMetaData(db, "Toni", "toni@gmail.com", `{"key": "value", "days":[{"dayOne": "1"}], "user-id": "1"}`)
        log.Println("Created user:", newUser)
    }()

    func() {
        newUser := createuserWithMetaData(db, "John", "john@gmail.com", `{"key": "value two", "days":[{"dayOne": "2"}], "user-id": "2"}`)
        log.Println("Created user:", newUser)
    }()

    func() {
        newUser := createuserWithMetaData(db, "Sam", "sam@gmail.com", `{"key": "value three", "days":[{"dayOne": "3"}], "user-id": "3"}`)
        log.Println("Created user:", newUser)
    }()

    // Query user by ID
    user, err := getUserByID(db, 2)
    if err != nil {
        log.Fatal(err)
    }
    log.Println("User by ID:", user)

    var result User
    db.Where("meta_data->>'user-id' = ?", "2").First(&result)
    log.Println(result)
}

 

Updates

We can use byte type instead of pgtype libray which simplifies the code a bit.

type Users struct {
    ID       uint   `gorm:"primaryKey"`
    Username string `gorm:"unique"`
    MetaData []byte `gorm:"type:jsonb" json:"meta-data"`
}

 

func createuserWithMetaData(db *gorm.DB) bool {
    metaData := "{\"one\":\"1\"}"
    // Create a user
    newUser := Users{
        Username: "TEST 123",
        MetaData: []byte(metaData),
    }

    db.Create(&newUser)

    return true
}