Manipulate SQLite files outside the OPFS

[alexturpin]

Hello! First of all fantastic project. I was wondering if there were plans to support the File System API? https://developer.chrome.com/docs/capabilities/web-apis/file-system-access

My use case requires storing data in a separate file and the API works well with the raw SQL. I've seen overrideDatabaseFile but I was hoping to have the data source be the local file rather than having to re-inject it into SQLocal.

If this feature would be appreciated I could take a stab at a PR; perhaps the constructor could take a file handle instead, for example.

[DallasHoff]

Can you clarify what user flow you are trying to achieve? I'm not sure what you are suggesting. getDatabaseFile and overwriteDatabaseFile should already allow you to get and set the database through the File System Access API.

const { overwriteDatabaseFile } = new SQLocal('db.sqlite3');
[fileHandle] = await window.showOpenFilePicker();
const file = await fileHandle.getFile();
await overwriteDatabaseFile(file);

[alexturpin]

Thanks for your answer! Sorry for the late reply, was on vacation.

Fair enough; to clarify, I was hoping for the source of data to be the local file referenced by the file handle, rather than OPFS.

Copying it around myself using overwriteDatabaseFile and getDatabaseFile is certainly an option, but the downside seems to be that there is no easy way to know when the database has been modified in order to write the local file (other than wrapping my own calls, either in JS or in SQL with createCallbackFunction).

[DallasHoff]

Ah, I see. You want SQLite to perform operations on a file outside the OPFS. Unfortunately, this is not possible. The File System Access API is more restricted in certain ways by the browser. SQLite needs unrestricted synchronous access to its database file, and the only way it can get that in a browser is through the OPFS API.

Opening the database, performing a query, immediately closing the database, transferring it to the user's file system, and repeating is very inefficient. I'd recommend rethinking your approach. If you are using SQLite as an application file format, you should have the user explicitly save in order to trigger the transfer out of the OPFS, rather than try to keep it constantly in sync.

[alexturpin]

I understand, thanks for the explanation. I was starting to have the same doubts after reading through the code and seeing that seemingly the worker is able to modify the OPFS file directly without any explicit write step.

Unfortunately I do need to find a way to constantly persist the data to a local file because a different offline sync mechanism needs to be used for the data (think dropbox).

Do you have any thoughts about a sort of hook API to be able to perform some action upon the database (potentially) changing? It looks like it might be possible to instrument something like that in the postMessage "router" but I imagine at this point this just may not be within the scope of what you're trying to do here with this library and my weird use case.

[DallasHoff]

Can you explain more about what your goal is in creating a second copy of the database and why is has to remain constantly in sync with the OPFS copy? This will help me better address your use case.

[alexturpin]

Ah that's very kind of you, but I'm afraid I just have a lot of annoying requirements for this project. We're trying to build software to be ran in a corporate environment with strict runtime and storage requirements. It's a laborious process to get approval for new desktop software, so trying to use what is already installed on their devices. To make matters worse several users need to work on the same data, but the only approved spots to store data are locally or in the corporate OneDrive synced directory.

Users have Microsoft Access installed so an Access file in OneDrive is likely the natural choice, but simply put Access is just the worst so I was exploring the web app angle with file system data storage. Once I realized their installed Chrome could run the File System API without a problem, I wondered if a WASM SQLite solution might work. Then I wanted to use Drizzle and found your project.

If you have any suggestions I'd be happy to hear them, but at the end of the day if I want to do a web app I might just keep it simple and use JSON with the file system API and call it a day.

[alexturpin]

In the meantime it sounds like this might just be out of scope for this project in general.