@promakeai/orm

Core ORM package with query builder, schema validation, and multi-language support. Platform-agnostic - works in both browser and Node.js.

Installation

npm install @promakeai/orm

JSON Schema (AI-Friendly)

The JSON schema format is the runtime-friendly version and supports native arrays, typed objects, and role-based $permissions:

{
  "name": "myapp",
  "languages": ["en", "tr"],
  "defaultLanguage": "en",
  "tables": {
    "products": {
      "$permissions": {
        "anon": ["read"],
        "user": ["read"],
        "admin": ["read", "create", "update", "delete"]
      },
      "id": { "type": "id" },
      "tags": { "type": ["string"] },
      "metadata": { "type": { "color": "string", "weight": "number" } },
      "variants": { "type": [{ "sku": "string", "price": "number" }] }
    }
  }
}

Field Types

| Type | SQL | Description | |------|-----|-------------| | "id" | INTEGER PRIMARY KEY AUTOINCREMENT | Auto-increment primary key | | "string" | VARCHAR | Short text | | "text" | TEXT | Long text | | "int" | INTEGER | Integer | | "decimal" | REAL/DECIMAL | Decimal number | | "bool" | INTEGER (0/1) | Boolean | | "timestamp" | TEXT (ISO) | ISO datetime string | | "json" | TEXT | JSON serialized data |

JSON schema type syntax supports ["string"], ["number"], ["boolean"], { "key": "string" }, and [{ "key": "string" }] which are stored as JSON in SQL (TEXT) and typed in TS.

Field Modifiers (JSON)

{
  "email": {
    "type": "string",
    "required": true,
    "unique": true,
    "lowercase": true,
    "trim": true,
    "minLength": 1,
    "maxLength": 255,
    "enum": ["a", "b", "c"],
    "match": "^[a-z]+$",
    "default": "value"
  },
  "age": {
    "type": "int",
    "min": 0,
    "max": 150
  },
  "categoryId": {
    "type": "int",
    "ref": "categories"
  },
  "name": {
    "type": "string",
    "translatable": true
  }
}

Query Builder (MongoDB-style)

import { buildWhereClause } from '@promakeai/orm';

// Simple equality
buildWhereClause({ status: 'active' });
// WHERE status = ?  params: ['active']

// Comparison operators
buildWhereClause({ price: { $gt: 100 } });    // > 100
buildWhereClause({ price: { $gte: 100 } });   // >= 100
buildWhereClause({ price: { $lt: 100 } });    // < 100
buildWhereClause({ price: { $lte: 100 } });   // <= 100
buildWhereClause({ status: { $ne: 'deleted' } }); // != 'deleted'

// Array operators
buildWhereClause({ id: { $in: [1, 2, 3] } });    // IN (?, ?, ?)
buildWhereClause({ id: { $nin: [1, 2, 3] } });   // NOT IN (?, ?, ?)

// String matching (ESCAPE '\' so a literal % or _ can be matched)
buildWhereClause({ name: { $like: '%john%' } });     // LIKE ? ESCAPE '\'
buildWhereClause({ name: { $notLike: '%test%' } });  // NOT LIKE ? ESCAPE '\'

// Range and null
buildWhereClause({ price: { $between: [10, 100] } }); // BETWEEN ? AND ?
buildWhereClause({ deletedAt: { $isNull: true } });   // IS NULL
buildWhereClause({ email: { $isNull: false } });      // IS NOT NULL

// JSON array contains
buildWhereClause({ tags: { $contains: "sale" } });    // json_each(...) = "sale"
buildWhereClause({ tags: { $containsAny: ["sale", "new"] } }); // json_each(...) IN (...)

// Logical operators
buildWhereClause({
  $or: [
    { active: true },
    { role: 'admin' }
  ]
});
// (active = ? OR role = ?)

buildWhereClause({
  $and: [
    { price: { $gt: 50 } },
    { stock: { $gt: 0 } }
  ]
});
// (price > ? AND stock > ?)

buildWhereClause({
  $nor: [
    { banned: true },
    { suspended: true }
  ]
});
// NOT (banned = ? OR suspended = ?)

// Negation
buildWhereClause({
  age: { $not: { $lt: 18 } }
});
// NOT (age < ?)

// Combined
buildWhereClause({
  status: 'active',
  price: { $gt: 100, $lt: 500 },
  category: { $in: ['electronics', 'books'] },
});
// status = ? AND price > ? AND price < ? AND category IN (?, ?)

Multi-Language Support

Schema Configuration

{
  "languages": ["en", "tr", "de"],
  "defaultLanguage": "en",
  "tables": {
    "products": {
      "id": { "type": "id" },
      "price": { "type": "decimal" },
      "name": { "type": "string", "translatable": true },
      "description": { "type": "text", "translatable": true }
    }
  }
}

