Orange ORM

https://orange-orm.io/docs
Admin
Orange ORM is an Object-Relational Mapping (ORM) tool, typically used to simplify database...
Tokens:19,699
Snippets:157
Trust Score:6.2
Update:10 months ago
Show doc for...
Tokens:
Raw
### Install and Connect to PostgreSQL Database

Source: https://orange-orm.io/docs/index

Shows how to install the `pg` package for PostgreSQL connections and connect using a standard connection string. It also includes an example of specifying a search path for databases with schemas. Note that Bun includes built-in support.

```bash
npm install pg

```

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

```

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

```

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

### Install and Connect to MS SQL Database

Source: https://orange-orm.io/docs/index

Details the installation of the `tedious` package for MS SQL Server connectivity and demonstrates how to configure the connection using an object with server details, encryption options, and authentication credentials.

```bash
npm install tedious

```

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

```

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

### Install Orange ORM

Source: https://orange-orm.io/docs/index

This snippet shows the command to install the Orange ORM package using npm. Ensure you have Node.js and npm installed to use this command.

```bash
npm install orange-orm

```

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

### Install and Connect to MySQL Database

Source: https://orange-orm.io/docs/index

Provides instructions for installing the `mysql2` package and connecting to a MySQL database using a connection string. This allows the Orange ORM to interact with a MySQL instance.

```bash
$ npm install mysql2

```

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

```

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

### Initialize SQLite Database with SQL Statements

Source: https://orange-orm.io/docs/index

This snippet demonstrates initializing a SQLite database by splitting a SQL string into individual statements and executing them sequentially using `db.query`. It is asynchronous and suitable for setup routines.

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

async function init() {
  const statements = sql.split(';');
  for (let i = 0; i < statements.length; i++) {
    await db.query(statements[i]);
  }
}
export default init;

```

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

### Connect to PGlite In-Memory Database

Source: https://orange-orm.io/docs/index

Demonstrates connecting to an in-memory PostgreSQL database using PGlite. This requires installing the `@electric-sql/pglite` package. Configuration options can be passed to the `map.pglite` function.

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

```

```typescript
import map from './map';
const db = map.pglite( /* config? : PGliteOptions */);

```

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

### Connect to SQLite Database (Node.js < 21)

Source: https://orange-orm.io/docs/index

Establishes a connection to a SQLite database file named 'demo.db'. This method requires the `sqlite3` package to be installed and is suitable for Node.js versions 21 and earlier. The connection is managed as a single client instance.

```bash
npm install sqlite3

```

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

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

```

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

### Enable Query Logging with Orange ORM

Source: https://orange-orm.io/docs/index

Illustrates how to enable and use query logging in Orange ORM by listening to the 'query' event. The logged output includes the SQL statement and any associated parameters, which is invaluable for debugging database operations. The example updates an order and logs the generated SQL.

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

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

### SAP Adaptive Server (ASE) Connection via ODBC

Source: https://orange-orm.io/docs/index

Shows how to connect to SAP Adaptive Server (ASE) using the Orange ORM via an ODBC driver. This requires installing the 'msnodesqlv8' package and having the correct ODBC driver file ('libsybdrvodb.so') and connection string.

```bash
npm install msnodesqlv8

```

```javascript
import { fileURLToPath } from 'url';
import { dirname } from 'path';
import map from './map';

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);
//download odbc driver from sap web pages
const db = map.sap(`Driver=${__dirname}/libsybdrvodb.so;SERVER=sapase;Port=5000;UID=sa;PWD=sybase;DATABASE=test`);

```

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

### Oracle Database Connection with Orange ORM

Source: https://orange-orm.io/docs/index

Demonstrates how to install the Oracle driver and connect to an Oracle database using the Orange ORM in JavaScript. Requires the 'oracledb' npm package and specific connection details.

```bash
npm install oracledb

```

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

```

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

### Node.js Server Setup with Express and Orange ORM

Source: https://orange-orm.io/docs/index

Sets up an Express.js server to handle ORM requests. It configures middleware for JSON parsing and CORS, integrates Orange ORM with a SQLite database, and applies a base filter for orders based on authorization headers. Includes a token validation middleware for demonstration.

```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', validateToken)
  .use('/orange', db.express({
    order: {
      baseFilter: (db, req, _res) => {
        const customerId = Number.parseInt(req.headers.authorization.split(' ')[1]); //Bearer 2
        return db.order.customerId.eq(Number.parseInt(customerId));
      }
    }
  }))
  .listen(3000, () => console.log('Example app listening on port 3000!'));

function validateToken(req, res, next) {
  // For demo purposes, we're just checking against existence of authorization header
  // In a real-world scenario, this would be a dangerous approach because it bypasses signature validation
  const authHeader = req.headers.authorization;
  if (authHeader)
    return next();
  else
    return res.status(401).json({ error: 'Authorization header missing' });
}
```

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

### Deleting Many Rows

Source: https://orange-orm.io/docs/index

Example of fetching multiple orders based on a customer name and then deleting them.

```APIDOC
## Deleting Many Rows

### Description
This snippet demonstrates fetching all orders for a specific customer ('George') and then deleting these orders from the database.

### Method
N/A (Code example)

### Endpoint
N/A

### Parameters
N/A

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

deleteRows();

async function deleteRows() {
  let orders = await db.order.getAll({
    where: x => x.customer.name.eq('George')
  });

  await orders.delete();
}
```

### Response
N/A
```

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

### Orange ORM: Fetching Orders with Delivery Address

Source: https://orange-orm.io/docs/index

Example of fetching all orders along with their associated delivery address details using Orange ORM. This demonstrates including a full relation in the query.

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

getRows();

async function getRows() {
  const rows = await db.order.getAll({
    deliveryAddress: true 
  });  
}
```

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

### Define Composite Primary Keys

Source: https://orange-orm.io/docs/index

This example illustrates how to define composite primary keys in Orange ORM by marking multiple columns with `.primary()`. It shows a scenario with orders and order lines, where a combination of columns uniquely identifies each record.

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

const map = orange.map(x => ({
  order: x.table('_order').map(({ column }) => ({
    orderType: column('orderType').string().primary().notNull(),
    orderNo: column('orderNo').numeric().primary().notNull(),
    orderDate: column('orderDate').date().notNull(),
  })),

  orderLine: x.table('orderLine').map(({ column }) => ({
    orderType: column('orderType').string().primary().notNull(),
    orderNo: column('orderNo').numeric().primary().notNull(),
    lineNo: column('lineNo').numeric().primary().notNull(),
    product: column('product').string(),
  }))
})).map(x => ({
  order: x.order.map(v => ({
    lines: v.hasMany(x.orderLine).by('orderType', 'orderNo'),
  }))
}));

```

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

### Fetch All Rows with Related Data in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Illustrates how to fetch all rows from the 'order' table using Orange ORM's `getAll` method. This example demonstrates fetching associated data for 'customer', 'deliveryAddress', and nested 'lines' with their 'packages', showcasing the ORM's capability to eagerly load related entities.

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

getRows();

async function getRows() {
  const orders = await db.order.getAll({
    customer: true, 
    deliveryAddress: true, 
    lines: {
      packages: true
    }
  });
}
```

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

### Delete Orders with Overwrite Concurrency

Source: https://orange-orm.io/docs/index

This example demonstrates how to delete order records even if their delivery address has been modified concurrently. It utilizes the 'overwrite' concurrency strategy to force the deletion.

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

deleteRows();

async function deleteRows() {
  let orders = await db.order.getAll({
    where: x => x.deliveryAddress.name.eq('George'),
    customer: true, 
    deliveryAddress: true, 
    lines: true
  });

  await orders.delete({
    deliveryAddress: {
      concurrency: 'overwrite'
    }
  });
}
```

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

### Define a table mapping in Orange ORM

Source: https://orange-orm.io/docs/index

Example of how to define a table mapping for a 'demo' table with a 'uuid' primary key column named 'id' using the Orange ORM.

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

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

const map = orange.map(x => ({
    demo: x.table('demo').map(x => ({
      id: x.column('id').uuid().primary().notNull()

```

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

### Configuring Conflict Resolution Strategies

Source: https://orange-orm.io/docs/index

