使用 sqlite wasm 的解法, 很多, 原本我想挑戰 sql.js 的解法, 但一直無法成功, 無限的卡關. 只好使用 sqlite 官方的sqlite3.js.
sqlite 官方下戴點:
https://sqlite.org/download.html
sqlite3 WebAssembly & JavaScript Documentation
https://sqlite.org/wasm/doc/trunk/index.md
使用的範例:
https://sqlite.org/wasm/doc/trunk/demo-123.md
如果是只有要在 chrome browser 上跑的話, 是可以使用 WebSQL, This is the deprecated SQLite interface that is built into Chrome. It does not use WASM. It is coded directly in C/C++. WebSQL only runs in the main thread. WebSQL 在少量資料與巨量資料下的反應時間都比 sqlite3 來的優異, 參考看看效能比較表:
https://sqlite-wasm-opfs.glitch.me/speedtest.html
目前所有瀏覽器都可以用的是 Indexed Database:
https://www.w3.org/TR/IndexedDB/
IndexedDB 與 sql.js 那邊跑的較快?
使用這組 keyword 問 google: indexeddb sqlite wasm performance
https://sqlite.org/forum/info/4a97813fcbd4f63e?t=h
We did not benchmark against IndexedDb because, frankly, IndexedDb doesn’t really concern us. It’s a whole different beast with a whole different API, and any sort of comparisons would be apples vs. oranges. We experimented only very briefly (a single afternoon) with using IDB as backing store for an sqlite db, but we didn’t like where that was headed. That approach simply doesn’t sit well with any of us.
The speed of non-persistent I/O is essentially limited to that of the underlying JS and WASM engines. All we can really do there, in terms of influencing the speed, is compile at different optimization levels. (Compiling with -O2 consistently gives faster results than any other level.)
We don’t have any truly user-friendly benchmarking tools, as we just use them during testing and development, but they’re accessible at:
https://wasm-testing.sqlite.org/
In particular, the “speedtest1” tools are our basis for benchmarking because they’re WASM builds of the C library’s primary benchmarking tool. Using that, we can do direct comparisons against “native” (out-of-browser) speeds.
Persistence. I know that Chrome’s new APIs make file system persistence possible, but I think it’s really important that we have an IndexedDb (or equiv) fallback.
cough localStorage cough sessionStorage cough
An IDB-based backend is not currently in the cards for this project. There are other projects which store sqlite in IDB:
- https://github.com/jlongster/absurd-sql
- https://github.com/rhashimoto/wa-sqlite
Figuring out whether/how it’s possible to import wa-sqlite’s VFSes into client-level code which uses our API is on my TODO list. That would be a huge win, as Roy has done some amazing work on providing a framework for building and testing new VFSes.
This is not to be misunderstood as an official project-level stance on the topic, but i’m personally in no way keen on supporting IDB. That said, my strongly negative opinion on IDB is admittedly based mostly on the anecdotes of others, rather than personal experience, and counter-anecdotes would be dutifully considered1. Even so, the list of JS/WASM TODOs and want-to-dos is long enough that, even if someone were to convince us that an IDB-based implementation was worth the effort of development and supporting it forever, it’s unlikely that it would actually get done before the middle of 2023. By that time, the non-Chromium browsers will certainly (we can hope) have caught up with OPFS, making IDB a non-topic for us.
sqlite3 官方版本的使用範例.
Step 1: Create the HTML
loads main sqlite3.js
<!doctype html>
<html lang="en-us">
<head>
<meta charset="utf-8">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Hello, sqlite3</title>
</head>
<body>
<script src="jswasm/sqlite3.js"></script>
<script src="demo-123.js"></script>
</body>
</html>Step 2: Create a JS App
window.sqlite3InitModule().then(function(sqlite3){
// The module is now loaded and the sqlite3 namespace
// object was passed to this function.
console.log("sqlite3:", sqlite3);
});附上的測試用的範例:
window.sqlite3InitModule().then(function(sqlite3){
// The module is now loaded and the sqlite3 namespace
// object was passed to this function.
console.log("sqlite3:", sqlite3);
sqlite3.config.warn("Installing sqlite3 bits as global S for local dev/test purposes.");
window.S = sqlite3;
capi = sqlite3.capi;
wasm = sqlite3.wasm;
console.log("sqlite3 version:",capi.sqlite3_libversion(),
capi.sqlite3_sourceid());
if(wasm.bigIntEnabled){
console.log("BigInt/int64 support is enabled.");
}else{
console.log('warning',"BigInt/int64 support is disabled.");
}
const oo = sqlite3.oo1; /*high-level OO API*/
let db;
if (sqlite3.opfs) {
db = new sqlite3.opfs.OpfsDb('/mydb.sqlite3');
console.log('The OPFS is available.');
} else {
db = new oo.DB('/mydb.sqlite3', 'ct');
console.log('The OPFS is not available.');
}
console.log('transient db =', db.filename);
try {
console.log('Create a table...');
db.exec('CREATE TABLE IF NOT EXISTS t(a,b)');
console.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],
});
}
console.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) {
console.log('row ', ++this.counter, '=', row);
}.bind({ counter: 0 }),
});
} finally {
db.close();
}
});瀏覽器 console 裡的訊息:
