Multi-Source ORM

A Prisma-like DSL → IR → Type-safe TypeScript client for stitching data from multiple sources (PostgreSQL, MS SQL Server, HTTP APIs).

✨ Prisma-Like Workflow

This ORM follows Prisma's architecture: Schema → Generate → Type-Safe Client

const orm = new ORMClient(schema);       // 1. Parse schema
const client = orm.generate();           // 2. Generate client (like `prisma generate`)
const users = await client.User.findMany({ ... }); // 3. Type-safe queries

Features

• 🎯 Type-safe client: Prisma-like API with full IntelliSense support
• 💾 Multi-datasource: Query across PostgreSQL, MS SQL Server, HTTP APIs
• 🔐 Windows Authentication: First-class support for SQL Server Windows Auth (ODBC)
• 🔗 Client-side joins: Stitch data across different datasources
• ⚡ Smart query planning: Automatic bulk fetching and concurrency control
• 🔒 Strict/non-strict modes: Control error handling behavior
• 🔑 OAuth support: Built-in OAuth with refresh token handling
• ⚙️ Computed fields: Sync and async field resolvers with timeout control
• 💰 ACID transactions: Full transaction support with isolation levels
• 🏊 Connection pooling: Efficient connection management for PostgreSQL and MS SQL Server
• ⚡ Query caching: In-memory cache with TTL and LRU eviction (10-100x speedup)
• 📊 Configurable limits: Prevent over-fetching and fan-out explosions
• 🚀 Production-ready: Handles 200+ concurrent users with 226 req/sec

Quick Start

1. Install Dependencies

npm install

2. Create a Schema

Create a .qts schema file (see orm.qts):

config {
  maxIncludeDepth = 5
  maxFanOut = 100
  strictMode = false
}

datasource main_db {
  provider = "sqlserver"
  url = env("DATABASE_URL")
}

model User @datasource(main_db) {
  id          Int       @id @default(autoincrement())
  email       String    @unique
  name        String?
  role        String
  tenant_id   Int
  created_at  DateTime?
}

3. Generate Types

npm run generate

4. Use Type-Safe Client

import { ORMClient } from '@aradox/multi-orm';
import * as fs from 'fs';

// Load schema
const schema = fs.readFileSync('./orm.qts', 'utf-8');

// Create ORM and generate client
const orm = new ORMClient(schema, { schemaPath: './orm.qts' });
const db = orm.generate();

// Type-safe queries with IntelliSense
const users = await db.User.findMany({
  where: { 
    role: { eq: 'admin' },
    is_active: { eq: true }
  },
  orderBy: { created_at: 'desc' },
  take: 10
});

console.log(users);

// Clean up
await db.$disconnect();

5. Add Row-Level Security (Optional)

import { tenantIsolationMiddleware, rbacMiddleware } from '@aradox/multi-orm/dist/src/middleware/examples';

// Register middleware
db.use(tenantIsolationMiddleware(['User', 'Order', 'Customer']));
db.use(rbacMiddleware({ 'User': ['admin'], 'Order': ['admin', 'user'] }));

// Set context per request
db.setContext({
  user: {
    id: session.user.id,
    tenantId: session.user.tenantId,
    role: session.user.role
  }
});

// All queries automatically filtered by tenant
const customers = await db.Customer.findMany({});
// SQL: SELECT * FROM customers WHERE tenant_id = {session.user.tenantId}

6. Use Transactions (Optional)

// ACID transactions with automatic commit/rollback
await db.$transaction(async (tx) => {
  const customer = await tx.Customer.create({
    data: { name: 'John', email: '[email protected]' }
  });

  const order = await tx.Orders.create({
    data: {
      customerId: customer.Id,
      total: 100.00,
      status: 'pending'
    }
  });

  // Automatic commit if successful
  // Automatic rollback on any error
});

7. Enable Query Caching (Optional)

// Configure in-memory cache with TTL and LRU eviction
const orm = new ORMClient(schema, {
  schemaPath: './orm.qts',
  cache: {
    enabled: true,
    ttl: 60000,      // 60 seconds
    maxSize: 1000    // Max 1000 cached queries
  }
});

const db = orm.generate();

// First query: Cache miss (45ms)
const users1 = await db.User.findMany({ where: { active: true } });

// Second identical query: Cache hit (0.4ms) - 100x faster!
const users2 = await db.User.findMany({ where: { active: true } });

// Cache automatically invalidated on mutations
await db.User.create({ data: { name: 'Jane' } });

Documentation

📚 Complete User Guide - Comprehensive guide for using the ORM

Quick Links

