Orange ORM

https://github.com/alfateam/orange-orm
Admin
Orange ORM is a database-agnostic Object Relational Mapper for Node.js, Bun, and Deno that supports...
Tokens:125,022
Snippets:855
Trust Score:7.5
License:ISC
Update:2 weeks ago
Show doc for...
Tokens:
Raw
### PostgreSQL: install pg package

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Install the pg package for PostgreSQL support. Bun has built-in PostgreSQL support, but this install is shown for compatibility.

```bash
npm install pg
```

--------------------------------

### Example: server-side table usage

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/04-table-definition.md

Example of defining tables and inserting data using orange-orm.

```APIDOC
## Example: server-side table usage

### Description
Example of defining tables and inserting data using orange-orm.

### Code Example
```js
const rdb = require('orange-orm');
const db = rdb.postgres('postgres://user:pass@localhost/db');

const order = rdb.table('_order');
order.primaryColumn('id').numeric().notNullExceptInsert().as('id');
order.column('orderDate').date().notNull();
order.column('customerId').numeric();

const customer = rdb.table('customer');
customer.primaryColumn('id').numeric();
customer.column('name').string();
order.join(customer).by('customerId').as('customer');

await order.insert(db, { orderDate: new Date(), customerId: 1 });
```

### Notes
Server-side `insert` is context-first — `db` passed as the first argument works because provider `db` objects double as session contexts.
```

--------------------------------

### SAP Adaptive Server: install msnodesqlv8

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Install the msnodesqlv8 package for SAP Adaptive Server (also used for MS SQL).

```bash
npm install msnodesqlv8
```

--------------------------------

### PGlite: install @electric-sql/pglite

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Install the @electric-sql/pglite package for PGlite, an in-memory Postgres implementation

```bash
npm install @electric-sql/pglite
```

--------------------------------

### Install Orange ORM with npm

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Install the Orange ORM package using npm. This is the first step to use the library.

```bash
npm install orange-orm
```

--------------------------------

### Oracle: install oracledb

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Install the oracledb package for Oracle database connectivity.

```bash
npm install oracledb
```

--------------------------------

### Complete example: filter + order + limit

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Fetches products with price greater than 50, ordered by name, limited to 10 with no offset.

```TypeScript
const products = await db.product.getMany({
  where: x => x.price.greaterThan(50),
  orderBy: 'name',
  limit: 10,
  offset: 0
});
```

--------------------------------

### Express server setup

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Sets up an Express server with CORS and JSON body parsing, mounting the Orange database adapter at '/orange' and listening on port 3000.

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

--------------------------------

### MySQL connection: npm install mysql2 and map.mysql

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Connecting to MySQL with Orange ORM. Requires the `mysql2` package (`npm install mysql2`).

```bash
$ npm install mysql2
```

```javascript
import map from './map';
const db = map.mysql('mysql://test:test@mysql/test');
```

--------------------------------

### MS SQL connection: npm install tedious and map.mssql

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Connecting to MS SQL Server with Orange ORM. Requires the `tedious` package (`npm install tedious`).

```bash
npm install tedious
```

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

--------------------------------

### Postgres row-level security setup with tenant isolation

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Postgres RLS setup: creates a role, table, enables RLS, and adds a policy filtering by current_setting('app.tenant_id'). Seeds demo data (two tenants). For production, validate tenant id from trusted source, not client input.

```sql
create role rls_app_user nologin;

create table tenant_data (
  id serial primary key,
  tenant_id int not null,
  value text not null
);

alter table tenant_data enable row level security;
create policy tenant_data_tenant on tenant_data
  using (tenant_id = current_setting('app.tenant_id', true)::int);

grant select, insert, update, delete on tenant_data to rls_app_user;

insert into tenant_data (tenant_id, value) values
  (1, 'alpha'),
  (1, 'beta'),
  (2, 'gamma');
```

--------------------------------

### Hono browser client usage

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Browser client setup for the Hono adapter, using map.http() and the same operations as the Express example; can be reused by replacing db.express() with db.hono() on the server.

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

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

updateRows();

