# Orange ORM

Orange ORM is a powerful Object Relational Mapper for Node.js, Bun, and Deno that enables seamless integration with PostgreSQL, MySQL, MS SQL, SQLite, Oracle, SAP ASE, Cloudflare D1, and PGlite databases. It supports both TypeScript and JavaScript (CommonJS and ECMAScript), offering full IntelliSense without code generation. The library implements the Active Record Pattern, providing an expressive syntax for database interactions with rich querying capabilities, relationship management, and optimistic concurrency control.

Orange ORM can be used both server-side and in the browser through Express.js or Hono plugins, which securely proxy database operations without exposing SQL queries or credentials to clients. The library features automatic change tracking, cascade deletes for owned relationships, JSON column support with partial updates, and comprehensive filtering with logical operators (and, or, not, any, all, none). It supports connection pooling, transactions, and various concurrency strategies (optimistic, overwrite, skipOnConflict) for handling concurrent updates.

## Mapping Tables

Define database table mappings using the `map()` method, linking tables and columns to object properties with support for relationships like `hasOne`, `hasMany`, and `references`.

```typescript
import orange from 'orange-orm';

const map = orange.map(x => ({
  customer: x.table('customer').map(({ column }) => ({
    id: column('id').numeric().primary().notNullExceptInsert(),
    name: column('name').string(),
    balance: column('balance').numeric(),
    isActive: column('isActive').boolean(),
  })),

  order: x.table('_order').map(({ column }) => ({
    id: column('id').numeric().primary().notNullExceptInsert(),
    orderDate: column('orderDate').date().notNull(),
    customerId: column('customerId').numeric().notNullExceptInsert(),
  })),

  orderLine: x.table('orderLine').map(({ column }) => ({
    id: column('id').numeric().primary(),
    orderId: column('orderId').numeric(),
    product: column('product').string(),
    amount: column('amount').numeric(),
  })),

  deliveryAddress: x.table('deliveryAddress').map(({ column }) => ({
    id: column('id').numeric().primary(),
    orderId: column('orderId').numeric(),
    name: column('name').string(),
    street: column('street').string(),
    postalCode: column('postalCode').string(),
    postalPlace: column('postalPlace').string(),
    countryCode: column('countryCode').string().enum(['NO', 'SE', 'DK', 'FI', 'IS']),
  }))

})).map(x => ({
  orderLine: x.orderLine.map(({ hasMany }) => ({
    packages: hasMany(x.package).by('lineId')
  })),
  order: x.order.map(({ hasOne, hasMany, references }) => ({
    customer: references(x.customer).by('customerId'),
    deliveryAddress: hasOne(x.deliveryAddress).by('orderId'),
    lines: hasMany(x.orderLine).by('orderId')
  }))
}));

export default map;
```

## Connecting to SQLite

Connect to SQLite databases with optional connection pooling. Node.js 22+, Bun, and Deno have built-in SQLite support; older Node.js versions require the `sqlite3` package.

```javascript
import map from './map';

// Simple connection
const db = map.sqlite('demo.db');

// With connection pool
const dbPool = map.sqlite('demo.db', { size: 10 });

// Use the database
const customers = await db.customer.getMany();

// Close connection (important for serverless)
await db.close();
```

## Connecting to PostgreSQL

Connect to PostgreSQL databases using connection strings. Bun has built-in support; Node.js requires the `pg` package.

```javascript
import map from './map';

// Basic connection
const db = map.postgres('postgres://user:password@localhost/mydb');

// With schema
const dbWithSchema = map.postgres('postgres://user:password@localhost/mydb?search_path=custom');

// Query data
const orders = await db.order.getMany({ customer: true, lines: true });
```

## Connecting to MySQL

Connect to MySQL databases using connection strings. Requires the `mysql2` package.

```javascript
import map from './map';

const db = map.mysql('mysql://user:password@localhost/mydb');

const customers = await db.customer.getMany({
  where: x => x.isActive.eq(true)
});
```

## Connecting to MS SQL

Connect to Microsoft SQL Server using either tedious configuration objects or connection strings.