• User Guide - Complete documentation for developers
• Architecture & Performance Guide - Performance benchmarks and best practices
• Query Caching Guide - Detailed caching configuration
• Nested Queries Explained - How cross-datasource stitching works
• Hooks & Middleware Guide - Row-level security and middleware
• SQL Logging Guide - Debug SQL queries
• CRUD Guide - CRUD operations reference
• Enum Guide - Type-safe enums reference
• AI Coding Guide - Build multi-tenant applications
• Quick Reference - One-page cheat sheet

Type-Safe API

All queries are type-safe with full IntelliSense support:

// Query methods
await db.User.findMany({ where: { ... } })
await db.User.findUnique({ where: { id: 1 } })
await db.User.findFirst({ where: { ... } })
await db.User.count({ where: { ... } })

// Mutation methods
await db.User.create({ data: { ... } })
await db.User.createMany({ data: [...] })
await db.User.update({ where: { ... }, data: { ... } })
await db.User.updateMany({ where: { ... }, data: { ... } })
await db.User.delete({ where: { ... } })
await db.User.deleteMany({ where: { ... } })
await db.User.upsert({ where: { ... }, create: { ... }, update: { ... } })

// Transaction methods
await db.$transaction(async (tx) => { ... })
await db.$transaction(async (tx) => { ... }, { isolationLevel: 'SERIALIZABLE' })

// Utility methods
await db.$disconnect()

DSL Reference

Config Block

config {
  strict = false
  limits = {
    maxIncludeDepth = 2
    maxFanOut = 2000
    maxConcurrentRequests = 10
    requestTimeoutMs = 10000
    postFilterRowLimit = 10000
  }
}

Datasources

PostgreSQL

datasource pg_main {
  provider = "postgres"
  url      = env("DATABASE_URL")
}

Connection String Format:

postgresql://user:password@localhost:5432/database

MySQL

datasource mysql_main {
  provider = "mysql"
  url      = env("MYSQL_CONNECTION_STRING")
}

Connection String Format:

mysql://user:password@localhost:3306/database

Key Features:

• Supports all MySQL operators and filters
• Uses mysql2/promise with connection pooling
• Snake_case convention for table and column names
• Full ACID transaction support with isolation levels
• Configurable pool size (default: 10 connections)

MongoDB

datasource mongo_main {
  provider = "mongodb"
  url      = env("MONGODB_CONNECTION_STRING")
}

Connection String Format:

mongodb://user:password@localhost:27017/database
mongodb+srv://user:[email protected]/database

Key Features:

• NoSQL document database support
• Automatic _id to id field conversion
• Supports MongoDB query operators ($eq, $in, $regex, etc.)
• Full ACID transaction support (requires replica set)
• Lowercase collection names (e.g., "users", "orderitems")
• Connection pooling with configurable pool size

MS SQL Server / T-SQL

datasource sqlserver_main {
  provider = "mssql"
  url      = env("MSSQL_CONNECTION_STRING")
}

Connection String Formats:

SQL Server Authentication:

Server=localhost;Database=mydb;User Id=sa;Password=yourPassword;Encrypt=true

Windows Authentication:

Server=ServerName;Database=mydb;Integrated Security=true;Domain=DOMAIN;TrustServerCertificate=true

Key Features:

• Supports all T-SQL operators and filters
• Windows Authentication (Integrated Security) and SQL Server Authentication
• Uses OUTPUT INSERTED.* for returning created/updated records
• Handles SQL Server's 2100 parameter limit for IN queries
• Supports TOP, OFFSET/FETCH NEXT pagination
• PascalCase convention for table and column names

See: WINDOWS_AUTH.md for detailed Windows Authentication setup

HTTP API

datasource crm_api {
  provider = "http"
  baseUrl  = "https://api.example.com"
  oauth    = {
    script: "./auth/crm_oauth.ts",
    cacheTtl: "55m",
    optional: false
  }
}

Enums

Define enums for type-safe string values:

enum UserRole {
  SUPER_ADMIN
  ADMIN
  MODERATOR
  USER
  GUEST
}

enum OrderStatus {
  PENDING
  PROCESSING
  SHIPPED
  DELIVERED
  CANCELLED
}

Usage in models:

model User @datasource(pg_main) {
  id     Int      @id @default(autoincrement())
  email  String   @unique
  role   UserRole              // Enum field
  status UserStatus?           // Optional enum field
}

model Order @datasource(pg_main) {
  id       Int          @id
  status   OrderStatus           // Enum field
  priority OrderPriority[]      // Enum array field (if supported by database)
}

Key Features:

• Enums are emitted as TypeScript string union types
• Full IntelliSense support in queries and mutations
• Values should be ALL_CAPS (recommended convention)
• Enum names should be PascalCase
• Can be used with optional (?) and array ([]) modifiers
• Integrated with StringFilter for querying

Type Generation:

