Roll back in Batch Statements

S

SQQ•1/29/23, 5:49 PM

From the docs:

Batched statements are SQL transactions. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence.

Batched statements are SQL transactions. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence.

Batched statements are SQL transactions. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence.

Batched statements are SQL transactions. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence.

When I have a couple of

INSERT

INSERT

INSERT

INSERT statements and the second one has a typo, an error is thrown but the first insert still works. What do I need to add to the

.batch

.batch

.batch

.batch statement to tell it to roll back the whole transaction?

S

SQQ•1/29/23, 5:56 PM

const db = context.env.ACCOUNT_D1_TEST;

  await db.batch([
    db
      .prepare(
        /*sql*/ `
        INSERT INTO account
        (id, date_created) 
        VALUES (?,?);
    `
      )
      .bind(newAccountId, now),
    db
      .prepare(
        /*sql*/ `
        INSERTZ INTO account_user
        (id, date_created, account_id, credential_username, credential_password_hash, role_of_owner)
        VALUES (?1,?2,?3,?4,?5,?6);
    `
      )
      .bind(newAccountUserId, now, newAccountId, username, cipherText, 1),
  ]);

const db = context.env.ACCOUNT_D1_TEST;

  await db.batch([
    db
      .prepare(
        /*sql*/ `
        INSERT INTO account
        (id, date_created) 
        VALUES (?,?);
    `
      )
      .bind(newAccountId, now),
    db
      .prepare(
        /*sql*/ `
        INSERTZ INTO account_user
        (id, date_created, account_id, credential_username, credential_password_hash, role_of_owner)
        VALUES (?1,?2,?3,?4,?5,?6);
    `
      )
      .bind(newAccountUserId, now, newAccountId, username, cipherText, 1),
  ]);

const db = context.env.ACCOUNT_D1_TEST;

  await db.batch([
    db
      .prepare(
        /*sql*/ `
        INSERT INTO account
        (id, date_created) 
        VALUES (?,?);
    `
      )
      .bind(newAccountId, now),
    db
      .prepare(
        /*sql*/ `
        INSERTZ INTO account_user
        (id, date_created, account_id, credential_username, credential_password_hash, role_of_owner)
        VALUES (?1,?2,?3,?4,?5,?6);
    `
      )
      .bind(newAccountUserId, now, newAccountId, username, cipherText, 1),
  ]);

const db = context.env.ACCOUNT_D1_TEST;

  await db.batch([
    db
      .prepare(
        /*sql*/ `
        INSERT INTO account
        (id, date_created) 
        VALUES (?,?);
    `
      )
      .bind(newAccountId, now),
    db
      .prepare(
        /*sql*/ `
        INSERTZ INTO account_user
        (id, date_created, account_id, credential_username, credential_password_hash, role_of_owner)
        VALUES (?1,?2,?3,?4,?5,?6);
    `
      )
      .bind(newAccountUserId, now, newAccountId, username, cipherText, 1),
  ]);

S

SQQ•1/29/23, 5:56 PM

The

INSERTZ

INSERTZ

INSERTZ

INSERTZ is deliberate to trigger the error

S

SQQ•1/29/23, 6:01 PM

Reading the docs I thought that

aborts

aborts

aborts

aborts was the default reaction so I assumed I'd have to modify the statement to get the

roll back

roll back

roll back

roll back functionality.

S

SQQ•1/29/23, 6:03 PM

Also this is happening in the local development environment using the command:

wrangler dev --local --persist

wrangler dev --local --persist

wrangler dev --local --persist

wrangler dev --local --persist

S

SQQ•1/29/23, 6:36 PM

Roll back seems to work in production

S

SQQ•1/29/23, 10:45 PM

New Issue created: https://github.com/cloudflare/miniflare/issues/484

GitHub

D1: roll-back in batch command not working · Issue #484 · cloudflar...

From the docs: Batched statements are SQL transactions. If a statement in the sequence fails, then an error is returned for that specific statement, and it aborts or rolls back the entire sequence....

Similar Threads

Similar Threads

Similar Threads