Category Archives: GoLang

GraphQL server with Gqlgen and PostgreSQL

Create graphQL server using gqlgen following gqlgen tutorial

mkdir gqlgen-users
cd gqlgen-users
go mod init github.com/[username]/gqlgen-users

Create tools.go with gqlgen library imported.

tools.go

//go:build tools
// +build tools

package tools

import (
    _ "github.com/99designs/gqlgen"
)

Install packages

go mod tidy

Create the project skeleton

go run github.com/99designs/gqlgen init

Create database connector

databaseConnector/databaseConnector.go

package databaseConnector

import (
    "fmt"

    "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 autoMigrateDB(db *gorm.DB) {
    // Perform database migration
    err := db.AutoMigrate(&User{})
    if err != nil {
        fmt.Println(err)
    }
}

func connectToPostgreSQL() (*gorm.DB, error) {
    // dsn := "user=mynews password=test123 dbname=tests host=localhost port=5432 sslmode=disable"
    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, age int, metaData string) (*User, error) {
    jsonData := pgtype.JSONB{}
    err := jsonData.Set([]byte(metaData))
    if err != nil {
        return nil, err
    }
    // Create a user
    newUser := User{Username: username, Email: email, Age: age, MetaData: jsonData}
    err = createUser(db, &newUser)
    if err != nil {
        return nil, err
    }
    return &newUser, nil
}
func createUser(db *gorm.DB, user *User) error {
    result := db.Create(user)
    if result.Error != nil {
        return result.Error
    }
    return nil
}

func CreateDB(tableName string) error {
    db, err := connectToPostgreSQL()
    if err != nil {
        return err
    }
    autoMigrateDB(db)
    return nil
}

func CreateUser(username string, email string, age int, metaData string) (*User, error) {
    db, err := connectToPostgreSQL()
    if err != nil {
        return nil, err
    }
    user, err := createuserWithMetaData(db, username, email, age, metaData)
    return user, err
}

func GetUserByID(userID uint) (*User, error) {
    db, err := connectToPostgreSQL()
    if err != nil {
        return nil, err
    }

    var user User
    result := db.First(&user, userID)
    if result.Error != nil {
        return nil, result.Error
    }
    return &user, nil
}

func GetUserByMetaData(metaDataFilter string) (*User, error) {
    db, err := connectToPostgreSQL()
    if err != nil {
        return nil, err
    }

    var user User
    // result := db.First(&user, userID)

    result := db.Where(metaDataFilter).First(&user)

    if result.Error != nil {
        return nil, result.Error
    }
    return &user, nil
}

Edit schema adding the new Customer type, queries and mutations to retrieve and create new users.

graph/schema.graphqls

# GraphQL schema example
#
# https://gqlgen.com/getting-started/


type Customer {
  customerId: String!
  username: String!
  email: String!,
  age: Int!
  metaData: String!
}

input NewCustomer {
  customerId: String!
  username: String!
  email: String!,
  age: Int!
  metaData: String!
}

type Query {
  getCustomer(customerId: String!): Customer!
  getCustomerByMetaData(metaData: String!): Customer!
}

type Mutation {
  saveCustomer(input: NewCustomer!):Boolean!
  createDB(tableName: String!):Boolean!
}

Re-generate resolvers with the new schema

go run github.com/99designs/gqlgen generate

Implement the resolvers

graph/schema.resolvers.go

package graph

// This file will be automatically regenerated based on the schema, any resolver implementations
// will be copied through when generating and any unknown code will be moved to the end.
// Code generated by github.com/99designs/gqlgen version v0.17.44

import (
    "context"
    "strconv"
    "tutorials/gqlgen-users/databaseConnector"
    "tutorials/gqlgen-users/graph/model"
)

// SaveCustomer is the resolver for the saveCustomer field.
func (r *mutationResolver) SaveCustomer(ctx context.Context, input model.NewCustomer) (bool, error) {
    databaseConnector.CreateUser(input.Username, input.Email, input.Age, input.MetaData)
    return true, nil
}

// CreateDb is the resolver for the createDB field.
func (r *mutationResolver) CreateDb(ctx context.Context, tableName string) (bool, error) {
    err := databaseConnector.CreateDB(tableName)

    if err != nil {
        // handle error
        return false, err
    }
    return true, nil
}

// GetCustomer is the resolver for the getCustomer field.
func (r *queryResolver) GetCustomer(ctx context.Context, customerID string) (*model.Customer, error) {
    cid, _ := strconv.Atoi(customerID)
    var customer *databaseConnector.User
    var err error
    customer, err = databaseConnector.GetUserByID(uint(cid))

    if err != nil {
        // handle error
        return nil, err
    }

    // get the underlying byte slice.
    jsonbText, _ := customer.MetaData.Value()
    // Convert byte slice to string
    jsonString := string(jsonbText.([]byte))

    // map returned customer structure from the DB into the model
    c := model.Customer{
        CustomerID: strconv.FormatUint(uint64(customer.ID), 10),
        Username:   customer.Username,
        Email:      customer.Email,
        Age:        customer.Age,
        MetaData:   jsonString,
    }

    return &c, nil
}

// GetCustomerByMetaData is the resolver for the getCustomerByMetaData field.
func (r *queryResolver) GetCustomerByMetaData(ctx context.Context, metaData string) (*model.Customer, error) {
    customer, err := databaseConnector.GetUserByMetaData(metaData)

    if err != nil {
        // handle error
        return nil, err
    }

    // get the underlying byte slice.
    jsonbText, _ := customer.MetaData.Value()
    // Convert byte slice to string
    jsonString := string(jsonbText.([]byte))

    // map returned customer structure from the DB into the model
    c := model.Customer{
        CustomerID: strconv.FormatUint(uint64(customer.ID), 10),
        Username:   customer.Username,
        Email:      customer.Email,
        Age:        customer.Age,
        MetaData:   jsonString,
    }

    return &c, nil
}

// Mutation returns MutationResolver implementation.
func (r *Resolver) Mutation() MutationResolver { return &mutationResolver{r} }

// Query returns QueryResolver implementation.
func (r *Resolver) Query() QueryResolver { return &queryResolver{r} }

type mutationResolver struct{ *Resolver }
type queryResolver struct{ *Resolver }

Using GORM library to access postgreSQL with JSONB field with 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
}

Toni-Develops

My personal developing blog