@promakeai/orm

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

Installation

npm install @promakeai/orm

Schema Definition

import { defineSchema, f } from '@promakeai/orm';

const schema = defineSchema({
  name: 'myapp',
  languages: ['en', 'tr', 'de'],
  tables: {
    users: {
      id: f.id(),
      email: f.string().required().unique().lowercase(),
      name: f.string().required().trim(),
      age: f.int().min(0).max(150),
      role: f.string().enum(['user', 'admin']).default('user'),
      bio: f.text().translatable(),
      createdAt: f.timestamp(),
      metadata: f.json(),
    },
    products: {
      id: f.id(),
      sku: f.string().required().unique(),
      price: f.decimal().required().min(0),
      stock: f.int().default(0),
      name: f.string().translatable().required(),
      description: f.text().translatable(),
      categoryId: f.int().ref('categories'),
      tagIds: f.json().ref('tags'),  // Array reference
    },
    categories: {
      id: f.id(),
      slug: f.string().required().unique(),
      name: f.string().translatable().required(),
      parentId: f.int().ref({
        table: 'categories',
        onDelete: 'SET_NULL',
      }),
    },
  },
});

JSON Schema (AI-Friendly)

The JSON schema format is the runtime-friendly version and supports native arrays and typed objects:

{
  "name": "myapp",
  "languages": ["en", "tr"],
  "defaultLanguage": "en",
  "tables": {
    "products": {
      "id": { "type": "id" },
      "tags": { "type": ["string"] },
      "metadata": { "type": { "color": "string", "weight": "number" } },
      "variants": { "type": [{ "sku": "string", "price": "number" }] }
    }
  }
}

Field Types

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

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

Field Modifiers

Constraints

f.string()
  .required()              // NOT NULL
  .nullable()              // Allow NULL (default)
  .unique()                // UNIQUE constraint
  .primary()               // PRIMARY KEY
  .default('value')        // DEFAULT value

String Transforms

f.string()
  .trim()                  // Remove whitespace
  .lowercase()             // Convert to lowercase
  .uppercase()             // Convert to uppercase

Validation

f.string()
  .minLength(1)            // Minimum length
  .maxLength(255)          // Maximum length
  .enum(['a', 'b', 'c'])   // Allowed values
  .match(/^[a-z]+$/)       // RegExp pattern

f.int()
  .min(0)                  // Minimum value
  .max(100)                // Maximum value

References

// Simple reference
f.int().ref('users')

// With options
f.int().ref({
  table: 'users',
  field: 'id',             // Default: 'id'
  onDelete: 'CASCADE',     // CASCADE | SET_NULL | RESTRICT | NO_ACTION
  onUpdate: 'CASCADE',
})

// Array reference (JSON field with refs)
f.json().ref('tags')

Multi-Language

f.string().translatable()  // Stored in {table}_translations
f.text().translatable()

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

const schema = defineSchema({
  languages: ['en', 'tr', 'de'],  // Supported languages
  // OR
  languages: {
    default: 'en',
    supported: ['en', 'tr', 'de'],
  },
  tables: {
    products: {
      id: f.id(),
      price: f.decimal(),
      name: f.string().translatable(),      // In translation table
      description: f.text().translatable(), // In translation table
    },
  },
});

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
);

Schema Helpers

import {
  getTranslatableFields,
  getNonTranslatableFields,
  getReferenceFields,
  getPrimaryKeyField,
  getRequiredFields,
  isRequiredField,
  getMainTableFields,
  getTranslationTableFields,
  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)) {
  // ...
}

Schema Merging

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

const schema1 = defineSchema({
  tables: { users: { ... } },
});

const schema2 = defineSchema({
  tables: { products: { ... } },
});

const merged = mergeSchemas([schema1, schema2]);
// { tables: { users: {...}, products: {...} } }

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,
} from '@promakeai/orm';

Related Packages

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

License

MIT