Sqlx: Retrieve ID from insert to DB

Created on 8 Sep 2014 · 4Comments · Source: jmoiron/sqlx

Hi!

I have this insert into the database:

user := new(User)
res, err := db.NamedExec("INSERT INTO users (email) VALUES (:email) RETURNING id", user)

And I'd like to get the ID of that record. I believe I'm using the RETURNING id incorrectly. Is this the correct way to do it via db.NamedExec? If not, what are the proper ways to do so?

Thanks!
@luiscvega

Source

luiscvega

Most helpful comment

Hi Luis,

Try using NamedQuery instead and scanning the resulting rows:

var id int
var user User
rows, err := db.NamedQuery("INSERT INTO users (email) VALUES (:email) RETURNING id", user)
// handle err
if rows.Next() {
    rows.Scan(&id)
}

You could also use a prepared named statement which you can then use Get with.

Hope this helps!

jmoiron on 8 Sep 2014

👍16 ❤2 👀1 🚀1

All 4 comments

Hi Luis,

Try using NamedQuery instead and scanning the resulting rows:

var id int
var user User
rows, err := db.NamedQuery("INSERT INTO users (email) VALUES (:email) RETURNING id", user)
// handle err
if rows.Next() {
    rows.Scan(&id)
}

You could also use a prepared named statement which you can then use Get with.

Hope this helps!

jmoiron on 8 Sep 2014

👍16 ❤2 👀1 🚀1

Thanks jmoiron! Really great job, especially with http://go-database-sql.org!

luiscvega on 9 Sep 2014

@jmoiron Hi
When the insert fails you still get the id returned. err is also nil.
How can you tell if the insert was not successful (e.g. due to a foreign key constraint)?

dontogbe on 13 Aug 2017

👍6

@jmoiron I am running into the same issue as @dontogbe - I am using NamedQuery() to insert values into a table such that a foreign key constraint is violated.

However, the error returned by NamedQuery() is nil, and the issue can only be caught when the transaction is being committed.

Is there a way for us to catch this error sooner?