// Generated TypeScript types:
export type UserRole = 'SUPER_ADMIN' | 'ADMIN' | 'MODERATOR' | 'USER' | 'GUEST';
export type OrderStatus = 'PENDING' | 'PROCESSING' | 'SHIPPED' | 'DELIVERED' | 'CANCELLED';

// Used in model types:
export type User = {
  id: number;
  email: string;
  role: UserRole;
  status?: UserStatus | null;
};

Query Examples:

// Type-safe enum filtering
const admins = await db.User.findMany({
  where: { role: { eq: 'ADMIN' } }  // IntelliSense shows available enum values
});

// Enum array filtering
const orders = await db.Order.findMany({
  where: { 
    status: { in: ['PENDING', 'PROCESSING'] }  // Type-safe array of enum values
  }
});

// Create with enum values
const user = await db.User.create({
  data: {
    email: '[email protected]',
    role: 'SUPER_ADMIN'  // IntelliSense autocomplete
  }
});

Models

model User @datasource(pg_main) {
  id          Int      @id @default(autoincrement())
  email       String   @unique
  name        String?
  role        UserRole              // Enum field
  createdAt   DateTime @default(now())
  displayName String   @computed(resolver: "./resolvers/user.displayName.ts", async: false)
  
  lead Lead? @relation(fields: [email], references: [email], strategy: "lookup")
}

HTTP Endpoints

model Lead @datasource(crm_api) {
  id     String @id
  email  String
  status String
  
  @endpoint(findMany: {
    method: "GET",
    path: "/leads",
    query: { status: "$where.status.eq?", offset: "$skip", limit: "$take" },
    response: { items: "$.data.items", total: "$.data.total" }
  })
  
  @endpoint(findManyBulkById: {
    method: "POST",
    path: "/leads/bulk",
    body: { ids: "$where.id.in" },
    response: { items: "$.data" }
  })
}

Query Examples

Basic Query

const users = await orm.findMany('User', {
  where: { email: { contains: '@acme.com' } },
  take: 100
});

Cross-Datasource Join

const users = await orm.findMany('User', {
  where: { email: { contains: '@acme.com' } },
  include: {
    lead: {
      where: { status: 'new' },
      select: { id: true, status: true }
    }
  }
});

Strict Mode

const users = await orm.findMany('User', {
  include: { lead: true },
  $options: {
    strict: true,
    limits: { maxFanOut: 1000 }
  }
});

OAuth Hooks

Create an OAuth hook script (see auth/crm_oauth.ts):

import type { OAuthContext, OAuthResult } from '../src/types/adapter';

export default async function auth(ctx: OAuthContext): Promise<OAuthResult> {
  const accessToken = await getAccessToken();
  
  return {
    headers: { Authorization: `Bearer ${accessToken}` },
    expiresAt: Date.now() + 3600000,
    refresh: async (reason) => {
      const newToken = await refreshToken();
      return {
        headers: { Authorization: `Bearer ${newToken}` },
        expiresAt: Date.now() + 3600000
      };
    }
  };
}

Computed Fields

Create a resolver (see resolvers/user.displayName.ts):

import type { ComputedContext } from '../src/types/adapter';

// Synchronous computed field
export default function displayName(ctx: ComputedContext): string {
  const { row } = ctx;
  return row.name || row.email.split('@')[0];
}

Async computed fields with I/O:

// In your schema:
model User {
  id Int @id
  email String
  enrichedData Json @computed(
    resolver: "./resolvers/user.enrichedData.ts",
    async: true,
    io: true,
    timeout: 5000,
    cache: true
  )
}

// In resolvers/user.enrichedData.ts:
export default async function enrichedData(ctx: ComputedContext): Promise<any> {
  const response = await fetch(`https://api.example.com/enrich?email=${ctx.row.email}`);
  return response.json();
}

VS Code Extension

🎨 Syntax highlighting, IntelliSense, and validation for .qts files!

Features

• Syntax Highlighting: Full color coding for keywords, types, attributes
• IntelliSense: Smart autocomplete for models, fields, datasources, enums
• Code Snippets: 20+ snippets for rapid schema development
• Real-time Validation: Instant error detection and warnings
• Navigation: Go to definition, hover docs, document outline

Installation

cd vscode-extension
npm install
npm run compile

Then press F5 in VS Code to launch the Extension Development Host.

See: vscode-extension/QUICKSTART.md for full documentation

Build & Test

# Build
npm run build

# Run tests
npm test

# Watch mode
npm run dev

Adapters

PostgreSQL Adapter

• Uses pg library with connection pooling
• Snake_case naming convention
• Supports all PostgreSQL operators
• Max IN clause: 65,000 parameters
• Full ACID transaction support with isolation levels
• Configurable pool size (default: max 10, min 2)

MySQL Adapter

