Expo-SQlite atom with initial load and live-updates

[PhilipGrefe]

please see my comment below

--
Hi all,

I'm writing a react-native app with expo-sqlite.
Inspired by drizzles useLiveQuery, I wanted an atom with the following features:

1. Simple get/set functionality that we all love from jotai
2. Persist data to expo-sqlite
3. Get/set data synchronously (no DB roundtrip when setting+getting to not hang the UI)
4. Initial load from database

I use drizzle as my ORM, but it can be replaced by any other solution.
Feel free to use it and please, feel free to give any feedback!

import { db } from 'db'
import { allMyTables } from 'db/schema'
import { eq } from 'drizzle-orm'
import { addDatabaseChangeListener } from 'expo-sqlite'
import { atom, Getter, Setter } from 'jotai'
import { atomEffect } from 'jotai-effect'

export const atomWithQuery = <T>(
  getter: (get: Getter) => Promise<T>,
  // setter must set an updatedAt in order to know if data needs to be updated in the updaterAtom
  setter: null | ((get: Getter, set: Setter, updatedAt: number, value: T) => void),
  // tables to observe for changes
  tableNames: SchemaTable[],
) => {
  const EMPTY = Symbol()

  // combination of defaultAtom and overwrittenAtom makes sure the inital value is loaded from the db
  const defaultAtom = atom((get) => getter(get))
  const overwrittenAtom = atom<T | typeof EMPTY>(EMPTY)

  const cacheUpdatedAtAtom = atom(0)
  const anAtom = atom(
    (get) => {
      get(dbSubscription)
      const overwritten = get(overwrittenAtom)
      if (overwritten !== EMPTY) {
        return overwritten
      }
      return get(defaultAtom)
    },
    (get, set, value: T) => {
      const now = Date.now()
      set(cacheUpdatedAtAtom, now)
      set(overwrittenAtom, value)
      if (setter) setter(get, set, now, value)
    },
  )

  // combination of subscription and updaterAtom makes sure that value is updated when database changes (e.g. by another atom or a direct write to the db)
  const updaterAtom = atom(null, async (get, set, tableName: SchemaTable, rowId: number) => {
    const cacheUpdatedAt = get(cacheUpdatedAtAtom)
    // in order to query by row-id, all tables need an integer primary key.
    // See https://www.sqlite.org/rowidtable.html
    // LATER find a better way to do the query:
    const dbUpdatedAt = // query your tables for updatedAt timestamp

    if (dbUpdatedAt && dbUpdatedAt > cacheUpdatedAt) {
      const dbValue = await getter(get)
      const localValue = get(anAtom)
      if (JSON.stringify(dbValue) !== JSON.stringify(localValue)) {
        set(anAtom, dbValue)
      }
    }
  })

  const dbSubscription = atomEffect((get, set) => {
    const listener = addDatabaseChangeListener(async ({ tableName, rowId }) => {
      if (!isSchemaTable(tableName)) {
        console.error('Unknown table:', tableName)
        return
      }

      if (tableNames && tableNames.includes(tableName as SchemaTable)) {
        set(updaterAtom, tableName, rowId)
      }
    })
    return () => {
      listener.remove()
    }
  })

  return anAtom
}

[PhilipGrefe]

Whatever got to me: This was a stupid idea 🤦
Simply set up your expo-sqlite and drizzle and use atomWithStorage in combination with queries in a custom storage implementation 😌

Here is an example:

export const testAtom4 = atomWithStorage(
  'foo',
  'bar',
  {
    getItem: async (key, initialValue) => {
      const someColumn = await db.query.someTable.findFirst({ columns: { someColumn: true } })
      return someColumn?.someColumn ?? initialValue
    },
    setItem: async (key, value) => {
      db.update(someTable).set({ someColumn: value }).where(eq(someTable.id, 1)).execute()
    },
    removeItem: async (key) => {
      db.delete(timerEventTable).where(eq(timerEventTable.id, 1)).execute()
    },
    subscribe: (key, callback, initialValue) => {
      const listener = addDatabaseChangeListener(async ({ tableName, rowId }) => {
        const someColumn = await db.query.someTable.findFirst({ columns: { someColumn: true } }) // typically the same query as for getItem
        callback(someColumn?.someColumn ?? initialValue)
      })
      return () => {
        listener.remove()
      }
    },
  },
  { getOnInit: true },
)