Common SQL mistakes

sql.Open

Mistake

This does not always open the connection, it merely prepares it for later use.

db, err := sql.Open("mysql", dsn)
if err != nil {
  return err
}

Fix

db, err := sql.Open("mysql", dsn)
if err != nil {
  return err
}

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

Connection pooling

sql.Open doesn’t return a single connection, but a pool of connections. We can modify how this pool behaves with the following methods:

Not using prepared statements

A prepared statement is a feature most SQL databases implement to execute a repeated SQL statement. This is in the interest of efficiency as well as it reduces the risk of SQL injections.

statement, err := db.Prepare("SELECT * FROM order WHERE ID = ?")
if err != nil {
  return err
}

rows, err := statement.Query(id)

Mishandling null values

Mistake

rows, err := db.Query("SELECT dept, age FROM emp WHERE id = ?", id)
if err != nil {
  return err
}

var (
  // this is problematic if dept can be NULL
  department string
  age int
)

for rows.Next() {
  err := rows.Scan(&department, &age)
  if err != nil {
    return err
  }
}

Fix

rows, err := db.Query("SELECT dept, age FROM emp WHERE id = ?", id)
if err != nil {
  return err
}

var (
  department *string
  age int
)

for rows.Next() {
  err := rows.Scan(&department, &age)
  if err != nil {
    return err
  }
}

Not handling row iteration errors

Mistake

func get(ctx context.Context, db *sql.DB, id string) (string, int, error) {
  rows, err := db.QueryContext(
    ctx,
    "SELECT dep, age FROM emp WHERE id = ?",
    id,
  )
  if err != nil {
    return "", 0, err
  }
  defer func(){
    err := rows.Close()
    if err != nil {
      // log this
    }
  }()

  var (
    department string
    age int
  )

  for rows.Next() {
    err := rows.Scan(&department, &age)
    if err != nil {
      return "", 0, err
    }
  }

  // we are missing the case if row.Next() fails for whatever reason

  return department, age, nil
}

Fix

func get(ctx context.Context, db *sql.DB, id string) (string, int, error) {
  rows, err := db.QueryContext(
    ctx,
    "SELECT dep, age FROM emp WHERE id = ?",
    id,
  )
  if err != nil {
    return "", 0, err
  }
  defer func(){
    err := rows.Close()
    if err != nil {
      // log this
    }
  }()

  var (
    department string
    age int
  )

  for rows.Next() {
    err := rows.Scan(&department, &age)
    if err != nil {
      return "", 0, err
    }
  }

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

  return department, age, nil
}

References