Expose `createSqlitePersister` in package for custom SQLite set-ups

[jamesgpearce]

Discussed in #166

^{Originally posted by hdoro July 30, 2024}
I'm trying to use Tinybase with Tauri. I can store my data as a simple custom persister that saves a JSON of the Tinybase store, but that has a bigger overhead as I'd be writing/reading the entire data at every operation.

Instead, I'd like to leverage SQLite's smartness via Tauri's SQL plugin. From reading the sqlite persisters' code, I can see it leverages an internal createSqlitePersister, which I imagine could handle tauri/sql's db.execute (syntax from sqlx)?

As that's an internal module, I can't access it, and it's too tied into the repo to simply copy and paste, so I haven't tried it yet - could it work? 🤔

[jamesgpearce]

OK! Do you want to take a look at v5.2.0-beta.5? Just released and there are now two new exposed functions at the top level of the persisters module. The createCustomSqlitePersister function and createCustomPostgreSqlPersister function can be used to build Persisters for SQLite and PostgreSQL SDKs (or forks) that are not already included.

Hoping it works for Tauri!

[hdoro]

Sorry for the wait on this and thank you for the work, @jamesgpearce !

I've tested it on my side and it's working well ✨

Tauri's current SQL implementation has no support for listeners (such as sqlite3_update_hook), so addChangeListener and delChangeListener are unused in my implementation. I'm assuming this means I can't modify the SQL file outside of the app as changes wouldn't be propagated into Tinybase. Is there another issue that could arise from this?

Also, it seems like values aren't being persisted - when I do store.setValue, neither executeCommand or onSqlCommand are ran. Should I file an issue or this is intended behavior, persisters working only for tables?

Here's the code for my current implementation, open to feedback!:

import Database from '@tauri-apps/plugin-sql'
import type { Store } from 'tinybase'
import {
  type DatabasePersisterConfig,
  type Persister,
  type Persists,
  createCustomSqlitePersister,
} from 'tinybase/persisters'

export async function createTauriPersister(
  store: Store,
  storeName: string,
  config: DatabasePersisterConfig,
): Promise<Persister<Persists.StoreOrMergeableStore>> {
  const db = await Database.load(`sqlite:${storeName}.db`)

  return createCustomSqlitePersister(
    store,
    // configOrStoreTableName
    config,
    // executeCommand
    async (sql, params) => {
      // Tauri's SQL has no support for transactions from the JS front-end, so we're skipping begin & end statements
      if (sql.trim() === 'BEGIN' || sql.trim() === 'END') return []

      if (sql.trim().toUpperCase().startsWith('SELECT')) {
        return await db.select<object[]>(sql, params)
      }

      // UPDATE / DELETE
      await db.execute(sql, params)
      return []
    },
    // addChangeListener - Tauri's SQL has no support for update hooks, so we're skipping it
    (_listener) => {},
    // delChangeListener
    (_listenerHandle) => {},
    // onSqlCommand
    (_sql, _params) => {},
    // onIgnoredError
    (_error) => {},
    // destroy
    () => 0,
    // persist
    3 as Persists.StoreOrMergeableStore,
    db,
  )
}

[waynesbrain]

...values aren't being persisted

@hdoro Just curious when you pass the DatabasePersisterConfig are you using JSON Serialization mode?

Maybe it's unrelated but I'm just curious because I remembered reading in the docs that the Sqlite3 persisters require JSON mode to persist a MergeableStore.

[hdoro]

You're correct, @waynesbrain - mergeable stores don't work with tabular serialization. For me currently this is fine as I'm working on a single-player, local-only app where I'm more interested in having a clean SQLite database.

I'll circle back to it in the future if I need to. Still, though, values aren't being persisted, which seems like a bug?

[jamesgpearce]

I do think eventually we can get tabular and mergeable working together, but it's a little meaningless to then be watching (auto load) since it's unlikely another system will know how to get the CRDT metadata right.

If you can't listen for table changes, you could just not use auto load and call .load from the app whenever you want (eg on a timer or when the app is idle).

[jamesgpearce]

As for values not being stored, did you enable that in the config?

[hdoro]

I skipped the rest of the config, didn't know about the values part of it 🙈

Thank you for the pointer!