Transaction Support

[katm201]

When trying to create a transaction in Node, I am consistently running into this error:

OperationFailedError: Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

When I looked up the issue, I found this thread from 2 years ago in the forums: https://support.snowflake.net/s/question/0D50Z00007UwSeWSAV/transaction-mode-in-node-js-driver

A Python user mentioned that they got around this problem for transactions by using the cursor object from their connector's API (https://docs.snowflake.net/manuals/user-guide/python-connector-api.html#cursor). When I looked for similar functionality from the Node sdk, it wasn't there.

Can we please get a reliable way to handle transactions? This is obviously a huge problem for complex inserts/updates.

[sfc-gh-stakeda]

By default, autotcommit is enabled, so when you start a transaction, you need to call begin first followed by DMLs and finish it by commit or rollback.

The above error occurs because multiple statements are submitted in a single execution context. As of today, only JDBC supports multiple statements. Other supports are in the pipeline but no ETA can be given. But even JDBC doesn't provide transaction specific API, so the application will still have to call begin and commit/rollback.

Can you elaborate how you run your transaction? A sample code would be useful for us to understand what you need.

[katm201]

Thanks for getting back to me.

Here's the example code I was trying to run (I've stripped out the error handling, setting up the Snowflake connection, caching, etc.):

const sql = `
  BEGIN TRANSACTION;

  UPDATE props_payloads
  SET status_id = (
    SELECT status_id FROM status WHERE LOWER(status) = 'retired'
  ), retired_at = TO_TIMESTAMP_NTZ(CURRENT_TIMESTAMP())
  WHERE status_id = (SELECT status_id FROM status WHERE LOWER(status) = 'active')
  AND prop_id = (SELECT prop_id FROM props WHERE prop_name = '${propsId}');

  INSERT INTO props_payloads (prop_id, payload)
  SELECT column1 AS prop_id, PARSE_JSON(column2) AS payload
  FROM VALUES (${PROP_ID}, '${JSON.stringify(payload)}');

  COMMIT;
`;

await snowflake.execute(sql);

Since it uses multiple statements, I'm not surprised that it failed with that error. What I can't figure out is how to run an equivalent to that transaction using the Node SDK.

I broke it up into 2 queries for now, but that obviously runs the risk of not being able to roll back the first one if the second one fails:

const retireSQL = `
  UPDATE props_payloads
  SET 
    status_id = (SELECT status_id FROM status WHERE LOWER(status) = 'retired'),
    retired_at = TO_TIMESTAMP_NTZ(CURRENT_TIMESTAMP())
  WHERE status_id = (SELECT status_id FROM status WHERE LOWER(status) = 'active')
    AND prop_id = (SELECT prop_id FROM props WHERE prop_name = '${propsId}');
`;

const insertSQL = `
  INSERT INTO props_payloads (prop_id, payload)
  SELECT column1 AS prop_id, PARSE_JSON(column2) AS payload
  FROM VALUES (${PROP_ID}, '${JSON.stringify(payload)}');
`;

await snowflake.execute(retireSQL);
await snowflake.execute(insertSQL);

I'll be the first to admit I'm not super knowledgeable about raw SQL or Snowflake. Up until recently, I was working primarily with MySQL, PostgreSQL, and MariaDB using the Knex.js ORM. Here's how Knex deals with transactions (and what I'd love to see from the Snowflake SDK): http://knexjs.org/#Transactions

If you can give me an idea of how to implement the above using transactions from what's already available in the SDK, I would be forever grateful.

[brugos]

I can see threads on google requesting this feature back to 2017 !!! I can't understand how Snowflake can state long in the market without a reasonable amount of features for developers can build their applications on top of it. And to be fair, this a quite BASIC feature as far as I'm concerned !

[smbkr]

@sfc-gh-stakeda any update on where this sits on your roadmap?

[sfc-gh-stakeda]

All issues have been in the queue of product roadmaps. No ETA is given at the moment.

[safareli]

This is a joke!

[kning]

bumping this thread any updates? we really need this feature as well, very similar use case as @katm201.

[jdanielmyers]

Hey folks -- I've bumped this up in priority internally here at Snowflake. Stay tuned on the timeline.

[kning]

somebody on my team actually pointed out this thread to me as a workaround: https://community.snowflake.com/s/question/0D50Z00007UwSeWSAV/transaction-mode-in-node-js-driver

[dylangrandmont]

somebody on my team actually pointed out this thread to me as a workaround: https://community.snowflake.com/s/question/0D50Z00007UwSeWSAV/transaction-mode-in-node-js-driver

@kning that workaround does not appear to work with the node js driver.

  await conn.execute(`
    BEGIN TRANSACTION;
    USE WAREHOUSE ...;
    USE DATABASE ...;
    COMMIT;
  `);

will still throw OperationFailedError: Multiple SQL statements in a single API call are not supported; use one API call per statement instead.

[kning]

We just did:

await conn.execute('BEGIN TRANSACTION;');
await conn.execute('USE WAREHOUSE ...;');
await conn.execute('USE DATABASE ...;');
await conn.execute('COMMIT;');

And seemed to work. If you check the Snowflake logs after you'll see they should all have the same session id.

[dylangrandmont]

await conn.execute('BEGIN TRANSACTION;'); await conn.execute('USE WAREHOUSE ...;'); await conn.execute('USE DATABASE ...;'); await conn.execute('COMMIT;');

That will certainly execute but those are four separate API calls (compared with the goal of executing multiple statements with a single API call). Each API call incurs some round-trip performance costs.

[kning]

Yeah that's true, but in our case this was part of a regularly scheduled batch script so we don't care too much about performance. Definitely a workaround and agree the multi-statement with single API call would be preferred!

[brugos]

you can use this package to manage multiple requests if you want: https://www.npmjs.com/package/snowflake-multisql

[vaibhavphutane]

@jdanielmyers any update on this?

[sfc-gh-dszmolka]

after all this time, finally a version (1.6.18) of the connector was just released which now supports multistatement. thank you everyone for the patience here !