SQL, or structured Query Language, is a domain-specific language designed to access and manipulate data in relational databases. With the help of SQL statements, you can create, update, retrieve, and delete data on a relational database.

A relational database is a data model that uses rows and columns to organize data in a table. While there may be multiple relational database management systems that use SQL, almost all of them are designed as server-side processes. These systems include, inter alia.

  • 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 will go through the following steps and provide a detailed example for each section.

  • What is sql.js?
  • Advantages and disadvantages of using SQL.js
  • Install (browser and Node.js)
  • Write SQL queries and prepare statements

What is sql.js?

Sql.js is a JavaScript library that allows you to create and query a relational database entirely in your browser. It uses a virtual database file stored in the browser’s memory, so it does not persist changes to the database.

The library also uses Emscripten to compile SQLite into WebAssembly (Wasm). With this feature, you can easily import existing SQLite databases into SQL.js for use, or convert databases created in SQL.js to SQLite.

Advantages and disadvantages of using SQL.js

There are several advantages to using SQL.js. It is built for the client side and works entirely on the client side, meaning that it does not require any server-side processes to work. It’s easier to set up than MySQL, PostgreSQL, and other programs that require third-party software. Getting started with SQL.js is as easy as installing jQuery in an existing HTML project. Sql.js also provides support for executing a single SQL string containing multiple statements, as shown in the figure below.

sqlstr = "CREATE TABLE tableName(colA, colB);" ; sqlstr += "INSERT INTO hello VALUES (0, 'hello');" .Copy the code

However, there is an important drawback: changes to the database when using SQL.js are not durable. Read it again: When you reload your browser, all the changes you made to your database are gone. This is because SQL.js uses a virtual database file stored in the browser’s memory. However, you can import any existing SQLite file and export the created database as an array of JavaScript types.

Install SQL. Js

Browser Installation

Integrating SQL.js into a new client-based project is super easy. You can get started by including the CDN or downloading the source file and linking it to your tag page.

By default, SQL.js uses WebAssembly, and you need to load a. Wasm file in addition to the JavaScript library. You can download the file online and use the locateFile function to load the file in your web page, like this.

<! 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 > SQL. / / the Load 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>Copy the code

In the above code block, we use initSqlJs to load the Wasm binary asynchronously and initialize sqL.js after loading the required files.

The installation of the Node. Js

Installing SQL.js in a Node-based project is also very simple. To install it, you can simply run it.

npm install sql.js

Copy the code

Alternatively, you can download SQL-wasM. js and SQL-wasm. wasm from the previous link and load them in your project using the Node.jsrequire function.

Also, in node-based projects, you can skip the locateFile method to load the SQL-wasm.wasm file, because if it’s in the same folder as the current file you’re working on, it will load automatically. So our code will look something like this.

var initSqlJs = require("./sql-wasm.js");

initSqlJs().then(function (SQL) {
  console.log("sql.js initialized 🎉");
});

Copy the code

Write SQL queries and prepare statements

Now that we’ve followed all the required steps to install and initialize SQl.js, let’s take a closer look at its use.

Create a database

The following code creates a new database.

const db = new SQL.Database();

Copy the code

Note that the SQL.database () method takes an optional parameter, data, which must be a Uint8Array that represents the SQLite Database file. For example, in Node.js, we can load an existing.sqLite file like this.

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);
});

Copy the code

In the above code, we use the built-in Node. JSFS and path modules to read our existing sample.sqlite file.

Running SQL statements

An SQL statement can be a request to create or retrieve a piece of information in a database, or an operation on existing data.

With sql.js, you can easily run a statement without reading its results. The syntax is as follows.

db.run(stmt);

Copy the code

The argument STMT, of course, is your SQL statement. Here is an example of how to create a new table named Users in our database with columns such as ID, name, phone number, and address. It will also insert a new record into the 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')`
  );
});

Copy the code

Prepared SQL statements

You can use prepared statements to execute the same or similar SQL statements repeatedly and efficiently. Prepared statements take much less time to parse than running SQL statements, because the preparation of the query is done only once. They are also useful for preventing SQL injection because you do not need to escape parameter values that will be transmitted later using a different protocol.

With sql.js, we can also write prepared statements using the.prepare() method.

var stmt = db.prepare(preparedStatement);

Copy the code

Here is an example of getting all users with ids between 1 and 10 in our old 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));
  }

Copy the code

After writing our prepared SQL statement, we use the.bind() method to bind the values needed for our statement (in the above example, start and end). Moving forward, we’ll use a while loop to return all possible rows and log them to the console.

Write the database to disk

Sql.js also provides an option to export/write the database to disk as an.sqLite file using the db.export() method. The result will be Uint8Array, where you can use node.js’s Buffer class and filesystem 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);Copy the code

After running the code above, you should see a new file named new-db.sqlite in your project root directory.

conclusion

In this article, we introduced SQL.js, the capabilities it provides, and how to use it in real-world applications. While SQL.js may be good for building an offline first application, such as a Notepad application, you may want to consider using other relational databases (MySQL, PostgreSQL) in cases where you want to manage user data from the admin panel.

Further reading

  • PostGraphile PostgreSQL and GraphQL introduction
  • Speed up your development environment with SQLite
  • SQL.js GitHub
  • Emscripten
  • The script

The postA detailed look at basic SQL.js featuresappeared first onLogRocket Blog.