TodoMVSQLite - TodoMVC with an SQL datastore

tl;dr: We implement TodoMVC with sqlite-wasm as its datastore using database triggers and hooks to update the UI.

You can try it out here.

At the bottom there's a SQL console that shows the SQL statements the app is running. And you can run your own SQL to live update todo items.

A todo list with a couple of check marks stands in front of a of a monochrome terminal screen.

Introduction

This is an experience report on implementing TodoMVC with sqlite-wasm.

TodoMVC by Addy Osmani and others, is an todo list app to demonstrate MVC frameworks. It has been implemented in 64 different frameworks. It's great because it's small enough to understand and complex enough to demonstrate the challenges of showing dynamic data with different views. We're not suggesting that this is a good way to implement MVC. Nor that it's a good way to use SQLite. It's just an experiment to see how it can be done.

SQLite is an embedded relational database library. It is very reliable, compact and fast. It's very popular and used in everywhere, even on other planets. @sqlite.org/sqlite-wasm is a npm package containing a WebAssembly build of SQLite with JavaScript bindings. It makes it easy to use SQLite in a browser.

TodoMVC

TodoMVC allows the user:

Our implementation

We adapt a TodoMVC implementation vanilla-es6 by Luke Edwards and Aaron Muir Hamilton. vanilla-es6 is made with plain ES6 JavaScript. It uses a JSON array of todo items stored in window.localStorage. Our implementation also uses localStorage but SQLite manages it entirely for us.

This is not an good example of how to use the MVC pattern. It would be proper to have a separate Store class like vanilla-es6 does. But to keep changes from the original mostly in one file we do all database access directly from the controller. The code is available on github, controller.js.

To initialize the sqlite WebAssembly module we do:

import sqlite3InitModule from '@sqlite.org/sqlite-wasm';

const sqlite3 = await sqlite3InitModule();

The sqlite3 module exposes oo1, the object oriented API, and capi, the C API. We mostly use the object oriented API to query and update the database. The C API we use to add a commit hook for some UI updates and tracing for our SQL console.

Creating a database using localStorage is done with:

this.ooDB = new sqlite3.oo1.JsStorageDb('local');

We create a table for todos:

CREATE TABLE IF NOT EXISTS todos (
	title TEXT NOT NULL,
	completed INTEGER NOT NULL DEFAULT 0,
	CHECK (title <> ''),
	CHECK (completed IN (0, 1)));

By default inserted items are active. Empty item titles are not allowed so we add a constraint to prevent them. We also add a constraint to ensure that completed is either 0 or 1 as SQLite uses 0 and 1 for booleans.

In controller.js we just call exec to create the table:

this.ooDB.exec(`
CREATE TABLE IF NOT EXISTS todos (
	title TEXT NOT NULL,
	completed INTEGER NOT NULL DEFAULT 0,
	CHECK (title <> ''),
	CHECK (completed IN (0, 1)));

CREATE INDEX IF NOT EXISTS completed_index ON todos (completed);`);

We add an index on completed for good measure.

We use named parameters to pass values to SQL, here is addItem:

addItem = ($title) =>
  this.ooDB.exec(`INSERT INTO todos (title) VALUES ($title)`, {
    bind: { $title },
  });

For brevity of notation we use $ prefixes for parameter names bound in SQL statements. We won't list the other command functions here but they are available from line 247.

Triggers

In the original vanilla-es6 the controller calls the view explicitly after making changes to the store. Instead our controller adds triggers to the database that updates the view as they happen. This has the advantage that the UI will update if items are added in other ways. We add user defined functions and call them from a temporary triggers.

Here we add a trigger for item insertion.

// controller.js constructor
...
// insert item trigger
this.ooDB.createFunction('inserted_item_fn', (_, id, title, completedInt) => {
  this.view.clearNewTodo();
  const completed = !!completedInt;
  // add item if it should be visible in the current route
  if (this.isAllRoute() || completed === this.isCompletedRoute())
    this.view.addItem({ id, title, completed });
});

this.ooDB.exec(`
CREATE TEMPORARY TRIGGER insert_trigger AFTER INSERT ON todos
	BEGIN SELECT inserted_item_fn(new.rowid, new.title, new.completed); END`);
...