async function updateRows() {
  const order = await db.order.getOne({
    where: x => x.lines.any(line => line.product.startsWith('Magic wand'))
      .and(x.customer.name.startsWith('Harry'),
    lines: true
  });
  
  order.lines.push({
    product: 'broomstick',
    amount: 300,
  });

  await order.saveChanges();
}


```

--------------------------------

### Hono server setup

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Sets up a Hono server with CORS, mounting the Orange database adapter at '/orange' and serving on port 3000.

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

--------------------------------

### MariaDB connection: npm install mysql2 and map.mariadb

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Connecting to MariaDB with Orange ORM. Requires the `mysql2` package (`npm install mysql2`).

```bash
$ npm install mysql2
```

```javascript
import map from './map';
const db = map.mariadb('mariadb://test:test@mariadb/test');
```

--------------------------------

### Express host example

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/08-http-hosting.md

Sets up an Express app with orange-orm, mapping a customer table and exposing it via the `/api` route. Uses `db.express` with a PostgreSQL connection, `readonly: false`, and an `afterCommit` hook that logs the request URL.

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

const db = orange.map(x => ({
  customer: x.table('customer').map(({ column }) => ({
    id: column('id').numeric().primary(),
    name: column('name').string(),
  })),
}));

const app = express();
app.use(express.json());
app.use('/api', db.express({
  db: orange.postgres('postgres://user:pass@localhost/db'),
  readonly: false,
  hooks: {
    afterCommit: async (client, req, res) => console.log('committed', req.url),
  },
}));
app.listen(3000);
```

--------------------------------

### connectViaPool function implementation (src/index.js)

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/01-entry-point.md

The main entry point function that routes connection strings starting with 'mariadb', 'mysql', or 'postgres' to the corresponding provider, otherwise falling back to a generic client. This is the core of the module's root export.

```javascript
// src/index.js, line 19
var connectViaPool = function(connectionString) {
	if (connectionString.indexOf && connectionString.indexOf('mariadb') === 0)
		return connectViaPool.mariadb.apply(null, arguments);
	else if (connectionString.indexOf && connectionString.indexOf('mysql') === 0)
		return connectViaPool.mySql.apply(null, arguments);
	else if (connectionString.indexOf && connectionString.indexOf('postgres') === 0)
		connectViaPool.pg.apply(null, arguments);
	else
		return client.apply(null, arguments);
};
```

--------------------------------

### Composite Filter Example

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/06-filters.md

Demonstrates how to use a composite filter with logical operators (and/or) and ordering/limit options when querying records.

```APIDOC
## Composite Filter

### Description
Use a composite filter to query records with multiple conditions combined using logical operators (and/or), along with ordering and limit.

### Method
GET (conceptual - query operation)

### Endpoint
`client.order.getMany`

### Parameters
#### Query Parameters
- **where** (object) - Required - Filter conditions. Supports chaining methods like `between`, `startsWith`, `gt`, and logical operators `.and()` and `.or()`.
- **orderBy** (string) - Optional - Column name and direction, e.g., `'total desc'`.
- **limit** (number) - Optional - Maximum number of records to return.

### Request Example
```ts
const rows = await client.order.getMany({
  where: c =>
    c.total.between(100, 1000)
      .and(c.customer.name.startsWith('Harry'))
      .or(c.customer.balance.gt(5000)),
  orderBy: 'total desc',
  limit: 10,
});
```

### Response
#### Success Response (200)
- **rows** (array) - List of order records matching the filter.

#### Response Example
```json
[
  {
    "id": 1,
    "total": 500,
    "customerId": 1
  }
]
```
```

--------------------------------

### Express server setup with RLS and token validation

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Sets up an Express server with a PostgreSQL mapped database, row-level security (RLS) via afterBegin hook, and a demo token validation middleware. The db.express() middleware handles routes under /orange; hooks set the RLS role and tenant ID for each transaction.

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

const db = map.postgres('postgres://postgres:postgres@localhost/postgres');

express().disable('x-powered-by')
  .use(json({ limit: '100mb' }))
  .use(cors())
  .use('/orange', validateToken)
  .use('/orange', db.express({
    hooks: {
      transaction: {
        //beforeBegin: async (db, req) => ...,
        afterBegin: async (db, req) => {
          const tenantId = Number.parseInt(String(req.user?.tenantId ?? ''), 10);
          if (!Number.isFinite(tenantId)) throw new Error('Missing tenant id');
          await db.query('set local role rls_app_user');
          await db.query({
            sql: 'select set_config(\'app.tenant_id\', ?, true)',
            parameters: [String(tenantId)]
          });
        },
        //beforeCommit: async (db, req) => ...,
        //afterCommit: async (db, req) => ...,
        // afterRollback: async (db, req, error) => {
        //   console.dir(error);
        // }
      }
    }
  }))
  .listen(3000, () => console.log('Example app listening on port 3000!'));

function validateToken(req, res, next) {
  const authHeader = req.headers.authorization;
  if (!authHeader) return res.status(401).json({ error: 'Authorization header missing' });
  try {
    const token = authHeader.replace(/^Bearer\s+/i, '');
    const payload = decodeFakeJwt(token); // demo-only, do not use in production
    req.user = { tenantId: String(payload.tenantId) };
    return next();
  } catch (error) {
    return res.status(401).json({ error: 'Invalid token' });
  }
}

function decodeFakeJwt(token) {
  // Demo-only format: "tenant:<id>"
  const match = /^tenant:(\d+)$/.exec(token);
  if (!match) throw new Error('Invalid demo token');
  return { tenantId: Number(match[1]) };
}
```

--------------------------------

### get()

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/08-http-hosting.md

Returns the table metadata graph including id, keys, columns, and relations.

```APIDOC
## get()

### Description
Returns the table metadata graph.

### Method
GET (conceptual)

### Endpoint
N/A (local operation)

### Response
Returns metadata object: `{ id, keys: [{name,type}], columns: {<alias>: {serializable}}, relations: <id or meta> }`.
```

--------------------------------

### Log queries with orange-orm query event and save changes

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Enables query logging via the 'query' event, showing SQL and parameters. The example updates an order by adding a line and saving changes, with the expected SQL output displayed.

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

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

updateRow();

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

  await order.saveChanges();
}
```

```bash
select  _order.id as s_order0,_order.orderDate as s_order1,_order.customerId as s_order2 from _order _order where _order.id=2 order by _order.id limit 1
select  orderLine.id as sorderLine0,orderLine.orderId as sorderLine1,orderLine.product as sorderLine2,orderLine.amount as sorderLine3 from orderLine orderLine where orderLine.orderId in (2) order by orderLine.id
BEGIN
select  _order.id as s_order0,_order.orderDate as s_order1,_order.customerId as s_order2 from _order _order where _order.id=2 order by _order.id limit 1
INSERT INTO orderLine (orderId,product,amount) VALUES (2,?,300)
[ 'broomstick' ]
SELECT id,orderId,product,amount FROM orderLine WHERE rowid IN (select last_insert_rowid())
select  orderLine.id as sorderLine0,orderLine.orderId as sorderLine1,orderLine.product as sorderLine2 from orderLine orderLine where orderLine.orderId in (2) order by orderLine.id
COMMIT
```

--------------------------------

### SQLite connection: npm install sqlite3 and db.close()

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Connecting to SQLite with a single client connection. Requires `sqlite3` for Node.js 21 and earlier; built-in for Node.js 22+, Bun, or Deno. The comment emphasizes calling `db.close()` for serverless functions.

```bash
npm install sqlite3
```

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

// IMPORTANT for serverless functions:
await db.close();           // closes the client connection
```

--------------------------------

### Browser usage: server.ts (db.express) and browser.ts (map.http)

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Using Orange from the browser: this pair shows the required server (server.ts) and client (browser.ts) examples. The server uses `db.express()` to expose the Express plugin, while the client connects via `map.http('http://localhost:3000/orange')`. The server file is labeled 📄 server.ts and the client file 📄 browser.ts.

```ts
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())
  //for demonstrational purposes, authentication middleware is not shown here.
  .use('/orange', db.express())
  .listen(3000, () => console.log('Example app listening on port 3000!'));