Explains the default optimistic concurrency strategy and introduces 'overwrite' and 'skipOnConflict' strategies for handling row insertions. It provides an example of setting different concurrency strategies at the table and column levels to manage conflicts during updates.

```plaintext
By default, the strategy for inserting rows is set to an optimistic approach. In this case, if a row is being inserted with an already existing primary key, the database raises an exception.
Currently, there are three concurrency strategies:
  * **`optimistic`**Raises an exception if another row was already inserted on that primary key.
  * **`overwrite`**Overwrites the property, regardless of changes by others.
  * **`skipOnConflict`**Silently avoids updating the property if another user has modified it in the interim.

The **concurrency** option can be set either for the whole table or individually for each column. In the example below, we've set the concurrency strategy on **vendor** table to **overwrite** except for the column **balance** which uses the **skipOnConflict** strategy. In this particular case, a row with **id: 1** already exists, the **name** and **isActive** fields will be overwritten, but the balance will remain the same as in the original record, demonstrating the effectiveness of combining multiple **concurrency** strategies.

```

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

### Fetch Rows with Aggregated Results and Data Elevation in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Shows how to perform aggregate calculations and elevate related data to the parent level when fetching rows with Orange ORM. This example uses `getAll` to count associated lines, sum amounts, and access a customer's balance directly, simplifying data access for reporting and analysis.

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

getRows();

async function getRows() {
  const orders = await db.order.getAll({
    numberOfLines: x => x.count(x => x.lines.id),
    totalAmount: x => x.sum(x => lines.amount),
    balance: x => x.customer.balance
  });
}
```

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

### Set Default Values for Columns

Source: https://orange-orm.io/docs/index

Demonstrates how to set default values for columns in Orange ORM. This is useful when the database doesn't support native default value generation. Examples include a UUID for an ID and a boolean for an active status.

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

const map = orange.map(x => ({
  myTable: x.table('myTable').map(({ column }) => ({
    id: column('id').uuid().primary().default(() => crypto.randomUUID()),
    name: column('name').string(),
    balance: column('balance').numeric(),
    isActive: column('isActive').boolean().default(true),
  }))
}));

export default map;

```

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

### Filter by String Start/End

Source: https://orange-orm.io/docs/index

Retrieve rows where a string field starts or ends with a specified substring. This is useful for partial string matching in queries. It uses a JavaScript ORM for database interaction.

```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.getAll({
    where: x => x.name.startsWith('Harr')
  });
}

```

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

getRows();

async function getRows() {
  const rows = await db.customer.getAll({
    where: x => x.name.endsWith('arry')
  });
}

```

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

### Get Many Rows by Primary Key

Source: https://orange-orm.io/docs/index

Retrieves multiple orders based on a list of primary keys. This method allows fetching several specific records efficiently, along with their associated customer, delivery address, and order line details.

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

getRows();

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

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

### Insert Rows with Concurrency Control in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Demonstrates inserting rows into a SQLite database using Orange ORM. It showcases the 'skipOnConflict' and 'overwrite' concurrency strategies for handling potential data conflicts during insertion. The example shows how to insert initial data and then update a record, illustrating how 'overwrite' affects fields other than those specified.

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

insertRows();

async function insertRows() {

  db2 = db({
    vendor: {
      balance: {
        concurrency: 'skipOnConflict'
      },
      concurrency: 'overwrite'
    }
  });

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

  //this will overwrite all fields but balance
  const george = await db2.vendor.insert({
    id: 1,
    name: 'George',
    balance: 177,
        isActive: false
  });
  console.dir(george, {depth: Infinity});
  // {
  //   id: 1,
  //   name: 'George',
  //   balance: 100,
  //   isActive: false
  // }
}
```

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

### Update Rows with Conflict Resolution in JavaScript

Source: https://orange-orm.io/docs/index

Demonstrates updating rows with an optimistic concurrency strategy. If a property being edited is altered by another user in the interim, an exception is raised. This example sets the concurrency strategy for 'orderDate' to 'overwrite', ensuring that any concurrent changes to 'orderDate' are overridden.

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

update();

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

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

### Get Single Row by Primary Key

Source: https://orange-orm.io/docs/index

