Issue with repeated prepares in hot paths

[zth]

This is for SQLite used together with Bun.

I've got a hot path where I run many many SQLite queries at once. It's hot enough that calling db.prepare for each invocation (which is what the codegen emits right now) ends up adding massive overhead.

I'm wondering, would it be possible to provide some sort solution/escape hatch for this, so that all/some queries are always prepared just once? OTOH, I can think of a couple of ways to fix this:

1. Always call prepare on the query as soon as the query file is loaded. Then always use the prepared query and never call prepare again. This is probably a bit dangerous because it creates boot overhead that you can't control.
2. Generate a "prepare" function into the generated files, that when called prepares the query locally in the file and switches so the query always uses the prepared one from that point on.
3. Add an arg to the current execution functions that let you toggle forcing the query to be prepared once before running it. Essentially the above, but behind an option.

For dynamic queries where the query is not known statically I assume some more clever solution is needed, so they probably shouldn't be prepared by default. But for the rest, maybe one of the above could work. What do you think?

Thanks for this great lib btw.

[zth]

For anyone hitting the same issue, here's a temporary solution I came up with that wraps the db with a Proxy to cache the prepare call:

import type { Database } from "bun:sqlite";

type PreparedStatement = ReturnType<Database["prepare"]>;

export type PrepareCallOptions = {
  useCache?: boolean;
};

export type PreparedStatementCacheOptions = {
  cacheByDefault?: boolean;
};

export type PreparedStatementCachingDatabase = Database & {
  prepare(sql: string, options?: PrepareCallOptions): PreparedStatement;
};

export const withPreparedStatementCache = (
  db: Database,
  options: PreparedStatementCacheOptions = {},
): PreparedStatementCachingDatabase => {
  const cacheByDefault = options.cacheByDefault ?? true;
  const prepareCache = new Map<string, PreparedStatement>();
  let closed = false;

  const finalizeCachedStatements = () => {
    for (const stmt of prepareCache.values()) {
      try {
        stmt.finalize();
      } catch {}
    }
    prepareCache.clear();
  };

  const proxy = new Proxy(db, {
    get(target, property, receiver) {
      if (property === "prepare") {
        return (sql: string, prepareOptions: PrepareCallOptions = {}) => {
          const useCache = prepareOptions.useCache ?? cacheByDefault;
          if (!useCache) {
            return target.prepare(sql);
          }
          const cached = prepareCache.get(sql);
          if (cached) {
            return cached;
          }
          const statement = target.prepare(sql);
          prepareCache.set(sql, statement);
          return statement;
        };
      }

      if (property === "close") {
        return () => {
          if (closed) {
            return;
          }
          closed = true;
          finalizeCachedStatements();
          target.close();
        };
      }

      // Use the underlying DB as the getter receiver so native/private fields
      // on Bun's Database object remain accessible (e.g. `inTransaction`).
      const value = Reflect.get(target, property, target);
      if (typeof value === "function") {
        return value.bind(target);
      }
      return value;
    },
  });

  return proxy as PreparedStatementCachingDatabase;
};