Skip to main content
Back to blog
SQLiteIndexedDBArchitectureCrasterDoc

SQLite inside the browser with sql.js and IndexedDB, without losing documents

5 min read

CrasterDoc has no accounts and no user database: each person's documents live in their browser. Instead of storing a pile of loose JSON in localStorage, the vault uses a SQLite database compiled to WebAssembly (sql.js) and persists it to IndexedDB. This article covers why and how, with code from src/lib/bd.ts.

Why SQLite and not localStorage

A vault has nested folders, tags, search, and links between documents with the [[code]] syntax. Those are queries: “the documents in this folder”, “who does this document link to”, “which ones have this tag”. In SQL each is one line; with JSON in localStorage they are loops and a small quota. The tables are few and clear:

CREATE TABLE IF NOT EXISTS documentos (
  id TEXT PRIMARY KEY,
  carpeta_id TEXT NOT NULL,
  json TEXT NOT NULL,
  codigo TEXT,
  titulo TEXT,
  modulo TEXT,
  etiquetas TEXT,
  actualizado_en TEXT
);
CREATE TABLE IF NOT EXISTS enlaces (
  origen_id TEXT NOT NULL,
  destino TEXT NOT NULL,
  PRIMARY KEY (origen_id, destino)
);

The full document is stored as JSON in one column, and the separate columns (codigo, titulo, etiquetas) exist to search and sort without deserializing everything.

Persisting: exporting the whole database to IndexedDB

sql.js works in memory. For the data to survive closing the tab, every write exports the whole database as a Uint8Array and stores it in IndexedDB:

async function persistir(): Promise<void> {
  const e = estado()
  e.persistiendo = e.persistiendo.then(async () => {
    if (!e.db) return
    await escribirBytes(e.db.export())
  })
  return e.persistiendo
}

The key is e.persistiendo.then(...): writes are chained on a promise, so two saves in a row never race for IndexedDB or land out of order. It is not the most sophisticated approach, and exporting the full database on every save is only reasonable because one person's database is small. It is a conscious choice: simple, easy to reason about, and enough for this data size.

State hangs from globalThis (because of hot reload)

This was a real bug. With Next's hot reload a module is evaluated again. If the open database lived in a module variable, after a reload the variable went back to null while the screen still believed the vault was ready, producing “La base SQLite aún no está lista” and a full-reload loop. The fix is to keep the state on globalThis:

const ESTADO_GLOBAL = '__crasterdocBd'
function estado(): EstadoBd {
  const g = globalThis as unknown as Record<string, EstadoBd | undefined>
  return (g[ESTADO_GLOBAL] ??= { db: null, persistiendo: Promise.resolve() })
}

The ??= creates the state the first time and reuses it afterwards, even if the module is re-evaluated. It is a general pattern for development singletons under HMR.

A corrupt document is not lost: it is set aside

This is the product decision I care about most. When the database opens, every row of documentos is validated. If the JSON is damaged, or not the expected shape, the document is neither deleted nor ignored: it moves to a recuperados (recovered) table with its original text and the reason.

function aislarInvalidosSync() {
  const { recuperados } = clasificarFilas(filasDocumentosSync())
  for (const r of recuperados) {
    sql().run('INSERT OR REPLACE INTO recuperados (id, json, motivo, titulo) VALUES (?, ?, ?, ?)', [
      r.id, r.json, r.motivo, r.titulo,
    ])
    sql().run('DELETE FROM documentos WHERE id = ?', [r.id])
    sql().run('DELETE FROM enlaces WHERE origen_id = ?', [r.id])
  }
}

The vault screen warns and lets you download the document exactly as it was stored. It even tries to recover a readable title from the versions so the person can recognize the file. The rule is simple: if something cannot be opened, the program tells the user; it never decides on their behalf that it is lost.

Deleting is not final either

Deleting a document moves it to the trash for 30 days, and it can be restored. Internally each deletion records its date and the list of deletions is merged without duplicates (fusionarBajas). When importing a vault, the user chooses “Combinar” (merge: what is in the file is added, and if a document exists on both sides the most recently modified one wins) or “Reemplazar todo” (replace everything, with confirmation).

Migrating without breaking anyone

The first version kept everything in localStorage. When the database starts empty, iniciarBd reads the old store, copies folders, documents, brands, and recovered items to SQLite, persists, and only then removes the old key. If anything fails before that, the old data is still there.

What I take away

  1. SQLite in the browser is viable for personal data of modest size, and SQL simplifies a vault with folders, tags, and links a great deal.
  2. Serialize writes: a promise chain is enough and avoids races.
  3. Development singletons need globalThis if you use HMR.
  4. Design to not lose data: setting aside and warning beats discarding. It is the part people only notice when it is missing.

Each person keeps their documents in their browser: localhost and the published site do not share a vault, which is why the product offers a local copy and an optional Drive backup. Try it at crasterdoc.morelodev.com, see the product page, or read How it was built.