Joqi

Joqi is a registry-backed JSON query compiler for TypeScript apps.

At its core, Joqi should expose two standard contracts:

1. Query Schema
2. Registry Schema

Given an untrusted JSON query and a trusted registry, Joqi validates what is allowed, lowers the query into a normalized IR, and hands that IR to an adapter such as Drizzle or Prisma.

unknown JSON
  -> QuerySpecSchema
  -> ResolvedRegistry validation
  -> QueryIR
  -> adapter compiler
  -> adapter execution

The package is currently private under the @ypanagidis npm scope.

Install

pnpm add @ypanagidis/joqi@alpha

Usage

Most applications should use createQueryRuntime. The runtime resolves the registry, validates and binds query params, lowers to IR, compiles SQL, executes through an adapter, and validates result rows.

import { createQueryRuntime } from "@ypanagidis/joqi";
import { drizzleExecutor } from "@ypanagidis/joqi-drizzle";

const spec = {
  version: "v1",
  source: "placement",
  select: ["name", "budget"],
  where: { field: "budget", op: "gte", value: { $param: "minBudget" } },
  orderBy: [{ field: "budget", direction: "desc" }],
  limit: { $param: "limit" },
};

const runtime = createQueryRuntime({
  db,
  physicalRegistry: physical,
  defaults,
  policy,
  dialect: "mysql",
  executor: drizzleExecutor(),
});

const result = await runtime.run({
  spec,
  params: {
    minBudget: 10000,
    limit: 50,
  },
  explain: true,
});

console.log(result.rows);
console.log(result.explain.sqlPlan);

When explain: true is passed, result.explain is typed as present and includes the resolved registry, QueryIR, and SQLPlan. Omit explain for just rows.

Runtime Errors

runtime.run(...) does not wrap every failure in one catch-all error. It keeps stage errors visible so callers can handle the right boundary:

• Registry parsing/resolution failures throw RegistryParseError or RegistryResolutionError.
• Query parsing/validation failures throw QueryParseError or QueryValidationError.
• Missing $param values and invalid param types are QueryValidationError issues.
• Adapter execution failures are thrown by the configured executor. For Drizzle, drizzleExecutor() uses DrizzleExecutionError from @ypanagidis/joqi-drizzle.
• Result row validation failures come from the result schema parser.

Validation happens before execution. If params are missing or invalid, the executor is not called.

Advanced APIs

The runtime is a small wrapper over lower-level APIs. Use these directly when you need to inspect or customize individual compiler stages.

import {
  compileQuerySpecToSQL,
  lowerQuerySpecToIR,
  parseQuerySpec,
  resolveRegistry,
  validateQuerySpec,
} from "@ypanagidis/joqi";

const query = parseQuerySpec(spec);
const registry = resolveRegistry({ physical, defaults, policy });
const validatedQuery = validateQuerySpec({ query, registry, params });
const ir = lowerQuerySpecToIR({ query: validatedQuery, registry, params });
const sqlPlan = compileQuerySpecToSQL({ query: validatedQuery, registry, dialect: "postgres" });

All schemas are also exported directly for advanced validation flows:

import { QuerySpecSchema, ResolvedRegistrySchema } from "@ypanagidis/joqi";

const result = QuerySpecSchema.safeParse(input);

Effect API

The core pipeline is Effect-first. Sync and promise helpers are convenience facades. The Effect APIs are exposed from the Effect subpath:

import { Effect } from "effect";
import {
  compileQuerySpecToSQLEffect,
  lowerQuerySpecToIREffect,
  resolveRegistryEffect,
  validateQuerySpecEffect,
} from "@ypanagidis/joqi/effect";

const program = Effect.gen(function* () {
  const registry = yield* resolveRegistryEffect({ physical, defaults, policy });
  const validatedQuery = yield* validateQuerySpecEffect({ query, registry, params });
  const ir = yield* lowerQuerySpecToIREffect({ query: validatedQuery, registry, params });
  const sqlPlan = yield* compileQuerySpecToSQLEffect({
    query: validatedQuery,
    registry,
    dialect: "postgres",
  });

  return { registry, validatedQuery, ir, sqlPlan };
});

Resolver failures are Effect-native tagged errors:

program.pipe(
  Effect.catchTags({
    RegistryParseError: (error) => Effect.succeed(error.error),
    RegistryResolutionError: (error) => Effect.succeed(error.issues),
  }),
);

QueryIR And SQLPlan

Query lowering validates the query, resolves public paths to physical field refs, and emits deduplicated joins. The current IR is adapter-neutral:

type QueryIR = {
  kind: "select";
  source: QueryIRSourceRef;
  select: QueryIRFieldRef[];
  joins: QueryIRJoin[];
  where?: QueryIRFilter;
  groupBy: QueryIRFieldRef[];
  orderBy: QueryIROrderBy[];
  limit?: number;
  offset?: number;
};

The SQL compiler returns raw SQL plus bound params. It defaults to MySQL and can also emit PostgreSQL or SQLite SQL:

type SQLDialect = "mysql" | "postgres" | "sqlite";

type SQLPlan = {
  dialect: SQLDialect;
  sql: string;
  params: readonly JsonValue[];
};

Core Contracts

Query Schema

The query schema is the public JSON shape accepted by Joqi.

It should describe query intent, not raw SQL:

{
  "version": "v1",
  "source": "placement",
  "select": ["name", "status", "budget", "campaign.name"],
  "where": {
    "and": [
      { "field": "status", "op": "eq", "value": { "$param": "status" } },
      { "field": "budget", "op": "gte", "value": { "$param": "minBudget" } }
    ]
  },
  "orderBy": [{ "field": "budget", "direction": "desc" }],
  "limit": { "$param": "limit" }
}

