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();
        }
      );

score 1 · Answer 1 · answered Jun 19 '21 at 21:58

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).

answered Jun 19 '21 at 21:58

Quentin

914,110
126
1,211
1,335

Sort of what I also did in the interim, but it got really unweildly. I ended up writing some sort of wrapper functions with my limited JS skills. – Archimedes Trajano Jun 19 '21 at 23:20

Archimedes Trajano · Answer 2 · 2021-07-23T15:51:54.653

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;
  });
};

+1. This is also known as the [promise disposer pattern](https://stackoverflow.com/questions/28915677/what-is-the-promise-disposer-pattern). Sure, you can use more OOP instead of plain function, but it's the same concept after all. Of course, I'm pretty certain there are libraries out there that already do exactly that, so you don't have to write all of this yourself. — Bergi, Jun 20 '21 at 01:17
The solution I wrote actually does not work with expo-sqlite due to https://github.com/nolanlawson/node-websql/issues/46 — Archimedes Trajano, Jul 23 '21 at 15:51

score 1 · Answer 3 · answered Jul 23 '21 at 08:17

1

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);

answered Jul 23 '21 at 08:17

alostale

770
1
11
21

Ohh I like, very elegant way of using recursion. BUT we already have a facility like that built in to expo-sqlite. The main reason I don't use it is because I need the value from query1 in query2. – Archimedes Trajano Jul 23 '21 at 15:47
@ArchimedesTrajano you could get the results in the `tx.executeSql` success callback and send it to the `sqlExecution` and `executeBatch` to handle it. – alostale Jul 26 '21 at 07:31
That would cause the problem I had started the question with. – Archimedes Trajano Jul 26 '21 at 11:18

score 0 · Answer 4 · answered Jun 19 '21 at 22:05

0

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();
  }
  
}

answered Jun 19 '21 at 22:05

Ben Taber

6,426
1
22
17

I would add to this that you can, on top of that and depending on your case, use Promise.all when you have promises that dont depend on one another. you can start multiple asyncs without the await and then once await Promise.all([promise1, promise2, ... ]) – Noriller Jun 19 '21 at 22:16
The API usage does not look like the Expo WebSQL for one we don't have rollback and commit. – Archimedes Trajano Jun 19 '21 at 23:03

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

4 Answers4