Orange ORM

Relationships are defined in a second .map() call chained after table definitions. They determine how data is fetched and how ownership/deletion behaves.

Relationship Types

• hasMany(targetTable).by('foreignKeyColumn'): One-to-many. The parent owns the children. Deleting the parent triggers a cascade delete of the children. Returns an array.
• hasOne(targetTable).by('foreignKeyColumn'): One-to-one. A special case of hasMany. The parent owns the child (cascade delete). Returns a single object or null.
• references(targetTable).by('foreignKeyColumn'): Many-to-one. The current table holds the foreign key. The target is independent (no cascade delete). Returns a single object or null.

Ownership Rules

• Owned (hasMany/hasOne): Deleting the parent deletes the children. Updating the parent can manage child lifecycle.
• Independent (references): Deleting the referencing row does NOT delete the referenced row. You can set the reference to null to detach it.

// Example: Author and Book (one-to-many)
import orange from 'orange-orm';

const map = orange.map(x => ({
  author: x.table('author').map(({ column }) => ({
    id: column('id').numeric().primary().notNullExceptInsert(),
    name: column('name').string().notNull(),
  })),

  book: x.table('book').map(({ column }) => ({
    id: column('id').numeric().primary().notNullExceptInsert(),
    authorId: column('authorId').numeric().notNull(),
    title: column('title').string().notNull(),
    year: column('year').numeric(),
  }))
})).map(x => ({
  author: x.author.map(({ hasMany }) => ({
    books: hasMany(x.book).by('authorId')
  })),
  book: x.book.map(({ references }) => ({
    author: references(x.author).by('authorId')
  }))
}));

You can perform 'upserts' (insert or update on conflict) by configuring the concurrency strategy. If a row with the same primary key is inserted, the overwrite strategy will update the existing record instead of failing.

You can set the strategy for an entire table or for specific columns. This allows for granular control, such as overwriting most fields but using skipOnConflict for a specific sensitive field like a balance.

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

async function insertRows() {
  // Configure table-level and column-level concurrency
  const db2 = db({
    vendor: {
      balance: {
        concurrency: 'skipOnConflict'
      },
      concurrency: 'overwrite'
    }
  });

  // First insert
  await db2.vendor.insert({
    id: 1,
    name: 'John',
    balance: 100,
    isActive: true
  });

  // This will overwrite name and isActive, but skip updating balance because of 'skipOnConflict'
  const george = await db2.vendor.insert({
    id: 1,
    name: 'George',
    balance: 177,
    isActive: false
  });
  
  console.dir(george, {depth: Infinity});
  // Output: { id: 1, name: 'George', balance: 100, isActive: false }
}

Orange allows you to control the depth and breadth of data retrieved to optimize performance. By default, it fetches all columns for the requested entity without any relations.

• Include a relation: Pass the relation name as a boolean true in the options object to fetch all columns of that relation.
• Include a subset of columns: Pass an object to the relation name specifying which columns to include.
• Exclude columns: Pass false to a column name to explicitly exclude it.

Note: If you provide a mix of explicitly included and excluded columns for a table (or relation), all other columns not mentioned will be excluded.

// Fetching entire relations
const rows = await db.order.getMany({
  deliveryAddress: true 
});

// Fetching specific columns from a relation and excluding others
const rows = await db.order.getMany({
  orderDate: false, // Exclude this column
  deliveryAddress: {
    countryCode: true, // Only include these
    name: true
  } 
});

Formula discriminators distinguish data subsets using logical expressions rather than static column values. They are used during fetch and delete operations but not during inserts (as they rely on existing data).

Use the '@this' placeholder within the formula to represent the table alias. This prevents ambiguity during complex joins.

Example: Categorizing bookings based on a numeric range in the booking_no column.

import orange from 'orange-orm';

const map = 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'),
}));

Orange ORM uses optimistic concurrency by default. If a row is modified by another process between your fetch and your save, an exception is thrown.

Concurrency Strategies

1. optimistic (Default): Throws an error if the row was changed by another user.
2. overwrite: Overwrites the database regardless of interim changes.
3. skipOnConflict: Silently skips the update if the row has been modified.

Configuration

You can set the strategy per-column during a save, or globally at the table level during database initialization.

// Set concurrency per-column on saveChanges
const order = await db.order.getById(1);
order.orderDate = new Date();
await order.saveChanges({
  orderDate: { concurrency: 'overwrite' }
});

