How to get last inserted row id in SQLite3?

Questions & Answers
1

Simple question: how can I get the (last) inserted row id using SQLite3 with Racket and the db module/package (whatever it is called)?

In SQLite3 there's the sqlite3_last_insert_rowid function. Is there any equivalent in Racket?

...or is there a different solution to the following transaction problem:

  insert a new row
  get the inserted row id
  insert another row, using the inserted row id
  ...
1 Like
2

Hi, @Tomek.

Is this what you mean?

#lang racket

(require db)

(define conn
  (sqlite3-connect
   #:database 'temporary
   #:mode     'create))

(query-exec conn
            "CREATE TEMPORARY TABLE the_numbers (n NUMBER, d TEXT)")

(query conn
       "INSERT INTO the_numbers VALUES (3, 'a crowd')")

; => (simple-result '((insert-id . 1) (affected-rows . 1)))

Found here: query.

3 Likes
3

Yes, I believe that's it.

Thanks.

I'm new to Racket, and I'm still evaluating it (comparing mostly to Python and C). Documentation is vast, and I'm still learning how to read it.

4

I'm glad.

For what it's worth, I came from Python/Java to Racket, and I also felt overwhelmed at first, but as you say the documentation is vast, and contains many a shiny pearl.

Happy reading!

5

Since SQLite 3.35, you can also use RETURNING: like

INSERT INTO the_numbers (n, d) VALUES (12, 'a dozen') RETURNING rowid;
4 Likes