Exploring SQLite 3 with the File System Access API
Written on
Chapter 1: Introduction to SQLite 3 and File System Access API
In previous discussions, we've explored how to create, alter, and load an SQLite 3 database in the browser. Today, we will delve into the process of opening, reading, and modifying a locally stored database. Our aim is to replicate the functionality of a desktop application that interacts with an SQLite 3 database. To achieve this, we will utilize the JavaScript version of SQLite 3 compiled in WebAssembly, alongside the File System Access API. For our UI, we'll be using Svelte.
Before diving in, I recommend checking out the earlier parts of this series:
- SQLite 3 in Action: A Beginner's Guide to Using a Relational Database in the Browser
- SQLite 3 in Action: How to Open a Local Database With JavaScript and WebAssembly
Section 1.1: Opening a Local Database File
In contrast to our previous article, we will leverage the File System Access API, enabling us to access, read, modify, and save local files. This approach provides an experience akin to traditional desktop applications.
To start, we'll need a straightforward function to open a local file. Below is a simple implementation:
let fileHandle;
const openFile = async () => {
[fileHandle] = await globalThis.showOpenFilePicker();
const file = await fileHandle.getFile();
const contents = await file.arrayBuffer();
};
The showOpenFilePicker function opens a dialog for file selection. Once a file is chosen, a FileHandle object is returned, which allows us to access the selected file. We can utilize the getFile method along with arrayBuffer to retrieve the file's contents. Additionally, there are methods like text and stream available for obtaining file content as a string or a ReadableStream.
Subsection 1.1.1: Reading the Database
We can reuse the readDatabase() function to extract the database from the file:
const readDatabase = (arrayBuffer) => {
const sqlite3 = self["sqlite3"];
let bytes = new Uint8Array(arrayBuffer);
const p = sqlite3.wasm.allocFromTypedArray(bytes);
let db = new sqlite3.oo1.DB();
let rc = sqlite3.capi.sqlite3_deserialize(
db.pointer,
"main",
p,
bytes.length,
bytes.length,
sqlite3.capi.SQLITE_DESERIALIZE_FREEONCLOSE |
sqlite3.capi.SQLITE_DESERIALIZE_RESIZEABLE
);
db.checkRc(rc);
return db;
};
Now, we can enhance our openFile function to return the database object:
let fileHandle;
const openFile = async () => {
[fileHandle] = await globalThis.showOpenFilePicker();
const file = await fileHandle.getFile();
const contents = await file.arrayBuffer();
const db = readDatabase(contents);
return db;
};
The fileHandle variable is defined globally to facilitate saving the file later. Next, we can create a button that triggers the openFile function:
<button onclick="openFileAndListTable()">Open File</button>
Section 1.2: Saving the SQLite 3 Database
To save our SQLite 3 database, we will again use the File System Access API. The first step involves fetching the file handle and making it writable:
const writable = await handle.createWritable();
We then retrieve the byte array using the sqlite3_js_db_export method:
const byteArray = self["sqlite3"].capi.sqlite3_js_db_export(db.pointer);
Finally, we create a Blob and write it to the file:
const blob = new Blob([byteArray.buffer], {
type: "application/x-sqlite3",
});
await writable.write(blob);
await writable.close();
The complete saving function looks like this:
const saveFile = async (handle, db) => {
const writable = await handle.createWritable();
const byteArray = self["sqlite3"].capi.sqlite3_js_db_export(db.pointer);
const blob = new Blob([byteArray.buffer], {
type: "application/x-sqlite3",});
await writable.write(blob);
await writable.close();
};
To facilitate saving, we can add another button:
<button onclick="saveFile(fileHandle, db)">Save File</button>
Section 1.3: Saving as a New File
To save the SQLite 3 database under a new filename, we can use the File System Access API again. We will define a function to show the save file picker:
const showSaveFilePicker = async () => {
const options = {
types: [
{
description: "SQLite 3",
accept: {
"application/x-sqlite3": [".sqlite", ".sqlite3", ".db"],},
},
],
};
const handle = await globalThis.showSaveFilePicker(options);
return handle;
};
We then integrate this into our button for saving as a new file:
<button onclick="saveAsNewFile()">Save File As</button>
And that's a wrap for today's discussion! Thank you for reading. Stay tuned for more insightful articles. Don't forget to subscribe to my Medium email list for updates!
The first video, Up and Running with SQLite3 in a NodeJS API, provides a comprehensive introduction to setting up SQLite3 in a Node.js environment.
The second video, JavaScript File System Access API Tutorial - Read, Write and Save Files, offers a detailed guide on utilizing the File System Access API in JavaScript.