@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
buildWhereClause({ name: { $like: '%john%' } });     // LIKE ?
buildWhereClause({ name: { $notLike: '%test%' } });  // NOT LIKE ?

// 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's schema module:

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

resolvePermission(table, 'user', 'read');
// "allow"      — no permissions on the table, 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.

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