• Uses mysql2 library with connection pooling
• Snake_case naming convention
• Supports all MySQL operators
• Max IN clause: 65,000 parameters
• Full ACID transaction support with isolation levels (READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE)
• Configurable pool size (default: limit 10)
• Connection via connection strings or config object

MongoDB Adapter

• Uses mongodb native driver with connection pooling
• Lowercase collection names (e.g., "users", "orderitems")
• Supports MongoDB query operators
• Max array size: 100,000+ elements
• Full ACID transaction support (requires replica set)
• Configurable pool size (default: max 10, min 0)
• Automatic _id to id conversion for consistency with SQL adapters

MS SQL Server Adapter

• Uses odbc library (MSSQL Native) with connection pooling
• PascalCase naming convention
• Supports T-SQL operators and filters
• Max IN clause: 2,100 parameters (SQL Server limit)
• Uses OUTPUT INSERTED.* / OUTPUT DELETED.*
• Full ACID transaction support with isolation levels
• Windows Authentication and SQL Server Authentication
• Configurable pool size (default: max 10, min 2)
• Connection via standard connection strings or ODBC DSN

HTTP API Adapter

• Uses axios for requests
• Configurable endpoint mappings
• OAuth 2.0 with refresh token support
• Automatic retry on 401
• Bulk endpoint detection
• Rate limiting and backoff

Architecture

1. DSL Parser (src/parser/) - Parses .schema files into IR
2. IR (src/types/ir.ts) - Intermediate representation (JSON)
3. Adapters (src/adapters/) - Database/API specific implementations
   • PostgreSQL (postgres.ts)
   • MS SQL Server (mssql.ts)
   • HTTP APIs (http.ts)
4. Stitcher (src/runtime/stitcher.ts) - Cross-datasource join engine
5. Type Generator (planned) - Generate TypeScript client types

Performance Benchmarks

Test Environment: Intel Core i7, 16GB RAM, SQL Server 2022 (localhost)

| Test | Performance | Notes | |------|-------------|-------| | Query (no cache) | 439 queries/sec | 2.27ms avg | | Query (with cache) | 4,545 queries/sec | 0.21ms avg, 10.8x speedup | | 50 Concurrent Queries | 213 queries/sec | Connection pooling | | Cross-Datasource | 32 queries/sec | Includes HTTP API calls | | Nested Includes (3 levels) | 1,667 queries/sec | Smart bulk fetching | | Write Operations | 472 creates/sec | With automatic cache invalidation | | Transactions | 188 tx/sec | 5.23ms avg with ACID guarantees | | Count Operations | 962 counts/sec | Optimized COUNT queries | | 200 Concurrent Users | 226 req/sec | 100% success rate | | Memory Usage | 7.98 MB | For 500 cached queries |

Key Findings:

• ✅ Cache provides 10.8x speedup with 99% hit rate
• ✅ Handles 200 concurrent users with zero failures
• ✅ 472 writes/second throughput
• ✅ 188 transactions/second with full ACID compliance
• ✅ Memory-efficient: only 7.98 MB for 500 queries

See: ARCHITECTURE_PERFORMANCE_GUIDE.md for detailed benchmarks

Roadmap

Completed ✅

• [x] DSL parser with error reporting
• [x] IR validation
• [x] PostgreSQL adapter with connection pooling
• [x] MySQL adapter with connection pooling
• [x] MongoDB adapter with connection pooling
• [x] MS SQL Server adapter (Windows Auth + SQL Auth)
• [x] HTTP API adapter with OAuth 2.0
• [x] OAuth hook runner with refresh tokens
• [x] Stitcher with fan-out control
• [x] Type generator with IntelliSense support
• [x] Full CRUD operations (create, read, update, delete)
• [x] Row-level security middleware
• [x] RBAC (Role-Based Access Control)
• [x] Audit logging middleware
• [x] Multi-datasource support (SQL + NoSQL + HTTP)
• [x] Cross-datasource joins
• [x] SQL query logging
• [x] Comprehensive documentation
• [x] Database-level RLS setup scripts
• [x] Computed fields executor (sync/async with timeout and caching)
• [x] Transaction support (ACID with isolation levels for PostgreSQL, MySQL, MongoDB, MSSQL)
• [x] Connection pooling (PostgreSQL, MySQL, MongoDB, MS SQL Server)
• [x] Query result caching (in-memory with TTL/LRU eviction)
• [x] Performance benchmarks (200+ concurrent users, 226 req/sec)
• [x] Enum support (type-safe string unions with IntelliSense)

In Progress 🚧

• [ ] Redis-based distributed caching
• [ ] Deadlock retry with exponential backoff

Planned 📋

• [ ] GraphQL adapter
• [ ] CLI tools (migrate, seed, studio)
• [ ] Schema migrations
• [ ] Visual studio extension
• [ ] Performance profiling tools

License

MIT