```

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

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

--------------------------------

### Define multiple tables with orange.map()

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Example of defining four related tables: customer, order, orderLine, and deliveryAddress. Shows how to map multiple tables in a single orange.map() call, with various column types and constraints.

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

export default map;
```

--------------------------------

### Inserting rows: map.sqlite example with fetching strategy

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Insert rows into SQLite tables using the ORM. Uses autoincrement keys, and the second argument to db.order.insert is a fetching strategy to retrieve associated customer, deliveryAddress, and lines.

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

insertRows();

async function insertRows() {
  await init();

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

  const harry = await db.customer.insert({
    name: 'Harry',
    balance: 200,
    isActive: true
  });

  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: harry,
      orderDate: new Date(2021, 0, 11, 12, 22, 45),
      deliveryAddress: {
        name: 'Harry Potter',
        street: '4 Privet Drive, Little Whinging',
        postalCode: 'GU4',
        postalPlace: 'Surrey',
        countryCode: 'UK'
      },
      lines: [
        { product: 'Magic wand', amount: 300 }
      ]
    }
  ], {customer: true, deliveryAddress: true, lines: true}); //fetching strategy
```

--------------------------------

### Starts with

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Filter records where a column starts with a specified prefix.

```APIDOC
## Starts with

### Description
Filters records where the specified column starts with a given prefix.

### Method
SDK Method

### Endpoint
`db.customer.getMany({ where: x => x.name.startsWith('Harr') })`

### Parameters
- **column** (string) - Required - The column to filter on.
- **value** (string) - Required - The prefix to match.

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

async function getRows() {
  const rows = await db.customer.getMany({
    where: x => x.name.startsWith('Harr')
  });
}
```

### Response
Returns an array of matching records.
```

--------------------------------

### Connect to Oracle

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Creates a database client for Oracle using a configuration object with user, password, connectString, and privilege.

```TypeScript
import map from './map';
const db = map.oracle({ user: 'sys', password: 'pass', connectString: 'oracle/XE', privilege: 2 });
```

--------------------------------

### Connect to MS SQL

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Creates a database client for MS SQL using a configuration object with server, options, and authentication.

```TypeScript
import map from './map';
const db = map.mssql({
  server: 'mssql',
  options: { encrypt: false, database: 'test' },
  authentication: {
    type: 'default',
    options: { userName: 'sa', password: 'password' }
  }
});
```

--------------------------------

### Starts with filter: x.name.startsWith('Harr')

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Uses the startsWith filter to fetch customers whose name starts with 'Harr'. Shows both a standalone filter variable and an inline usage. Requires an initialized database handle from map.sqlite('demo.db').

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

getRows();

async function getRows() {
  const filter = db.customer.name.startsWith('Harr');

  const rows = await db.customer.getMany({
    where: x => x.name.startsWith('Harr')
  });
}
```

--------------------------------

### Connect to MySQL

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Creates a database client for MySQL using a connection string.

```TypeScript
import map from './map';
const db = map.mysql('mysql://user:pass@host/dbname');
```

--------------------------------

### getMany - Get all rows

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Retrieves all rows from a table. Returns an array of entity objects.

```APIDOC
## getMany - Get all rows

### Description
Retrieves all rows from a table. Returns an array of entity objects.

### Method
- (async method)

### Endpoint
`db.<table>.getMany()`

### Parameters
None.

### Request Example
```ts
const products = await db.product.getMany();
```

### Response
#### Success Response
- Returns an array of entity objects.

#### Response Example
```ts
// Array of product entities
```
```

--------------------------------

### Raw SQL Filter with Parameters

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/06-filters.md

Demonstrates how to use a raw SQL filter with parameters for advanced querying.

```APIDOC
## Raw SQL Filter with Parameters

### Description
Use a raw SQL filter to specify custom SQL conditions with parameters for safe querying.

### Method
GET (conceptual - query operation)

### Endpoint
`db.order.getMany`

### Parameters
#### Query Parameters
- **where** (object) - Required - Object containing `sql` and `parameters`.
  - **sql** (string) - Required - Raw SQL condition string with placeholders (`?`).
  - **parameters** (array) - Required - Array of values to bind to the placeholders.

### Request Example
```js
const results = await db.order.getMany({
  where: {
    sql: 'total > ? and customerId in (select id from customer where balance > ?)',
    parameters: [100, 500]
  }
});
```

### Response
#### Success Response (200)
- **results** (array) - List of order records matching the SQL condition.

#### Response Example
```json
[
  {
    "id": 2,
    "total": 200,
    "customerId": 2
  }
]
```
```

--------------------------------

### Connect to PGlite (in-memory Postgres)

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Creates a database client for PGlite, an in-memory Postgres, with no arguments.

```TypeScript
import map from './map';
const db = map.pglite();
```

--------------------------------

### GET db.order.getOne

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Retrieves a single order record with optional filtering and related data fetching.

```APIDOC
## [GET] db.order.getOne

### Description
Retrieves a single order record with optional filtering and related data fetching.

### Method
GET

### Endpoint
db.order.getOne

### Parameters
#### Query Parameters
- **where** (function) - Optional - A filter function or pre-built filter object to select a single row.
- **customer** (boolean) - Optional - Include related customer data.
- **deliveryAddress** (boolean) - Optional - Include related delivery address data.
- **lines** (boolean) - Optional - Include related line items.

### Request Example
```javascript
const order = await db.order.getOne({
  where: x => x.customer(customer => customer.isActive.eq(true)
                 .and(customer.startsWith('Harr'))),
  customer: true, 
  deliveryAddress: true, 
  lines: true
});
```

### Response
#### Success Response (200)
- **order** (object) - The order object with requested related data.

