How do you write a chain of SQL queries and updates based on the query data in SQLite without callback hell?

Question

In Java land I would do something like

@Transactional
FormData update(FormData updatedFormData) {
  var result = dsl
    .query(
      "select id, formData from formStuff where formId = ?", 
      updatedFormData.formId
    );
  var result2 = dsl
    .query(
      "select reference from referenceStuff where formStuffId = ?", 
      result.get("id")
    );
  var mergedFormData = merge(
    result.get("formData"), 
    result2.get("reference"), 
    updatedFormData
  );
  var updateResult = dsl
    .executeUpdate(
      "update formStuff set formData = ? where id = ?",
      mergedFormData,
      result.get("id")
    );
  return mergedFormData;
}

I am trying to do something similar on Expo SQLite but it started to appear like callback hell

async function update(db, updatedFormData) {
  return
    new Promise((resolve, reject) => {
      db.transaction(
        (tx) => {
          tx.executeSql(
            "select id, formData from formStuff where formId = ?",
            [updatedFormData.formId],
            (tx1, resultSet1) => {
              tx1.executeSql(
                "select reference from referenceStuff where formStuffId = ?",
                [resultSet1.rows.item(0).id],
                (tx2, resultSet2) => {
                   const mergedFormData = merge(
                     resultSet1.rows.item(0).formData, 
                     resultSet2.rows.item(0).reference, 
                     updatedFormData
                   );
                   tx2.executeSql(
                     "update formStuff set formData = ? where id = ?",
                     [mergedFormData, resultSet1.rows.item(0).id],
                     (tx3) => {
                       resolve(mergedFormData)
                     },
                     (tx3, error) => {
                      console.log(error);
                      reject(error);
                      return true;
                     }
                   )
                }
                (tx2, error) => {
                  console.log(error);
                  reject(error);
                  return true;
                }
              )
            },
            (tx1, error) => {
              console.log(error);
              reject(error);
              return true;
            }
          );
        },
        (tx, error) => {
          console.error(error);
          reject(error);
        },
        () => {
          resolve();
        }
      );

Answer 1

Wrap each call to executeSql in its own promise.

Generally it is better to then wrap each promise in its own function (which you can give a sensible name and arguments).

Then await the return value of each function in turn (which lets you assign the resolved value to a variable and pass it to the next function).

Answer 2

Use async/await to make things feel synchronous. Assuming your sqlite lib is promise ready.

async function update(db, updatedFormData) {

  const tx = await db.transaction();

  try {
    const resultSet1 = await tx.executeSql(
      "select id, formData from formStuff where formId = ?",
      [updatedFormData.formId]
    );

    const resultSet2 = await tx.executSql(
      "select reference from referenceStuff where formStuffId = ?",
      [resultSet1.rows.item(0).id] 
    );

    // ... etc

    tx.commit();
  }
  catch (e) {
    tx.rollback();
  }
  
}
  

Answer 3

UPDATE this does not work due to https://github.com/nolanlawson/node-websql/issues/46

I just did a quick hack of a module to do this for me. Likely there are better ways of doing this with extending classes and what not (plus I am limited to JavaScript though I use VSCode's TS check with JSDoc)

// @ts-check
/**
 * This module provides an async/await interface to Expo SQLite.  For now this provides a functional interface rather than a class based interface.
 * @module
 */
/**
 * @typedef {import("expo-sqlite").SQLTransaction} SQLTransaction
 * @typedef {import("expo-sqlite").SQLError} SQLError
 * @typedef {import("expo-sqlite").SQLResultSet} SQLResultSet
 * @typedef {(tx: SQLTransaction)=>Promise<any>} AsyncTransactionCallback
 */
import * as SQLite from "expo-sqlite";

/**
 *
 * @param {string} name
 * @param {string} [version]
 * @returns {Promise<SQLite.WebSQLDatabase>}
 */
export async function openDatabaseAsync(name, version) {
  return new Promise((resolve) => {
    SQLite.openDatabase(name, version, "", 0, (db) => {
      resolve(db);
    });
  });
}

/**
 *
 * @param {SQLTransaction} tx transaction
 * @param {string} sqlStatement
 * @param {any[]} [args]
 * @return {Promise<SQLResultSet>}
 */
export async function executeSqlAsync(tx, sqlStatement, args = []) {
  return new Promise((resolve, reject) => {
    tx.executeSql(
      sqlStatement,
      args,
      (txObj, resultSet) => {
        resolve(resultSet);
      },
      (error) => {
        console.log(error);
        reject(error);
        return true;
      }
    );
  });
}

/**
 *
 * @param {SQLite.WebSQLDatabase} db
 * @return {(fn: AsyncTransactionCallback)=>Promise<any>}
 */
export function txn(db) {
  return async (f) => {
    new Promise((resolve, reject) => {
      db.transaction(
        (tx) => {
          f(tx)
            .then((result) => resolve(result))
            .catch(reject);
        },
        /**
         *
         * @param {SQLError} error error
         */
        (error) => {
          reject(error);
        },
        () => {
          resolve();
        }
      );
    });
  };
}

For my scenario it is used like this

async function update(db, updatedFormData) {
  return await txn(db)(async (tx) => {
    // there's probably a less retarded way of writing this using bind or something
    const resultSet1 = await executeSqlAsync(tx, 
      "select id, formData from formStuff where formId = ?",
      [updatedFormData.formId]);
    const resultSet2 = await executeSqlAsync(tx,
      "select reference from referenceStuff where formStuffId = ?",
      [resultSet1.rows.item(0).id]);
    const mergedFormData = merge(
      resultSet1.rows.item(0).formData, 
      resultSet2.rows.item(0).reference, 
      updatedFormData
    );
    await executeSqlAsync(tx,
      "update formStuff set formData = ? where id = ?",
      [mergedFormData, resultSet1.rows.item(0).id],
    );
    return mergedFormData;
  });
};

Maybe I'll figure out how to tweak it so it looks like this in the future, but for now what I have does what I need.

async function update(db: AsyncSQLiteDatabase, updatedFormData: FormData) {
  return await db.asyncTransaction<FormData>(async (tx) => {
    // there's probably a less retarded way of writing this using bind or something
    const resultSet1 = await tx.executeSqlAsync( 
      "select id, formData from formStuff where formId = ?",
      [updatedFormData.formId]);
    const resultSet2 = await tx.executeSqlAsync( 
      "select reference from referenceStuff where formStuffId = ?",
      [resultSet1.rows.item(0).id]);
    const mergedFormData = merge(
      resultSet1.rows.item(0).formData, 
      resultSet2.rows.item(0).reference, 
      updatedFormData
    );
    await tx.executeSqlAsync(
      "update formStuff set formData = ? where id = ?",
      [mergedFormData, resultSet1.rows.item(0).id],
    );
    return mergedFormData;
  });
};

Answer 4

Maybe you can chain queries using recursion on success/error, something like:

    function executeBatch(queries, ready) {
      if (queries.length === 0) {
        console.log('all done!');
        ready();
        return;
      }
    
      const queryId = `q${queries.length}`;
      const query = queries.shift();
      console.log(`starting ${query}`);
      console.time(queryId);
    
      const continueExecution = () => {
        console.timeEnd(queryId);
        executeBatch(queries);
      };
    
      db.transaction(tx =>
        tx.executeSql(
          query,
          [],
          () => {
            console.log('ok');
            continueExecution();
          },
          () => {
            console.error('fail');
            continueExecution();
          }
        )
      );
    }
    
    executeBatch(['query1','query2',...], doneCallback);