How to handle errors in transactionAsync function of expo-sqlite properly?

Question

I noticed that some errors are not being thrown. Specifically errors caused by UNIQUE constraint, and duplicate primary keys.

export const insertMetricGroup = async (db: SQLite.SQLiteDatabase, name: string, description: string = '', grpOrder: number = 0) => {
    await db.transactionAsync(async (tx) => {
        const idFromIncrementTable = await getAndIncreaseIncrementAsync(tx, null, 'MetricGroupTable');
        console.log(idFromIncrementTable);
        // THIS EXECUTES CORRECTLY
        const res1 = await tx.executeSqlAsync(`INSERT INTO MetricGroupTable
        (id, name, description, grpOrder)
        VALUES (?, ?, ?, ?)`, [idFromIncrementTable, name, description, grpOrder]);
        console.log(res1);

        // THIS DOES NOT (which is fine, since PK already exists)
        // HOWEVER executeSqlAsync function in this case never returns anything (res2 is never printed).
        const res2 = await tx.executeSqlAsync(`INSERT INTO MetricGroupTable
        (id, name, description, grpOrder)
        VALUES (?, ?, ?, ?)`, [idFromIncrementTable, name, description, grpOrder]);
        console.log(res2);
        await metricGroupReorder(tx);
      });
  };

getAndIncreaseIncrementAsync method returns a number for increment from the separate increment table.

The example above will behave like res2 is pending, without ever throwing any error or returning result. Similarly sqlite database i sbehaving like the transaction is running (locked database) and not rollbacked or commited

In case I reload the application (I am testing an app on android using expo go mobile app), any attempt to insert into any table will hang in the similar manner (even queries that should be inserted normaly because there is no constraint violation) further increasing the probability that previous transaction remains hanging indefinitely. This issue will persist until database is deleted:

const db: SQLite.SQLiteDatabase = await SQLite.openDatabase('HabitsDb.db');
await db.closeAsync();
await db.deleteAsync();

From what I can tell transactionAsync used to return either resulterror or result set, however that was changed about a year ago due to this issue: https://github.com/expo/expo/issues/23884

As execution of javascript is completely halted on the point of error (which is similar to what i have experienced in other projects when thread is waiting for some lock to be released on the database) PACKAGE DEPENDENCIES:

"dependencies": {
"@react-navigation/bottom-tabs": "^6.5.20",
"@react-navigation/native": "^6.1.17",
"@react-navigation/native-stack": "^6.9.26",
"expo": "~50.0.17",
"expo-status-bar": "~1.11.1",
"react": "18.2.0",
"react-native": "0.73.6",
"react-native-popup-menu": "^0.16.1",
"react-native-safe-area-context": "4.8.2",
"react-native-screens": "~3.29.0",
"expo-sqlite": "~13.4.0"},

The following is failing with the same result, so git commit in expo-sqlite lib that changed the type of return probably didn't cause the error:

export const insertMetricGroupLowLevel = async(db: SQLite.SQLiteDatabase, name: string, description: string = '', grpOrder: number = 0) => {
// validate data
if (name.length < 1)
{
    throw('NAME TO SHORT');
}
await db.execAsync([{ sql: 'BEGIN;', args: [] }], false);
try
{
  const res1: any = await getAndIncreaseIncrementAsyncLowLevel(db, 'MetricGroupTable');
  await db.execAsync([{ sql: `INSERT INTO MetricGroupTable
  (id, name, description, grpOrder)
  VALUES (?, ?, ?, ?);`, args: [res1, name, description, grpOrder] }], false);
  await db.execAsync([{ sql: `INSERT INTO MetricGroupTable
  (id, name, description, grpOrder)
  VALUES (?, ?, ?, ?);`, args: [res1, name, description, grpOrder] }], false);
  await metricGroupReorderLowLevel(db);
  await db.execAsync([{ sql: 'END;', args: [] }], false);
  console.log('INSERTED METRIC GROUP')
}
catch(error)
{
  await db.execAsync([{ sql: 'ROLLBACK;', args: [] }], false);
  console.log('FAILED INSERT OF METRIC GROUP', error);
}

}

Tables:

CREATE TABLE IF NOT EXISTS MetricGroupTable (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      description TEXT NOT NULL,
      grpOrder INTEGER NOT NULL
    );

CREATE TABLE IF NOT EXISTS IncrementsTable (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      value INTEGER NOT NULL
    );

Answer 1

expo-sqlite received a new major update (SDK 51 and version 14.0.3)

While interfaces, methods and function calls were greatly changed, I can confirm that now unique constrain violation throws errors as it should, rather then keeping transaction hanging

await db.withExclusiveTransactionAsync(async (tx) => {
    const idFromIncrementTable = await getAndIncreaseIncrementAsync(tx, null, 'MetricGroupTable');
    console.log(idFromIncrementTable);
    const statement = await tx.prepareAsync(`INSERT INTO MetricGroupTable
    (id, name, description, grpOrder)
    VALUES (?, ?, ?, ?);`);
    await statement.executeAsync(idFromIncrementTable, name, description, grpOrder);
    console.log('FIRST');
    await statement.executeAsync(idFromIncrementTable, name, description, grpOrder);
    console.log('SECOND'); // this does not print, but transaction throws an error as it should
    await metricGroupReorder(tx);
    });

there is no hanging locks on the database or anything