800+ IT
News
als RSS Feed abonnieren📚 SQLite Wasm in the browser backed by the Origin Private File System
💡 Newskategorie: Programmierung
🔗 Quelle: developer.chrome.com
In our blog post Deprecating and removing Web SQL, we promised a replacement for Web SQL based on SQLite. The SQLite Wasm library with the Origin Private File System persistence backend is our fulfillment of this promise.
About SQLite
SQLite is a popular, open-source, lightweight, embedded relational database management system. Many developers use it to store data in a structured, easy-to-use manner. Because of its small size and low memory requirements, SQLite is often leveraged as a database engine in mobile devices, desktop applications, and web browsers.
One of the key features of SQLite is that it is a serverless database, which means that it does not require a separate server process to operate. Instead, the database is stored in a single file on the user's device, making it easy to integrate into applications.
SQLite based on Web Assembly
There are a number of unofficial SQLite versions based on Web Assembly (Wasm), allowing it to be used in web browsers, for example, sql.js. The sqlite3 WASM/JS subproject is the first effort that is officially associated with the SQLite project making Wasm builds of the library established members of the family of supported SQLite deliverables. The concrete goals of this project include:
- Binding a low-level sqlite3 API which is as close to the C one as feasible in terms of usage.
- A higher-level object-oriented API, more akin to sql.js and Node.js-style implementations, that speaks directly to the low-level API. This API must be used from the same thread as the low-level API.
- A Worker-based API which speaks to the previous APIs via Worker messages. This one is intended for use in the main thread, with the lower-level APIs installed in a Worker thread, and talking to them via Worker messages.
- A Promise-based variant of the Worker API which entirely hides the cross-thread communication aspects from the user.
- Support for persistent client-side storage using available JavaScript APIs, including the Origin Private File System (OPFS).
Using SQLite Wasm with the Origin Private File System persistence backend
Downloading the library
Download the official Wasm build from the
SQLite homepage. Be sure
to download the right build. The description needs to mention sqlite3.wasm. After the download has
finished, extract the archive.
The Origin Private File System
The Origin Private File System (OPFS, part of the File System Access API) is augmented with a special surface that brings very performant access to data. This new surface differs from existing ones by offering in-place and exclusive write access to a file's content. This change, along with the ability to consistently read unflushed modifications and the availability of a synchronous variant on dedicated workers, significantly improves performance and unblocks new use cases.
As you can imagine, the last point of the project's goals, Support for persistent client-side
storage using available JavaScript APIs, comes with strict performance requirements
regarding persisting data to the database file. This is where the Origin Private File System, and,
more specifically, the
createSyncAccessHandle()
method of FileSystemFileHandle
objects comes into play. This method returns a Promise which resolves to a
FileSystemSyncAccessHandle
object that can be used to synchronously read from and write to a file. The synchronous nature of
this method brings performance advantages, but therefore it is only usable inside dedicated
Web Workers for files within the
Origin Private File System so the main thread can't be blocked.
Setting the required headers
Setting these headers is a required step for the Origin Private File System persistence backend to work.
Among other files, the downloaded SQLite Wasm archive contains the sqlite3.js and sqlite3.wasm
files, which make up the sqlite3 WASM/JS build. The jswasm directory contains the core sqlite3
deliverables and the top-level directory contains demonstration and test apps. Browsers will not
serve Wasm files from file:// URLs, so any apps you build with this require a web server and that
server must include the following headers in its response when serving the files:
Cross-Origin-Opener-Policyset to thesame-origindirective, which isolates the browsing context exclusively to same-origin documents. Cross-origin documents are not loaded in the same browsing context.Cross-Origin-Embedder-Policyset to therequire-corpdirective, so a document can only load resources from the same origin, or resources explicitly marked as loadable from another origin.
The reason for these headers is that SQLite Wasm depends on
SharedArrayBuffer,
and setting these headers is part of its
security requirements.
If you inspect the traffic with DevTools, you should find the following information:
Getting started code sample
As mentioned previously, SQLite Wasm with the Origin Private File System persistence backend needs to run from a Worker context. So to use it, in the main thread, you need to create the worker and listen to messages from it.
When using SQLite Wasm in production, be sure to also check out its Worker1 Promiser API, which hides the worker logic behind an abstraction. The example below uses a more classic Worker setup for the sake of clarity.
const logHtml = function (cssClass, ...args) {
const ln = document.createElement('div');
if (cssClass) {
ln.classList.add(cssClass);
}
ln.append(document.createTextNode(args.join(' ')));
document.body.append(ln);
};
const worker = new Worker('worker.js?sqlite3.dir=jswasm');
worker.onmessage = function ({ data }) {
switch (data.type) {
case 'log':
logHtml(data.payload.cssClass, ...data.payload.args);
break;
default:
logHtml('error', 'Unhandled message:', data.type);
}
};After that, in the worker thread, you can then set up the communication with the main thread, initialize the Wasm module, and finally start working with SQLite and execute queries.
const logHtml = function (cssClass, ...args) {
postMessage({
type: 'log',
payload: { cssClass, args },
});
};
const log = (...args) => logHtml('', ...args);
const error = (...args) => logHtml('error', ...args);
const start = function (sqlite3) {
const capi = sqlite3.capi; /*C-style API*/
const oo = sqlite3.oo1; /*high-level OO API*/
log('sqlite3 version', capi.sqlite3_libversion(), capi.sqlite3_sourceid());
let db;
if (sqlite3.opfs) {
db = new sqlite3.opfs.OpfsDb('/mydb.sqlite3');
log('The OPFS is available.');
} else {
db = new oo.DB('/mydb.sqlite3', 'ct');
log('The OPFS is not available.');
}
log('transient db =', db.filename);
try {
log('Create a table...');
db.exec('CREATE TABLE IF NOT EXISTS t(a,b)');
log('Insert some data using exec()...');
let i;
for (i = 20; i <= 25; ++i) {
db.exec({
sql: INSERT INTO t(a,b) VALUES (?,?)',
bind: [i, i * 2],
});
}
log("Query data with exec() using rowMode 'array'...");
db.exec({
sql: 'SELECT a FROM t ORDER BY a LIMIT 3',
rowMode: 'array', // 'array' (default), 'object', or 'stmt'
callback: function (row) {
log('row ', ++this.counter, '=', row);
}.bind({ counter: 0 }),
});
} finally {
db.close();
}
};
log('Loading and initializing sqlite3 module...');
let sqlite3Js = 'sqlite3.js';
const urlParams = new URL(self.location.href).searchParams;
if (urlParams.has('sqlite3.dir')) {
sqlite3Js = urlParams.get('sqlite3.dir') + '/' + sqlite3Js;
}
importScripts(sqlite3Js);
self
.sqlite3InitModule({
print: log,
printErr: error,
})
.then(function (sqlite3) {
log('Done initializing. Running demo...');
try {
start(sqlite3);
} catch (e) {
error('Exception:', e.message);
}
});Demo
See the above code in action in the embedded demo. Be sure to check out the source code on Glitch.
Debugging the Origin Private File System
To debug SQLite Wasm's Origin Private File System output, use the OPFS Explorer Chrome extension.
After installing the extension, open the Chrome DevTools, select the OPFS Explorer tab, and you're then ready to inspect what SQLite Wasm writes to the Origin Private File System.
If you click on any of the files in the OPFS Explorer window in DevTools, you can save it to the
local disk. You can then use an app like [SQLite Viewer](https://sqliteviewer.app/) to inspect the
database, so you can assure yourself that SQLite Wasm actually works as promised.
Getting help and providing feedback
SQLite Wasm is developed and maintained by the SQLite community. Get help and provide feedback by searching in and posting to the support forum. The full documentation is available on the SQLite site.
Acknowledgements
Hero image by Tobias Fischer on Unsplash.
...