SQLite3 with vanilla JavaScript and OPFS – Hello world

Leave a Comment / JavaScript, SQLite3 / By

In this post I will show you how to use SQLite3 directly in the web browser using the original C library through WebAssembly and OPFS to store the database. In this way we will have original SQLite3 on the web to work in the browser with JS on the client side.

We can do this with pure JavaScript or with any framework; but I will show you a basic and well explained example of SQLite3 with vanilla JavaScript. Then I’ll show you an example with Tailwind, Svelte and Progressive Web apps.

Using SQLite3 on the web

The use of this library is not new; what is new is the OPFS that in simple words allows a file system in the web browser, and that was exactly what was missing to be able to use SQLite3 in the browser.

For this to work, we will always need to add these headers when serving the WASM from the library:

  • Cross-Origin-Opener-Policy: same-origin
  • Cross-Origin-Embedder-Policy: require-corp

And to interact with the database with OPFS it is mandatory to use a Web Worker. You will see that it is not complex, but do not forget these 2 important things.

Finished project and source code

If you want to explore the full source code look at the GitHub repository: https://github.com/parzibyte/hello-sqlite3

You can also try the online demo: https://stackblitz.com/edit/vitejs-vite-jbwamt?file=main.js

I recommend reading the entire post to solve all your possible doubts, and at the end of it, if you want to explore more, you can watch the following video:

Installing library

We can install the library with:

npm install @sqlite.org/sqlite-wasm

Although we are going to use pure client-side JS, we will need NPM to manage the packages. If you already use NPM then you’ll understand what I’m talking about, just install that dependency.

In case you have a clean project, I recommend Vite, because it doesn’t force us to use any framework, it’s extremely light, it allows us to use pure JS and it’s fast. I have created my project with:

npm create vite@latest

After that, I installed the sqlite-wasm library. By the way, if you use vite you need to add or modify the vite.config.js so that it looks like this:

import { defineConfig } from 'vite';

export default defineConfig({
	base: "./",
	server: {
		headers: {
			'Cross-Origin-Opener-Policy': 'same-origin',
			'Cross-Origin-Embedder-Policy': 'require-corp',
		},
	},
	optimizeDeps: {
		exclude: ['@sqlite.org/sqlite-wasm'],
	},
});

The Web Worker

Now let’s see the main worker where we are going to init the database and do the operations (insert, select, delete, update and any SQL query).

For this case I am going to insert a person with their name and date of birth, as well as make a function to get all the people. All operations will be made by using SQL queries.

The worker is divided into the main functions that interact with the database:

const iniciar = async () => {
	const sqlite3 = await sqlite3InitModule({
		print: console.log,
		printErr: console.error,
	});
	if ('opfs' in sqlite3) {
		db = new sqlite3.oo1.OpfsDb(NOMBRE_BASE_DE_DATOS);
		console.log('OPFS is available, created persisted database at', db.filename);
	} else {
		db = new sqlite3.oo1.DB(NOMBRE_BASE_DE_DATOS, 'ct');
		console.log('OPFS is not available, created transient database', db.filename);
	}
	await db.exec(`CREATE TABLE IF NOT EXISTS personas(
				id INTEGER PRIMARY KEY AUTOINCREMENT,
				nombre TEXT NOT NULL,
				fechaNacimiento TEXT NOT NULL)`);
}

const insertarPersona = async (nombre, fechaNacimiento) => {
	const filas = await db.exec({
		sql: "INSERT INTO personas(nombre, fechaNacimiento) VALUES (?, ?) RETURNING *",
		bind: [nombre, fechaNacimiento],
		returnValue: "resultRows",
		rowMode: "object",
	});
	return filas[0];
}
const obtenerPersonas = async () => {
	return await db.exec({
		sql: "SELECT id, nombre, fechaNacimiento FROM personas",
		returnValue: "resultRows",
		rowMode: "object",
	});
}

And in the communication with the main thread, because keep in mind that the worker cannot modify the DOM directly:

