sql.js-httpvfs

To compile sql.js yourself, you must first install the EMSDK as described in the Emscripten documentation. Once installed, you can rebuild the project using the provided npm script.

npm run rebuild

By default, sql.js uses WebAssembly (WASM) and requires loading a .wasm file in addition to the JavaScript library. You must use the initSqlJs function and provide a locateFile property in the configuration object to tell the library where to find the .wasm binary.

In Node.js, you can omit locateFile entirely. In a browser, you can host the .wasm file yourself or load it from a CDN.

const initSqlJs = require('sql.js');

const SQL = await initSqlJs({
  // Required to load the wasm binary asynchronously.
  // You can omit locateFile completely when running in node
  locateFile: file => `https://sql.js.org/dist/${file}`
});

To prevent users from loading stale or corrupted database files due to browser caching, use the cacheBust property in your configuration. This property appends a value as a query parameter to the database url or urlPrefix.

When updating your database, change this value to a new random string. If using from: 'jsonconfig', ensure you also cachebust the configUrl.

const config = {
  from: "inline",
  config: {
    serverMode: "full",
    requestChunkSize: 4096,
    url: "/foo/bar/test.sqlite3",
    cacheBust: "random_string_per_update" // Appends as query param
  }
};

After running the local server script, open the following URL in your browser to view the examples:

http://localhost:8081/index.html

To view the project examples locally, you must first start the local server using the provided Python script. Once the server is running, you can access the examples via your web browser.

./start_local_server.py

To use sql.js in Node.js, install it via npm: npm install sql.js. You can read database files from the disk using the fs module and write them back by converting the exported Uint8Array to a Buffer.

var fs = require('fs');
var initSqlJs = require('sql-wasm.js');

// Read a database from the disk
var filebuffer = fs.readFileSync('test.sqlite');
initSqlJs().then(function(SQL){
  var db = new SQL.Database(filebuffer);
});

// Write a database to the disk
var data = db.export();
var buffer = Buffer.from(data);
fs.writeFileSync("filename.sqlite", buffer);

To ensure optimal performance and compatibility with the HTTP-Range-request based virtual file system, you should prepare your SQLite database with specific settings. This is especially important for index efficiency and managing request overhead.

Run the following SQL commands on your database:

1. Set journal_mode to delete to allow setting the page size.
2. Set page_size (e.g., 1024) to balance the number of HTTP requests against overhead.
3. Run optimize on any FTS (Full Text Search) tables.
4. Run vacuum to reorganize the database and apply the new page size.

-- add indices as needed
pragma journal_mode = delete;
pragma page_size = 1024;

-- for every FTS table
insert into ftstable(ftstable) values ('optimize');

vacuum;

To enable specific SQLite extensions such as FTS5, you must modify the CFLAGS in the Makefile before running the rebuild command. For example, to enable FTS5, add -DSQLITE_ENABLE_FTS5 to the CFLAGS list.

CFLAGS = \
        -O2 \
        -DSQLITE_OMIT_LOAD_EXTENSION \
        -DSQLITE_DISABLE_LFS \
        -DSQLITE_ENABLE_FTS3 \
        -DSQLITE_ENABLE_FTS3_PARENTHESIS \
+       -DSQLITE_ENABLE_FTS5 \
        -DSQLITE_ENABLE_JSON1 \
        -DSQLITE_THREADSAFE=0

The createDbWorker function accepts configuration objects in two modes:

1. inline: Define the database details directly in the config object.

   • serverMode: Set to "full" if the file is a plain SQLite database.
   • requestChunkSize: The page size of the SQLite database (default is 4096).
   • url: The relative or full URL to the database file.

2. jsonconfig: Point to a JSON configuration file generated by the create_db.sh script.

   • configUrl: The URL to the .json configuration file.

// Inline configuration
const configInline = {
  from: "inline",
  config: {
    serverMode: "full",
    requestChunkSize: 4096,
    url: "/foo/bar/test.sqlite3"
  }
};

// Remote JSON configuration
const configRemote = {
  from: "jsonconfig",
  configUrl: "/foo/bar/config.json"
};

The dom virtual table allows you to interact with the browser's DOM using SQL queries. To use it, you must query the dom table using a MATCH operator on the selector column with a CSS selector.

Required Query Pattern: SELECT ... FROM dom WHERE selector MATCH '<css-selector>'

Available Columns:

• idx: The index of the element in the result set.
• id: The element's ID.
• tagName: The element's tag name.
• textContent: The text content of the element.
• innerHTML: The inner HTML of the element.
• outerHTML: The outer HTML of the element.
• className: The element's class name.
• parent: The selector for the parent element.
• selector: The CSS selector used for the query.
• querySelector: The query selector string.

If your queries are triggering excessive network requests, use these techniques to diagnose and optimize:

1. Analyze Query Plans: Use EXPLAIN QUERY PLAN <your_query>.

   • SCAN TABLE t1: Indicates a full table download. Avoid this.
   • SCAN TABLE t1 USING INDEX i1 (a=?): Efficient index lookup.
   • SCAN TABLE t1 USING COVERING INDEX i1 (a): Most efficient; reads only from the index without touching the table.
   • Tip: Create covering indexes that include both the columns in your WHERE clause and the columns in your SELECT clause.

2. Inspect Read Pages:

   • Use worker.getResetAccessedPages() to get a log of read pages.
   • Use the dbstat virtual table in an SQLite shell to inspect the content of specific pages (e.g., SELECT * FROM dbstat WHERE pageno = <page_number>).