Mysql / Ruby Sequel last insert ID value, what method?

Question

I just want to get the last_insert_id() using Ruby's Sequel:

insertret = @con.run("INSERT INTO `wv_persons` ( `id` ) VALUES ( NULL )")
pp insertret.inspect # returns "nil", expected that..
last_insert_id = @con.run("SELECT LAST_INSERT_ID() AS last_id;")
pp last_insert_id.inspect # returns "nil", should be an ID

The SELECT query should return the last_id but .run does not return it. What method should I use instead?

Solution: (thanks to Josh Lindsey)

last_insert_id = @con[:wv_persons].insert({})
last_insert_id = last_insert_id.to_s
puts "New person ["+ last_insert_id  +"]"

Camorra

Answer 1

The Dataset#insert method should return the last insert id:

DB[:wv_persons].insert({})

Will insert the default values and return the id.

Database#run will always return nil.

Gastrology

Answer 2

Actually, Database#insert is not guaranteed to return the id of the last inserted record.

From the documentation: "...Inserts values into the associated table. The returned value is generally the value of the primary key for the inserted row, but that is adapter dependent."

Jurado

Answer 3

The sequel gem is supposed to return the id of newly inserted records but as others said:

• the returned value is adapter dependent

also i'd like to add...

• isn't sure what to return when faced with a composite primary key

You can get around this by telling sequel exactly what should be returned using the #returning method.

For instance:

DB[:posts].returning(:id).insert(category_id: 5, id: 1, ...)

will return [{id: 1}]

and

DB[:posts].returning(:id, :category_id).insert(category_id: 5, id: 1, ...)

will return [{id: 1, category_id: 5}]

Ichneumon