Fetches a single order by its primary key (ID) and includes related data like customer, delivery address, and order lines. This is useful for retrieving detailed information about a specific record.

```javascript
async function getRows() {
  const order = await db.order.getById(1, {
    customer: true, 
    deliveryAddress: true, 
    lines: true
  });
}
```

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

### Aggregate Functions (Across All Rows) in Orange ORM

Source: https://orange-orm.io/docs/index

Efficiently groups data across all rows using aggregate functions. This example counts order lines per customer and calculates the total amount, internally executing a SQL GROUP BY customerId and customerName. It includes filtering by order date.

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

getAggregates();

async function getAggregates() {
  const orders = await db.order.aggregate({
    where: x => x.orderDate.greaterThan(new Date(2022, 0, 11, 9, 24, 47)),
    customerId: x => x.customerId,
    customerName: x => x.customer.name,
    numberOfLines: x => x.count(x => x.lines.id),

```

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

### Raw SQL Queries in Orange ORM

Source: https://orange-orm.io/docs/index

Allows direct execution of SQL queries, bypassing the ORM. Raw SQL filters from browser inputs are prohibited due to security risks (SQL injection), resulting in a 403 Forbidden error. The example demonstrates querying customer data using a LIKE clause.

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

getRows();

async function getRows() {
  const query = {
    sql: 'select * from customer where name like ?',
    parameters: ['%arry']
  };
                 
  const rows = await db.query(query)   
}
```

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

### Formula Discriminators in Orange ORM

Source: https://orange-orm.io/docs/index

Formula discriminators use logical expressions to categorize data in the same table, unlike column discriminators that use static values. The example categorizes bookings into 'customerBooking' and 'internalBooking' based on the 'booking_no' range. '@this' acts as a placeholder for the table alias during query construction.

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

export default map;
```

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

### Fetch Single Row with Filtered Criteria in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Shows how to retrieve a single row based on specific filtering conditions using Orange ORM's `getOne` method. This example uses a `where` clause to filter customers based on their 'isActive' status and 'startsWith' condition on their name, while also fetching related data.

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

getRows();

async function getRows() {
  const order = await db.order.getOne(undefined /* optional filter */, {
    where: x => x.customer(customer => customer.isActive.eq(true)
                 .and(customer.startsWith('Harr'))),
    customer: true, 
    deliveryAddress: true, 
    lines: true
  });
}
```

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

### Initialize SQLite Database with Schema using Orange ORM

Source: https://orange-orm.io/docs/index

Resets and initializes an SQLite database file ('demo.db') by dropping existing tables and creating new ones based on the provided SQL schema. The SQL string is split into individual statements due to SQLite's single-statement execution limitation.

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

const sql = `DROP TABLE IF EXISTS deliveryAddress;
DROP TABLE IF EXISTS package;
DROP TABLE IF EXISTS orderLine;
DROP TABLE IF EXISTS _order;
DROP TABLE IF EXISTS customer;

CREATE TABLE customer (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance NUMERIC,
    isActive INTEGER
);

CREATE TABLE _order (
    id INTEGER PRIMARY KEY,
    orderDate TEXT,
    customerId INTEGER REFERENCES customer
);

CREATE TABLE orderLine (
    id INTEGER PRIMARY KEY,
    orderId INTEGER REFERENCES _order,
    product TEXT,
    amount NUMERIC(10,2)
);

CREATE TABLE package (
    packageId INTEGER PRIMARY KEY,
    lineId INTEGER REFERENCES orderLine,
    sscc TEXT
);

CREATE TABLE deliveryAddress (
    id INTEGER PRIMARY KEY,
    orderId INTEGER REFERENCES _order,
    name TEXT, 
    street TEXT, 

```

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

### Cloudflare D1 Database Configuration and Fetching

Source: https://orange-orm.io/docs/index

Configures a Cloudflare D1 database binding in wrangler.toml and demonstrates fetching all customers using the ORM in a TypeScript fetch handler. Ensure the binding name in wrangler.toml matches the `DB` interface in your code.

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

export interface Env {
  // Must match the binding name in wrangler.toml  
  DB: D1Database;
}

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

```

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

### Orange ORM: Filtering Customers by Equal Name

Source: https://orange-orm.io/docs/index

Retrieves all customers whose name is exactly 'Harry' using the 'equal' filter in Orange ORM. This is a basic example of precise data targeting.

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

getRows();

async function getRows() {
  const rows = await db.customer.getAll({
    where x => x.name.equal('Harry')
  });
}
```

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

### Configure Cloudflare D1 Database Binding

Source: https://orange-orm.io/docs/index

Illustrates how to configure a Cloudflare D1 database binding within a `wrangler.toml` file. This step is necessary to associate a D1 database with a Cloudflare Worker project, enabling serverless SQL database access.

```toml
name = "d1-tutorial"
main = "src/index.ts"
compatibility_date = "2025-02-04"

# Bind a D1 database. D1 is Cloudflare’s native serverless SQL database.

```

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

### Inserting and Fetching Rows with SQLite and ORM

Source: https://orange-orm.io/docs/index

Illustrates inserting customer and order data into a SQLite database using the Orange ORM. It includes inserting multiple customers and orders, demonstrating automatic primary key generation and specifying fetching strategies to retrieve associated data like customer, delivery address, and order 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
}

```

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

### Insert New Order with Details

Source: https://orange-orm.io/docs/index

This snippet demonstrates how to insert a new order into the database. It includes adding details like order date, customer information, delivery address, and order lines. The changes are then saved.

```javascript
orders.splice(1, 1);

  //will insert a new order with lines, deliveryAddress and set customerId
  orders.push({
    orderDate: new Date(2022, 0, 11, 9, 24, 47),
    customer: {
      id: 1
    },
    deliveryAddress: {
      name: 'George',
      street: 'Node street 1',
      postalCode: '7059',
      postalPlace: 'Jakobsli',
      countryCode: 'NO'
    },
    lines: [
      { product: 'Magic tent', amount: 349 }
    ]
  });

  await orders.saveChanges();
```

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

### Orange ORM in the Browser (Express Adapter)

Source: https://orange-orm.io/docs/index

Demonstrates how to integrate Orange ORM with an Express server for browser-based database operations, ensuring security by replaying method calls instead of sending raw SQL.

```APIDOC
## Orange ORM in the Browser (Express Adapter)

### Description
This setup allows using Orange ORM in a browser environment by integrating with an Express server. Client-side method calls are recorded and replayed on the server, enhancing security by preventing direct SQL exposure. Raw SQL queries, filters, and transactions are disabled on the HTTP client.

### Method
N/A (Server and Client Setup)

### Endpoint
N/A

### Parameters
N/A

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

**Client (`browser.ts`):**
```typescript
import map from './map';

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

updateRows();

async function updateRows() {
  const order = await db.order.getOne(undefined, {
    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();
}
```

### Response
N/A
```

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

### Adding a New Order

Source: https://orange-orm.io/docs/index

Demonstrates how to add a new order with associated lines, delivery address, and customer information, followed by saving the changes.

```APIDOC
## Adding a New Order

### Description
This code snippet shows how to add a new order to the `orders` collection. It includes details like `orderDate`, `customer`, `deliveryAddress`, and `lines`. The `saveChanges()` method is called to persist the new order.

### Method
N/A (This is a code example, not a direct API endpoint)

### Endpoint
N/A

### Parameters
N/A

### Request Example
```javascript
// Assuming 'orders' is an array or collection object
orders.push({
  orderDate: new Date(2022, 0, 11, 9, 24, 47),
  customer: {
    id: 1
  },
  deliveryAddress: {
    name: 'George',
    street: 'Node street 1',
    postalCode: '7059',
    postalPlace: 'Jakobsli',
    countryCode: 'NO'
  },
  lines: [
    { product: 'Magic tent', amount: 349 }
  ]
});

await orders.saveChanges();
```

### Response
N/A
```

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

### Perform transactional database operations with rollback

Source: https://orange-orm.io/docs/index

Demonstrates how to initiate a database transaction using `db.transaction`. Operations within the transaction should use the provided `tx` object. An error is thrown to showcase automatic rollback, ensuring data integrity.

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

execute();

async function execute() {
  await db.transaction(async tx => {
    const customer = await tx.customer.getById(1);
      customer.balance = 100;
      await customer.saveChanges();
      throw new Error('This will rollback');
  });
}
```

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

### Exclude Sensitive Data with Orange ORM

Source: https://orange-orm.io/docs/index

Shows how to prevent sensitive data, like 'balance', from being serialized to JSON using the `serializable(false)` attribute in the ORM schema. The `sensitive.ts` example inserts a customer and then logs the JSON string, demonstrating that the 'balance' field is omitted.

```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().serializable(false),
    isActive: column('isActive').boolean(),
  }))
}));

export default map;
```

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

getRows();

async function getRows() {

  const george = await db.customer.insert({
    name: 'George',
    balance: 177,
    isActive: true
  });
  
  console.dir(JSON.stringify(george), {depth: Infinity});
  //note that balance is excluded:
  //'{"id":1,"name":"George","isActive":true}'
}
```

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

### Fetch Rows with Limit, Offset, and Ordering in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Demonstrates advanced data retrieval using Orange ORM's `getAll` method. This snippet shows how to paginate results by setting an 'offset' and 'limit', sort data using 'orderBy' on multiple columns, and fetch related entities including nested ones with their own sorting criteria.

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

getRows();

async function getRows() {
  const orders = await db.order.getAll({
    offset: 1,
    orderBy: ['orderDate desc', 'id'],
    limit: 10,
    customer: true, 
    deliveryAddress: true, 
    lines: {
      packages: true,
      orderBy: 'product'
    },
  });
}
```

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

### Connect to SQLite Database with Connection Pool

Source: https://orange-orm.io/docs/index

Sets up a connection pool for a SQLite database, allowing for multiple concurrent connections. This is useful for handling higher loads. The pool size is configurable, and like the single client, it requires `sqlite3` for older Node.js versions. Explicitly closing the pool is crucial in serverless environments.

```bash
npm install sqlite3

```

```typescript
import map from './map';
const db = map.sqlite('demo.db', { size: 10 });
// … use the pool …

// IMPORTANT for serverless functions:
await pool.close();         // closes all pooled connections

```

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

### Connect to Express Server from Browser

Source: https://orange-orm.io/docs/index

Demonstrates how a browser client can connect to a backend Express server that is exposing a SQLite database via the Orange ORM's Express plugin. It uses `map.http` to create a client instance that communicates with the specified server URL.

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

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

```

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

### Interceptors and Base Filter for Security

Source: https://orange-orm.io/docs/index

Explains a security pattern using client-side Axios interceptors to add authorization headers and server-side Express middleware to validate tokens and apply base filters to tables.

```APIDOC
## Interceptors and Base Filter for Security

### Description
This setup enhances security by using Axios interceptors on the client to attach authorization tokens and server-side Express middleware to validate these tokens. A base filter is applied to the `order` table to restrict access based on `customerId` extracted from the token, ensuring users only see their data. This pattern is recommended for production environments, with a suggestion to implement more robust token management and error handling.

### Method
N/A (Conceptual setup, not direct API calls)

### Endpoint
N/A

### Parameters
N/A

### Request Example
*   **Client-side (Axios Interceptor):** Add an `Authorization` header to outgoing requests.
*   **Server-side (Express Middleware):** Validate the `Authorization` header and apply a base filter to the `order` table using the extracted `customerId`.

### Response
N/A
```

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

### Batch Delete Customers by Primary Key

Source: https://orange-orm.io/docs/index

This snippet shows how to efficiently delete multiple customer records by providing an array of their primary keys. This method is useful when the IDs of the records to be deleted are known.

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

deleteRows();

async function deleteRows() {
  db.customer.delete([{id: 1}, {id: 2}]);
}
```

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

### Orange ORM: Fetching Orders with Subset of Columns

Source: https://orange-orm.io/docs/index

Demonstrates fetching orders and selectively including specific columns from the order and its related delivery address. It shows how to exclude columns and include only a subset of fields from a relation.

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

getRows();

async function getRows() {
  const rows = await db.order.getAll({
    orderDate: false,
    deliveryAddress: {
      countryCode: true,
      name: true
    } 
  });  
}
```

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

### Logical Query Operators (AND, OR, NOT, EXISTS)

Source: https://orange-orm.io/docs/index

Construct complex queries using logical operators like AND, OR, NOT, and EXISTS. These operators enable granular control over data retrieval, allowing for sophisticated conditional filtering.

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

getRows();

async function getRows() {
  const rows = await db.order.getAll({
    where: x => x.customer.name.equal('Harry')
      .and(x.orderDate.greaterThan('2023-07-14T12:00:00'))
  });  
}

```

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

getRows();

async function getRows() {

  const rows = await db.order.getAll({
    where: y => y.customer( x => x.name.equal('George')
      .or(x.name.equal('Harry')))
  });  
}

```

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

getRows();

async function getRows() {
  //Neither George nor Harry
  const rows = await db.order.getAll({
    where: y => y.customer(x => x.name.equal('George')
        .or(x.name.equal('Harry')))
      .not()
  });  
}

```

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

getRows();

async function getRows() {
  const rows = await db.order.getAll({
    where: x => x.deliveryAddress.exists()
  });

```

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

### Filter Rows with Orange ORM (TypeScript)

Source: https://orange-orm.io/docs/index

This TypeScript code shows how to retrieve orders from the database using Orange ORM, applying complex filtering criteria. It demonstrates filtering based on a condition involving related 'lines' (product contains 'broomstick') and 'customer' (name starts with 'Harry'), and includes eager loading of related 'lines', 'packages', 'deliveryAddress', and 'customer' data.

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

getRows();

async function getRows() {
  const orders = await db.order.getAll({
    where: x => x.lines.any(line => line.product.contains('broomstick'))
      .and(x.customer.name.startsWith('Harry')),
    lines: {
      packages: true
    },
    deliveryAddress: true,    
    customer: true
  });  
}

```

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

### Set up Express Server for Browser SQLite Access

Source: https://orange-orm.io/docs/index

Configures an Express.js server to securely handle database operations for browser clients using the Orange ORM's Express plugin. It enables CORS, limits JSON payload size, and exposes the database via a '/orange' endpoint. This prevents direct exposure of database credentials to the client.

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

```

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

### Orange ORM Data Type Mappings to SQL

Source: https://orange-orm.io/docs/index

Illustrates Orange ORM's data type mappings to various SQL types, ensuring database agnosticism. Covers string, numeric, bigint, boolean, uuid, date, dateWithTimeZone, binary, and json types.

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

### Query records with at least one item matching criteria (Any Operator)

Source: https://orange-orm.io/docs/index

The 'any' operator is used to find records where at least one item in a collection satisfies the specified condition. It's useful for scenarios involving related records.

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

getRows();

async function getRows() {
  const rows = await db.order.getAll({
    where: y => y.lines.any(x => x.product.contains('guitar'))
    //equivalent syntax:
    //where: x => x.lines.product.contains('guitar')
  });
}
```

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

### Fetch Many Rows with Complex Filtering in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Illustrates fetching multiple rows based on complex filtering criteria using Orange ORM. This snippet demonstrates using the `getAll` method with a `where` clause that combines conditions on related entities ('lines.any' and 'customer.balance.greaterThan'). It also shows fetching related data.

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

getRows();

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

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

### Deleting with Concurrency (Overwrite)

Source: https://orange-orm.io/docs/index

Shows how to delete orders even if related data like the delivery address has been modified, using the 'overwrite' concurrency strategy.

```APIDOC
## Deleting with Concurrency (Overwrite)

### Description
This example illustrates how to delete orders while ensuring the operation proceeds even if concurrent modifications have occurred. It utilizes the `overwrite` concurrency strategy for the `deliveryAddress`.

### Method
N/A (Code example)

### Endpoint
N/A

### Parameters
N/A

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

deleteRows();

async function deleteRows() {
  let orders = await db.order.getAll({
    where: x => x.deliveryAddress.name.eq('George'),
    customer: true,
    deliveryAddress: true,
    lines: true
  });

  await orders.delete({
    deliveryAddress: {
      concurrency: 'overwrite'
    }
  });
}
```

### Response
N/A
```

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

### Browser Client Interaction with Orange ORM

Source: https://orange-orm.io/docs/index

Demonstrates client-side interaction with a remote Orange ORM instance via HTTP. It sets up request interceptors to add authorization headers and handle 401 errors, then fetches and modifies an order, and saves the changes.

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

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

updateRows();

async function updateRows() {
  
  db.interceptors.request.use((config) => {
    // For demo purposes, we're just adding hardcoded token
    // In a real-world scenario, use a proper JSON web token
    config.headers.Authorization = 'Bearer 2' //customerId
    return config;
  });

  db.interceptors.response.use(
    response => response, 
    (error) => {
      if (error.response && error.response.status === 401) {
        console.dir('Unauthorized, dispatch a login action');
        //redirectToLogin();
      }
      return Promise.reject(error);
    }
  );

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

  await order.saveChanges();

}
```

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

### Batch Delete by Primary Key

Source: https://orange-orm.io/docs/index

Deletes records efficiently by specifying their primary keys directly.

```APIDOC
## Batch Delete by Primary Key

### Description
This method allows for efficient deletion of records when their primary keys are known. You can provide an array of primary key identifiers to delete multiple records at once.

### Method
N/A (Code example)

### Endpoint
N/A

### Parameters
N/A

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

deleteRows();

async function deleteRows() {
  db.customer.delete([{id: 1}, {id: 2}]);
}
```

### Response
N/A
```

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

### Fetch Single Row with Independent Filter in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Demonstrates fetching a single row using an independently constructed filter with Orange ORM's `getOne` method. This alternative syntax allows for building the filter separately and then passing it to `getOne`, offering flexibility in query construction. It shows filtering based on customer status and name prefix.

```javascript
async function getRows() {
  const filter = db.order.customer(customer => customer.isActive.eq(true)
                 .and(customer.startsWith('Harr')));
                 //equivalent, but creates slighly different sql:
                 // const filter = db.order.customer.isActive.eq(true).and(db.order.customer.startsWith('Harr'));
  const order = await db.order.getOne(filter, {
    customer: true, 
    deliveryAddress: true, 
    lines: true
  });
}
```

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

### Fetch Single Row by Primary Key in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Provides a basic structure for fetching a single row by its primary key using Orange ORM. This snippet shows the import and initialization of the database connection, setting the stage for a `getOne` call likely using the primary key as an implicit or explicit filter.

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

```

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

### Raw SQL and Combined Filters

Source: https://orange-orm.io/docs/index

Utilize raw SQL queries for specific filtering needs or combine them with standard ORM filters. This provides flexibility for complex or performance-critical queries. Note that direct use of raw SQL with user input is restricted to prevent injection.

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

getRows();

async function getRows() {
  const rawFilter = {
    sql: 'name like ?',
    parameters: ['%arry']
  };
                  
  const rowsWithRaw = await db.customer.getAll({
    where: () => rawFilter
  });

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

```

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

### Query records where all items match criteria (All Operator)

Source: https://orange-orm.io/docs/index

The 'all' operator ensures that every item within a collection meets the defined condition. This is useful for enforcing constraints across related data.

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

getRows();

async function getRows() {
  const rows = await db.order.getAll({
    where: y => y.lines.all(x => x.product.contains('a'))
  });
}
```

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

### Count Records with Orange ORM

Source: https://orange-orm.io/docs/index

Demonstrates how to efficiently count records in a table using Orange ORM's count method with a filter. It filters orders to count those containing a 'broomstick' product. This method is useful for quick record counts without fetching the entire dataset.

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

getCount();

async function getCount() {
  const filter = db.order.lines.any(
    line => line.product.contains('broomstick')
  );
  const count = await db.order.count(filter);
  console.log(count); //2
}
```

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

### Fetch Many Rows with Independent Filter in Orange ORM (JavaScript)

Source: https://orange-orm.io/docs/index

Demonstrates an alternative way to filter data when fetching multiple rows using Orange ORM's `getMany` method. This approach allows constructing the filter independently from the fetching strategy and combining it with fetch options. It shows how to use `lines.any` and `customer.balance.greaterThan` within the filter.

```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(filter, {
    //where: x => ... can be combined as well
    customer: true, 
    deliveryAddress: true, 
    lines: true
  });
}
```
Tokens:
Raw