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
• 📊 Configurable limits: Prevent over-fetching and fan-out explosions

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}

Documentation

📚 Complete User Guide - Comprehensive guide for using the ORM

Quick Links

• User Guide - Complete documentation for developers
• Hooks & Middleware Guide - Row-level security and middleware
• SQL Logging Guide - Debug SQL queries
• CRUD Guide - CRUD operations 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: { ... } })

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")
}

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
  }
}

Models

model User @datasource(pg_main) {
  id          Int      @id @default(autoincrement())
  email       String   @unique
  name        String?
  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';

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

Build & Test

# Build
npm run build

# Run tests
npm test

# Watch mode
npm run dev

Adapters

PostgreSQL Adapter

• Uses pg library
• Snake_case naming convention
• Supports all PostgreSQL operators
• Max IN clause: 65,000 parameters
• Full transaction support

MS SQL Server Adapter

• Uses tedious library
• PascalCase naming convention
• Supports T-SQL operators and filters
• Max IN clause: 2,100 parameters (SQL Server limit)
• Uses OUTPUT INSERTED.* / OUTPUT DELETED.*
• Connection via standard connection strings

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

Roadmap

Completed ✅

• DSL parser with error reporting
• IR validation
• PostgreSQL adapter with connection pooling
• MS SQL Server adapter (Windows Auth + SQL Auth)
• HTTP API adapter with OAuth 2.0
• OAuth hook runner with refresh tokens
• Stitcher with fan-out control
• Type generator with IntelliSense support
• Full CRUD operations (create, read, update, delete)
• Row-level security middleware
• RBAC (Role-Based Access Control)
• Audit logging middleware
• Multi-datasource support
• Cross-datasource joins
• SQL query logging
• Comprehensive documentation
• Database-level RLS setup scripts

In Progress 🚧

• Computed fields executor
• Transaction support
• Connection pooling for all adapters
• Query result caching

Planned 📋

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

License

MIT