sql.js

A javascript library to run SQLite on the web.

README


SQLite compiled to JavaScript

CI status npm CDNJS version

sql.js is a javascript SQL database. It allows you to create a relational database and query it entirely in the browser. You can try it in this online demo. It uses a virtual database file stored in memory, and thusdoesn't persist the changes made to the database. However, it allows you to import any existing sqlite file, and to export the created database as a JavaScript typed array.

sql.js uses emscripten to compile SQLite to webassembly (or to javascript code for compatibility with older browsers). It includes contributed math and string extension functions.

sql.js can be used like any traditional JavaScript library. If you are building a native application in JavaScript (using Electron for instance), or are working in node.js, you will likely prefer to use a native binding of SQLite to JavaScript. A native binding will not only be faster because it will run native code, but it will also be able to work on database files directly instead of having to load the entire database in memory, avoiding out of memory errors and further improving performances.

SQLite is public domain, sql.js is MIT licensed.

API documentation

A full API documentation for all the available classes and methods is available.
It is generated from comments inside the source code, and is thus always up to date.

Usage


By default, sql.js uses wasm, and thus needs to load a.wasm file in addition to the javascript library. You can find this file in ./node_modules/sql.js/dist/sql-wasm.wasm after installing sql.js from npm, and instruct your bundler to add it to your static assets or load it from a CDN. Then use the [locateFile](https://emscripten.org/docs/api_reference/module.html#Module.locateFile) property of the configuration object passed to initSqlJs to indicate where the file is. If you use an asset builder such as webpack, you can automate this. See this demo of how to integrate sql.js with webpack (and react).

  1. ``` js
  2. const initSqlJs = require('sql.js');
  3. // or if you are in a browser:
  4. // const initSqlJs = window.initSqlJs;

  5. const SQL = await initSqlJs({
  6.   // Required to load the wasm binary asynchronously. Of course, you can host it wherever you want
  7.   // You can omit locateFile completely when running in node
  8.   locateFile: file => `https://sql.js.org/dist/${file}`
  9. });

  10. // Create a database
  11. const db = new SQL.Database();
  12. // NOTE: You can also use new SQL.Database(data) where
  13. // data is an Uint8Array representing an SQLite database file


  14. // Execute a single SQL string that contains multiple statements
  15. let sqlstr = "CREATE TABLE hello (a int, b char); \
  16. INSERT INTO hello VALUES (0, 'hello'); \
  17. INSERT INTO hello VALUES (1, 'world');";
  18. db.run(sqlstr); // Run the query without returning anything

  19. // Prepare an sql statement
  20. const stmt = db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval");

  21. // Bind values to the parameters and fetch the results of the query
  22. const result = stmt.getAsObject({':aval' : 1, ':bval' : 'world'});
  23. console.log(result); // Will print {a:1, b:'world'}

  24. // Bind other values
  25. stmt.bind([0, 'hello']);
  26. while (stmt.step()) console.log(stmt.get()); // Will print [0, 'hello']
  27. // free the memory used by the statement
  28. stmt.free();
  29. // You can not use your statement anymore once it has been freed.
  30. // But not freeing your statements causes memory leaks. You don't want that.

  31. const res = db.exec("SELECT * FROM hello");
  32. /*
  33. [
  34.   {columns:['a','b'], values:[[0,'hello'],[1,'world']]}
  35. ]
  36. */

  37. // You can also use JavaScript functions inside your SQL code
  38. // Create the js function you need
  39. function add(a, b) {return a+b;}
  40. // Specifies the SQL function's name, the number of it's arguments, and the js function to use
  41. db.create_function("add_js", add);
  42. // Run a query in which the function is used
  43. db.run("INSERT INTO hello VALUES (add_js(7, 3), add_js('Hello ', 'world'));"); // Inserts 10 and 'Hello world'

  44. // You can create custom aggregation functions, by passing a name
  45. // and a set of functions to `db.create_aggregate`:
  46. //
  47. // - an `init` function. This function receives no argument and returns
  48. //   the initial value for the state of the aggregate function.
  49. // - a `step` function. This function takes two arguments
  50. //    - the current state of the aggregation
  51. //    - a new value to aggregate to the state
  52. //  It should return a new value for the state.
  53. // - a `finalize` function. This function receives a state object, and
  54. //   returns the final value of the aggregate. It can be omitted, in which case
  55. //   the final value of the state will be returned directly by the aggregate function.
  56. //
  57. // Here is an example aggregation function, `json_agg`, which will collect all
  58. // input values and return them as a JSON array:
  59. db.create_aggregate(
  60.   "json_agg",
  61.   {
  62.     init: () => [],
  63.     step: (state, val) => [...state, val],
  64.     finalize: (state) => JSON.stringify(state),
  65.   }
  66. );

  67. db.exec("SELECT json_agg(column1) FROM (VALUES ('hello'), ('world'))");
  68. // -> The result of the query is the string '["hello","world"]'

  69. // Export the database to an Uint8Array containing the SQLite database file
  70. const binaryArray = db.export();
  71. ```

Demo

There are a few examples available here. The most full-featured is the Sqlite Interpreter.

Examples

The test files provide up to date example of the use of the api.

Inside the browser

Example HTML file:

  1. ``` html
  2. <meta charset="utf8" />
  3. <html>
  4.   <script src='/dist/sql-wasm.js'></script>
  5.   <script>
  6.     config = {
  7.       locateFile: filename => `/dist/${filename}`
  8.     }
  9.     // The `initSqlJs` function is globally provided by all of the main dist files if loaded in the browser.
  10.     // We must specify this locateFile function if we are loading a wasm file from anywhere other than the current html page's folder.
  11.     initSqlJs(config).then(function(SQL){
  12.       //Create the database
  13.       const db = new SQL.Database();
  14.       // Run a query without reading the results
  15.       db.run("CREATE TABLE test (col1, col2);");
  16.       // Insert two rows: (1,111) and (2,222)
  17.       db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]);
  18.       // Prepare a statement
  19.       const stmt = db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");
  20.       stmt.getAsObject({$start:1, $end:1}); // {col1:1, col2:111}
  21.       // Bind new values
  22.       stmt.bind({$start:1, $end:2});
  23.       while(stmt.step()) { //
  24.         const row = stmt.getAsObject();
  25.         console.log('Here is a row: ' + JSON.stringify(row));
  26.       }
  27.     });
  28.   </script>
  29.   <body>
  30.     Output is in Javascript console
  31.   </body>
  32. </html>
  33. ```

Creating a database from a file chosen by the user

SQL.Database constructor takes an array of integer representing a database file as an optional parameter.
The following code uses an HTML input as the source for loading a database:
  1. ``` js
  2. dbFileElm.onchange = () => {
  3.   const f = dbFileElm.files[0];
  4.   const r = new FileReader();
  5.   r.onload = function() {
  6.     const Uints = new Uint8Array(r.result);
  7.     db = new SQL.Database(Uints);
  8.   }
  9.   r.readAsArrayBuffer(f);
  10. }
  11. ```
See : https://sql-js.github.io/sql.js/examples/GUI/gui.js

Loading a database from a server


using fetch

  1. ``` js
  2. const sqlPromise = initSqlJs({
  3.   locateFile: file => `https://path/to/your/dist/folder/dist/${file}`
  4. });
  5. const dataPromise = fetch("/path/to/database.sqlite").then(res => res.arrayBuffer());
  6. const [SQL, buf] = await Promise.all([sqlPromise, dataPromise])
  7. const db = new SQL.Database(new Uint8Array(buf));
  8. ```

using XMLHttpRequest

  1. ``` js
  2. const xhr = new XMLHttpRequest();
  3. // For example: https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite
  4. xhr.open('GET', '/path/to/database.sqlite', true);
  5. xhr.responseType = 'arraybuffer';

  6. xhr.onload = e => {
  7.   const uInt8Array = new Uint8Array(xhr.response);
  8.   const db = new SQL.Database(uInt8Array);
  9.   const contents = db.exec("SELECT * FROM my_table");
  10.   // contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]
  11. };
  12. xhr.send();
  13. ```
See: https://github.com/sql-js/sql.js/wiki/Load-a-database-from-the-server


Use from node.js


sql.js is hosted on npm. To install it, you can simply runnpm install sql.js.
Alternatively, you can simply download sql-wasm.js and sql-wasm.wasm, from the download link below.

read a database from the disk:

  1. ``` js
  2. const fs = require('fs');
  3. const initSqlJs = require('sql-wasm.js');
  4. const filebuffer = fs.readFileSync('test.sqlite');

  5. initSqlJs().then(function(SQL){
  6.   // Load the db
  7.   const db = new SQL.Database(filebuffer);
  8. });

  9. ```

write a database to the disk

You need to convert the result of db.export to a buffer
  1. ``` js
  2. const fs = require("fs");
  3. // [...] (create the database)
  4. const data = db.export();
  5. const buffer = Buffer.from(data);
  6. fs.writeFileSync("filename.sqlite", buffer);
  7. ```

See : https://github.com/sql-js/sql.js/blob/master/test/test_node_file.js

Use as web worker

If you don't want to run CPU-intensive SQL queries in your main application thread,
you can use the more limited WebWorker API.

You will need to download worker.sql-wasm.js and worker.sql-wasm.wasm from the release page.

Example:
  1. ``` html
  2. <script>
  3.   const worker = new Worker("/dist/worker.sql-wasm.js");
  4.   worker.onmessage = () => {
  5.     console.log("Database opened");
  6.     worker.onmessage = event => {
  7.       console.log(event.data); // The result of the query
  8.     };
  9.     worker.postMessage({
  10.       id: 2,
  11.       action: "exec",
  12.       sql: "SELECT age,name FROM test WHERE id=$id",
  13.       params: { "$id": 1 }
  14.     });
  15.   };
  16.   worker.onerror = e => console.log("Worker error: ", e);
  17.   worker.postMessage({
  18.     id:1,
  19.     action:"open",
  20.     buffer:buf, /*Optional. An ArrayBuffer representing an SQLite Database file*/
  21.   });
  22. </script>
  23. ```

Enabling BigInt support

If you need BigInt support, it is partially supported since most browsers now supports it including Safari.Binding BigInt is still not supported, only getting BigInt from the database is supported for now.

  1. ``` html
  2. <script>
  3.   const stmt = db.prepare("SELECT * FROM test");
  4.   const config = {useBigInt: true};
  5.   /*Pass optional config param to the get function*/
  6.   while (stmt.step()) console.log(stmt.get(null, config));
  7.   /*OR*/
  8.   const result = db.exec("SELECT * FROM test", config);
  9.   console.log(results[0].values)
  10. </script>
  11. ```
On WebWorker, you can just add config param before posting a message. With this, you wont have to pass config param on get function.

  1. ``` html
  2. <script>
  3.   worker.postMessage({
  4.     id:1,
  5.     action:"exec",
  6.     sql: "SELECT * FROM test",
  7.     config: {useBigInt: true}, /*Optional param*/
  8.   });
  9. </script>
  10. ```

See examples/GUI/gui.js for a full working example.

Flavors/versions Targets/Downloads


This library includes both WebAssembly and asm.js versions of Sqlite. (WebAssembly is the newer, preferred way to compile to JavaScript, and has superceded asm.js. It produces smaller, faster code.) Asm.js versions are included for compatibility.

Upgrading from 0.x to 1.x


Version 1.0 of sql.js must be loaded asynchronously, whereas asm.js was able to be loaded synchronously.

So in the past, you would:
  1. ``` html
  2. <script src='js/sql.js'></script>
  3. <script>
  4.   const db = new SQL.Database();
  5.   //...
  6. </script>
  7. ```
or:
  1. ``` js
  2. const SQL = require('sql.js');
  3. const db = new SQL.Database();
  4. //...
  5. ```

Version 1.x:
  1. ``` html
  2. <script src='dist/sql-wasm.js'></script>
  3. <script>
  4.   initSqlJs({ locateFile: filename => `/dist/${filename}` }).then(function(SQL){
  5.     const db = new SQL.Database();
  6.     //...
  7.   });
  8. </script>
  9. ```
or:
  1. ``` js
  2. const initSqlJs = require('sql-wasm.js');
  3. initSqlJs().then(function(SQL){
  4.   const db = new SQL.Database();
  5.   //...
  6. });
  7. ```

NOTHING is now a reserved word in SQLite, whereas previously it was not. This could cause errors like Error: near "nothing": syntax error

Downloading/Using: ###

Although asm.js files were distributed as a single Javascript file, WebAssembly libraries are most efficiently distributed as a pair of files, the .js  loader and the .wasm file, like sql-wasm.js and sql-wasm.wasm. The .js file is responsible for loading the .wasm file. You can find these files on our release page




Versions of sql.js included in the distributed artifacts

You can always find the latest published artifacts on https://github.com/sql-js/sql.js/releases/latest.

For each release, you will find a file calledsqljs.zip in the release assets. It will contain:
- sql-wasm.js : The Web Assembly version of Sql.js. Minified and suitable for production. Use this. If you use this, you will need to include/ship sql-wasm.wasm as well.
- sql-wasm-debug.js : The Web Assembly, Debug version of Sql.js. Larger, with assertions turned on. Useful for local development. You will need to include/ship sql-wasm-debug.wasm if you use this.
- sql-asm.js : The older asm.js version of Sql.js. Slower and larger. Provided for compatibility reasons.
- sql-asm-memory-growth.js : Asm.js doesn't allow for memory to grow by default, because it is slower and de-optimizes. If you are using sql-asm.js and you see this error (Cannot enlarge memory arrays), use this file.
- sql-asm-debug.js : The _Debug_ asm.js version of Sql.js. Use this for local development.
- worker.* - Web Worker versions of the above libraries. More limited API. See examples/GUI/gui.js for a good example of this.

Compiling/Contributing


General consumers of this library don't need to read any further. (The compiled files are available via the release page.)

If you want to compile your own version of SQLite for WebAssembly, or want to contribute to this project, see CONTRIBUTING.md.