Support for Durable Objects’ SQLite Storage

[acusti]

i was recently listening to DHH opine (that link takes you to the timestamp where the discussion takes place) about the possibilities of a multi-tenant architecture in which every tenant gets their own SQLite DB and what that provides in terms of scaling, infra costs and reliability, data portability, simplicity, etc. in that same conversation, i also heard about Turso, which provides SQLite DBs as a service.

then i remembered the announcement from Cloudflare about the new zero-latency (i.e. synchronous) SQLite storage option for Durable Objects that is currently in beta, which i realized is a version of this one-SQLite-DB-per-tenant architecture.

however, i really like Superflare’s approach to defining schemas and deriving models + types from those schemas, as well as its automatic relational stuff and migrations management.

so that made me wonder how hard it would be to make it possible to choose whether to have that functionality target either a D1 DB or a particular Durable Object class. i actually don’t have much experience working with DO’s, so it’s hard for me to imagine how it would be possible to have a DO based on superflare’s models/schemas, but it definitely feels like superflare could allow us to do something better than this (the first example in the docs):

import { DurableObject } from "cloudflare:workers";

export class MyDurableObject extends DurableObject {
  sql: SqlStorage;
  constructor(ctx: DurableObjectState, env: Env) {
    super(ctx, env);
    this.sql = ctx.storage.sql;

    this.sql.exec(`CREATE TABLE IF NOT EXISTS artist(
      artistid    INTEGER PRIMARY KEY,
      artistname  TEXT
    );INSERT INTO artist (artistid, artistname) VALUES
      (123, 'Alice'),
      (456, 'Bob'),
      (789, 'Charlie');`
    );
  }
}

or this (the second example in the docs):

let cursor = this.sql.exec("SELECT * FROM artist ORDER BY artistname ASC;");
let rawResult = cursor.raw().next();

if (!rawResult.done) {
  console.log(rawResult.value); // prints [ 123, 'Alice' ]
} else {
  // query returned zero results
}

console.log(cursor.toArray()); // prints [{ artistid: 456, artistname: 'Bob' },{ artistid: 789, artistname: 'Charlie' }]

it would be so nice if the first example could be a combination of a superflare schema migration + a db/seed.ts file, and if the second example could be:

const artists = this.Artist.all();
if (artists.length) {
  console.log(artists);
} else {
  // query returned zero results
}

i omitted await on purpose, but it could also be async to match the D1-based API if that felt less confusing.

i think this is a pretty compelling architecture in general, which is a big part of my interest in it, from the data isolation provided by each tenant getting their own SQLite storage, to the speed and simplicity of synchronous DB access, to the possibility of even replicating a user’s entire DB to their local device and using a syncing mechanism in place of an API in a local-first style. but another part of my interest is that this would completely put to rest any fear about scaling beyond D1’s fairly modest database size limit of 10GB.