npm package: @promakeai/orm
Description: Database-agnostic ORM core - works in browser and Node.js
Author: Promake Inc.
Latest version: 1.4.0

# @promakeai/orm

Core ORM package with query builder, schema validation, and multi-language support. Platform-agnostic - works in both browser and Node.js.

## Installation

```bash
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`:

```json
{
  "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)

```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)

```typescript
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

```json
{
  "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

```sql
-- 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

```typescript
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.

```typescript
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:

```json
"$permissions": {
  "anon": ["read"],
  "user": ["read", "create"],
  "admin": ["read", "create", "update", "delete"]
}
```

- **Roles:** `anon`, `user`, `admin`
- **Actions:** `create`, `read`, `update`, `delete`
- Not a database column — stored as metadata on `TableDefinition.permissions`

```typescript
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'] }
```

## REST Adapter

REST API adapter with Bearer auth support. Works in both Node.js and browser.

```typescript
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

```typescript
import {
  getTranslatableFields,
  getNonTranslatableFields,
  getReferenceFields,
  getPrimaryKeyField,
  getRequiredFields,
  isRequiredField,
  getMainTableFields,
  getTranslationTableFields,
  getTablePermissions,
  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

```typescript
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:

```typescript
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

```typescript
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';
```

## Related Packages

- [@promakeai/dbcli](../dbcli) - CLI tool for database operations
- [@promakeai/dbreact](../dbreact) - React hooks and providers

## License

MIT