Adapter Spec v1

[heiwen]

Summary

This document defines the v1 adapter interface for no-orm.

The adapter layer is responsible for exposing a generic persistence interface over concrete backends such as:

• SQLite
• Postgres
• MySQL
• MongoDB
• memory

This layer is generic and has nothing to do with any single host library or resource type.

Purpose

The adapter layer exists to:

• consume the canonical Schema
• support minimal schema bootstrap through migrate()
• expose a normalized persistence interface across SQL and non-SQL backends
• hide backend-specific mechanics behind a common contract

This layer does not own:

• request-aware policy enforcement
• schema extension hooks
• write/query scoping hooks
• host-library-specific logic

Those concerns belong in higher layers.

Design Principles

• The interface should be generic, not tied to a specific resource such as conversations.
• The interface should be method-oriented rather than SQL-oriented.
• The interface should be complete enough to cover standard CRUD-style persistence needs.
• The interface should work for both SQL and non-SQL backends.
• The interface should prefer generic operators such as eq over backend-specific operators such as json_eq.

Interface

export interface Adapter {
  migrate?(args: {
    schema: Schema;
  }): Promise<void>;

  transaction?<T>(fn: (tx: Adapter) => Promise<T>): Promise<T>;

  create<T = Record<string, unknown>>(args: {
    model: string;
    data: T;
    select?: Select<T>;
  }): Promise<T>;

  update<T = Record<string, unknown>>(args: {
    model: string;
    where: Where<T>;
    data: Partial<T>;
  }): Promise<T | null>;

  updateMany<T = Record<string, unknown>>(args: {
    model: string;
    where?: Where<T>;
    data: Partial<T>;
  }): Promise<number>;

  upsert?<T = Record<string, unknown>>(args: {
    model: string;
    where: Where<T>;
    create: T;
    update: Partial<T>;
    select?: Select<T>;
  }): Promise<T>;

  delete<T = Record<string, unknown>>(args: {
    model: string;
    where: Where<T>;
  }): Promise<void>;

  deleteMany?<T = Record<string, unknown>>(args: {
    model: string;
    where?: Where<T>;
  }): Promise<number>;

  find<T = Record<string, unknown>>(args: {
    model: string;
    where: Where<T>;
    select?: Select<T>;
  }): Promise<T | null>;

  findMany<T = Record<string, unknown>>(args: {
    model: string;
    where?: Where<T>;
    select?: Select<T>;
    sortBy?: SortBy<T>[];
    limit?: number;
    offset?: number;
    cursor?: Cursor<T>;
  }): Promise<T[]>;

  count?<T = Record<string, unknown>>(args: {
    model: string;
    where?: Where<T>;
  }): Promise<number>;
}

Supporting Types

export type FieldName<T> = Extract<keyof T, string>;

export type Select<T> = ReadonlyArray<FieldName<T>>;

export type Where<T = Record<string, unknown>> =
  | {
      field: FieldName<T>;
      op: "eq" | "ne";
      value: unknown;
    }
  | {
      field: FieldName<T>;
      op: "gt" | "gte" | "lt" | "lte";
      value: unknown;
    }
  | {
      field: FieldName<T>;
      op: "in" | "not_in";
      value: unknown[];
    }
  | {
      and: Where<T>[];
    }
  | {
      or: Where<T>[];
    };

export interface SortBy<T = Record<string, unknown>> {
  field: FieldName<T>;
  direction?: "asc" | "desc";
}

export interface Cursor<T = Record<string, unknown>> {
  after: Partial<Record<FieldName<T>, unknown>>;
}

Method Semantics

migrate

migrate?(args: { schema: Schema }): Promise<void>

Purpose:

• prepare backend storage structures from the canonical schema

This is intentionally minimal:

• create model if not exists
• create indexes if needed
• use backend-specific idempotent DDL where supported

This is not a full migration framework.

transaction

transaction?<T>(fn: (tx: Adapter) => Promise<T>): Promise<T>

Purpose:

