Unlocking SQLite 3: Your Beginner's Guide to Browser Databases
Written on
Chapter 1: Introduction to SQLite 3
SQLite 3 recently became accessible in web browsers through its WebAssembly compilation. This innovation allows users to leverage the capabilities of one of the most efficient relational databases directly from their browser environment. After exploring this feature, I created a test page for basic operations. Below, I’ll share my insights on the initial steps to get started.
What is SQLite 3?
SQLite is a lightweight, free relational database stored in a single file. Unlike traditional databases, it operates without the need for a server, similar to MS Access. Two key aspects that draw me to this technology are:
- It is free of charge.
- It is open-source and can run locally.
This local functionality enables the development of applications that function entirely offline. For comprehensive documentation and downloads, visit the official SQLite website at sqlite.org.
Chapter 2: Setting Up SQLite 3 in Your Browser
Previously, using SQLite directly within a web page was a challenge, only achievable through various independent projects. To address this, a WebAssembly version of SQLite 3 was created. You can find extensive information and documentation on the official site.
How to Utilize SQLite 3 in a Web Environment
To begin using SQLite 3 in a browser, the first step is to download necessary files. Look for the sqlite-wasm-XXXX.zip file (where XXXX signifies your chosen SQLite 3 version) and download it. Within this file, you will find the jswasm folder containing essential files such as:
- sqlite3.js
- sqlite3.wasm
- Additional files like sqlite3-opfs-async-proxy.js, sqlite3-worker1.js, and sqlite3-worker1-promiser.js.
Create a project folder and include references to these files. You only need to import sqlite3.js once to load sqlite3.wasm. Before utilizing SQLite 3, initialization is required. Here’s a function to assist with that:
const initSqlite3 = async () => {
const sqlite3 = await self["sqlite3InitModule"]();
self["sqlite3"] = sqlite3;
return sqlite3;
};
This function allows access to SQLite 3 via self["sqlite3"] from any part of your code. You can verify the setup with the following code:
initSqlite3().then((s) => {
sqlite3 = s;
console.log("sqlite3:", sqlite3);
console.log("sqlite3 version", sqlite3.capi.sqlite3_libversion());
});
The first video, How to Use DB Browser for SQLite (Absolute Beginners Guide), provides an excellent introduction to using SQLite in a browser environment. It explains the core functionalities and helps beginners get started effectively.
Chapter 3: Creating and Managing Databases
Once SQLite 3 is initialized, you can proceed to create a database. Here’s a function to simplify this process:
export const createDatabase = (filename = ":memory:", flags = "c") =>
new self["sqlite3"].oo1.DB({ filename, flags });
In this function, the flags parameter allows you to specify the behavior of the database creation:
- c: Create if it doesn’t exist; otherwise, fail.
- w: Write access.
- r: Read-only unless w or c is provided.
- t: Enable SQL tracing.
To create a database, run the following code:
const db = createDatabase("xyz.sqlite3", "ct");
console.log("db", db.filename); // xyz.sqlite3
Chapter 4: Adding Tables and Inserting Data
An empty database is not particularly useful, so let's create a table using the exec method:
const createTable = (db, tableName) =>
db.exec({
sql: CREATE TABLE IF NOT EXISTS "${tableName}"(a,b),});
You can execute multiple operations at once with:
db.exec(["create table t(a);", "insert into t(a) values(10),(20),(30)"]);
To insert values into a table, use the exec method similarly:
const insertData = (db, tableName, data) =>
db.exec({
sql: INSERT INTO "${tableName}" VALUES(?,?),
bind: data,
});
Generating random numbers can be done like this:
const rand = (max) => Math.floor(Math.random() * max);
To insert multiple values, you can create a loop:
const addFakeData = (tableName) => {
for (let i = 20; i <= 25; ++i) {
insertData(db, tableName, [rand(100), rand(100)]);}
};
The second video, SQLite Introduction - Beginners Guide to SQL and Databases, delves into fundamental concepts of SQL and how to effectively use databases, making it a great resource for newcomers.
Chapter 5: Reading Data from SQLite 3
Next, let's explore how to read data from a table using the exec method. You can specify the format of the returned data with rowMode and provide a callback function for processing:
const readData = (db, tableName, callback) =>
db.exec({
sql: SELECT * FROM "${tableName}",
rowMode: "array",
callback,
});
For example, to retrieve fake data, you might use:
const getFakeData = (db, tableName) => {
let result = [];
db.exec({
sql: SELECT a AS aa, b AS bb FROM "${tableName}" ORDER BY aa LIMIT 10,
rowMode: "object",
callback: function (row) {
console.log("row ", ++this.counter, "=", JSON.stringify(row));
result = [...result, row];
}.bind({ counter: 0 }),
});
return result;
};
You can further simplify the code by setting returnValue to resultRows:
const getFakeData = (db, tableName) =>
db.exec({
sql: SELECT a AS aa, b AS bb FROM "${tableName}" ORDER BY aa LIMIT 10,
rowMode: "object",
returnValue: "resultRows",
});
Chapter 6: Saving Your Database
To save your SQLite database locally, utilize the C-style API with the sqlite3_js_db_export() method:
const byteArray = self["sqlite3"].capi.sqlite3_js_db_export(db.pointer);
This method returns a Uint8Array, which you can convert to a Blob:
const blob = new Blob([byteArray.buffer], {
type: "application/x-sqlite3",
});
To streamline this process, create a function:
const downloadDB = (db) => {
const byteArray = self["sqlite3"].capi.sqlite3_js_db_export(db.pointer);
const blob = new Blob([byteArray.buffer], {
type: "application/x-sqlite3",});
const a = document.createElement("a");
document.body.appendChild(a);
a.href = window.URL.createObjectURL(blob);
a.download = db.filename.split("/").pop() || db.name;
a.addEventListener("click", function () {
setTimeout(function () {
console.log("Exported (possibly auto-downloaded) database");
window.URL.revokeObjectURL(a.href);
a.remove();
}, 500);
});
a.click();
};
Thank you for reading! Stay tuned for more insights on using SQLite 3 effectively in your browser.