Generated Tables

-- Main table
CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  price REAL
);

-- Translation table
CREATE TABLE products_translations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id INTEGER NOT NULL,
  lang TEXT NOT NULL,
  name TEXT,
  description TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  UNIQUE (product_id, lang)
);

Translation Query Builder

import { buildTranslationQuery } from '@promakeai/orm';

const { sql, params } = buildTranslationQuery('products', schema, {
  lang: 'tr',
  fallbackLang: 'en',
  where: { price: { $gt: 100 } },
  orderBy: [{ field: 'name', direction: 'ASC' }],
  limit: 10,
});

// SELECT
//   m.id, m.price,
//   COALESCE(t.name, fb.name) AS name,
//   COALESCE(t.description, fb.description) AS description
// FROM products m
// LEFT JOIN products_translations t ON m.id = t.product_id AND t.lang = ?
// LEFT JOIN products_translations fb ON m.id = fb.product_id AND fb.lang = ?
// WHERE price > ?
// ORDER BY name ASC
// LIMIT 10

Populate Resolver

Batch-fetches references to prevent N+1 queries:

Populate options accept a space-separated string, string array, or nested object for deeper population.

import { resolvePopulate } from '@promakeai/orm';

const posts = await adapter.list('posts');

// Resolve author references
const postsWithAuthors = await resolvePopulate(
  posts,
  { userId: true },
  schema,
  adapter
);
// [{ id: 1, title: '...', userId: { id: 1, name: 'John' } }]

// Nested populate
const postsWithDetails = await resolvePopulate(
  posts,
  {
    userId: true,
    comments: {
      populate: { authorId: true },
      where: { approved: true },
      limit: 5,
    },
  },
  schema,
  adapter
);

// Array reference populate
const postsWithTags = await resolvePopulate(
  posts,
  { tagIds: true },  // JSON array of tag IDs
  schema,
  adapter
);

Permissions ($permissions)

Role-based access control defined as table metadata in the JSON schema:

"$permissions": {
  "anon": ["read"],
  "user": ["read", "create"],
  "admin": ["read", "create", "update", "delete"]
}

• Roles: anon, user, admin
• Actions: create, read, update, delete, and the ownership variants read:own, update:own, delete:own (there is no create:own).
• Not a database column — stored as metadata on TableDefinition.permissions.
• Enforced by the backend, not by the ORM. The ORM provides the shared semantics (validation + resolution) the backend pins to via contract fixtures.

import { getTablePermissions } from '@promakeai/orm';
import type { PermissionRole, PermissionAction, TablePermissions } from '@promakeai/orm';

const permissions = getTablePermissions(schema.tables.products);
// { anon: ['read'], user: ['read'], admin: ['read', 'create', 'update', 'delete'] }

:own Ownership

A <verb>:own grant means a role may act only on rows it owns. Owned rows are tracked by a reserved owner_id column (a nullable int; NULL marks an orphan / site-owned row with no owner). The backend injects and manages owner_id — never declare it yourself:

• When parsing a table whose $permissions use any :own action, the JSON converter auto-injects the canonical owner_id column (int, nullable), overwriting any user-declared owner_id so a wrong type can never silently break ownership. Such a table is also auto-marked live (visitor-owned data is runtime data, preserved across publish).
• On a table that does not use ownership, declaring owner_id is a RESERVED_OWNER_ID validation error.
• A role cannot hold both the broad and the :own variant of the same verb (e.g. update + update:own) — that is an AMBIGUOUS_OWNERSHIP_GRANT error.

"$permissions": {
  "anon": ["read"],
  "user": ["read:own", "update:own", "delete:own", "create"],
  "admin": ["read", "create", "update", "delete"]
}

resolvePermission returns a three-valued outcome the backend acts on (OWNER_ID_FIELD is the reserved column name; admin bypass is handled by the caller). These ownership helpers live in schema/schemaHelpers.ts and are exported from the package root:

import {
  resolvePermission,
  tableUsesOwnership,
  OWNER_ID_FIELD,
  type PermissionOutcome,
} from '@promakeai/orm';

resolvePermission(table, 'user', 'read');
// "allow"      — no permissions on the table (except "forms": create-only default), or the role has the broad verb
// "allow-own"  — the role has only `<verb>:own` (read/update/delete; not create)
// "deny"       — otherwise

tableUsesOwnership(table);  // true when any role is granted a `:own` action
OWNER_ID_FIELD;             // "owner_id"

Live Tables ($live)

Mark a table with "$live": true to declare it a live table — one that holds runtime / visitor-written data (bookings, accounts, reviews, …) as opposed to authored content. The backend preserves live tables across publish/snapshot and resolves them to a canonical DB regardless of DB-MODE / DB-VERSION. Like $permissions, this is table metadata enforced by the backend, not the ORM.

{
  "tables": {
    "bookings": {
      "$live": true,
      "$permissions": { "user": ["read:own", "create"] },
      "id": { "type": "id" },
      "slot": { "type": "timestamp", "required": true }
    }
  }
}

In JSON the flag is the $live metadata key; it normalizes to table.live on the TableDefinition, and is preserved in dbcli generate output (and forwarded by the REST adapter's generate). Helpers:

import { isLiveTable, getLiveTables } from '@promakeai/orm';

isLiveTable(schema.tables.bookings);  // true
getLiveTables(schema);                // ['bookings', ...] (insertion order, NOT contractual — sort at boundaries)

REST Adapter

REST API adapter with Bearer auth support. Works in both Node.js and browser.

import { RestAdapter } from '@promakeai/orm';

// Static token (server-side / CLI)
const adapter = new RestAdapter({
  baseUrl: 'https://api.example.com',
  token: 'my-api-key',
  schema,
  defaultLang: 'en',
});

// Dynamic token callback (browser — called on every request)
const adapter = new RestAdapter({
  baseUrl: 'https://api.example.com',
  getToken: () => localStorage.getItem('token'),
  schema,
  defaultLang: 'en',
});

getToken takes priority over static token when both are provided.

Errors from the backend

A non-2xx response throws an Error whose message is the body's message, else its error, else HTTP <status> <statusText>. That order is stable; callers match on it. The exceptions are get and delete, which turn a 404 (a missing table included) into null / false, so these fields never reach their callers. The error also carries:

| Field | Type | Set when | |-------|------|----------| | status | number | Always: the HTTP status. | | backendError | string \| undefined | The JSON body has a string error field. | | backendMessage | string \| undefined | The JSON body has a string message field. |

The two backend fields are kept separately because message often hides the specific fact. A missing table answers 404 with {"error":"Table 'x' does not exist","message":"Use /database/generate to create tables first"}: message is the generic remedy, backendError names the table. A non-JSON body (a proxy's HTML page) sets neither field.

try {
  await adapter.list('categories');
} catch (e) {
  const err = e as Error & { status?: number; backendError?: string };
  if (err.status === 404 && err.backendError?.startsWith('Table ')) {
    // the table does not exist; list tables instead of retrying
  }
}

Record ids the adapter refuses

get, update, delete and upsertTranslation put the record id into the URL path, so two classes of id are refused instead of being sent. Both throw an error for which isUnaddressableIdError(err) is true; its reason field says which.

| reason | Refused by | Why | |---|---|---| | path-delimiter | all four | The WHATWG URL parser does not preserve the id as exactly one path segment. This includes /, ?, #, \\, ., .., whitespace, non-ASCII text, and control characters; the request would otherwise address a different row (or no row). | | reserved-segment | get only | The id routes to a fixed GET /{table}/... endpoint the tenant backend registers before /{table}/:id, so the response would be that endpoint's envelope, not the row. PUT and DELETE register no such sibling, so writes to the same id are fine. |

Why the rule is that wide, given the list above reads like an over-correction: the id is sent unencoded, and the tenant backend runs fiber with UnescapePath unset, so the handler binds whatever the URL parser produced. A Turkish, Arabic or Cyrillic id is refused for that reason, not as caution.

None of those resolved to its own row before the refusal existed either: an encoded id made the handler bind the escape sequence and report the row absent, and /, ? and # addressed the wrong row (or no row) outright - ? and # end the path, so AB#1 was served as row AB. So the refusal replaces a silent wrong answer with a named error; it did not remove a working call. Lifting it for the encodable cases needs the server to decode the path segment first, tracked in dbcli#37. Reach such a row by filter on its primary key, and write to it through the batch endpoints, which carry ids in the request body.

import { isUnaddressableIdError } from '@promakeai/orm';

try {
  await adapter.get('products', 'a@b/c');
} catch (err) {
  if (isUnaddressableIdError(err)) {
    // Reach the row by filter instead of by id.
    const [row] = await adapter.list('products', {
      where: { sku: 'a@b/c' },
      limit: 1,
    });
  }
}

Use the predicate rather than instanceof: the class is exported as a type only, because an identity check breaks when more than one copy of the bundle is loaded. A caller that uses get() purely to test whether a row exists should treat a reserved-segment refusal as "cannot tell" and let the write proceed, since that write cannot collide.

Schema Helpers

import {
  getTranslatableFields,
  getNonTranslatableFields,
  getReferenceFields,
  getPrimaryKeyField,
  getRequiredFields,
  isRequiredField,
  getMainTableFields,
  getTranslationTableFields,
  getTablePermissions,
  isLiveTable,
  getLiveTables,
  toTranslationTableName,
  toTranslationFKName,
  singularize,
  pluralize,
  toPascalCase,
  toCamelCase,
  toSnakeCase,
} from '@promakeai/orm';

const table = schema.tables.products;

getTranslatableFields(table);    // ['name', 'description']
getNonTranslatableFields(table); // ['id', 'price', 'sku', 'stock', 'categoryId']
getReferenceFields(table);       // [['categoryId', { table: 'categories', ... }]]
getPrimaryKeyField(table);       // 'id'
getRequiredFields(table);        // ['sku', 'price', 'name']

toTranslationTableName('products');  // 'products_translations'
toTranslationFKName('products');     // 'product_id'

singularize('products');  // 'product'
pluralize('product');     // 'products'
toPascalCase('user_id');  // 'UserId'
toCamelCase('user_id');   // 'userId'
toSnakeCase('userId');    // 'user_id'

Schema Validation

import {
  validateSchema,
  assertValidSchema,
  isValidSchema,
} from '@promakeai/orm';

// Returns array of errors
const errors = validateSchema(schema);
// [{ path: 'tables.users.email', message: '...' }]

// Throws on invalid schema
assertValidSchema(schema);

// Boolean check
if (isValidSchema(schema)) {
  // ...
}

IDataAdapter Interface

All adapters must implement this interface:

interface IDataAdapter {
  schema?: SchemaDefinition;
  defaultLang?: string;

  // Query methods
  list<T>(table: string, options?: QueryOptions): Promise<T[]>;
  get<T>(table: string, id: string | number, options?: QueryOptions): Promise<T | null>;
  count(table: string, options?: QueryOptions): Promise<number>;
  paginate<T>(table: string, page: number, limit: number, options?: QueryOptions): Promise<PaginatedResult<T>>;

  // Write methods
  create<T>(table: string, data: Record<string, unknown>): Promise<T>;
  update<T>(table: string, id: string | number, data: Record<string, unknown>): Promise<T>;
  delete(table: string, id: string | number): Promise<boolean>;

  // Batch methods
  createMany<T>(table: string, records: Record<string, unknown>[], options?: { ignore?: boolean }): Promise<{ created: number; ids: (number | bigint)[] }>;
  updateMany(table: string, updates: { id: number | string; data: Record<string, unknown> }[]): Promise<{ updated: number }>;
  deleteMany(table: string, ids: (number | string)[]): Promise<{ deleted: number }>;

  // Translation methods
  createWithTranslations<T>(table: string, data: Record<string, unknown>, translations?: Record<string, Record<string, unknown>>): Promise<T>;
  upsertTranslation(table: string, id: string | number, lang: string, data: Record<string, unknown>): Promise<void>;
  getTranslations<T>(table: string, id: string | number): Promise<T[]>;

  // Raw queries
  raw<T>(query: string, params?: unknown[]): Promise<T[]>;
  execute(query: string, params?: unknown[]): Promise<{ changes: number; lastInsertRowid: number | bigint }>;

  // Transactions
  beginTransaction(): Promise<void>;
  commit(): Promise<void>;
  rollback(): Promise<void>;

  // Schema
  getTables?(): Promise<string[]>;
  getTableSchema?(table: string): Promise<unknown[]>;

  // Lifecycle
  connect?(): void | Promise<void>;
  close(): void | Promise<void>;
}

TypeScript Types

import type {
  SchemaDefinition,
  TableDefinition,
  FieldDefinition,
  FieldType,
  FieldReference,
  QueryOptions,
  WhereClause,
  OrderByOption,
  PopulateOption,
  PaginatedResult,
  IDataAdapter,
  PermissionRole,
  PermissionAction,
  TablePermissions,
} from '@promakeai/orm';

import { RestAdapter } from '@promakeai/orm';
import type { RestAdapterConfig } from '@promakeai/orm';

backend.json Manifest

@promakeai/orm also exports the backend.json manifest types and validator (packages/orm/src/manifest/): the single source of truth for per-tenant platform configuration — crons, settings-only storage policy, email templates, realtime tables, inference limits, and function runtime settings. It is mirrored on the Go customer-backend side and kept in lockstep via shared contract fixtures.

import {
  validateManifest, assertValidManifest, isValidManifest,
  ManifestValidationErrorCode,
} from '@promakeai/orm';
import type { BackendManifest, ManifestFiles } from '@promakeai/orm';

const errors = validateManifest(manifest, dbSchema, files); // [] when valid

For the full shape, every field, validation rule, error code, and file-reference contract, see docs/manifest-contract.md.

Related Packages

• @promakeai/dbcli - CLI tool for database operations
• @promakeai/dbreact - React hooks and providers

License

MIT