```javascript
import map from './map';

const db = map.mssql({
  server: 'mssql',
  options: {
    encrypt: false,
    database: 'test'
  },
  authentication: {
    type: 'default',
    options: {
      userName: 'sa',
      password: 'P@ssword123',
    }
  }
});

const orders = await db.order.getMany();
```

## Connecting to Cloudflare D1

Use Orange ORM with Cloudflare D1 serverless databases in Workers.

```typescript
import map from './map';

export interface Env {
  DB: D1Database;
}

export default {
  async fetch(request, env): Promise<Response> {
    const db = map.d1(env.DB);
    const customers = await db.customer.getMany();
    return Response.json(customers);
  },
} satisfies ExportedHandler<Env>;
```

## Connecting via HTTP (Browser)

Use Orange ORM in the browser by connecting to an Express or Hono server endpoint that proxies database operations securely.

```typescript
import map from './map';

const db = map.http('http://localhost:3000/orange');

const orders = await db.order.getMany({
  where: x => x.customer.name.startsWith('Harry'),
  lines: true
});
```

## Inserting Rows

Insert single or multiple rows with automatic handling of auto-incremental keys and relationship data.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Insert single row
const george = await db.customer.insert({
  name: 'George',
  balance: 177,
  isActive: true
});

// Insert multiple rows with relations
const orders = await db.order.insert([
  {
    orderDate: new Date(2022, 0, 11, 9, 24, 47),
    customer: george,
    deliveryAddress: {
      name: 'George',
      street: 'Node street 1',
      postalCode: '7059',
      postalPlace: 'Jakobsli',
      countryCode: 'NO'
    },
    lines: [
      { product: 'Bicycle', amount: 250 },
      { product: 'Small guitar', amount: 150 }
    ]
  }
], { customer: true, deliveryAddress: true, lines: true });
```

## Fetching Rows with getMany

Retrieve multiple rows with optional filtering, pagination, ordering, and related data fetching.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Fetch all orders with relations
const orders = await db.order.getMany({
  customer: true,
  deliveryAddress: true,
  lines: { packages: true }
});

// With pagination and sorting
const paginatedOrders = await db.order.getMany({
  offset: 10,
  limit: 20,
  orderBy: ['orderDate desc', 'id'],
  customer: true,
  lines: { orderBy: 'product' }
});

// With aggregates
const ordersWithStats = await db.order.getMany({
  numberOfLines: x => x.count(x => x.lines.id),
  totalAmount: x => x.sum(x => x.lines.amount),
  customerName: x => x.customer.name
});
```

## Fetching Single Row by ID

Retrieve a single row by its primary key with optional related data.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Get by primary key
const order = await db.order.getById(1, {
  customer: true,
  deliveryAddress: true,
  lines: true
});

// Try get (returns null if not found)
const maybeOrder = await db.order.tryGetById(999);
```

## Fetching with getOne

Retrieve a single row matching filter criteria.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

const order = await db.order.getOne({
  where: x => x.customer(customer =>
    customer.isActive.eq(true).and(customer.name.startsWith('Harry'))
  ),
  customer: true,
  deliveryAddress: true,
  lines: true
});
```

## Filtering with Basic Operators

Apply filters using comparison operators like equal, contains, startsWith, greaterThan, between, and in.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Equal
const harry = await db.customer.getMany({
  where: x => x.name.equal('Harry')
});

// Contains
const withArr = await db.customer.getMany({
  where: x => x.name.contains('arr')
});

// Starts with
const startsH = await db.customer.getMany({
  where: x => x.name.startsWith('H')
});

// Greater than with dates
const recentOrders = await db.order.getMany({
  where: x => x.orderDate.greaterThan('2023-01-01')
});

// Between
const dateRange = await db.order.getMany({
  where: x => x.orderDate.between('2023-01-01', '2024-01-01')
});

// In list
const specific = await db.customer.getMany({
  where: x => x.name.in(['George', 'Harry'])
});
```

## Filtering with Logical Operators

Combine filters using and, or, and not operators for complex queries.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// AND
const filtered = await db.order.getMany({
  where: x => x.customer.name.equal('Harry')
    .and(x.orderDate.greaterThan('2023-01-01'))
});

// OR
const eitherCustomer = await db.order.getMany({
  where: y => y.customer(x =>
    x.name.equal('George').or(x.name.equal('Harry'))
  )
});

// NOT
const notGeorgeOrHarry = await db.order.getMany({
  where: y => y.customer(x =>
    x.name.equal('George').or(x.name.equal('Harry'))
  ).not()
});

// EXISTS
const withAddress = await db.order.getMany({
  where: x => x.deliveryAddress.exists()
});
```

## Filtering Relations with any, all, none

Filter based on related collections using any, all, and none operators.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Any - at least one line contains 'guitar'
const withGuitar = await db.order.getMany({
  where: x => x.lines.any(line => line.product.contains('guitar'))
});

// All - every line contains 'a'
const allContainA = await db.order.getMany({
  where: x => x.lines.all(line => line.product.contains('a'))
});

// None - no lines with 'Magic wand'
const noMagicWand = await db.order.getMany({
  where: x => x.lines.none(line => line.product.equal('Magic wand'))
});

// Count filter
const fewLines = await db.order.getMany({
  where: x => x.lines.count().le(2)
    .and(x.lines.count(line => line.product.contains('guitar')).eq(1))
});
```

## Updating Rows

Modify rows and save changes using the Active Record pattern with automatic change tracking.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Update single row
const order = await db.order.getById(1, {
  customer: true,
  deliveryAddress: true,
  lines: true
});

order.orderDate = new Date();
order.deliveryAddress = null;
order.lines.push({ product: 'Cloak of invisibility', amount: 600 });

await order.saveChanges();

// Update many rows
const orders = await db.order.getMany({
  orderBy: 'id',
  lines: true,
  deliveryAddress: true
});

orders[0].orderDate = new Date();
orders[0].deliveryAddress.street = 'Node street 2';
orders[0].lines[1].product = 'Big guitar';

await orders.saveChanges();
```

## Selective Updates

Use the update method to modify specific columns across rows matching a filter.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

const propsToModify = {
  orderDate: new Date(),
  customerId: 2,
  lines: [
    { id: 1, product: 'Bicycle', amount: 250 },
    { id: 2, product: 'Small guitar', amount: 150 },
    { product: 'Piano', amount: 800 } // new line
  ]
};

const strategy = { customer: true, deliveryAddress: true, lines: true };
const orders = await db.order.update(
  propsToModify,
  { where: x => x.id.eq(1) },
  strategy
);
```

## Replace and updateChanges

Replace entire rows or apply partial updates based on differences between original and modified objects.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Replace entire row
const modified = {
  id: 1,
  orderDate: '2023-07-14T12:00:00',
  customer: { id: 2 },
  deliveryAddress: {
    name: 'Roger',
    street: 'Node street 1',
    postalCode: '7059',
    postalPlace: 'Jakobsli',
    countryCode: 'NO'
  },
  lines: [
    { id: 1, product: 'Bicycle', amount: 250 },
    { product: 'Piano', amount: 800 }
  ]
};

const order = await db.order.replace(modified, {
  customer: true, deliveryAddress: true, lines: true
});

// Partial update from JSON diff
const original = { id: 1, orderDate: '2023-07-14', deliveryAddress: { id: 1, name: 'George' } };
const modifiedCopy = JSON.parse(JSON.stringify(original));
modifiedCopy.deliveryAddress.name = 'Roger';

await db.order.updateChanges(modifiedCopy, original, { deliveryAddress: true });
```

## Deleting Rows

Delete single rows, multiple rows, or perform batch deletes with optional cascade.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Delete single row (cascades to owned relations)
const order = await db.order.getById(1);
await order.delete();

// Delete from array
const orders = await db.order.getMany({ lines: true });
orders.splice(1, 1); // Remove second order
await orders.saveChanges();

// Batch delete with filter
const filter = db.order.deliveryAddress.name.eq('George');
await db.order.delete(filter);

