TodoMVSQLite - TodoMVC with an SQL data store
tl;dr: We make TodoMVC with sqlite-wasm as its data store 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.
Introduction
In this post we make TodoMVC with sqlite-wasm.
TodoMVC by Addy Osmani and others, is a todo list app to demonstrate MVC frameworks. It has been made in 64 different frameworks so far. 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 use the MVC pattern. 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's very reliable, compact and fast. It's very popular and used 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:
- to add, edit, and delete items
- to mark items as completed or make them active again
- to mark all items as completed/active
- to delete all completed items
- to filter items by completed and active, or show them all
- the hash part of the URL is updated to reflect the filter
Our approach
We adapt 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 adaptation also uses localStorage but SQLite manages it for us.
This is not a 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 update 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 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`);
...
In our trigger function, we check if the inserted item is visible in the current route before adding it to the view. 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 add a similar function and trigger for when the 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. 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 the deletion of items and updates to item titles as well. We don't show those 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 or rollback. 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 run update_completed_trigger for each item. Which is good 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 completion counts. 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.
We solve this by adding a commit hook that runs after a transaction is committed. The hook calls refreshViewItemTotalStatus on every commit even when 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 in other sessions.
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.
Update: 2023-Nov-08, BroadcastChannel
Another kind expert on the forum, same thread as above, suggested using a BroadcastChannel to detect changes from other tabs. This is a much better solution than the one above. Now all we need is:
const commitChannel = new BroadcastChannel('localStorageCommit');
// listen for changes from other browser sessions/tabs
commitChannel.addEventListener('message', this.reloadView);
...
// inside commit hook
commitChannel.postMessage(null);
Thank you Stephan and Roy!
Conclusion
It is possible to use SQLite as a data store for a webapp. 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:
// wishful 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