SQL, or Structured Query Language, is a domain-specific language designed for accessing and manipulating data held in a relational database. With the help of SQL statements, you can perform operations such as creating, updating, retrieving, and deleting data on a relational database.
A relational database is a data model that organizes data in a table using rows and columns. While there might be multiple relational database management systems that use SQL, almost all of these are designed to work as server-side processes. These include, among others:
- MySQL
- PostgreSQL
- SQLite
- MSSQL
In this tutorial, we’ll discuss SQL.js, a JavaScript SQL library that enables you to create and query relational databases entirely in your browser.
We’ll walk through the following steps with a detailed example for each section:
- What is SQL.js?
- Pros and cons of using SQL.js
- Installation (browser and Node.js)
- Writing SQL queries and prepared statements
What is SQL.js?
SQL.js is a JavaScript library that allows you to create and query a relational database entirely in the browser. It uses a virtual database file stored in the browser memory, so it doesn’t persist the changes made to the database.
This library also uses Emscripten to compile SQLite to WebAssembly (Wasm). With this feature, you can easily bring in an existing SQLite database to use in SQL.js and also convert a database created in SQL.js to SQLite.
Pros and cons of using SQL.js
There are several pros to using SQL.js. It’s built for and works entirely on the client side, which means it won’t require any server-side processes to work. It’s easier to set up than MySQL, PostgreSQL, and others, which require third-party software to use. Getting started with SQL.js is as easy as installing jQuery in an existing HTML project. And SQL.js provides support for executing single SQL strings that contain multiple statements, as seen below:
sqlstr = "CREATE TABLE tableName(colA, colB);";
sqlstr += "INSERT INTO hello VALUES (0, 'hello');"
....
But there is an important con as well: changes to your database when using SQL.js are not persistent. Read it again: all changes made to your database will cease to exist when you reload your browser. This is because SQL.js uses a virtual database file stored in the browser memory. However, you can import any existing SQLite file and export the created database as a JavaScript typed array.
Installing SQL.js
Browser installation
Integrating SQL.js into a new client-side-based project is super easy. You can get started by including the CDN or downloading the source files and linking them to your markup page.
By default, SQL.js uses WebAssembly and needs to load a .wasm file in addition to the JavaScript library. You can download this file online and use the locateFile function to load the file in your webpage, like so:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Page Title</title>
<script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.5.0/sql-wasm.js"></script>
<script>
// Load sql.js WebAssembly file
let config = {
locateFile: () => "/path/to/downloaded/sql-wasm.wasm",
};
initSqlJs(config).then(function (SQL) {
console.log("sql.js initialized 🎉");
});
</script>
</head>
<body></body>
</html>
In the code block above, we used initSqlJs to load the Wasm binary file asynchronously and initialize SQL.js after loading the required files.
Node.js installation
Installing SQL.js in a Node-based project is pretty straightforward as well. To install it, you can simply run:
npm install sql.js
Alternatively, you can download sql-wasm.js and sql-wasm.wasm from the previous links and use the Node.js require function to load them in your project.
Also, in a Node-based project, you can skip the locateFile method to load the sql-wasm.wasm file because it will load automatically if it’s in the same folder as the current file you are working on. So our code will look like this:
var initSqlJs = require("./sql-wasm.js");
initSqlJs().then(function (SQL) {
console.log("sql.js initialized 🎉");
});
Writing SQL queries and prepared statements
Now that we’ve followed all the required steps to install and initialize SQL.js, let’s dive into its usage.
Creating a database
The code below creates a new database:
const db = new SQL.Database();
It’s worth noting that the SQL.Database() method accepts an optional parameter data, which must be a Uint8Array representing a SQLite database file. For example, in Node.js we can load an existing .sqlite file like below:
let fs = require("fs");
let initSqlJs = require("./sql-wasm.js");
let filebuffer = fs.readFileSync("/path/to/sample.sqlite");
initSqlJs().then(function (SQL) {
// Create a new database with our existing sample.sqlite file
const db = new SQL.Database(filebuffer);
});
In the code above, we used the built-in Node.js fs and path modules to read our existing sample.sqlite file.
Running SQL statements
An SQL statement could be a request to create or retrieve a piece of information in the database or to perform an operation on existing data.
With SQL.js, you can easily run a statement without reading its results. The syntax is as written below:
db.run(stmt);
The parameter stmt is, of course, your SQL statement. Below is an example of how to create a new table named users with columns for ID, name, phone number, and address in our database. It will also insert a new row into this table.
let initSqlJs = require("./sql-wasm.js");
initSqlJs().then(function (SQL) {
const db = new SQL.Database();
// RUNNING SQL QUERIES 👇
db.run("CREATE TABLE users (id, name, phone, address);");
db.run(
`INSERT INTO users (id, name, phone, address)
VALUES (1, 'John Doe', '+234-907788', '12 Igodan Street, Okitipupa')`
);
});
Prepared SQL statements
You can use prepared statements to execute the same or similar SQL statements repeatedly with high efficiency. Prepared statements have a much shorter parsing time than running SQL statements because the preparation on the query is done only once. They are also very useful against SQL injections because you don’t need to escape parameter values, which are transmitted later using a different protocol.
With SQL.js, we can also write prepared statements using the .prepare() method:
var stmt = db.prepare(preparedStatement);
Below is an example to fetch all users with an ID between one and 10 in our previous database:
var stmt = db.prepare("SELECT * FROM users WHERE id BETWEEN $start AND $end");
stmt.bind({ $start: 1, $end: 2 });
while (stmt.step()) {
var row = stmt.getAsObject();
console.log("Here is a user row: " + JSON.stringify(row));
}
After writing our prepared SQL statements, we use the .bind() method to bind our statement required values (start and end in the above example). Moving forward, we’ll use a while loop to return all the possible rows and log them to the console.
Write a database to the disk
SQL.js also provides an option for exporting/writing a database to disk as a .sqlite file via the db.export() method. The result will be returned as a Uint8Array, and you can use the Node.js Buffer class and file system package to write the database to disk:
var fs = require("fs");
/***
Code to create a database here
***/
// Export database 👇
var data = db.export();
var buffer = new Buffer(data);
fs.writeFileSync("new-db.sqlite", buffer);
After running the code above, you should see a new file named new-db.sqlite in your project root folder.
Conclusion
In this article, we covered SQL.js, the features it offers, and how to use it in real-world applications. While SQL.js might be great for building an offline-first application such as a note-taking app, you may want to consider using other relational databases (MySQL, PostgreSQL) in cases where you want to manage user data from an admin panel.
Are you adding new JS libraries to improve performance or build new features? What if they’re doing the opposite?
There’s no doubt that frontends are getting more complex. As you add new JavaScript libraries and other dependencies to your app, you’ll need more visibility to ensure your users don’t run into unknown issues.
LogRocket is a frontend application monitoring solution that lets you replay JavaScript errors as if they happened in your own browser so you can react to bugs more effectively.
https://logrocket.com/signup/LogRocket works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store. Instead of guessing why problems happen, you can aggregate and report on what state your application was in when an issue occurred. LogRocket also monitors your app’s performance, reporting metrics like client CPU load, client memory usage, and more.
Build confidently — Start monitoring for free.
Too bad it is only saved on the memory
I have tried something similar called dexie.js, it has the upper hand of saving the data to indexeddb, so the data will persist after each reload
How safe is it for production?
Why storing data in the memory? what’s the benefit ? and what kind of data can be stored in the memory?
Thank you for sharing this information with us , But I have a question with you.
How safe is it for production?
Please reply it will helpful for me .
Really appreciate your work.
Hi Isabella, glad you found the article helpful.
SQL.js is an open source project, and yes it is safe to use in production