#### Response Example
```javascript
// order object with customer, deliveryAddress, and lines populated
```
```

--------------------------------

### PostgreSQL: map.postgres with search_path

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Connect to a PostgreSQL database using the map.postgres factory with a custom search path in the connection string.

```javascript
import map from './map';
const db = map.postgres('postgres://postgres:postgres@postgres/postgres?search_path=custom');
```

--------------------------------

### GET db.order.getMany

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Retrieves multiple order records with optional filtering and related data fetching.

```APIDOC
## [GET] db.order.getMany

### Description
Retrieves multiple order records with optional filtering and related data fetching.

### Method
GET

### Endpoint
db.order.getMany

### Parameters
#### Query Parameters
- **where** (function) - Optional - A filter function or pre-built filter object to select rows.
- **customer** (boolean) - Optional - Include related customer data.
- **deliveryAddress** (boolean) - Optional - Include related delivery address data.
- **lines** (boolean) - Optional - Include related line items.

### Request Example
```javascript
const orders = await db.order.getMany({
  where: x => x.lines.any(line => line.product.contains('i'))
    .and(x.customer.balance.greaterThan(180)),
  customer: true, 
  deliveryAddress: true, 
  lines: true
});
```

### Response
#### Success Response (200)
- **orders** (array) - List of order objects with requested related data.

#### Response Example
```javascript
// orders array with customer, deliveryAddress, and lines populated
```
```

--------------------------------

### Provider selection with error handling for SQLite

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/01-entry-point.md

Use rdb.sqlite() to create a SQLite database. On Node < 22.5, non-Bun, non-Deno, it uses the sqlite3 provider. Catch the error message 'SQLite is not supported in this environment' to handle unsupported runtimes.

```javascript
const rdb = require('orange-orm');

try {
  const sqliteDb = rdb.sqlite('demo.db');
  // Node < 22.5, non-Bun, non-Deno: uses the sqlite3 provider
} catch (e) {
  if (e.message === 'SQLite is not supported in this environment')
    console.error('This runtime cannot run SQLite through orange-orm');
}
```

--------------------------------

### Get one row (first match) with getOne

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Fetches the first row that matches the where condition.

```TypeScript
const product = await db.product.getOne({
  where: x => x.name.eq('Bicycle')
});
```

--------------------------------

### Get all rows with getMany

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Fetches all rows from the product table. Returns an array of product entities.

```TypeScript
const products = await db.product.getMany();
```

--------------------------------

### express(client, options?)

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/01-entry-point.md

Binds the Express host against a client.

```APIDOC
## express(client, options?)

### Description
Binds the Express host against a client.

### Method
Function call

### Endpoint
N/A (JavaScript function)

### Parameters
- **client** - Required - The client object.
- **options** (optional) - Optional - Configuration options.

### Request Example
```js
const handler = express(client, { ... });
```

### Response
Returns an Express RequestHandler.
```

--------------------------------

### emptyFilter (rdb.filter)

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/06-filters.md

The emptyFilter function is the starting point for building filters. It provides and, or, not methods and empty sql/parameters.

```APIDOC
## emptyFilter (rdb.filter)

### Description
The emptyFilter function is the starting point for building filters. It provides and, or, not methods and empty sql/parameters.

### Usage
`rdb.filter(...)` or `emptyFilter(...)`

### Members
- **and(context?, ...filters)** - Combined AND; when called with a single argument it treats it as a filter with `context = null`.
- **or(context?, ...filters)** - Combined OR (variadic).
- **not(context?, ...filters)** - Negates the first filter, ANDs the rest.
- **sql** - Empty SQL `''`.
- **parameters** - Empty array `[]`.

### Example
```js
const filter = rdb.filter.and({ sql: 'age > ?', parameters: [18] });
```
```

--------------------------------

### DBClient<M>

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/types.md

Documentation for the DBClient interface, the main entry point for database operations.

```APIDOC
## DBClient<M>

### Description
Represents the main database client interface. It provides access to tables, query building, transactions, and framework adapters.

### Type
TypeScript interface

