Read an existing file on the filesystem without copying

[gmaclennan]

I am trying to understand how I might use this to read an existing sqlite database in the user filesystem without copying the database into OPFS. I am trying to build an offline web app for reading sqlite files (specifically, mbtiles files). The files can be large (> 1GB) so I want to avoid reading them into memory.

In an ideal world, we could use window.showOpenFilePicker() to return a FileSystemFileHandle and then call createSyncAccessHandle() to get a FileSystemSyncAccessHandle, which I could then pass to a VFS, but I'm not sure which one?

For supporting browsers that don't support window.showOpenFilePicker() (unfortunately, anything other than Chrome), then it seems like I could maybe use OPFSAnyContextVFS to read a File object from opening a file selected from a user input, but I'm unclear about how I could pass the File object to initialize the database?

The alternative is to streaming copy a File object into OPFS, and then use an OPFS VFS to read the copied file, although I'm not entirely sure which one to use.

I understand that SQLite needs to create other files for writing, but for just reading a database can it just use a single file handle to read the DB file?

[rhashimoto]

The alternative is to streaming copy a File object into OPFS, and then use an OPFS VFS to read the copied file, although I'm not entirely sure which one to use.

This will work. You could use any OPFS VFS with filesystem transparency (see feature table). The drawback is the time to copy a big file.

If you want to access the user's file directly (without copying to OPFS), you'll need to write a custom VFS. I'm unsure whether you can get a FileSystemSyncAccessHandle on a picked file (i.e. outside OPFS) if you're not on a Chromium browser; my guess is that Firefox and Safari don't support it. Also note that FileSystemWritableFileStream isn't supported on Safari and Android Chrome.

If you only want to read the user's database file then your custom VFS might work with a simple modification to OPFSAnyContextVFS. You could try replacing this code:

wa-sqlite/src/examples/OPFSAnyContextVFS.js

Lines 72 to 74 in 605100a

	const create = !!(flags & VFS.SQLITE_OPEN_CREATE);
	const [directoryHandle, filename] = await getPathComponents(pathname, create);
	file.fileHandle = await directoryHandle.getFileHandle(filename, { create });

with something like:

  file.fileHandle = getUsersFileSystemFileHandle(zName);

where you would implement getUsersFileSystemFileHandle() to return the user's file handle or throw if any other file was requested.

I understand that SQLite needs to create other files for writing, but for just reading a database can it just use a single file handle to read the DB file?

Mostly yes, but it will generally still attempt to open other files to check that the database wasn't left in an inconsistent state. In the typical case these other files will be not present or empty.

[gmaclennan]

Thank you @rhashimoto this is really helpful. Still investigating the best way to do this - the answer might just be "wait a year for better browser support".