Expose createSqlitePersister in package for custom SQLite set-ups #168

Activity

1. jamesgpearce commented on Aug 28, 2024

   

   jamesgpearce

   on Aug 28, 2024

   ContributorAuthor

   More actions

   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!
2. jamesgpearce

   closed this as completedon Aug 28, 2024
3. hdoro commented on Sep 17, 2024

   

   hdoro

   on Sep 17, 2024

   Last edited by hdoro

   More actions

   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,
     )
   }
4. waynesbrain commented on Sep 17, 2024

   

   waynesbrain

   on Sep 17, 2024

   More actions

   ...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.
5. hdoro commented on Oct 3, 2024

   

   hdoro

   on Oct 3, 2024

   More actions

   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?
6. jamesgpearce commented on Oct 7, 2024

   

   jamesgpearce

   on Oct 7, 2024

   ContributorAuthor

   More actions

   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).
7. jamesgpearce commented on Oct 7, 2024

   

   jamesgpearce

   on Oct 7, 2024

   ContributorAuthor

   More actions

   As for values not being stored, did you enable that in the config?
8. hdoro commented on Oct 18, 2024

   

   hdoro

   on Oct 18, 2024

   Last edited by hdoro

   More actions

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

   Thank you for the pointer!