### Properties
- **close** (() => Promise<void>) - Closes the database connection.
- **filter** (Filter) - A filter builder for constructing where clauses.
- **and** ((f: Filter | RawFilter[], ...filters: RawFilter[]) => Filter) - Combines filters with AND logic.
- **or** ((f: Filter | RawFilter[], ...filters: RawFilter[]) => Filter) - Combines filters with OR logic.
- **not** (() => Filter) - Negates a filter.
- **function** ((name: string, fn: (...args: any[]) => unknown) => Promise<unknown> | void) - Registers or calls a custom function.
- **query** ((filter: RawFilter | string) => Promise<unknown[]>) - Executes a raw query and returns results.
- **query<T>** ((filter: RawFilter | string) => Promise<T[]>) - Executes a raw query and returns typed results.
- **createPatch** ((original: any[], modified: any[]) => JsonPatch) - Creates a JSON patch between two arrays.
- **createPatch** ((original: any, modified: any) => JsonPatch) - Creates a JSON patch between two objects.
- **(config?: DbOptions<M>)** (() => DBClient<M>) - Callable to create a new client instance with options.
- **transaction** ((fn: (db: DBClient<M>) => Promise<TR> | TR) => Promise<TR>) - Runs a function within a transaction.
- **express** (() => import('express').RequestHandler) - Returns an Express middleware.
- **express** ((config: ExpressConfig<M>) => import('express').RequestHandler) - Returns an Express middleware with configuration.
- **hono** (() => HonoHandler) - Returns a Hono handler.
- **hono** ((config: HonoConfig<M>) => HonoHandler) - Returns a Hono handler with configuration.
- **metaData** (DbConcurrency<M>) - Metadata for concurrency control.
- **interceptors** (WithInterceptors) - Interceptors for lifecycle hooks.

### Usage Example
```ts
const db = createClient();
await db.transaction(async (tx) => {
  const rows = await tx.query('SELECT * FROM users');
});
```
```

--------------------------------

### GET db.order.getById

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Retrieves a single order record by its primary key with optional related data fetching.

```APIDOC
## [GET] db.order.getById

### Description
Retrieves a single order record by its primary key with optional related data fetching.

### Method
GET

### Endpoint
db.order.getById

### Parameters
#### Path Parameters
- **id** (number) - Required - The primary key of the order.

#### Query Parameters
- **customer** (boolean) - Optional - Include related customer data.
- **deliveryAddress** (boolean) - Optional - Include related delivery address data.
- **lines** (boolean) - Optional - Include related line items.

### Request Example
```javascript
const order = await db.order.getById(1, {
  customer: true, 
  deliveryAddress: true, 
  lines: true
});
```

### Response
#### Success Response (200)
- **order** (object) - The order object with requested related data.

#### Response Example
```javascript
// order object with customer, deliveryAddress, and lines populated
```
```

--------------------------------

### on

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/10-utilities.md

Subscribes to the `'query'` event emitter to receive query events.

```APIDOC
## `on` — subscribe to query events

### Description
Subscribes to the `'query'` event emitter to receive query events.

### Method
`rdb.on('query', cb)`

### Parameters
- `type` (string) - Required - Must be `'query'`; otherwise throws `Error('unknown event type: ' + type)`.
- `cb` (function) - Required - Callback receiving an event object `{ sql, parameters }`.

### Example
```js
rdb.on('query', (event) => console.log(event.sql, event.parameters));
```
```

--------------------------------

### GET db.order.getMany (by primary keys)

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Retrieves multiple order records by their primary keys with optional related data fetching.

```APIDOC
## [GET] db.order.getMany (by primary keys)

### Description
Retrieves multiple order records by their primary keys with optional related data fetching.

### Method
GET

### Endpoint
db.order.getMany

### Parameters
#### Query Parameters
- **where** (function) - Required - A function returning an array of primary key objects.
- **customer** (boolean) - Optional - Include related customer data.
- **deliveryAddress** (boolean) - Optional - Include related delivery address data.
- **lines** (boolean) - Optional - Include related line items.

### Request Example
```javascript
const orders = await db.order.getMany({
  where: () => [
    {id: 1},
    {id: 2}
  ],
  customer: true, 
  deliveryAddress: true, 
  lines: true
});
```

### Response
#### Success Response (200)
- **orders** (array) - List of order objects with requested related data.

#### Response Example
```javascript
// orders array with customer, deliveryAddress, and lines populated
```
```

--------------------------------

### Connect to PostgreSQL

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Creates a database client for PostgreSQL using a connection string.

```TypeScript
import map from './map';
const db = map.postgres('postgres://user:pass@host/dbname');
```

--------------------------------

### GET /?table=<name> — Table metadata graph

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/endpoints.md

Returns table metadata graph as text/plain for the specified table.

```APIDOC
## GET /?table=<name>

### Description
Returns table metadata graph as text/plain for the specified table.

### Method
GET

### Endpoint
/

### Parameters
#### Query Parameters
- **table** (string) - Required - The name of the table to retrieve metadata for.

