sql.js

sql.js is a JavaScript implementation of SQLite that allows you to create and query relational databases entirely in the browser or in environments like Node.js. It uses WebAssembly (via Emscripten) to run SQLite code.

Key Characteristics:

• In-Memory Storage: By default, it uses a virtual database file stored in memory, meaning changes are not persisted automatically.
• Import/Export: You can import existing SQLite files as a Uint8Array and export your current database as a Uint8Array (JavaScript typed array).
• Use Case Note: For native applications (e.g., Electron) or Node.js server-side work, a native SQLite binding (like sqlite3) is generally preferred for better performance and direct file system access to avoid out-of-memory errors.

The SqlJs object returned by initSqlJs provides access to the primary classes used for database interaction:

• Database: The main class representing an SQLite database instance.
• Statement: The class used for managing and executing prepared statements.

Because sql.js uses WebAssembly by default, you must load a .wasm file in addition to the JavaScript library.

1. Locate the .wasm file: After installing via npm, it is found at ./node_modules/sql.js/dist/sql-wasm.wasm.
2. Configure locateFile: Pass a configuration object to initSqlJs using the locateFile property to tell the library where to fetch the .wasm binary (e.g., from a CDN or your local static assets folder).

Note: You can omit locateFile when running in Node.js.

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

const SQL = await initSqlJs({
  // Required to load the wasm binary asynchronously.
  // You can host it wherever you want
  locateFile: file => `https://sql.js.org/dist/${file}`
});

To use sql.js in a browser environment, load the sql-wasm.js script. Because the library uses WebAssembly, you must use the asynchronous initSqlJs function to initialize it. You should provide a locateFile configuration option to tell the loader where to find the .wasm file, especially if it is not in the same directory as your HTML file.

Once initialized, you can create a new database using new SQL.Database() and execute queries using .run() or .exec().

<script src='/dist/sql-wasm.js'></script>
<script>
  const config = {
    locateFile: filename => `/dist/${filename}`
  };

  initSqlJs(config).then(function(SQL) {
    const db = new SQL.Database();
    db.run("CREATE TABLE test (col1, col2);");
    db.run("INSERT INTO test VALUES (?,?)", [1, 111]);
  });
</script>

To avoid blocking the main UI thread with heavy SQL queries, use the WebWorker API. You must use the specific worker builds (e.g., worker.sql-wasm.js and worker.sql-wasm.wasm) available on the release page. The WebWorker API is more limited than the main thread API.

Communication is handled via postMessage. You typically send an open action with the database buffer first, followed by query actions like exec.

const worker = new Worker("/dist/worker.sql-wasm.js");
worker.onmessage = event => {
  console.log(event.data);
};

// Open the database
worker.postMessage({
  id: 1,
  action: "open",
  buffer: buf
});

// Execute a query
worker.postMessage({
  id: 2,
  action: "exec",
  sql: "SELECT age,name FROM test WHERE id=$id",
  params: { "$id": 1 }
});

To view and interact with the sql.js examples on your own machine, you must first start a local development server. Run the provided Python script to host the files, then access the index page via your web browser.

1. Execute the server script: ./start_local_server.py
2. Open http://localhost:8081/index.html in your browser.

Install sql.js via npm: npm install sql.js. In Node.js, you must initialize the library asynchronously using initSqlJs.

To read a database from the disk, use the fs module to read the file into a buffer and pass it to new SQL.Database(buffer).

To save a database to the disk, use db.export() to get the database contents as a byte array, convert it to a Node.js Buffer, and write it to a file using fs.writeFileSync.

// Reading from disk
const fs = require('fs');
const initSqlJs = require('sql-wasm.js');
const filebuffer = fs.readFileSync('test.sqlite');

initSqlJs().then(function(SQL) {
  const db = new SQL.Database(filebuffer);
});

// Writing to disk
const data = db.export();
const buffer = Buffer.from(data);
fs.writeFileSync("filename.sqlite", buffer);

When upgrading to version 1.x, note the following breaking changes:

1. Asynchronous Loading: Version 1.0 must be loaded asynchronously using initSqlJs(). Synchronous loading (previously possible with asm.js) is no longer supported.
2. Reserved Words: NOTHING is now a reserved word in SQLite. If your previous queries used NOTHING as an identifier, they may now fail with a syntax error.

To load a remote SQLite database file, fetch the file as an arrayBuffer, convert it to a Uint8Array, and pass it to the SQL.Database constructor. It is recommended to use Promise.all to initialize sql.js and fetch the data concurrently.

const sqlPromise = initSqlJs({
  locateFile: file => `https://path/to/your/dist/folder/dist/${file}`
});
const dataPromise = fetch("/path/to/database.sqlite").then(res => res.arrayBuffer());

const [SQL, buf] = await Promise.all([sqlPromise, dataPromise]);
const db = new SQL.Database(new Uint8Array(buf));

You can use XMLHttpRequest to fetch a database file by setting the responseType to 'arraybuffer'. Once the request completes, convert the response to a Uint8Array to initialize the database.

const xhr = new XMLHttpRequest();
xhr.open('GET', '/path/to/database.sqlite', true);
xhr.responseType = 'arraybuffer';

xhr.onload = e => {
  const uInt8Array = new Uint8Array(xhr.response);
  const db = new SQL.Database(uInt8Array);
  const contents = db.exec("SELECT * FROM my_table");
};
xhr.send();

You can instantiate a SQL.Database by passing a Uint8Array representing the database file. When a user selects a file via an HTML <input type="file">, use a FileReader to read the file as an ArrayBuffer, then convert that buffer into a Uint8Array to pass to the constructor.

dbFileElm.onchange = () => {
  const f = dbFileElm.files[0];
  const r = new FileReader();
  r.onload = function() {
    const Uints = new Uint8Array(r.result);
    db = new SQL.Database(Uints);
  }
  r.readAsArrayBuffer(f);
}