Databases with Golang

Three approaches to use databases with Golang.

Everything by hand with use pgx or pq libs

How to use

get/put queries with pgx

if _, err := conn.Exec(`
   INSERT INTO 
    users 
    (email, username, password, status, age, created_at) 
   VALUES ($1,$2,$3,$4,$5,$6)`,
   u.Email,
   u.Username,
   u.Password,
   u.Status,
   u.Age,
   u.CreatedAt); err != nil {
   return err
  }
if err := conn.QueryRow(`
  SELECT 
   id, 
   email,
   username,
   password,
   status,
   age,
   created_at
  FROM
   users
  WHERE
   id=$1;`, id).Scan(&u.ID,
   &u.Email,
   &u.Username,
   &u.Password,
   &u.Status,
   &u.Age,
   &u.CreatedAt); err != nil {
   return err
  }

Advantage

• works fast

Bottleneck

• Lots of code
• Need to write queries
• Marshal rows
• pgx only PostgreSQL

You only write queries with jmoiron/sqlx

How to use

Can be used with the pq

"github.com/jmoiron/sqlx"
 _ "github.com/lib/pq"

or pgx driver

_ "github.com/jackc/pgx/stdlib"
 "github.com/jmoiron/sqlx"

Need describe tags

type User struct {
 ID        int       `db:"id"`
 Email     string    `db:"email"`
 Username  string    `db:"username"`
 Password  string    `db:"password"`
 Status    int       `db:"status"`
 Age       int       `db:"age"`
 CreatedAt time.Time `db:"created_at"`
}

get/put queries with sqlx

u := &User{}
if _, err := conn.NamedExec(`
  INSERT INTO
   users (email, username, password, status, age, created_at)
  VALUES 
   (:email, :username, :password, :status, :age, :created_at)`, u); err != nil {
   return err
  }
if err := conn.Get(u, `
  SELECT
   id,
   email,
   username,
   password,
   status,
   age,
   created_at
  FROM
   users 
  WHERE
   id=$1;`, id); err != nil {
   return err
  }

Advantage

• Works fast
• Marshal rows into structs
• Serialing struct/Slice to rows

Bottleneck

• Need to write queries

Working with models use GORM

How to use

graceful get/put

if err := conn.Create(&us).Error; err != nil {
   return err
  }
if err := conn.Where(&model.User{ID: 10}).
      First(&user).
      Error; err != nil {
   return err
  }

Advantage

• Dont to write queries
• Marshal rows into structs
• Serialing struct/Slice to rows

Bottleneck

• Works slower

Let’s compare these approaches. How much slower is GORM and are there so many allocations? Is there also a lot of allocations in sqlx?

Testing with PostgreSQL

pgx has few allocations and good speeds

sqlx with pgx have more allocations and good speeds

sqlx with pq are slightly slower

GORM is not so slow but there are a lot of allocations

pq/pgx work fast and cause few allocations but needs to write queries and marshal rows.
GORM works slower calls many allocations, and you have no idea what is going on.
sqlx gives the perfect balance between being simple and being convenient.