Our triggers are all temporary because we only use them in the current database and they should be discarded when the database is closed. We check if the inserted item is visible in the current route before adding it to the view.

We add a similar function and trigger for when completion status changes:

// controller.js constructor
...
this.ooDB.createFunction(
  'updated_completed_fn',
  (_, id, completedInt) => {
    const completed = !!completedInt;
    if (this.isAllRoute()) {
      this.view.setItemComplete(id, completed);
    } else {
      // add/remove item if it should be visible in the current route
      if (completed === this.isCompletedRoute())
        this.view.addItem({
          id,
          title: this.ooDB.selectValue(
            `SELECT title FROM todos WHERE rowid = $id`,
            { $id: id }
          ),
          completed,
        });
      else this.view.removeItem(id);
    }
  }
);

ooDB.exec(`
CREATE TEMPORARY TRIGGER update_completed_trigger AFTER UPDATE OF completed
  ON todos WHEN old.completed <> new.completed
	BEGIN
    SELECT updated_completed_fn(new.rowid, new.completed);
  END`);

A WHEN clause on the trigger ensures it only runs when the completed status changes. The logic in the function is more complex for updates. If the UI shows a filtered route we need to add or remove the item from view. When an item comes into view we select its title from the database.

We add triggers for deletion of items and updates to item titles as well. We don't show them here but they can be found in controller.js from line 102.

Transactions and hooks

One of the nicest things about databases is transactions. We can make multiple changes and be sure that either all changes are applied or none are in case of failure. With our simple data model with just one table it doesn't seem like we need transactions. But some of the statements we run make multiple changes and these are transactional as well. When we mark all items as completed we run:

UPDATE todos SET completed = 1

This will trigger our update_completed_trigger for each item. Which is fine as we need to update the view for each item. But we also need to update the active item count and hide or show the "Clear completed" button. As SQLite only supports row level triggers we would get multiple unnecessary updates to the view if we added triggers for this. Of course this is not a problem in our small app but there's a more general need hidden here. Some state change updates we want on statement or transaction level not row level.

To solve this we add a commit hook that runs after a transaction is committed. The hook calls refreshViewItemTotalStatus on every commit even when the counts are unchanged.

capi.sqlite3_commit_hook(
  this.ooDB,
  wasm.installFunction('i(p)', (_ctxPtr) => {
    setTimeout(this.refreshViewItemTotalStatus);
    return 0;
  }),
  0
);

It's important that the hook runs asynchronously, in setTimeout, as it's not allowed to run SQL statements inside a commit hook. It would cause an infinite loop if we called refreshViewItemTotalStatus directly. This happens even if we only perform select statements that don't commit changes. Trying the direct approach gave surprising behavior as detailed in this SQLite forum post.

Changes from other tabs/sessions

When other browser tabs change JsStorageDb there is currently no way builtin way to detect it. Clearing the journal is the last thing that happens when committing in journal_mode = DELETE, the default we're using. We can add a listener for storage events to detect when the journal is cleared.

window.addEventListener('storage', (event) => {
  if (
    event.storageArea === window.localStorage &&
    event.key === 'kvvfs-local-jrnl' &&
    event.newValue === null
  ) {
    // we don't know what changed, so just reload the entire view
    this.reloadView();
  }
});

It seems brittle to rely on a hard coded key name being cleared. But a kind expert on the SQLite forum assured us that the key name is unlikely to change.

Conclusion

It is possible to use SQLite as a datastore for a webapp. And triggers work fine for updating specific elements in the UI.

Adding a commit hook to update the view on every commit is not as elegant as one would have hoped. It would be nice if one could add a query change trigger along the lines of:

// wishfull non-working code
const activeCountQuery =
  db.createWatchedQuery(`SELECT COUNT() FROM todos WHERE completed = 0`);
this.view.setActiveCount(activeCountQuery.value());
activeCountQuery.addEventListener('change', this.view.setActiveCount);

Also it would be nice to have a built in way to detect changes from other tabs.

Try it out

Again the code is available on github. You can try it out here. At the bottom there's a SQL console that shows the SQL the app is running and allows you to run your own SQL to live update todo items.

Thank you for reading.

Feedback is welcome at blog@zoren.dk.

Published: 2023-11-07

Tagged: sqlite

Archive