• allow multiple operations to run atomically where the backend supports transactions

Backends that do not support transactions may omit this method.

create

create<T>(args): Promise<T>

Purpose:

• create a single record

update

update<T>(args): Promise<T | null>

Purpose:

• update a single matching record

updateMany

updateMany?(args): Promise<number>

Purpose:

• update multiple matching records
• return the number of affected records

upsert

upsert?(args): Promise<T>

Purpose:

• create or update a record according to backend semantics

Optional because some backends may not support native or efficient upsert in v1.

delete

delete<T>(args): Promise<void>

Purpose:

• delete a single matching record

deleteMany

deleteMany?<T>(args): Promise<number>

Purpose:

• delete multiple matching records
• return the number of affected records

find

find<T>(args): Promise<T | null>

Purpose:

• find a single record matching the filter

findMany

findMany<T>(args): Promise<T[]>

Purpose:

• find multiple records
• support filtering, ordering, pagination, and optional projection

count

count?<T>(args): Promise<number>

Purpose:

• count matching records

Optional because it is not required by the current hebo-gateway benchmark, but included for completeness.

Where Semantics

The where model is intentionally small and generic.

Supported leaf operators in v1:

• eq, ne
• gt, gte, lt, lte
• in, not_in

Supported logical composition in v1:

• and
• or

Notes:

• There is no dedicated json_eq operator in v1.
• Backend-specific JSON path behavior should not leak into the generic filter model unless a strong use case proves it is necessary.
• If nested-path filtering becomes necessary later, it should likely be modeled with an optional path field rather than a separate operator family.

Ordering and Pagination

sortBy

Supports sorting by one or more fields.

cursor

The cursor shape is intentionally generic:

{
  after: Partial<Record<FieldName<T>, unknown>>;
}

This allows backends to implement cursor pagination according to their own storage model while still constraining cursor fields to keys of T.

Why This Shape

This interface is based on the combined lessons from:

• the hebo-gateway storage-layer discussion in issue #83
• Better Auth’s adapter interface
• unadapter’s normalized database API approach

The key decisions are:

• use a generic CRUD-style backend interface rather than a SQL executor interface
• keep backend concerns in this layer
• keep request-aware policy and schema extension outside this layer
• keep the filter and operation surface small and portable

Out of Scope

This spec does not define:

• schema extension hooks
• request-aware write/query enforcement
• host-library integration layers
• the TypeScript inference layer
• a full query language
• migration history or diffing
• backend capability metadata

Current Recommendation

Use this interface as the v1 adapter contract.

Start with:

• one SQL adapter, likely SQLite
• one non-SQL or non-database adapter, likely memory

That pair is enough to validate whether the interface is genuinely portable.

[buibaoanh]

Technical Design Proposal: Decoupling Query Interpretation from Execution

1. Current Design: The Dialect Abstraction

The current architecture attempts to share code by using a centralized query execution engine powered by a "dialect" configuration (e.g., SqlFormat). This assumes database differences are purely syntactic.

This creates tight coupling: AST traversal is fused with string generation, and execution flows are hardcoded.

The Problem:
If a database (e.g., MySQL) does not support a RETURNING clause, it cannot use the centralized update helper, which strictly dictates a one-shot UPDATE ... RETURNING flow.

// BEFORE: Centralized, hardcoded flow using a dialect object
export async function update(exec, dialect, args) {
  // Parsing and SQL generation are permanently tangled together
  const sql = `UPDATE ... WHERE ${toWhere(dialect, args.where)} ${dialect.returning()}`;
  
  // Execution flow is rigid; fails if DB doesn't support returning data on update
  const row = await exec.get(sql); 
  return row;
}

To support this database, the adapter author must completely abandon the shared engine and rewrite both the toWhere parser and the execution logic from scratch.

2. Proposed Design: Autonomous Adapters

The solution is to dismantle the dialect-driven execution engine. We shift ownership of execution and query building to the adapters, providing them with pure, format-agnostic utilities to interpret query intent.

1. Decouple AST Traversal: Extract the complex logic that walks the Where tree into a pure utility that does not assume a specific string output.
2. Empower the Adapter: Adapters become the source of truth. They use core utilities to understand the intent, but independently control the formatting and execution sequence.

The Solution:
The adapter natively owns the flow. It uses generic builder functions to construct the query string, executing it according to its specific database capabilities.

// AFTER: Adapter-owned execution using pure builder utilities
class MySQLAdapter {
  async update(args) {
    // 1. Adapter uses pure utilities to build the query components
    const { sql, params } = buildUpdateSql(args.data, args.where);
    
    // 2. Adapter explicitly controls the execution flow
    await this.driver.execute(sql, params);
    
    // 3. Adapter handles database limitations natively (no RETURNING support)
    const { sql: selectSql, params: selectParams } = buildSelectSql(args.where);
    return await this.driver.query(selectSql, selectParams);
  }
}

3. Rationale

• True Extensibility: Acknowledges that databases differ in transactional behavior and execution flow, not just syntax.
• Adapter Autonomy: Adapters are not constrained by a generic engine. They use targeted, composable utilities rather than inheriting a rigid "mega-helper."
• Separation of Concerns: Complex query parsing is isolated from side-effect-producing driver execution.

---

[buibaoanh]

Technical Design: SQL Logic Consolidation

Goal

Eliminate code duplication (specifically recursive Where traversal and statement assembly) while maintaining Adapter Autonomy — the adapter controls its own SQL syntax and execution flow without a rigid "Dialect" configuration.

---

Option 1: The Staged Pipeline (Data-to-Data)

Shared code transforms the recursive Where tree into a flat execution plan. The adapter loops over leaf nodes and maps them to SQL fragments. Shared code then reassembles the fragments using the original AND/OR structure.

const leaves   = planWhere(where);
const fragments = leaves.map((l, i) => `${quote(l.field)} = $${i}`);
const whereSql  = assembleWhere(where, fragments);

• Pro: Maximum decoupling; SQL structure and Dialect syntax never meet in the same function.
• Con: The adapter must still manually manage global parameter indices (e.g., ensuring the WHERE clause starts after the SET clause).

---

Option 2: The Functional Visitor (Callback Injection)

A single recursive function drives tree traversal and delegates per-leaf rendering to the adapter via callback.

const { sql, params } = buildWhere(where, {
  startIndex: 0,
  render: (leaf, index) => ({
    sql:    `${quote(leaf.field)} = $${index + 1}`,
    params: [leaf.value]
  })
});

• Pro: The most significant duplication is the recursive logic for AND/OR. This approach extracts the walking logic into a shared helper that calls a provided callback for every "leaf" (field comparison).
• Con: Recursive traversal of deeply nested trees can produce difficult stack traces. Acceptable if query complexity is bounded.

---

Option 3: The Modular Builder (Fluent Methods)

This is the "Query Builder" approach. The adapter instantiates a local, stateful helper and "pushes" instructions into it. This keeps the execution flow entirely inside the adapter.

const qb  = new SqlBuilder({ quote, placeholder });
const sql = qb.update(modelName, data).where(where).returning("*").build();

• Pro: High developer experience — familiar API, easy feature flags (.returning() is a no-op on unsupported backends).
• Con: There is a risk that the builder eventually becomes a "Dialect in disguise" as more database-specific methods are added.

---

Conclusion

The Where clause recursion is the largest source of duplication (~150 lines per adapter) and the most likely place for bugs in parameter index tracking. Option 2 solves this structural duplication once and for all without assuming a single thing about SQL syntax.

Option 1's planWhere / assembleWhere utilities can be shipped as lower-level primitives that Option 2 builds on.

Option 3 is deferred. If developer experience becomes a friction point, it can be introduced as a convenience wrapper over Option 2 — but must not add new shared logic internally.

[buibaoanh]

Technical Design: The Atomic Clause Toolbox

