Rewrite for Expo SQLite ^14 (Expo 51) and Debouncing persistClient

[jgo80]

Hi @kapobajza,

I love your approach, it really helped me a lot!

I have taken the liberty of rewriting your approach to Expo SQLite ^14 (Expo 51). I also added a throttleTime to persistClient to debounce writes, as SQLite is quite CPU consuming when you run hundreds of tanStackQueries in parallel.

Just wanted to contribute and share my thoughts 🤗

import {
  DehydratedState,
  MutationKey,
  MutationState,
  QueryKey,
  QueryState,
} from '@tanstack/react-query';
import {
  PersistedClient,
  Persister,
} from '@tanstack/react-query-persist-client';
import * as SQLite from 'expo-sqlite';
import SuperJSON from 'superjson';

interface CreateSqlitePersisterProps {
  throttleTime?: number;
}

type ClientQueriesMutationsSchema = {
  mutation_value: string | null;
  query_value: string | null;
  query_hash: string;
  mutation_key: string | null;
};

type ClientQuerySchema = {
  timestamp: number;
  buster: string;
};

enum TableNames {
  QUERY_CLIENTS = 'query_clients',
  QUERY_CLIENT_QUERIES = 'query_client_queries',
  QUERY_CLIENT_MUTATIONS = 'query_client_mutations',
}

const QUERY_CLIENT_INITIAL_ID = 1;

const dbName = 'offline.db';
const db = SQLite.openDatabaseSync(dbName);

export const createSqlitePersister = ({
  throttleTime = 0,
}: CreateSqlitePersisterProps): Persister => {
  let debouncedPersistClient: NodeJS.Timeout | null = null;

  const persistClientWithDebounce = async ({
    clientState,
    timestamp,
    buster,
  }: PersistedClient) => {
    if (debouncedPersistClient) {
      clearTimeout(debouncedPersistClient);
    }

    debouncedPersistClient = setTimeout(async () => {
      await db.withExclusiveTransactionAsync(async () => {
        const queryPromises = clientState.queries.map((query) =>
          db.runAsync(
            `INSERT OR REPLACE INTO ${TableNames.QUERY_CLIENT_QUERIES} (query_hash, value, query_client_id)
            VALUES (?, ?, ?)`,
            [
              query.queryHash,
              SuperJSON.stringify(query.state),
              QUERY_CLIENT_INITIAL_ID,
            ]
          )
        );
        const mutationPromises = clientState.mutations
          .filter((mutation) => mutation.mutationKey)
          .map((mutation) =>
            db.runAsync(
              `INSERT OR REPLACE INTO ${TableNames.QUERY_CLIENT_MUTATIONS} (mutation_key, value, query_client_id)
              VALUES (?, ?, ?)`,
              [
                SuperJSON.stringify(mutation.mutationKey),
                SuperJSON.stringify(mutation.state),
                QUERY_CLIENT_INITIAL_ID,
              ]
            )
          );

        await db
          .runAsync(
            `INSERT OR REPLACE INTO ${TableNames.QUERY_CLIENTS} (id, timestamp, buster)
          VALUES (?, ?, ?)`,
            [QUERY_CLIENT_INITIAL_ID, timestamp, buster]
          )
          .catch(console.error);
        await Promise.all([...queryPromises, ...mutationPromises]);
      });
    }, throttleTime);
  };

  return {
    persistClient: persistClientWithDebounce,
    removeClient: async () =>
      db.withExclusiveTransactionAsync(async () => {
        await db.runAsync(
          `DELETE FROM ${TableNames.QUERY_CLIENTS}
          WHERE id = ?`,
          [QUERY_CLIENT_INITIAL_ID]
        );
      }),
    restoreClient: async () => {
      let client: PersistedClient | undefined;
      await db.withExclusiveTransactionAsync(async () => {
        const queriesMutationsResult = await db.getAllAsync(
          `SELECT qcq.value 'query_value', qcq.query_hash 'query_hash', qcm.mutation_key 'mutation_key', qcm.value 'mutation_value' FROM ${TableNames.QUERY_CLIENTS} AS qc
          LEFT JOIN ${TableNames.QUERY_CLIENT_MUTATIONS} AS qcm ON qc.id = qcm.query_client_id
          JOIN ${TableNames.QUERY_CLIENT_QUERIES} AS qcq ON qc.id = qcq.query_client_id
          WHERE qc.id = ?;`,
          [QUERY_CLIENT_INITIAL_ID]
        );

        let queries: DehydratedState['queries'] = [];
        let mutations: DehydratedState['mutations'] = [];

        for (const row of queriesMutationsResult) {
          const parsedRow = row as ClientQueriesMutationsSchema;
          if (parsedRow.query_value) {
            const state: QueryState = SuperJSON.parse(parsedRow.query_value);
            const queryKey: QueryKey = SuperJSON.parse(parsedRow.query_hash);

            queries.push({
              queryHash: parsedRow.query_hash,
              queryKey,
              state,
            });
          }
          if (parsedRow.mutation_value && parsedRow.mutation_key) {
            const state: MutationState = SuperJSON.parse(
              parsedRow.mutation_value
            );
            const mutationKey: MutationKey = SuperJSON.parse(
              parsedRow.mutation_key
            );
            mutations.push({
              mutationKey,
              state,
            });
          }
        }

        const clientQueryResult = await db.getAllAsync(
          `SELECT timestamp, buster FROM ${TableNames.QUERY_CLIENTS} WHERE id = ?;`,
          [QUERY_CLIENT_INITIAL_ID]
        );

        const firstRow = clientQueryResult[0] as ClientQuerySchema;
        if (firstRow) {
          client = {
            clientState: {
              queries,
              mutations,
            },
            timestamp: firstRow.timestamp,
            buster: firstRow.buster,
          };
        }
      });
      return client;
    },
  };
};

export const initDatabase = async () =>
  db.withExclusiveTransactionAsync(async () => {
    await db.runAsync(`
    CREATE TABLE IF NOT EXISTS ${TableNames.QUERY_CLIENTS} (
      id INTEGER PRIMARY KEY,
      timestamp INTEGER NOT NULL,
      buster TEXT
    )`);

    await db.runAsync(`
    CREATE TABLE IF NOT EXISTS ${TableNames.QUERY_CLIENT_QUERIES} (
      query_hash TEXT PRIMARY KEY,
      value TEXT,
      query_client_id INTEGER NOT NULL,
      FOREIGN KEY(query_client_id) REFERENCES query_clients(id)
    )`);

    await db.runAsync(`
    CREATE TABLE IF NOT EXISTS ${TableNames.QUERY_CLIENT_MUTATIONS} (
      mutation_key TEXT PRIMARY KEY,
      value TEXT,
      query_client_id INTEGER NOT NULL,
      FOREIGN KEY(query_client_id) REFERENCES query_clients(id)
    )`);
  });

export const dropDatabase = async () =>
  db.closeAsync().then(() => SQLite.deleteDatabaseAsync(dbName));

[kapobajza]

Hello @jgo80

Thank you for the contribution. Especially for the throttle one, because that's a great improvement.

I will definitely add it to the repo with some other improvements I had in mind. Maybe I'll also update the expo and expo-sqlite version along the way.

Have a good day!

[jgo80]

In the meanwhile I also added a IndexedDB version for Web to make it Platform agnostic for Expo iOS, Android and Web, if you're interested, I can share...