### Response
#### Success Response (200)
- Content-Type: text/plain
- Body: Table metadata graph.

#### Response Example
(No example provided in source.)
```

--------------------------------

### map.oracle()

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Connects to an Oracle database using a configuration object.

```APIDOC
## map.oracle()

### Description
Connects to an Oracle database using a configuration object.

### Method
CONNECT

### Endpoint
map.oracle(config)

### Parameters
#### Path Parameters
- **config** (object) - Required - Configuration object for Oracle connection.
  - **user** (string) - Required - Username.
  - **password** (string) - Required - Password.
  - **connectString** (string) - Required - Connection string.
  - **privilege** (number) - Optional - Privilege level.

### Request Example
```ts
const db = map.oracle({ user: 'sys', password: 'pass', connectString: 'oracle/XE', privilege: 2 });
```

### Response
#### Success Response (200)
- **db** (object) - Database client with table methods.

#### Response Example
```ts
const db = map.oracle({ user: 'sys', password: 'pass', connectString: 'oracle/XE', privilege: 2 });
```
```

--------------------------------

### GET / (no table) — TypeScript definitions

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/endpoints.md

Returns TypeScript declarations for all exposed tables as text/plain. Supports ?isNamespace=true for namespace-style declarations.

```APIDOC
## GET /

### Description
Returns TypeScript declarations for all exposed tables as text/plain. Supports ?isNamespace=true for namespace-style declarations.

### Method
GET

### Endpoint
/

### Parameters
#### Query Parameters
- **isNamespace** (boolean) - Optional - When true, returns namespace-style declarations.

### Response
#### Success Response (200)
- Content-Type: text/plain
- Body: Generated .d.ts source for the exposed tables.

#### Response Example
(No example provided in source.)
```

--------------------------------

### PostgreSQL: map.postgres connection

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Connect to a PostgreSQL database using the map.postgres factory with a default connection string.

```javascript
import map from './map';
const db = map.postgres('postgres://postgres:postgres@postgres/postgres');
```

--------------------------------

### Get many rows with fetching strategy

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Fetches multiple orders and includes related customer, deliveryAddress, and lines with nested packages.

```TypeScript
const orders = await db.order.getMany({
  customer: true,
  deliveryAddress: true,
  lines: {
    packages: true
  }
});
```

--------------------------------

### Get a single row by primary key with getById

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Fetches a single row by its primary key. Returns the row or undefined if not found.

```TypeScript
const product = await db.product.getById(1);
// Returns the row or undefined if not found
```

--------------------------------

### Data Types

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Lists the supported data types in Orange ORM and their mappings to JavaScript and SQL types, with an example of defining a table schema.

```APIDOC
## Data Types

### Description
Orange ORM supports various data types that map to JavaScript and SQL types. The table below lists the supported types and their mappings.

### Supported Types
| Orange Type         | JS Type          | SQL Types                                    |
|---------------------|------------------|----------------------------------------------|
| `string()`          | `string`         | VARCHAR, TEXT                                 |
| `numeric()`         | `number`         | INTEGER, DECIMAL, FLOAT, REAL, DOUBLE        |
| `bigint()`          | `bigint`         | BIGINT, INTEGER                              |
| `boolean()`         | `boolean`        | BIT, TINYINT(1), INTEGER                     |
| `uuid()`            | `string`         | UUID, GUID, VARCHAR                          |
| `date()`            | `string \| Date` | DATE, DATETIME, TIMESTAMP                    |
| `dateWithTimeZone()`| `string \| Date` | TIMESTAMP WITH TIME ZONE, DATETIMEOFFSET     |
| `binary()`          | `string` (base64)| BLOB, BYTEA, VARBINARY                       |
| `json()`            | `object`         | JSON, JSONB, NVARCHAR, TEXT                  |
| `jsonOf<T>()`       | `T`              | JSON, JSONB, NVARCHAR, TEXT (typed)          |

### Example
```ts
import orange from 'orange-orm';

const map = orange.map(x => ({
  demo: x.table('demo').map(x => ({
    id: x.column('id').uuid().primary().notNull(),
    name: x.column('name').string(),
    balance: x.column('balance').numeric(),
    regularDate: x.column('regularDate').date(),
    tzDate: x.column('tzDate').dateWithTimeZone(),
    picture: x.column('picture').binary(),
    isActive: x.column('isActive').boolean(),
    pet: x.column('pet').jsonOf<{ name: string; kind: string }>(),
    data: x.column('data').json(),
  }))
}));
```
```

--------------------------------

### getOne - Get one row (first match)

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Retrieves the first row that matches the given filter. Returns the row or undefined if no match.

```APIDOC
## getOne - Get one row (first match)