Goal

Eliminate SQL code duplication across all operations (UPDATE, DELETE, FIND, COUNT) while strictly adhering to a Shared-Nothing architecture. The solution avoids "Dialect" objects and "Pipeline" abstractions, focusing instead on pure, functional transformers.

---

1. The Pattern: Intent $\rightarrow$ Fragment

Instead of a monolithic builder, the shared logic provides a library of Atomic Transformers. Each function takes a specific part of the "Intent" (like a Where filter or a data object) and transforms it into a SQL fragment.

The Adapter remains the Sole Orchestrator. It owns the raw SQL template and decides exactly how to concatenate the strings.

2. The Shared API (shared/sql.ts)

The functions are named toXXX to emphasize that they are pure, stateless transformations.

export interface Fragment {
  /** The SQL string (e.g., '"name" = $1, "age" = $2') */
  readonly sql: string;
  
  /** The values for the placeholders (e.g., ['Alice', 30]) */
  readonly params: unknown[];
  
  /** The number of parameters used (useful for offsetting the next clause) */
  readonly paramCount: number;
}

/** 
 * toWhere: The "Visitor" for Filters.
 * Walks the 'Where' AST and renders SQL using the provided syntax functions.
 */
export const toWhere = (where: Where, quote: Fn, placeholder: Fn, offset = 0) => Fragment;

/** toSet: The Transformer for Assignments. */
export const toSet = (data: Record<string, any>, quote: Fn, placeholder: Fn, offset = 0) => Fragment;

/** toColumns: The Transformer for Projections. */
export const toColumns = (select: string[], quote: Fn, jsonExtract: Fn) => string;

/** toOrderBy: The Transformer for Sorting. */
export const toOrderBy = (sort: SortBy[], quote: Fn, jsonExtract: Fn) => string;

---

3. Usage Across Operations (Adapter Perspective)

The Adapter calls the "Toolbox" for the complex bits (recursion, parameter indexing) and performs the final assembly manually.

For UPDATE (Postgres):

// 1. Transform intent to fragments (Adapter owns the Syntax)
const set = toSet(data, quote, placeholder);
const where = toWhere(where, quote, placeholder, set.paramCount);

// 2. Assemble the final query (Adapter owns the Structure)
const sql = `UPDATE ${quote(table)} SET ${set.sql} WHERE ${where.sql} RETURNING *`;
const params = [...set.params, ...where.params];

For SELECT (SQLite):

const cols = toColumns(select, quote, jsonExtract);
const where = toWhere(where, quote, placeholder);
const order = toOrderBy(sort, quote, jsonExtract);

// Adapter handles DB-specific ordering of LIMIT/OFFSET natively
const sql = `SELECT ${cols} FROM ${quote(table)} WHERE ${where.sql} ORDER BY ${order} LIMIT ?`;
const params = [...where.params, limit];

---

4. Pros and Cons

Pros:

• Highest Autonomy: The Adapter is in 100% control of the final SQL string. No "Plan" object or "Pipeline" state is required.
• Zero Dialect Coupling: Uses Dependency Injection of pure functions (quote, placeholder). There is no "Dialect" class or configuration object.
• Logical Consolidation: The 200+ lines of recursive Where logic and parameter-tracking math are consolidated into the toolbox, eliminating the most bug-prone code from the adapters.

Cons:

• Manual Coordination: The Adapter author is responsible for tracking parameter offsets (set.paramCount) and merging parameter arrays.
• Explicit Boilerplate: Each adapter method contains a few lines of assembly code (concatenating the fragments), which is the price paid for total flexibility.

---

Rationale: Why this is the correct choice

This pattern treats the shared core as a Library of Parts rather than a Framework.

It acknowledges that while Statements (the assembly) vary between databases, Clauses (the logic of filters and assignments) are universal. By sharing the Clauses, we solve the duplication problem without ever creating a restrictive "Dialect" framework that might break when we add a database with unique syntax (like GreptimeDB or MongoDB).

---