self.onmessage = async (evento) => {
	const accion = evento.data[0];
	const argumentos = evento.data[1];
	switch (accion) {
		case "iniciar":
			await iniciar();
			self.postMessage(["iniciado"]);
			break;
		case "insertar_persona":
			const personaRecienInsertada = await insertarPersona(argumentos.nombre, argumentos.fechaNacimiento);
			self.postMessage(["persona_insertada", personaRecienInsertada]);
			break;
		case "obtener_personas":
			const personas = await obtenerPersonas();
			self.postMessage(["personas_obtenidas", personas]);
			break;
	}
}

Note: You can see the complete code for db.js in the GitHub repository. It is important to mention that calling sqlite3InitModule will download the WASM.

HTML code to interact with SQLite3

We now have our web worker ready to be called from anywhere. Let’s see our interface:

<!doctype html>
<html lang="en">

<head>
  <meta charset="UTF-8" />
  <link rel="icon" type="image/svg+xml" href="/vite.svg" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <title>Vite App</title>
</head>

<body>
  <button id="btnIniciarBaseDeDatos">Iniciar base de datos</button>
  <br>
  <br>
  <input disabled value="Parzibyte" id="nombre" type="text" placeholder="Nombre">
  <input disabled value="2050-01-01" id="fechaNacimiento" type="date">
  <button disabled id="btnInsertar">Insertar persona</button>
  <br>
  <br>
  <button disabled id="btnObtener">Obtener lista de personas</button>
  <div id="contenedorPersonas"></div>
  <script type="module" src="/main.js"></script>
</body>

</html>

We only have buttons and some text fields. The important thing is the main.js file, because that is where we will put both things together: the DOM and SQLite3 through the worker.

The code looks like the following, we simply listen for the click of the buttons, call a function of the worker and also listen when the worker returns the results or when it call us back:

const worker = new Worker(new URL("./db.js", import.meta.url), { type: "module" });
const $iniciarBaseDeDatos = document.querySelector("#btnIniciarBaseDeDatos"),
  $insertar = document.querySelector("#btnInsertar"),
  $obtener = document.querySelector("#btnObtener"),
  $nombre = document.querySelector("#nombre"),
  $fechaNacimiento = document.querySelector("#fechaNacimiento"),
  $contenedorPersonas = document.querySelector("#contenedorPersonas");

$insertar.addEventListener("click", () => {
  worker.postMessage(["insertar_persona", { nombre: $nombre.value, fechaNacimiento: $fechaNacimiento.value }]);
});
$obtener.addEventListener("click", () => {
  worker.postMessage(["obtener_personas"]);
});
$iniciarBaseDeDatos.onclick = () => {
  worker.postMessage(["iniciar"]);
}
worker.onmessage = evento => {
  const accion = evento.data[0];
  const argumentos = evento.data[1];
  switch (accion) {
    case "iniciado":
      [$nombre, $fechaNacimiento, $insertar, $obtener].forEach(elemento => elemento.disabled = false);
      break;
    case "persona_insertada":
      console.log({ argumentos });
      break;
    case "personas_obtenidas":
      const personas = argumentos;
      $contenedorPersonas.innerHTML = "";
      for (const persona of personas) {
        $contenedorPersonas.innerHTML += `<strong>${persona.nombre}</strong> ${persona.fechaNacimiento}<br>`;
      }
      break;
  }
}

Basically that’s all the code. There is a lot of code that goes into communicating the worker with the DOM, but we could simplify it with alternatives like Comlink.

Conclusion and next steps

As I told you at the beginning, this is a basic example of SQLite3 with OPFS directly in the web browser with pure JavaScript on the client side. If you want you can see the explanation on video.

I have already published my first impressions in my previous post on how to use SQLite3 with Svelte, and I have also developed a notes app that I have not yet published (although I have documented the making of).

I didn’t think the day would come when Workers, PWAs and SQLite3 would all be in one place, but the magic of JS did it.

I am available for hiring if you need help! I can help you with your project or homework feel free to contact me.
If you liked the post, show your appreciation by sharing it, or making a donation

Leave a Comment

Your email address will not be published. Required fields are marked *