Go: Using LastInsertId() with Postgres in sqlx

When using the sqlx package in Go with a Postgres database, you may come across the following error:

lastinsertid is not supported by this driver postgres

This error will happen when you want to access the LastInsertId() of a newly inserted entity. In this post, I will show you how to fix this error without making any changes to your database.

Let’s investigate a specific scenario where this error will occur:

func CreateAppointment(appointment Appointment) int {
  result, err := database.Db.NamedExec("INSERT INTO appointments (firstName, lastName, appointmentDateTime) VALUES (:firstName, :lastName, :appointmentDateTime)", appointment)
  if err != nil {
    log.Fatalln(err)
  }

  id, err := result.LastInsertId()
  if err != nil {
    log.Fatalln(err)
  }

  return id
}

The CreateAppointment function tries to persist a new entry to the Postgres database. Then, it returns the id of the newly persisted object.

But the above code does not work; sqlx will complain about Postgres not supporting the LastInsertId() command.

PrepareNamed to the rescue

However, there is a fix to this problem.

First, we need to modify the INSERT INTO statement. Postgres requires a more explicit description of what we want to return after the insertion is completed:

INSERT INTO appointments (firstname, lastname,
  appointmentdatetime) VALUES (:firstname, :lastname,
  :appointmentdatetime) RETURNING id

By adding RETURNING id, we tell Postgres that the id field of the entry must be returned to the client.

But this fix does not work for the NamedExec function. The creator of the sqlx library mentioned a fix in October 2015 on the official sqlx Github page:

Basically we need to use the PrepareNamed function instead:

func CreateAppointment(appointment Appointment) id {
  stmt, err := database.Db.PrepareNamed("INSERT INTO appointments (firstname , lastname, appointmentdatetime) VALUES (:firstname, :lastname, :appointmentdatetime) RETURNING id")

  var id int
  err = stmt.Get(&id, appointment)

  if err != nil {
    log.Fatalln(err)
  }
  return id
}

With both these changes, the id is now returned properly.