General Improvements to wa-sqlite

[kolodny]

First of all I'd like to thank you for creating wa-sqlite, it's the only IndexedDB enabled sqlite wrapper that works well for a large (> 1gb) DB I'm working with and allows importing existing DBs.

I'm working on some QOL improvements for this project. I actually started to create a thin wrapper package to encapsulate wa-sqlite, however most of my improvements can just be upstreamed to this project directly. I see that PRs are frowned upon so I'll just dump my ideas and improvements here and let you do what you will with them. I'm OK with however you'd like to own this.

First item is to improve building the wasm artifacts. I threw together a simple docker setup which should allow easy reproducible builds without needing to use Github Actions

# First stage: build the project
FROM ubuntu:24.04 AS builder

RUN apt-get update && apt-get install -y \
  build-essential \
  wget \
  python3 \
  git \
  cmake

# Install a specific version of Emscripten
RUN git clone https://github.com/emscripten-core/emsdk.git /emsdk && \
  cd /emsdk && \
  ./emsdk install 3.1.65 && \
  ./emsdk activate 3.1.65

SHELL ["/bin/bash", "-c"]

WORKDIR /app

COPY . /app/

RUN rm -rf dist

RUN source /emsdk/emsdk_env.sh && make

# Second stage: copy the dist directory to a clean image
FROM scratch AS export-stage
COPY --from=builder /app/dist /dist

# Run this command to build and export the dist directory:
# docker build --target export-stage --output type=local,dest=. .

Next up is a better typed tag function. The way it currently works doesn't appear to use the built-in prepared statements, this uses placeholders and has sqlite fill them in. I'm still on the fence if the return type should be a generator with methods attached vs attaching methods to the tagging function, right now it's the latter.

import { SQLITE_ROW } from "./sqlite-constants";

export interface Config {
  raw?: boolean;
}

type TagType<Returning> = (
  sqlParts: TemplateStringsArray,
  ...values: SQLiteCompatibleType[]
) => Returning;

type Row<Raw> = Raw extends true
  ? SQLiteCompatibleType[]
  : Record<string, SQLiteCompatibleType>;

type RowGenerator<Raw> = AsyncGenerator<Row<Raw>, void>;

type NewConfig = <C extends Config>(config?: C) => SQL<C>;

export type SQL<C extends Config = {}> = TagType<RowGenerator<C["raw"]>> & {
  rows: TagType<Promise<Row<C["raw"]>[]>>;
  first: TagType<Promise<Row<C["raw"]>>>;
  multiple: TagType<AsyncGenerator<RowGenerator<C["raw"]>, void>>;
} & NewConfig;

export const createTag = (sqlite3: SQLiteAPI, db: number) => {
  const newConfig = <C extends Config>(config?: C): SQL<C> => {
    const buildRow = (rawRow: SQLiteCompatibleType[], columns: string[]) => {
      const row: Record<string, SQLiteCompatibleType> = {};
      let qNum = -1;
      for (let i = 0; i < columns.length; i++) {
        const column = columns[i];
        // eslint-disable-next-line @typescript-eslint/restrict-template-expressions
        const suffix = column === "?" && ++qNum ? `_${qNum}` : "";

        row[column + suffix] = rawRow[i];
      }
      return row;
    };

    const execute = (
      sql: string,
      bindings: SQLiteCompatibleType[],
      onStatement?: (statement: number) => void,
    ) => {
      return (async function* () {
        const statements = sqlite3.statements(db, sql);

        for await (const statement of statements) {
          onStatement?.(statement);
          yield (async function* () {
            if (bindings.length) {
              sqlite3.bind_collection(statement, bindings);
            }
            const columns = sqlite3.column_names(statement);
            while ((await sqlite3.step(statement)) === SQLITE_ROW) {
              const rawRow = sqlite3.row(statement);
              const row: any = config?.raw ? rawRow : buildRow(rawRow, columns);
              yield row;
            }
          })();
        }
      })();
    };

    const firstStatement: TagType<RowGenerator<C["raw"]>> = (
      sqlParts,
      ...values
    ) => {
      const sql = sqlParts.join("?");
      return (async function* () {
        const executed = execute(sql, values);
        for await (const state of executed) {
          for await (const row of state) {
            yield row;
          }
        }
      })();
    };

    // AsyncIterator
    const tag: SQL<{ raw: false }> & {
      newConfig: <C extends Config>(config: C) => SQL<C>;
    } = ((sqlParts, ...values) => {
      const isTemplateStringsArray = Array.isArray(sqlParts) && sqlParts.raw;
      if (!isTemplateStringsArray) {
        return newConfig(sqlParts as never);
      }
      return firstStatement(sqlParts, ...values);
    }) as TagType<RowGenerator<C["raw"]>> as never;

    // AsyncIterator
    tag.multiple = (sqlParts, ...values) => {
      const sql = sqlParts.join("?");
      return {
        async *[Symbol.asyncIterator]() {
          yield* execute(sql, values);
        },
      } as any;
    };

    // Promise
    tag.rows = async (sql, ...values) => {
      const rows: any[] = [];
      for await (const row of firstStatement(sql, ...values)) {
        rows.push(row);
      }
      return rows;
    };

    // Promise
    tag.first = async (sqlParts, ...values) => {
      const sql = sqlParts.join("?");
      let statementId = 0;
      const executed = execute(sql, values, (id) => {
        statementId = id;
      });
      for await (const statement of executed) {
        for await (const row of statement) {
          await sqlite3.finalize(statementId);
          return row;
        }
      }
      throw new Error("No rows returned");
    };

    return tag as never;
  };

  return newConfig() as never as SQL;
};