// Batch delete with cascade
await db.order.deleteCascade(filter);
```

## Transactions

Execute multiple operations atomically within a transaction with automatic rollback on errors.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

await db.transaction(async tx => {
  const customer = await tx.customer.getById(1);
  customer.balance = 100;
  await customer.saveChanges();

  const order = await tx.order.insert({
    orderDate: new Date(),
    customerId: customer.id,
    lines: [{ product: 'New item', amount: 50 }]
  });

  // Throwing an error rolls back all changes
  // throw new Error('This will rollback');
});
```

## Aggregation Functions

Use count, sum, min, max, and avg on rows or across relations.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Aggregates on each row
const ordersWithStats = await db.order.getMany({
  numberOfLines: x => x.count(x => x.lines.id),
  totalAmount: x => x.sum(x => x.lines.amount),
  balance: x => x.customer.balance
});

// Aggregates across all rows (GROUP BY)
const byCustomer = await db.order.aggregate({
  where: x => x.orderDate.greaterThan(new Date(2022, 0, 1)),
  customerId: x => x.customerId,
  customerName: x => x.customer.name,
  orderCount: x => x.count(x => x.id),
  totalAmount: x => x.sum(x => x.lines.amount)
});

// Simple count
const orderCount = await db.order.count(
  db.order.lines.any(line => line.product.contains('broomstick'))
);
```

## Concurrency Strategies

Handle concurrent updates with optimistic locking, overwrite, or skipOnConflict strategies.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

// Configure concurrency per column
const order = await db.order.getById(1, { lines: true });
order.orderDate = new Date();
order.lines.push({ product: 'Cloak', amount: 600 });

await order.saveChanges({
  orderDate: { concurrency: 'overwrite' }
});

// Upsert with overwrite strategy
const db2 = db({
  vendor: {
    balance: { concurrency: 'skipOnConflict' },
    concurrency: 'overwrite'
  }
});

await db2.vendor.insert({
  id: 1,
  name: 'George',
  balance: 177,
  isActive: false
}); // Overwrites all but balance
```

## Express.js Server Integration

Expose database tables through Express.js middleware for browser clients.

```typescript
import map from './map';
import { json } from 'body-parser';
import express from 'express';
import cors from 'cors';

const db = map.sqlite('demo.db');

express().disable('x-powered-by')
  .use(json({ limit: '100mb' }))
  .use(cors())
  .use('/orange', db.express())
  .listen(3000, () => console.log('Server running on port 3000'));

// With base filter for row-level security
express()
  .use('/orange', db.express({
    order: {
      baseFilter: (db, req, res) => {
        const customerId = Number.parseInt(req.headers.authorization.split(' ')[1]);
        return db.order.customerId.eq(customerId);
      }
    }
  }))
  .listen(3000);
```

## Hono Server Integration

Expose database tables through Hono middleware for edge deployments.

```typescript
import map from './map';
import { Hono } from 'hono';
import { cors } from 'hono/cors';
import { serve } from '@hono/node-server';

const db = map.sqlite('demo.db');
const app = new Hono();

app.use('/orange', cors());
app.use('/orange/*', cors());
app.all('/orange', db.hono());
app.all('/orange/*', db.hono());

serve({ fetch: app.fetch, port: 3000 });
```

## Raw SQL Queries

Execute raw SQL queries directly when ORM abstractions are insufficient.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

const query = {
  sql: 'SELECT * FROM customer WHERE name LIKE ?',
  parameters: ['%arry']
};

const rows = await db.query(query);

// Raw SQL in filters (server-side only, blocked from HTTP clients)
const rawFilter = {
  sql: 'name LIKE ?',
  parameters: ['%arry']
};

const customers = await db.customer.getMany({
  where: x => x.balance.greaterThan(100).and(rawFilter)
});
```

## Query Logging

Listen to query events for debugging and monitoring database operations.

```javascript
import orange from 'orange-orm';
import map from './map';

const db = map.sqlite('demo.db');

orange.on('query', (e) => {
  console.log('SQL:', e.sql);
  if (e.parameters.length > 0)
    console.log('Params:', e.parameters);
});

