issue_comments: 1294296767

This data as json

html_url issue_url id node_id user created_at updated_at author_association body reactions issue performed_via_github_app
https://github.com/simonw/datasette/issues/1866#issuecomment-1294296767 https://api.github.com/repos/simonw/datasette/issues/1866 1294296767 IC_kwDOBm6k_c5NJWa_ 9599 2022-10-28T01:22:25Z 2022-10-28T01:23:09Z OWNER

Nasty catch on this one: I wanted to return the IDs of the freshly inserted rows. But... the insert_all() method I was planning to use from sqlite-utils doesn't appear to have a way of doing that:

https://github.com/simonw/sqlite-utils/blob/529110e7d8c4a6b1bbf5fb61f2e29d72aa95a611/sqlite_utils/db.py#L2813-L2835

SQLite itself added a RETURNING statement which might help, but that is only available from version 3.35 released in March 2021: https://www.sqlite.org/lang_returning.html - which isn't commonly available yet. https://latest.datasette.io/-/versions right now shows 3.34, and https://lite.datasette.io/#/-/versions shows 3.27.2 (from Feb 2019).

Two options then:

  1. Even for bulk inserts do one insert at a time so I can use cursor.lastrowid to get the ID of the inserted record. This isn't terrible since SQLite is very fast, but it may still be a big performance hit for large inserts.
  2. Don't return the list of inserted rows for bulk inserts
  3. Default to not returning the list of inserted rows for bulk inserts, but allow the user to request that - in which case we use the slower path

That third option might be the way to go here.

I should benchmark first to figure out how much of a difference this actually makes.

 
{
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
 1426001541