### Description
Retrieves the first row that matches the given filter. Returns the row or undefined if no match.

### Method
- (async method)

### Endpoint
`db.<table>.getOne(options)`

### Parameters
#### Options
- `where` (function) - Optional - A callback that receives a row reference and returns a filter expression.

### Request Example
```ts
const product = await db.product.getOne({
  where: x => x.name.eq('Bicycle')
});
```

### Response
#### Success Response
- Returns the first matching row entity or `undefined`.

#### Response Example
```ts
// product entity or undefined
```
```

--------------------------------

### Connect via HTTP (browser client)

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Creates a database client for HTTP, typically used in a browser, using a URL.

```TypeScript
import map from './map';
const db = map.http('http://localhost:3000/orange');
```

--------------------------------

### Connect to SQLite

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Creates a database client for SQLite using the map.sqlite() method. The connection string is a file path.

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

--------------------------------

### hostLocal(options)

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/04-table-definition.md

Exposes the table locally through the host adapter.

```APIDOC
## hostLocal(options)

### Description
Exposes the table locally through the host adapter.

### Method
hostLocal

### Parameters
- **options** (object) - Required - Options for the host adapter.

### Example
```js
table.hostLocal(options);
```
```

--------------------------------

### Get many rows with reusable where filter

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Shows an alternative approach: build the where filter separately and pass it in via the where option, making it reusable and independent of the fetching strategy.

```javascript
async function getRows() {
  const filter = db.order.lines.any(line => line.product.contains('i'))
                 .and(db.order.customer.balance.greaterThan(180));
  const orders = await db.order.getMany({
    where: filter,
    customer: true, 
    deliveryAddress: true, 
    lines: true
  });
}
```

--------------------------------

### map.mssql()

Source: https://github.com/alfateam/orange-orm/blob/master/SKILL.md

Connects to a Microsoft SQL Server database using a configuration object.

```APIDOC
## map.mssql()

### Description
Connects to a Microsoft SQL Server database using a configuration object.

### Method
CONNECT

### Endpoint
map.mssql(config)

### Parameters
#### Path Parameters
- **config** (object) - Required - Configuration object for MS SQL connection.
  - **server** (string) - Required - Server address.
  - **options** (object) - Required - Options object. Supports `encrypt` (boolean) and `database` (string).
  - **authentication** (object) - Required - Authentication object. Supports `type` (string) and `options` with `userName` and `password`.

### Request Example
```ts
const db = map.mssql({
  server: 'mssql',
  options: { encrypt: false, database: 'test' },
  authentication: {
    type: 'default',
    options: { userName: 'sa', password: 'password' }
  }
});
```

### Response
#### Success Response (200)
- **db** (object) - Database client with table methods.

#### Response Example
```ts
const db = map.mssql({
  server: 'mssql',
  options: { encrypt: false, database: 'test' },
  authentication: {
    type: 'default',
    options: { userName: 'sa', password: 'password' }
  }
});
```
```

--------------------------------

### Get many rows by primary key array

Source: https://github.com/alfateam/orange-orm/blob/master/README.md

Fetches multiple rows by primary key using a where function that returns an array of key objects. Includes the specified relations.

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

getRows();

async function getRows() {
  const orders = await db.order.getMany({
    where: () => [
      {id: 1},
      {id: 2}
    ],
    customer: true, 
    deliveryAddress: true, 
    lines: true
  });
}
```

--------------------------------

### hono(client, options?)

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/01-entry-point.md

Binds the Hono host against a client.

```APIDOC
## hono(client, options?)

### Description
Binds the Hono host against a client.

### Method
Function call

### Endpoint
N/A (JavaScript function)

### Parameters
- **client** - Required - The client object.
- **options** (optional) - Optional - Configuration options.

### Request Example
```js
const handler = hono(client, { ... });
```

### Response
Returns a HonoHandler.
```

--------------------------------

### hostLocal options keys

Source: https://github.com/alfateam/orange-orm/blob/master/_autodocs/api-reference/08-http-hosting.md

Lists the options keys accepted by hostLocal, as documented in the header comment of src/hostLocal.js.

```javascript
// { db, table, defaultConcurrency,
// 	concurrency,
// 	customFilters,
// 	baseFilter, strategy, transaction,
// 	readonly,
// 	disableBulkDeletes, isBrowser }
```
Tokens:
Raw