Clever SQL Handling with Golang

In this tutorial, I will share some tips for constructing queries in a reusable way.

Object Relational Mappers are great, but sometimes we want more control from the underlying libraries and database. In a recent project, I was using Gorm, a Golang ORM, and while it was great I realized I needed more customizations than I knew I could do with Gorm. In my search, I moved to Sqlx, then Sqlc, before finally deciding to write my own custom SQL.

Don’t get me wrong — I'm certain my problems could be solved with these libraries. The problem is, that it would require more research than I am willing to invest in, and let’s face it, more issues would come up in the future. Most importantly, I felt more comfortable working with the standard library knowing that I could combine basic solutions to solve bigger problems.

Insert Data

To insert data with the standard pgxpool (or pgx) library, we have code similar to this:

type userPgx struct {
  db *pgxpool.Pool
}

func (u userPgx) Create(user models.User) (models.User, error) {
 query := `INSERT INTO users (first_name, last_name, email, phone, country, password) VALUES (@firstName, @lastName, @email, @phone, @country, @password) RETURNING id, created_at, updated_at`
 args := pgx.NamedArgs{
  "firstName": user.FirstName,
  "lastName":  user.LastName,
  "email":     user.Email,
  "phone":     user.Phone,
  "country":   user.Country,
  "password":  user.Password,
 }

 err := u.db.QueryRow(context.TODO(), query, args).Scan(&user.ID, &user.CreatedAt, &user.UpdatedAt)
 if err != nil {
  return models.User{}, err
 }

 return user, nil
}

A way to make this reusable is to dynamically generate the insert query and allow any table or model to use the method. Here’s the method below and how to use it:

package db

import (
 "fmt"
 "strings"

 "github.com/jackc/pgx/v5"
 "github.com/samber/lo"
)

func GenerateInsertQuery(tablename string, values map[string]interface{}) (string, pgx.StrictNamedArgs) {
 query := `INSERT INTO ` + tablename

 mapKeys := lo.Keys(values)

 namedArgs := lo.Map(mapKeys, func(elem string, index int) string {
  return "@" + elem
 })

 keys := strings.Join(mapKeys, ", ")
 argKeys := strings.Join(namedArgs, ", ")
 query = fmt.Sprintf("%s (%s) VALUES (%s) RETURNING id, created_at, updated_at", query, keys, argKeys)

 args := pgx.StrictNamedArgs(values)

 return query, args
}

func (u userPgx) Create(user models.User) (models.User, error) {
 args := map[string]interface{}{
  "first_name": user.FirstName,
  "last_name":  user.LastName,
  "email":      user.Email,
 }
 query, queryArgs := db.GenerateInsertQuery("users", args)

 err := u.db.QueryRow(context.TODO(), query, queryArgs).Scan(&user.ID, &user.CreatedAt, &user.UpdatedAt)
 if err != nil {
  return models.User{}, err
 }

 return user, nil
}

I used the samber/lo library to manipulate the map by getting the map keys as a slice and then adding an ‘@’ to create a named argument placeholder. With this method, you can generate an insert query for any table with any fields.

Filter records

To filter records in a table, we need to generate a query dynamically. Here’s how I do it:

func (u userPgx) Filter(user map[string]interface{}) ([]models.User, error) {
 query := `SELECT * FROM users WHERE 1=1`
 for k := range user {
  query = fmt.Sprintf("%s AND %s = @%s", query, k, k)
 }
 args := pgx.NamedArgs(user)

 rows, err := u.db.Query(context.TODO(), query, args)
 if err != nil {
  return nil, fmt.Errorf("user Filter Query: %w", err)
 }
 users, err := getMultipleUsers(rows)
 return users, err
}

func getMultipleUsers(rows pgx.Rows) ([]models.User, error) {
 var users []models.User
 for rows.Next() {
  var user models.User
  err := rows.Scan(&user.ID, &user.CreatedAt, &user.UpdatedAt, &user.FirstName, &user.LastName, &user.Email)
  if err != nil {
   return users, fmt.Errorf("rows.Scan: %w", err)
  }
  users = append(users, user)
 }

 return users, nil
}

Getting all records is simpler:

func (u userPgx) GetAll() ([]models.User, error) {
 query := `SELECT * FROM users`
 rows, err := u.db.Query(context.TODO(), query)
 if err != nil {
  return nil, fmt.Errorf("users GetAll: %w", err)
 }

 return getMultipleUsers(rows)
}

Update Records

To update records, I also dynamically generated a query in this fashion:

func (u userPgx) Update(user models.User, updates map[string]interface{}) (models.User, error) {
 query := `UPDATE users SET`
 for k := range updates {
  query = fmt.Sprintf("%s %s = @%v,", query, k, k)
 }
 query = fmt.Sprintf("%s updated_at = '%s' WHERE id = %d", query, time.Now().Format(time.RFC3339), user.ID)

 log.Println(query)

 args := pgx.NamedArgs(updates)
 _, err := u.db.Exec(context.TODO(), query, args)
 if err != nil {
  return models.User{}, fmt.Errorf("db.Exec: %w", err)
 }
 return models.User{}, nil
}

Delete Record

func (u userPgx) Delete(user models.User) error {
 query := `DELETE FROM users WHERE id = $1`
 _, err := u.db.Exec(context.TODO(), query, user.ID)
 if err != nil {
  return fmt.Errorf("error deleting user: %w", err)
 }
 return nil
}

Conclusion

It is often better to use established solutions than reinvent the wheel — that’s why I’m writing Go and not Assembly. However, when the trade-off is high, or in my case, it is genuinely easier to do it yourself, it’s better to take the custom route.