// Set concurrency at the table level
const db2 = db({
  vendor: {
    balance: { concurrency: 'skipOnConflict' },
    concurrency: 'overwrite'
  }
});

You can secure your API by combining client-side interceptors with server-side baseFilter and middleware.

1. Client-side: Use db.interceptors.request.use to attach credentials (e.g., an Authorization header).
2. Server-side: Use standard middleware (like validateToken) to verify the identity, then use baseFilter within the adapter configuration to restrict the data the client can access.

Setting a baseFilter on a specific table ensures that all incoming requests for that table are automatically scoped (e.g., by customerId). If you want to expose a table without any filtering, set its tableName to an empty object in the configuration.

// Server-side: Applying a baseFilter to restrict access to a specific customer
.use('/orange', db.express({
  order: {
    baseFilter: (db, req, _res) => {
      const customerId = Number.parseInt(req.headers.authorization.split(' ')[1]);
      return db.order.customerId.eq(Number.parseInt(customerId));
    }
  }
}))

// Client-side: Adding the Authorization header via interceptors
db.interceptors.request.use((config) => {
    config.headers.Authorization = 'Bearer 2';
    return config;
});

Column discriminators allow you to treat different subsets of a single table as distinct entities in your mapping. You use .columnDiscriminators(expression) to define a rule (e.g., client_type='customer').

When using discriminators:

• Inserts: The column will automatically be assigned the correct discriminator value.
• Fetch/Delete: The discriminator expression is automatically added to the WHERE clause to ensure you only interact with the intended subset.

import orange from 'orange-orm';

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'`),
}));

These synchronous methods allow you to control the change-tracking baseline for individual rows or arrays.

acceptChanges()

Marks the current in-memory state as the new "original" baseline. Subsequent calls to saveChanges() will only persist changes made after this call.

• Use case: You want to skip persisting certain modifications or reset the baseline after custom logic.

clearChanges()

Reverts the row or array to its last accepted/original state. It undoes all in-memory mutations since the last acceptChanges() or since the data was fetched.

• Use case: Reverting an edit form without re-fetching from the database.

Relationship to other methods

• saveChanges() calls acceptChanges() internally upon success.
• refresh() reloads data and then calls acceptChanges().
• clearChanges() is purely in-memory and does not hit the database.

const product = await db.product.getById(1);
product.name = 'New name';
product.price = 999;

// Instead of saving, accept the changes as the new baseline
product.acceptChanges();

// Now modifying only price:
product.price = 500;
await product.saveChanges(); // Only sends price=500 to the DB

Orange ORM uses an optimistic concurrency approach by default. If a property being edited was modified by another user, an exception is raised. You can customize this behavior at the table or column level using one of three strategies:

• optimistic: (Default) Raises an exception if the property was changed by another user during the edit.
• overwrite: Overwrites the property regardless of any interim changes made by others.
• skipOnConflict: Silently avoids updating the property if it has been modified by another user.

Example of setting a specific column to overwrite during a save operation:

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

async function update() {
  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({
    orderDate: {
      concurrency: 'overwrite'
    }
  });
}

Orange ORM uses a functional mapping approach to define tables, columns, and relationships. You use orange.map() to define the schema structure and a second .map() call to define the relationships (like hasMany, hasOne, or references) between those tables. This approach provides full IntelliSense without requiring a code generation step.

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(),
  })),

  // ... other tables
})).map(x => ({
  order: x.order.map(v => ({
    customer: v.references(x.customer).by('customerId'),
    lines: v.hasMany(x.orderLine).by('orderId'),
    deliveryAddress: v.hasOne(x.deliveryAddress).by('orderId'),
  })),
}));

export default map;

You can delete rows individually, in batches, or via filters.

• Single row: Call .delete() on a fetched instance.
• Array elements: Remove an item from an array (e.g., using .splice()) and call .saveChanges() on the array to persist the deletion.
• Filtered/Batch delete: Use .delete(filter) on a table to delete all rows matching a condition.
• Cascade delete: Use .deleteCascade(filter) to delete rows and automatically remove their associated children (hasOne/hasMany).
• Primary key delete: Pass an array of objects containing primary keys to .delete() to remove specific rows.

// Delete a single row
const product = await db.product.getById(1);
await product.delete();

// Delete an element from an array then save
const orders = await db.order.getMany({ lines: true });
orders.splice(1, 1);            // remove second order
await orders.saveChanges();      // persists the deletion

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

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

// Batch delete by primary key
await db.customer.delete([{ id: 1 }, { id: 2 }]);