---

The create_function function is pretty unintuitive, I have this instead:

const cwrap = module.cwrap;
const sqlite3_double = cwrap("sqlite3_value_double", "number", ["number"]);
const sqlite3_type = cwrap("sqlite3_value_type", "number", ["number"]);
const sqlite3_text = cwrap("sqlite3_value_text", "string", ["number"]);
const sqlite3_bytes = cwrap("sqlite3_value_bytes", "number", ["number"]);
const sqlite3_blob = cwrap("sqlite3_value_blob", "number", ["number"]);
const extract_blob = function extract_blob(ptr: unknown) {
  const size = sqlite3_bytes(ptr);
  const blob_ptr = sqlite3_blob(ptr);
  const blob_arg = new Uint8Array(size);
  for (let j = 0; j < size; j += 1) {
    blob_arg[j] = module.HEAP8[blob_ptr + j];
  }
  return blob_arg;
};

const parseArg = (arg: any) => {
  const type = sqlite3_type(arg);
  if (type === VFS.SQLITE_INTEGER || type === VFS.SQLITE_FLOAT) {
    return sqlite3_double(arg);
  } else if (type === VFS.SQLITE_TEXT) {
    return sqlite3_text(arg);
  } else if (type === VFS.SQLITE_BLOB) {
    return extract_blob(arg);
  }
  return null;
};

const createFunction = (name: string, cb: (...args: any[]) => any) => {
  sqlite3.create_function(db, name, cb.length, 1, 0, (ctx, args) => {
    const parsedArgs = [...args].map(parseArg);
    const value = cb(...parsedArgs);
    sqlite3.result(ctx, value);
  });
};

// createFunction('add_nums', (a, b) => a + b);
// const result = await sql.first`SELECT add_nums(1, 2) as sum;`;

---

I also have a wrapper around IndexedDB that uses pako that, in my experience, reduces the on file DB cost by a factor of 3x. This file is a drop in replacement for IndexedDB usage in

wa-sqlite/src/examples/IDBBatchAtomicVFS.js

Line 583 in 160e2fb

const request = indexedDB.open(name, 6);

import pako from 'pako';

const fixedStores = new WeakMap<IDBObjectStore, boolean>();

class PakoIndexedDB implements IDBFactory {
  databases(): Promise<IDBDatabaseInfo[]> {
    return indexedDB.databases();
  }
  open(name: string, version?: number): IDBOpenDBRequest {
    let wrappedDb: IDBDatabase;
    let dbFixed = false;
    const fixDb = (db: IDBDatabase) => {
      if (!dbFixed) {
        dbFixed = true;
        const oldTransaction = db.transaction;
        db.transaction = (storeNames, mode, options) => {
          const transaction = oldTransaction.call(
            db,
            storeNames,
            mode,
            options
          );

          const oldObjectStore = transaction.objectStore;
          transaction.objectStore = (storeName: string) => {
            const store = oldObjectStore.call(transaction, storeName);
            if (fixedStores.get(store)) return store;
            fixedStores.set(store, true);

            const oldGet = store.get;
            store.get = (key) => {
              // debugger;
              const request = oldGet.call(store, key);
              let fixed = false;
              request.addEventListener('success', () => {
                const result = request.result;

                if (result?.data?.pako && !fixed) {
                  fixed = true;
                  const inflated = pako.inflate(result?.data?.pako);
                  const newData = { ...result, data: inflated };
                  Object.defineProperty(request, 'result', {
                    get: () => newData,
                  });
                }
              });

              return request;
            };

            const oldPut = store.put;
            store.put = (value, key) => {
              const buffer = value?.data?.buffer;
              if (buffer && buffer instanceof ArrayBuffer) {
                try {
                  // const byteLength = value.data.byteLength;
                  value.data = { pako: pako.deflate(value.data) };
                  // console.log(`was ${byteLength} bytes`);
                } catch {
                  // ignore
                }
              }
              return oldPut.call(store, value, key);
            };
            return store;
          };

          return transaction;
        };
      }
      return wrappedDb;
    };
    const request = indexedDB.open(name, version);
    const ok = (e: Event) => fixDb((e.target as IDBOpenDBRequest).result);
    request.addEventListener('success', ok);
    request.addEventListener('upgradeneeded', ok);

    return request;
  }

  deleteDatabase(name: string): IDBOpenDBRequest {
    return indexedDB.deleteDatabase(name);
  }

  cmp(first: unknown, second: unknown): number {
    return indexedDB.cmp(first, second);
  }
}

export const pakoIndexedDB = new PakoIndexedDB();

I have this as opt-outable since it's a pretty nasty hack, but hey "it works on my machine" so I like it.

Additionally when playing around with development for wa-sqlite, it would be helpful to have a vite project (or similar) within this repo that allows npm start to have a interactive page with a quick feedback cycle to try different things out. It will also be a good approximation for how end users will probably end up using this project.

I understand this is somewhat exploratory, but the VFS setup and bootstrapping is pretty janky (coming from a UI engineer POV). Is it possible to expose better primitives? I don't have a clear vision of what this will look like but I'm happy to discuss more if that's something you'd like to explore.

I know this is a giant brain dump so feel free to extract this into separate items if that makes more sense.

[rhashimoto]

Thanks for your interest and offer!

I have a high bar to accept non-trivial outside contributions, especially any that can be easily implemented in a separate complementary package. That includes higher-level APIs that abstract or simplify calling the base API, and alternative VFS classes. The primary reason is I want to keep my scope of support small. I think all your suggestions here would be fine in their own repo(s).