Saving the database outside the OPFS

[Metaxona]

do you have any plans on having an option of having the sqlite db run on the main thread and get the file from a local dir like the static asset dir, and not on OPFS? i get the benefit of OPFS but it is too unstable for permanent storage since there's a chance of having it evicted by the browser/system, so having an option to choose a mode for sqlocal to use either OPFS or Not would be great.

if if that is not planned, how about a local backup and restore thing that auto backups the db locally and periodically and before close and loading it on open

[DallasHoff]

Before anything else, it's worth noting that you actually can instruct the browser not to automatically evict your site's data by using navigator.storage.persist(). This MDN page has a good explanation of how you can address browser data eviction.

That said, you have options for saving the database outside the OPFS, but it cannot be as seamless due to the security restrictions that browsers put on the File System Access API. SQLite WASM uses the OPFS because the SQLite engine needs low-level, synchronous control of the file it writes to, and it can only get that with an OPFS file, not a file on the user's file system.

In order to write a file to the user's file system, you are required to prompt the user to select a file location and get their permission to write to it. That save process is also necessarily asynchronous since it happens on the main thread and cannot be blocking. This means SQLite cannot operate directly on a file outside the OPFS.

However, what you can do is offer your users an explicit save button, and when they click that, call SQLocal's getDatabaseFile method to get the database file out of the OPFS and either save it using the File System Access API, download it, or upload it to your server.

For more information about how the File System Access API works, this page has a good guide. Just keep in mind that browser support for these features has improved a lot in the years since that guide was posted.

Let me know if that all makes sense or you have further questions.

[Metaxona]

Before anything else, it's worth noting that you actually can instruct the browser not to automatically evict your site's data by using navigator.storage.persist(). This MDN page has a good explanation of how you can address browser data eviction.

That said, you have options for saving the database outside the OPFS, but it cannot be as seamless due to the security restrictions that browsers put on the File System Access API. SQLite WASM uses the OPFS because the SQLite engine needs low-level, synchronous control of the file it writes to, and it can only get that with an OPFS file, not a file on the user's file system.

In order to write a file to the user's file system, you are required to prompt the user to select a file location and get their permission to write to it. That save process is also necessarily asynchronous since it happens on the main thread and cannot be blocking. This means SQLite cannot operate directly on a file outside the OPFS.

However, what you can do is offer your users an explicit save button, and when they click that, call SQLocal's getDatabaseFile method to get the database file out of the OPFS and either save it using the File System Access API, download it, or upload it to your server.

For more information about how the File System Access API works, this page has a good guide. Just keep in mind that browser support for these features has improved a lot in the years since that guide was posted.

Let me know if that all makes sense or you have further questions.

so this

import { SQLocal } from 'sqlocal';

export const { getDatabaseFile } = new SQLocal('database.sqlite3');

getDatabaseFile can actually load a databse from a file? meaning if i create a system that asks a user to save the file locally, i will be able to load that db back to opfs? will the loaded file overwrite the database on OPFS?

[DallasHoff]

No no, getDatabaseFile gets the database file out of the OPFS. To put a file into the OPFS, you use overwriteDatabaseFile.

[Metaxona]

right, i also tried that navigator.storage.persist() before but it is not working it always goes to the database may be evicted part

also i found that you are missing a migration file for the drizzle part ex drizzle-orm bettersqlite3 migration

[Metaxona]

No no, getDatabaseFile gets the database file out of the OPFS. To put a file into the OPFS, you use overwriteDatabaseFile.

yup just looked at the docs and saw this

[focux]

What about adding support for wa-sqlite? Looks like it supports other backends besides OPFS, like IndexedDB, and according to their benchmark, it seems to be faster than OPFS.

[DallasHoff]

IndexDB is still browser storage. You'd still have to pull the database out of it explicitly and get permission to save it to the user's file system. It also has more overhead than OPFS. wa-sqlite's benchmarks compare their different backend implementations; it's not comparing itself to the official SQLite WASM build.

I prefer SQLite WASM because it is built by the core SQLite team, and they have a strong emphasis on long-term maintenance, performance, and updates. SQLite WASM is also very complete while wa-sqlite's implementation has serious drawbacks no matter which backend you use.

[focux]

I get your point but I think that OPFS is still not ready for production apps, mostly for the poor browser support. That's why some people still recommend using wa-sqlite with the IndexedDB backend instead of the official WASM build.

Anyways, I really appreciate the work that you are doing with this library, it's soo good. I'm currently trying to make it work on Expo web but it's been a challenge hah.

[DallasHoff]

OPFS has support in recent versions of Chrome, Firefox, and Safari, but yeah, it's still new enough that older versions of those browsers are still prevalent. I've always seen SQLocal and the idea of SQLite in the browser in general as very forward-looking. Support should only get better from here as time goes on though.

Thanks for the appreciation, and good luck with the Expo web implementation! I'm not really familiar with Expo, so I can't help much there.