How do I get the last insert ID for an autoincrement ID column?

[ksze]

Please answer these questions before submitting your issue. Thanks!

1. What version of NodeJS are you using (node --version and npm --version)? node v18.12.1 npm 8.19.2

2. What operating system and processor architecture are you using?

   Linux:
   No LSB modules are available.
   Distributor ID: Ubuntu
   Description:    Ubuntu 20.04.5 LTS
   Release:        20.04
   Codename:       focal

3. What are the component versions in the environment (npm list)?

   ├── @babel/preset-env@7.20.2
   ├── @babel/preset-typescript@7.18.6
   ├── @shopify/eslint-plugin@42.0.2
   ├── @types/async-retry@1.4.5
   ├── @types/chai@4.3.4
   ├── @types/compression@1.7.2
   ├── @types/cors@2.8.13
   ├── @types/express@4.17.15
   ├── @types/lodash@4.14.191
   ├── @types/luxon@3.1.0
   ├── @types/mocha@10.0.1
   ├── @types/morgan@1.9.3
   ├── @types/node@18.11.15
   ├── @types/snowflake-sdk@1.6.10
   ├── @types/swagger-ui-express@4.1.3
   ├── @types/yamljs@0.2.31
   ├── @typescript-eslint/eslint-plugin@5.46.1
   ├── @typescript-eslint/parser@5.46.1
   ├── async-retry@1.3.3
   ├── chai-http@4.3.0
   ├── chai-pretty-expect@1.0.1
   ├── chai@4.3.7
   ├── compression@1.7.4
   ├── cors@2.8.5
   ├── dotenv-safe@8.2.0
   ├── dotenv@16.0.3
   ├── eslint-plugin-deprecation@1.3.3
   ├── eslint@8.29.0
   ├── express@4.18.2
   ├── fp-ts@2.13.1
   ├── husky@8.0.2
   ├── io-ts@2.2.20
   ├── lint-staged@13.1.0
   ├── lodash@4.17.21
   ├── luxon@3.1.1
   ├── mocha@10.2.0
   ├── morgan@1.10.0
   ├── mysql2@2.3.3
   ├── nodemon@2.0.20
   ├── sequelize@6.27.0
   ├── snowflake-sdk@1.6.17
   ├── swagger-cli@4.0.4
   ├── swagger-ui-express@4.6.0
   ├── ts-node@10.9.1
   ├── typescript@4.9.4
   ├── winston@3.8.2
   └── yamljs@0.3.0

4. What did you do? I have a table with an autoincrement column. And I want to INSERT some rows using the nodejs connector. After executing the INSERT statement, how do I get the last insert ID? The documentation is painfully silent about this.

5. What did you expect to see? Clear documentation about this. And if I inspect the returned results of the INSERT statement, I expect to somehow see the last insert ID.

6. What did you see instead? Nothing in the documentation. And if I inspect the returned results, there is only one returned row, which is this: {"number of rows inserted":1} While we are at it, please ensure that any solution works nicely for inserting and upserting (i.e. MERGE) multiple rows.

[sfc-gh-dszmolka]

hi, there's really not much documentation on this :( so did a simple experiment

var statement = connection.execute({
    sqlText: 'insert into /* search for queryid */ mydb.myschema.mytable select 1;',
    complete: function(err, stmt, rows) {
        if (err) {
            console.error('Failed to execute statement due to the following error: ' + err.message);
        } else {
            console.log(statement);
            console.log('Number of rows produced: ' + rows.length);
            for (row of rows) {
        console.log(row);
        }
        }
    }
});

yielded:

RowStatementPreExec {
  _events: [Object: null prototype] {},
  _eventsCount: 0,
  _maxListeners: undefined,
  getSqlText: [Function (anonymous)],
  getStatus: [Function (anonymous)],
  getColumns: [Function (anonymous)],
  getColumn: [Function (anonymous)],
  getNumRows: [Function (anonymous)],
  getNumUpdatedRows: [Function (anonymous)],
  getSessionState: [Function (anonymous)],
  getRequestId: [Function (anonymous)],
  getStatementId: [Function (anonymous)],
  cancel: [Function (anonymous)],
  fetchRows: [Function (anonymous)],
  streamRows: [Function (anonymous)],
  [Symbol(kCapture)]: false
}
Number of rows produced: 1
{ 'number of rows inserted': 1 }

where getStatementId looked promising, so then calling statement.getStatementId() in a subsequent attempt yielded the same id (queryID) which is visible in Snowflake Query History for the particular INSERT INTO statement, as the queryID.

Not sure why it's exposed as statementID and not queryID, but even the unit tests suggests they are the same. Hope this helps. I'll also reach out to the Documentation team to consider providing documentation for this.

[sfc-gh-dszmolka]

closing this for now but please feel free to reopen if further assistance is needed or you found an issue.