const order = await db.order.getById(2, { lines: true });
order.lines.push({ product: 'broomstick', amount: 300 });
await order.saveChanges();

// Output:
// SQL: select _order.id, _order.orderDate, _order.customerId from _order where _order.id=2
// SQL: select orderLine.id, orderLine.orderId, orderLine.product from orderLine where orderLine.orderId in (2)
// SQL: BEGIN
// SQL: INSERT INTO orderLine (orderId,product,amount) VALUES (2,?,300)
// Params: ['broomstick']
// SQL: COMMIT
```

## Data Types and Validation

Define column types with validation, JSON schemas, and default values.

```typescript
import orange from 'orange-orm';

interface Pet {
  name: string;
  kind: string;
}

const petSchema = {
  properties: {
    name: { type: 'string' },
    kind: { type: 'string' }
  }
};

function validateName(value?: string) {
  if (value && value.length > 10)
    throw new Error('Length cannot exceed 10 characters');
}

const map = orange.map(x => ({
  demo: x.table('demo').map(x => ({
    id: x.column('id').uuid().primary().notNullExceptInsert(),
    name: x.column('name').string().validate(validateName),
    balance: x.column('balance').numeric(),
    discordId: x.column('discordId').bigint(),
    regularDate: x.column('regularDate').date(),
    tzDate: x.column('tzDate').dateWithTimeZone(),
    picture: x.column('picture').binary(),
    pet: x.column('pet').jsonOf<Pet>().JSONSchema(petSchema),
    isActive: x.column('isActive').boolean().default(true),
  }))
}));
```

## Column and Formula Discriminators

Use discriminators to map multiple logical tables to a single physical table based on column values or formulas.

```javascript
import orange from 'orange-orm';

// Column discriminator
const map = orange.map(x => ({
  customer: x.table('client').map(({ column }) => ({
    id: column('id').numeric().primary(),
    name: column('name').string()
  })).columnDiscriminators(`client_type='customer'`),

  vendor: x.table('client').map(({ column }) => ({
    id: column('id').numeric().primary(),
    name: column('name').string()
  })).columnDiscriminators(`client_type='vendor'`),
}));

// Formula discriminator
const bookingMap = orange.map(x => ({
  customerBooking: x.table('booking').map(({ column }) => ({
    id: column('id').uuid().primary(),
    bookingNo: column('booking_no').numeric()
  })).formulaDiscriminators('@this.booking_no between 10000 and 99999'),

  internalBooking: x.table('booking').map(({ column }) => ({
    id: column('id').uuid().primary(),
    bookingNo: column('booking_no').numeric()
  })).formulaDiscriminators('@this.booking_no between 1000 and 9999'),
}));
```

## SQLite User-Defined Functions

Register custom functions for use in SQLite queries.

```javascript
import map from './map';
const db = map.sqlite('demo.db');

await db.function('add_prefix', (text, prefix) => `${prefix}${text}`);

const rows = await db.query(
  "SELECT id, name, add_prefix(name, '[VIP] ') as prefixedName FROM customer"
);

// Within transaction
await db.transaction(async (db) => {
  await db.function('add_prefix', (text, prefix) => `${prefix}${text}`);
  return db.query(
    "SELECT id, name, add_prefix(name, '[VIP] ') as prefixedName FROM customer"
  );
});
```

## Summary

Orange ORM is ideal for applications requiring a type-safe, database-agnostic ORM with support for complex relationships and real-time change tracking. Common use cases include building REST APIs with Express or Hono, creating full-stack applications with browser-side database access, and developing serverless functions on Cloudflare Workers with D1. The Active Record pattern enables intuitive data manipulation where rows behave like objects that can be modified and saved with automatic SQL generation.

Integration patterns typically involve defining a centralized map configuration file that describes all tables and relationships, then importing this map across server and client code. For server applications, connect directly to databases using the appropriate driver method (sqlite, postgres, mysql, mssql, oracle). For browser applications, expose tables through the Express or Hono middleware and connect clients via HTTP. The consistent API across all environments means the same querying and mutation code works whether running on the server or in the browser, with the server automatically handling SQL generation and security.