The query schema should not expose raw table names, raw column names, raw SQL fragments, or arbitrary function names.

$param references are bound from params during validation, before SQL compilation. Missing params fail validation; filter params are checked against the resolved field type; params used for limit or offset must be non-negative integers.

Saved Query Templates

Because params are supplied separately, a query can be saved as reusable JSON and run with different request values:

const activePlacementReport = await loadQueryTemplate("active-placement-report");

const result = await runtime.run({
  spec: activePlacementReport,
  params: {
    status: "active",
    minBudget: 10000,
    campaignName: "spring",
    limit: 25,
  },
  explain: true,
});

The template stays stable:

{
  "version": "v1",
  "source": "placement",
  "select": ["name", "status", "budget", "campaign.name"],
  "where": {
    "and": [
      { "field": "status", "op": "eq", "value": { "$param": "status" } },
      { "field": "budget", "op": "gte", "value": { "$param": "minBudget" } },
      { "field": "campaign.name", "op": "contains", "value": { "$param": "campaignName" } }
    ]
  },
  "limit": { "$param": "limit" }
}

Field Paths And Derived Joins

Joqi intentionally uses public dotted field paths for relation fields:

name
budget
campaign.name

This keeps UI builders simple: a field picker can emit the selected public path directly into select, where, groupBy, or orderBy.

Dotted paths do not create arbitrary joins. During validation, every path segment must exist in the resolved registry and must have the required capability:

campaign.name
  -> placement has an exposed campaign relation
  -> campaign traversal is selectable/filterable for this query position
  -> campaign is within maxDepth
  -> campaign has an exposed name field

After validation, Joqi derives a deduplicated join plan from those field paths. The join plan is visible in QueryIR.joins and is what the SQL compiler uses.

So relation traversal is implicit in the public query for UI ergonomics, but explicit in the compiled plan for inspection and execution.

Registry Schema

The registry defines the allowed query universe.

Joqi uses three registry layers:

PhysicalRegistry
  generated from ORM/schema metadata

RegistryPolicy
  user-authored allowlist and customization layer

ResolvedRegistry
  per-request effective registry used by validation and compilation

The physical registry says what exists.

The registry policy says what is exposed.

The resolved registry is generated from both, plus engine defaults.

const resolved = resolveRegistry({
  physical,
  defaults,
  policies,
});

Resolution should be cheap and deterministic. It can happen on every query call so field visibility, limits, relation depth, and capabilities can vary by tenant, role, feature flag, or UI-managed configuration.

Registry Shape

The physical registry is adapter-generated and close to the ORM/database model:

type PhysicalRegistry = {
  sources: Record<string, PhysicalSource>;
};

type PhysicalSource = {
  kind: "table" | "view" | "model";
  name: string;
  schema?: string;
  primaryKey?: string[];
  fields: Record<string, PhysicalField>;
  relations?: Record<string, PhysicalRelation>;
};

The policy is user-authored data. It can come from code, a database, generated configuration, or a future UI.

const policy = {
  sources: {
    placements: {
      expose: true,
      exposeAs: "placement",
      fields: {
        name: {
          expose: true,
          filterable: true,
          sortable: true,
        },
        budgetCents: {
          expose: true,
          exposeAs: "budget",
          type: "number",
          filterable: true,
          sortable: true,
          aggregations: ["sum", "avg"],
        },
      },
    },
  },
};

The resolved registry is what Joqi actually compiles against. Queries only reference public names from the resolved registry.

placement.budget -> placements.budgetCents
placement.campaign.name -> join placements.campaignId = campaigns.id, then campaigns.name

Resolved relations preserve the physical join keys needed by later IR lowering:

type ResolvedRelation = {
  physicalSource: string;
  physicalRelation: string;
  target: string;
  kind: "one" | "many";
  localFields: string[];
  foreignFields: string[];
};

Adapters

Adapters have two jobs:

ORM/schema -> PhysicalRegistry
SQLPlan -> adapter execution

For Drizzle:

Drizzle schema -> PhysicalRegistry
SQLPlan -> db.execute(...)

The Drizzle adapter lives in @ypanagidis/joqi-drizzle, not core Joqi. It can create a PhysicalRegistry from Drizzle rc3 relation metadata and execute SQLPlan through db.execute(...). Core stays ORM-agnostic and produces SQLPlan; adapter packages execute or translate that plan.

For Prisma:

Prisma schema -> PhysicalRegistry
QueryIR -> Prisma raw SQL initially

Prisma object-query compilation can be added later for the subset Prisma can represent cleanly. The core should not be shaped around Prisma's findMany API.

Current Alpha

The current alpha exposes the public Zod schema layer for QuerySpec, PhysicalRegistry, RegistryPolicy, RegistryDefaults, and ResolvedRegistry, plus the runtime pipeline around these contracts:

QuerySpecSchema.parse
  -> resolveRegistry
  -> validateQuerySpec
  -> lowerQuerySpecToIR
  -> compileQuerySpecToSQL
  -> adapter.execute

Drivers remain useful as an optional layer for business-specific specs:

business JSON
  -> driver
  -> QuerySpec
  -> Joqi core

Design Constraints

Joqi should not become:

- a BI platform
- a no-code backend
- an ORM
- a GraphQL server
- a public SQL-in-JSON language
- an authorization framework

The registry controls query shape and field exposure. Per-user row-level authorization and mandatory tenant constraints should be supplied by the host application.