- Notifications
You must be signed in to change notification settings - Fork1.1k
A javascript library to run SQLite on the web.
License
sql-js/sql.js
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
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 inthis online demo. It uses avirtual database file stored in memory, and thusdoesn't persist the changes made to the database. However, it allows you toimport any existing sqlite file, and toexport the created database as aJavaScript typed array.
sql.js usesemscripten to compileSQLite to webassembly (or to javascript code for compatibility with older browsers). It includescontributed 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 usea 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.
Afull 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.
By default,sql.js useswasm, 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 froma CDN. Then use thelocateFile property of the configuration object passed toinitSqlJs to indicate where the file is. If you use an asset builder such as webpack, you can automate this. Seethis demo of how to integrate sql.js with webpack (and react).
constinitSqlJs=require('sql.js');// or if you are in a browser:// const initSqlJs = window.initSqlJs;constSQL=awaitinitSqlJs({// Required to load the wasm binary asynchronously. Of course, you can host it wherever you want// You can omit locateFile completely when running in nodelocateFile:file=>`https://sql.js.org/dist/${file}`});// Create a databaseconstdb=newSQL.Database();// NOTE: You can also use new SQL.Database(data) where// data is an Uint8Array representing an SQLite database file// Execute a single SQL string that contains multiple statementsletsqlstr="CREATE TABLE hello (a int, b char); \INSERT INTO hello VALUES (0, 'hello'); \INSERT INTO hello VALUES (1, 'world');";db.run(sqlstr);// Run the query without returning anything// Prepare an sql statementconststmt=db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval");// Bind values to the parameters and fetch the results of the queryconstresult=stmt.getAsObject({':aval' :1,':bval' :'world'});console.log(result);// Will print {a:1, b:'world'}// Bind other valuesstmt.bind([0,'hello']);while(stmt.step())console.log(stmt.get());// Will print [0, 'hello']// free the memory used by the statementstmt.free();// You can not use your statement anymore once it has been freed.// But not freeing your statements causes memory leaks. You don't want that.constres=db.exec("SELECT * FROM hello");/*[ {columns:['a','b'], values:[[0,'hello'],[1,'world']]}]*/// You can also use JavaScript functions inside your SQL code// Create the js function you needfunctionadd(a,b){returna+b;}// Specifies the SQL function's name, the number of it's arguments, and the js function to usedb.create_function("add_js",add);// Run a query in which the function is useddb.run("INSERT INTO hello VALUES (add_js(7, 3), add_js('Hello ', 'world'));");// Inserts 10 and 'Hello world'// You can create custom aggregation functions, by passing a name// and a set of functions to `db.create_aggregate`://// - an `init` function. This function receives no argument and returns// the initial value for the state of the aggregate function.// - a `step` function. This function takes two arguments// - the current state of the aggregation// - a new value to aggregate to the state// It should return a new value for the state.// - a `finalize` function. This function receives a state object, and// returns the final value of the aggregate. It can be omitted, in which case// the final value of the state will be returned directly by the aggregate function.//// Here is an example aggregation function, `json_agg`, which will collect all// input values and return them as a JSON array:db.create_aggregate("json_agg",{init:()=>[],step:(state,val)=>[...state,val],finalize:(state)=>JSON.stringify(state),});db.exec("SELECT json_agg(column1) FROM (VALUES ('hello'), ('world'))");// -> The result of the query is the string '["hello","world"]'// Export the database to an Uint8Array containing the SQLite database fileconstbinaryArray=db.export();
There are a few examplesavailable here. The most full-featured is theSqlite Interpreter.
The test files provide up to date example of the use of the api.
<metacharset="utf8"/><html><scriptsrc='/dist/sql-wasm.js'></script><script>config={locateFile:filename=>`/dist/${filename}`}// The `initSqlJs` function is globally provided by all of the main dist files if loaded in the browser.// We must specify this locateFile function if we are loading a wasm file from anywhere other than the current html page's folder.initSqlJs(config).then(function(SQL){//Create the databaseconstdb=newSQL.Database();// Run a query without reading the resultsdb.run("CREATE TABLE test (col1, col2);");// Insert two rows: (1,111) and (2,222)db.run("INSERT INTO test VALUES (?,?), (?,?)",[1,111,2,222]);// Prepare a statementconststmt=db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");stmt.getAsObject({$start:1,$end:1});// {col1:1, col2:111}// Bind new valuesstmt.bind({$start:1,$end:2});while(stmt.step()){//constrow=stmt.getAsObject();console.log('Here is a row: '+JSON.stringify(row));}});</script><body> Output is in Javascript console</body></html>
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:
dbFileElm.onchange=()=>{constf=dbFileElm.files[0];constr=newFileReader();r.onload=function(){constUints=newUint8Array(r.result);db=newSQL.Database(Uints);}r.readAsArrayBuffer(f);}
See :https://sql-js.github.io/sql.js/examples/GUI/gui.js
constsqlPromise=initSqlJs({locateFile:file=>`https://path/to/your/dist/folder/dist/${file}`});constdataPromise=fetch("/path/to/database.sqlite").then(res=>res.arrayBuffer());const[SQL,buf]=awaitPromise.all([sqlPromise,dataPromise])constdb=newSQL.Database(newUint8Array(buf));
constxhr=newXMLHttpRequest();// For example: https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlitexhr.open('GET','/path/to/database.sqlite',true);xhr.responseType='arraybuffer';xhr.onload=e=>{constuInt8Array=newUint8Array(xhr.response);constdb=newSQL.Database(uInt8Array);constcontents=db.exec("SELECT * FROM my_table");// contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]};xhr.send();
See:https://github.com/sql-js/sql.js/wiki/Load-a-database-from-the-server
sql.js ishosted on npm. To install it, you can simply runnpm install sql.js.Alternatively, you can simply downloadsql-wasm.js andsql-wasm.wasm, from the download link below.
constfs=require('fs');constinitSqlJs=require('sql-wasm.js');constfilebuffer=fs.readFileSync('test.sqlite');initSqlJs().then(function(SQL){// Load the dbconstdb=newSQL.Database(filebuffer);});
You need to convert the result ofdb.export to a buffer
constfs=require("fs");// [...] (create the database)constdata=db.export();constbuffer=Buffer.from(data);fs.writeFileSync("filename.sqlite",buffer);
See :https://github.com/sql-js/sql.js/blob/master/test/test_node_file.js
If you don't want to run CPU-intensive SQL queries in your main application thread,you can use themore limited WebWorker API.
You will need to downloadworker.sql-wasm.js andworker.sql-wasm.wasm from therelease page.
Example:
<script>constworker=newWorker("/dist/worker.sql-wasm.js");worker.onmessage=()=>{console.log("Database opened");worker.onmessage=event=>{console.log(event.data);// The result of the query};worker.postMessage({id:2,action:"exec",sql:"SELECT age,name FROM test WHERE id=$id",params:{"$id":1}});};worker.onerror=e=>console.log("Worker error: ",e);worker.postMessage({id:1,action:"open",buffer:buf,/*Optional. An ArrayBuffer representing an SQLite Database file*/});</script>
If you needBigInt support, it is partially supported since most browsers now supports it including Safari.BindingBigInt is still not supported, only gettingBigInt from the database is supported for now.
<script>conststmt=db.prepare("SELECT * FROM test");constconfig={useBigInt:true};/*Pass optional config param to the get function*/while(stmt.step())console.log(stmt.get(null,config));/*OR*/constresults=db.exec("SELECT * FROM test",config);console.log(results[0].values)</script>
On WebWorker, you can just addconfig param before posting a message. With this, you wont have to pass config param onget function.
<script>worker.postMessage({id:1,action:"exec",sql:"SELECT * FROM test",config:{useBigInt:true},/*Optional param*/});</script>
Seeexamples/GUI/gui.js for a full working example.
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.
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:
<scriptsrc='js/sql.js'></script><script>constdb=newSQL.Database();//...</script>
or:
constSQL=require('sql.js');constdb=newSQL.Database();//...
Version 1.x:
<scriptsrc='dist/sql-wasm.js'></script><script>initSqlJs({locateFile:filename=>`/dist/${filename}`}).then(function(SQL){constdb=newSQL.Database();//...});</script>
or:
constinitSqlJs=require('sql-wasm.js');initSqlJs().then(function(SQL){constdb=newSQL.Database();//...});
NOTHING is now a reserved word in SQLite, whereas previously it was not. This could cause errors likeError: near "nothing": syntax error
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, likesql-wasm.js andsql-wasm.wasm. The.js file is responsible for loading the.wasm file. You can find these files on ourrelease page
You can always find the latest published artifacts onhttps://github.com/sql-js/sql.js/releases/latest.
For eachrelease, you will find a file calledsqljs.zip in therelease 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/shipsql-wasm.wasmas 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/shipsql-wasm-debug.wasmif 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: TheDebug asm.js version of Sql.js. Use this for local development.worker.*- Web Worker versions of the above libraries. More limited API. Seeexamples/GUI/gui.js for a good example of this.
General consumers of this library don't need to read any further. (The compiled files are available via therelease page.)
If you want to compile your own version of SQLite for WebAssembly, or want to contribute to this project, seeCONTRIBUTING.md.
About
A javascript library to run SQLite on the web.
Topics
Resources
License
Uh oh!
There was an error while loading.Please reload this page.