Multiple Persisted Collections Shared Db

[ghardin1314]

• I've validated the bug against the latest version of DB packages

Describe the bug
Trying out persisted collections that are synced from a server. When trying to use the same database + coordinator for multiple collections shows this error:

__root.tsx:72 Failed to persist wrapped sync transaction: OPFSWorkerRequestError: cannot start a transaction within a transaction
at createWorkerError (opfs-database.ts?v=b19eef35:27:10)
at Worker.onMessage (opfs-database.ts?v=b19eef35:91:27)

const database = await openBrowserWASQLiteOPFSDatabase({
    databaseName: `persisted-synced.sqlite`,
  });
  const coordinator = new BrowserCollectionCoordinator({
    dbName: `persisted-synced`,
  });

  // --- Todos collection ---
  const todosPersistence = createBrowserWASQLitePersistence<Todo, string>({
    database,
    coordinator,
  });
  const todosQueryOpts = queryCollectionOptions({
    ....
  });
  const todosCollection = createCollection({
    ...persistedCollectionOptions({
      ...todosQueryOpts,
      persistence: todosPersistence,
      schemaVersion: 2,
    }),
    schema: todoSchema,
  });

  // --- Projects collection ---
  const projectsPersistence = createBrowserWASQLitePersistence<
    Project,
    string
  >({
    database,
    coordinator,
  });
  const projectsQueryOpts = queryCollectionOptions({
    ...
  });
  const projectsCollection = createCollection({
    ...persistedCollectionOptions({
      ...projectsQueryOpts,
      persistence: projectsPersistence,
      schemaVersion: 1,
    }),
    schema: projectSchema,
  });

Using a separate database + coordinator for each collection seems to work fine

To Reproduce
ghardin1314/tdb-pwa@1d2c516

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: MacOs
• Browser Brave
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

[kevin-dp]

The expected pattern is one createBrowserWASQLitePersistence instance shared across all collections, not one per collection.

In your example, both instances share the same underlying database and coordinator. Each persistence instance creates its own internal SQLiteDriver/adapter, so when both try to run transactions concurrently on the same OPFS database connection, you get "cannot start a transaction within a transaction."

This should work:

const database = await openBrowserWASQLiteOPFSDatabase({
    databaseName: `persisted-synced.sqlite`,
  });
  const coordinator = new BrowserCollectionCoordinator({
    dbName: `persisted-synced`,
  });
  // --> single shared persistence <--
  const persistence = createBrowserWASQLitePersistence<Todo, string>({
    database,
    coordinator,
  });

  // --- Todos collection ---
  const todosQueryOpts = queryCollectionOptions({
    ....
  });
  const todosCollection = createCollection({
    ...persistedCollectionOptions({
      ...todosQueryOpts,
      persistence,
      schemaVersion: 2,
    }),
    schema: todoSchema,
  });

  // --- Projects collection ---
  const projectsQueryOpts = queryCollectionOptions({
    ...
  });
  const projectsCollection = createCollection({
    ...persistedCollectionOptions({
      ...projectsQueryOpts,
      persistence,
      schemaVersion: 1,
    }),
    schema: projectSchema,
  });

[ghardin1314]

Ah I see thanks for the feedback.

Followup question, how are the types supposed to work on this? I noticed you still have <Todo, string> annotated on the persistence type which doesnt jive with the projects collection.

When testing this solution i have to cast each persistence instance when passing into the persistedCollectionOptions to avoid a type error.

  const todosCollection = createCollection({
    ...persistedCollectionOptions({
      ...todosQueryOpts,
      persistence: persistence as PersistedCollectionPersistence<Todo, string>,
      schemaVersion: 1,
    }),
    schema: todoSchema,
  });

  const projectsCollection = createCollection({
    ...persistedCollectionOptions({
      ...projectsQueryOpts,
      persistence: persistence as PersistedCollectionPersistence<
        Project,
        string
      >,
      schemaVersion: 1,
    }),
    schema: projectSchema,
  });

[kevin-dp]

@ghardin1314 Indeed, the types are still a bit off. Adding this to my list!

[kevin-dp]

Opened #1439 to improve the types.