SQLite is an embedded database that's used by thousands of applications. Instead of needing a server and a connection system like most databases, SQLite allows us to utilize it like any other library. But what's kept us from developing with this type of power in the browser has been a way to import this without needing native bindings. To utilize it in Node.js, we would need to utilize something like node-gyp and then create JavaScript bindings to the underlying C code.
We have a way of utilizing this database in the browser without needing these native bindings, thanks to WebAssembly. For a version that has already been compiled for us, go to https://github.com/kripken/sql.js/ and pull the repository into our local system. Let's go ahead and set up our static server to bring in all of the files for us. Follow these steps:
- Create a new directory called sqlitetest.
- Inside this directory, go ahead and run the following command to clone the repository from GitHub:
> git clone https://github.com/kripken/sql.js.git
- With this, we can create a basic index.html file and add the following code to it:
<!DOCTYPE html>
<html>
<head>
<script src='sqljs/dist/sql-wasm.js'></script>
</head>
<body>
<script type="module">
initSqlJs({locateFile: () => `sqljs/dist/sql-wasm.wasm`
}).then(function(SQL){
console.log("SQL", SQL);
});
</script>
</body>
</html>
If we look inside our developer tools, we will see that we have the SQLite library up and running in our browser! Let's go ahead and create some tables and populate them with some data:
- We are going to create a simple two-table database. These two tables will look as follows:
| id | first_name | last_name | username |
| <auto_increment> | <text> | <text> | <text> |
| id | customer_id |
op
|
timestamp |
| <auto_increment> | <foreign_key> | <text> | <integer> |
Essentially, we will be simulating a remote procedure call server where, when customers make calls to it, we will log which operation they performed and the timestamp that they performed it at.
To make these tables in our SQLite database, we will run the following code:
initSqlJs({locateFile: () => `sqljs/dist/sql-wasm.wasm` }).then(function(SQL){
const db = new SQL.Database();
db.run(`CREATE TABLE customer
(id INTEGER PRIMARY KEY ASC,
first_name TEXT,
last_name TEXT,
username TEXT UNIQUE)
`);
db.run(`CREATE TABLE rpc_operations
(id INTEGER PRIMARY KEY ASC,
customer_id INTEGER,
op TEXT,
timestamp INTEGER,
FOREIGN KEY(customer_id) REFERENCES customer(id))`);
});
Now, we have a simple two-table database that has everything we need in it to get moving.
- Let's go ahead and populate this with some data for each of the tables. We can do this with the following commands:
const insertCustomerData = `INSERT INTO customer VALUES (NULL, ?, ?, ?)`;
const insertRpcData = `INSERT INTO rpc_operations VALUES (NULL, ?, ?, time('now'))`;
const customers = [
['Morissa', 'Catford', 'mcatford0'],
['Aguistin', 'Blaxlande', 'ablaxlande1']
];
const ops = [
['1', 'add'],
['2', 'subtract']
]
for(let i = 0; i < customers.length; i++) {
db.run(insertCustomerData, customers[i]);
}
for(let i = 0; i < ops.length; i++) {
db.run(insertRpcData, ops[i]);
}
With this bit of code, we have entered our test data. Now, let's run the following command:
const statement = db.prepare("SELECT * FROM customer c JOIN rpc_operations ro ON c.id = ro.customer_id WHERE c.username = $username");
statement.bind({$username : 'mcatford0'});
while(statement.step()) {
const row = statement.getAsObject();
console.log(JSON.stringify(row));
}
We have successfully run a SQL database in our browser!
For more information on how to utilize this, go to https://github.com/kripken/sql.js/. To get the SQLite reference documentation, go to https://www.sqlite.org/lang.html.
Now, being able to run a SQL engine in our browser is awesome, but let's take a look at how some of the underlying C code got turned into something that our browser understands. If we head to https://www.sqlite.org/download.html and download the latest release, we can pull up the sqlite3.c code base. Now that we have the code base, let's look for something that we might be able to see in the WebAssembly printout. Follow these steps:
We will utilize the wasm2wat tool that we received when we installed the wasm binary tools. Head into the dist folder of the sqljs folder and run the following command:
> wasm2wat sql-wasm-debug.wasm --output=sql-wasm.wat
Now, we can open that file to see the generated WebAssembly in a human-readable fashion. As we can see, it isn't that readable, but near the top, we can see a bunch of imports from Emscripten. We should realize that all of these are functions that Emscripten provides from their JavaScript API and that they are utilized to compile everything to WebAssembly and be usable.
Next, let's go to the bottom of the file. We'll notice that there are a bunch of exports that are named. Each of these should correspond to a function found in the c file. Let's go ahead and take a look at a semi-simple one: sqlite3_data_count. It should look as follows:
else
i32.const 0
end
else
i32.const 0
end)
We will see this return type in the C code if the pointer is NULL. If the result is NULL, we will return 0. This is how we can debug C programs that we are porting to the web. While this isn't easy, it can help us when we need to do this type of debugging.
This chapter covered just a taste of the libraries that have already been ported. Every day, more libraries are being ported, as well as languages, that can be compiled to WebAssembly.