All examples below are found at npmjs.org/package/rdb-demo.
This is the Classic Documentation. Are you looking for the Modern Typescript Documentation ?

Connecting
connect to postgres
connect to mySql
connect to Ms Sql
connect to Sybase SAP
connect to sqlite
pool size
native bindings
schema
schema alternative 2
end pool
end all pools
logging

Basic querying
getById
tryGetById
tryGetFirst
join
hasMany
hasOne
composite keys
getById eagerly
tryGetFirst eagerly
toDto
toDto with strategy
toDto with orderBy
toDto with orderBy descending
toDto ignoring columns
toJSON
toJSON with strategy
getMany
getManyDto
getMany lazily
getMany eagerly
getManyDto eagerly
limit and offset
getMany with orderBy jsonb
getMany with orderBy jsonb descending
(many)ToDto
(many)ToDto with strategy
(many)ToJSON
(many)ToJSON with strategy
Raw SQL query
Raw SQL Query With Parameters

Streaming
streaming rows
streaming json

Persistence
update
insert
insertAndForget
delete
cascade delete
bulk delete
bulk cascade delete
default values
conventions
update a join-relation
update a hasOne-relation
update a hasMany-relation
row lock
transaction lock
session lock

Validation
plain validator
JSON Schema

Filters
equal
notEqual
not
lessThan
lessThanOrEqual
greaterThan
greaterThanOrEqual
between
in
startsWith
endsWith
contains
iEqual
iStartsWith
iEndsWith
iContains
exists
or
and
or alternative syntax
and alternative syntax
any filter
all filter
none filter
composite filter
raw sql filter

let orange = require('orange-orm');

let db = orange('mysql://root@localhost/rdbDemo?multipleStatements=true');
//alternatively: let db = orange.mySql('mysql://root@localhost/rdbDemo?multipleStatements=true');

return db.transaction(async () => {
    //transaction will commit after this function
});

connect to Ms Sql
Prerequisites:

npm install tedious

let orange = require('orange-orm');

let db = orange.mssql('server=.;Database=rdbDemo;Trusted_Connection=Yes;Driver={ODBC Driver 17 for SQL Server}');

return db.transaction(async () => {
    //transaction will commit after this function
});

connect to Sybase SAP
Prerequisites:

The adaptive server driver
npm install mssqlnodev8 (yes, even though we are not using ms sql here)

let orange = require('orange-orm');

let db = orange.sap('DRIVER=Adaptive Server Enterprise;server=.;Port=5000;Database=rdbDemo;UID=test;PWD=test');

return db.transaction(async () => {
    //transaction will commit after this function
});

connect to sqlite
Prerequisites:

npm install sqlite3

let orange = require('orange-orm');

let db = orange.sqlite(__dirname + '/db/rdbDemo');

return db.transaction(async () => {
    //transaction will commit after this function
});

pool size

let orange = require('orange-orm');
let poolOptions = {size: 20};

let db = orange('postgres://orange:orange@localhost/rdbdemo', poolOptions);

return db.transaction(async () => {
    //transaction will commit after this function
});

native bindings
(postgres only)
Prerequisites:

npm install pg-native

let orange = require('orange-orm');
let poolOptions = {native: true};

let db = orange('postgres://orange:orange@localhost/rdbdemo', poolOptions);

return db.transaction(async () => {
    //transaction will commit after this function
});

schema
(postgres only)

let orange = require('orange-orm');

let db = orange('postgres://orange:orange@localhost/rdbdemo');
//alternatively: let db = orange.pg('postgres://postgres:postgres@localhost/test');

await db.transaction({schema: ['mySchema', 'otherSchema']}, async () => {
    //or use string for single schema );
    //transaction will commit after this function
});

schema alternative 2
(postgres only)

let orange = require('orange-orm');

let db = orange('postgres://orange:orange@localhost/rdbdemo');
//alternatively: let db = orange.pg('postgres://postgres:postgres@localhost/test');

return db.transaction(async () => {
    await db.schema({schema: ['mySchema', 'otherSchema']});
    //or use string for single schema );
});

end pool

let orange = require('orange-orm');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    //transaction will commit after this function
});
await db.end();
console.log('Pool ended.');

end all pools

let orange = require('orange-orm');

let dbPg = orange('postgres://orange:orange@localhost/rdbdemo');
let dbMySql = orange('mysql://root@localhost/rdbDemo?multipleStatements=true');

await dbPg.transaction(async () => {
    //do pg stuff here
});
await dbMySql.transaction(async () => {
    //do mySql stuff here
});
await orange.end();
console.log('Pools ended.');

logging

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

orange.log(console.log); //will log sql and parameters

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
    customer.name = 'Ringo';
});

getById

let orange = require('orange-orm');
let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cRegdate').date().as('registeredDate');
Customer.column('cIsActive').boolean().as('isActive');
Customer.column('cPicture').binary().as('picture');
Customer.column('cDocument').json().as('document');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
    console.log(await customer.toDto());
});

tryGetById

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cRegdate').date().as('registeredDate');
Customer.column('cIsActive').boolean().as('isActive');
Customer.column('cPicture').binary().as('picture');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let customer = await Customer.tryGetById('a0000000-0000-0000-0000-000000000000');
    console.log(await customer.toDto());
});

tryGetFirst

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let filter = Customer.name.equal('John');
    let customer = await Customer.tryGetFirst(filter);
    console.log(await customer.toDto());
});

join

let orange = require('orange-orm');

let Customer = orange.table('_customer');
let Order = orange.table('_order');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').guid().as('customerId');
Order.join(Customer).by('oCustomerId').as('customer');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('a0000000-a000-0000-0000-000000000000');
    console.log(await order.toJSON({customer: null}));
});

hasMany

let orange = require('orange-orm');
let resetDemo = require('./db/resetDemo');
let inspect = require('util').inspect;

let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let dtos = await order.toDto();
    console.log(inspect(dtos, false, 10));
});

hasOne

let orange = require('orange-orm');
let {inspect} = require('util');

let Order = orange.table('_order');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let dtos = await order.toDto();
    console.log(inspect(dtos, false, 10));
});

composite keys

let orange = require('orange-orm');

let Order = orange.table('_compositeOrder');
let OrderLine = orange.table('_compositeOrderLine');

Order.primaryColumn('oCompanyId').numeric().as('companyId');
Order.primaryColumn('oOrderNo').numeric().as('orderNo');

OrderLine.primaryColumn('lCompanyId').numeric().as('companyId');
OrderLine.primaryColumn('lOrderNo').numeric().as('orderNo');
OrderLine.primaryColumn('lLineNo').numeric().as('lineNo');
OrderLine.column('lProduct').string().as('product');

let line_order_relation = OrderLine.join(Order).by('lCompanyId', 'lOrderNo').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let companyId = 1;
    let orderId = 1001;
    let order = await Order.getById(companyId, orderId);
    console.log(await order.toDto());
});

getById eagerly

let orange = require('orange-orm');
let inspect = require('util').inspect;

let Customer = orange.table('_customer');
let Order = orange.table('_order');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').guid().as('customerId');
Order.join(Customer).by('oCustomerId').as('customer');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let fetchingStrategy = { customer: null }; //alternatively: {customer : {}}
    let order = await Order.getById('a0000000-a000-0000-0000-000000000000', fetchingStrategy);
    console.log(await order.toDto());
    let customer = await order.customer;
    console.log(await customer.toDto());
});

tryGetFirst eagerly

let orange = require('orange-orm');

let Customer = orange.table('_customer');
let Order = orange.table('_order');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').guid().as('customerId');

Order.join(Customer).by('oCustomerId').as('customer');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let filter = Order.customer.name.equal('John');
    let strategy = { customer: null };
    let order = await Order.tryGetFirst(filter, strategy);
    if (order)
        console.log(await order.toDto());
});

toDto

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').string().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');


let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let dto = await order.toDto( /*strategy*/ );
    //default strategy, expand all hasOne and hasMany relations
    console.log(dto);
});

toDto with strategy

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').string().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');


let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let strategy = {customer : null, lines : null, deliveryAddress : null};
    let dto = await order.toDto(strategy);
    console.log(dto);
});

toDto with orderBy

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').string().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let strategy = {
        lines: {
            orderBy: ['product']
            //alternative: orderBy: ['product asc']
        }
    };
    let dto = await order.toDto(strategy);
    console.log(dto);
});

toDto with orderBy descending

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').string().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let strategy = {
        lines: {
            orderBy: ['product desc']
        }
    };
    let dto = await order.toDto(strategy);
    console.log(dto);
});

toDto ignoring columns

let orange = require('orange-orm');

let User = orange.table('_user');
User.primaryColumn('uId').guid().as('id');
User.column('uUserId').string().as('userId');
User.column('uPassword').string().as('password').serializable(false);
User.column('uEmail').string().as('email');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let user = await User.getById('87654400-0000-0000-0000-000000000000');
    console.log(await user.toDto());
    //will print all properties except password
    //because it is not serializable
});

toJSON

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').string().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let json = await order.toJSON( /*strategy*/ );
    //default strategy, expand all hasOne and hasMany relations
    console.log(json);
});

toJSON with strategy

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').string().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let strategy = {customer : null, lines : null, deliveryAddress : null};
    console.log(await order.toJSON(strategy));
});

getMany

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let customers = await Customer.getMany();
    let dtos = await customers.toDto();
    console.log(dtos);
});

getManyDto

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    console.log(await Customer.getManyDto());
});

getMany lazily

let orange = require('orange-orm');
let inspect = require('util').inspect;

let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let orders = await Order.getMany();
    let dtos = await orders.toDto();
    console.log(inspect(dtos, false, 10));
});

getMany eager

let inspect = require('util').inspect;
let orange = require('orange-orm');

let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let emptyFilter;
    let strategy = {
        lines: null
    };
    let orders = await Order.getMany(emptyFilter, strategy);
    let dtos = await orders.toDto();
    console.log(inspect(dtos, false, 10));
});

getManyDto eager

let orange = require('orange-orm');
let inspect = require('util').inspect;

let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let emptyFilter;
    let strategy = {lines : null};
    let orders = await Order.getManyDto(emptyFilter, strategy);
    console.log(inspect(orders, false, 10));
});

limit and offset

let orange = require('orange-orm');
let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let customers = await Customer.getMany(null, {limit: 2, offset: 1});
    let dtos = await customers.toDto();
    console.log(dtos);
});

getMany with orderBy jsonb

let orange = require('orange-orm');

let Order = orange.table('_jOrder');

Order.primaryColumn('oId').guid().as('id');
Order.column('oData').json().as('data');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let strategy = {
        orderBy: ['data->\'orderNo\'']
        //alternative: orderBy: ['data->>\'orderId\' asc']
    };
    let orders = await Order.getMany(null, strategy);
    let dtos = await orders.toDto();
    console.log(dtos);
});

getMany with orderBy jsonb descending

let orange = require('orange-orm');

let Order = orange.table('_jOrder');

Order.primaryColumn('oId').guid().as('id');
Order.column('oData').json().as('data');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let strategy = {
        orderBy: ['data->\'orderNo\' desc']
    };
    let orders = await Order.getMany(null, strategy);
    let dtos = await orders.toDto();
    console.log(dtos);
});

(many)ToDto

let orange = require('orange-orm');
let inspect = require('util').inspect;

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let orders = await Order.getMany();
    let dtos = await orders.toDto( /*strategy*/ );
    //default strategy, expand all hasOne and hasMany relations
    console.log(inspect(dtos, false, 10));
});

(many)ToDto with strategy

let inspect = require('util').inspect;
let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let orders = await Order.getMany();
    let strategy = { customer: null, lines: null, deliveryAddress: null };
    let dtos = await orders.toDto(strategy);
    console.log(inspect(dtos, false, 10));
});

(many)ToJSON

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let orders = await Order.getMany();
    console.log(await orders.toJSON( /*strategy*/ ));
    //default strategy, expand all hasOne and hasMany relations
});

(many)ToJSON with strategy

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').string().as('customerId');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await db.transaction(async () => {
    let orders = await Order.getMany();
    let strategy = {customer : null, lines : null, deliveryAddress : null};
    console.log(await orders.toJSON(strategy));
});

Raw SQL Query

let orange = require('orange-orm');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let result = await orange.query('SELECT DISTINCT oCustomerId AS "customerId" FROM _order');
    console.log(result);
});

Raw SQL Query With Parameters

let orange = require('orange-orm');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let result = await orange.query({
        sql: 'SELECT oOrderNo AS "orderNo" FROM _order WHERE oOrderNo LIKE ?',
        parameters: ['%04']
    });
    console.log(result);
});

Plain validator

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name').validate(validateName);

function validateName(value, row) {
    if (value && value.length > 10)
        throw new Error("Length cannot exceed 10 characters");
}

let db = orange('postgres://orange:orange@localhost/rdbdemo');
try {
    await resetDemo();
    await db.transaction(async () => {
        let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
        customer.name = 'Ringo Starr' //11 Chars. Will throw
    });
} catch (e) {
    console.log(e.message);
    //Length cannot exceed 10 characters
}

JSON Schema
Using ajv

let orange = require('orange-orm');

let documentSchema = {
    "properties": {
        "foo": { "type": "number" },
        "bar": { "type": "number" }
    }
};

let nameSchema = {
    type: "string",
    "maxLength": 20
};

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name').JSONSchema(nameSchema);
Customer.column('cBalance').numeric().as('balance');
Customer.column('cDocument').json().as('document').JSONSchema(documentSchema, {allErrors: true});

let db = orange('postgres://orange:orange@localhost/rdbdemo');
try {
    await resetDemo();
    await db.transaction(async () => {
        let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
        customer.name = 'Ringo Starr' //OK
        customer.document = {foo: 'not a number', bar: 'invalid'}; //violates schema
    });
} catch (e) {
    console.log(e.stack);
    console.log(e.errors);
// [ {  keyword: 'type',
//      dataPath: '.foo',
//      schemaPath: '#/properties/foo/type',
//      params: { type: 'number' },
//      message: 'should be number' },
//  {   keyword: 'type',
//      dataPath: '.bar',
//      schemaPath: '#/properties/bar/type',
//      params: { type: 'number' },
//      message: 'should be number' } ]
}

streaming rows

let orange = require('orange-orm');

let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://postgres:postgres@localhost/test');

let emptyFilter;
let strategy = {
    lines: {
        orderBy: ['product']
    },
    orderBy: ['orderNo'],
    limit: 5,
};

await Order.createReadStream(db, emptyFilter, strategy).on('data', printOrder);

function printOrder(order) {
    let format = 'Order Id: %s, Order No: %s';
    console.log(format, order.id, order.orderNo);
    order.lines.forEach(printLine);
}

function printLine(line) {
    let format = 'Line Id: %s, Order Id: %s, Product: %s';
    console.log(format, line.id, line.orderId, line.product);
}

streaming json

let orange = require('orange-orm');

let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://postgres:postgres@localhost/test');

let emptyFilter;
let strategy = {
    lines: {
        orderBy: ['product']
    },
    orderBy: ['orderNo'],
    limit: 5,
};

await Order.createJSONReadStream(db, emptyFilter, strategy).pipe(process.stdout);

update

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
    customer.name = 'Ringo';
    customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
    console.log(customer.name)
});

insert

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let id = 'abcdef00-0000-0000-0000-000000000000'
    let customer = Customer.insert(id)
    customer.name = 'Paul';
    customer = await Customer.getById(id);
    console.log(customer.name)
});

insertAndForget
If you don't have SELECT access, you want this instead of regular insert.

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let id = 'abcdef00-0000-0000-0000-000000000000'
    await Customer.insertAndForget({id, name: 'Paul'}); //returns empty promise
});

delete

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cRegdate').date().as('registeredDate');
Customer.column('cIsActive').boolean().as('isActive');
Customer.column('cPicture').binary().as('picture');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let customer = await Customer.getById('87654321-0000-0000-0000-000000000000');
    await customer.delete();
});

cascadeDelete

let orange = require('orange-orm');

let Customer = orange.table('_customer');
let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').guid().as('customerId');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');

let orderToCustomer = Order.join(Customer).by('oCustomerId').as('customer');
Customer.hasMany(orderToCustomer).as('orders');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let customer = await Customer.getById('87654399-0000-0000-0000-000000000000');
    await customer.cascadeDelete();
});

bulk delete

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cRegdate').date().as('registeredDate');
Customer.column('cIsActive').boolean().as('isActive');
Customer.column('cPicture').binary().as('picture');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.id.eq('87654321-0000-0000-0000-000000000000');
    await Customer.delete(filter);
});

bulk cascadeDelete

let orange = require('orange-orm');

let Customer = orange.table('_customer');
let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').guid().as('customerId');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');

let orderToCustomer = Order.join(Customer).by('oCustomerId').as('customer');
Customer.hasMany(orderToCustomer).as('orders');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.id.eq('87654399-0000-0000-0000-000000000000');
    await Customer.cascadeDelete(filter);
});

default values

let orange = require('orange-orm');

let buf = Buffer.alloc(10);
buf.write('\u00bd + \u00bc = \u00be', 0);

let Customer = orange.table('_customer');

/*unless overridden, numeric is default 0,
string is default null,
guid is default null,
date is default null,
binary is default null,
boolean is default false,
json is default null
*/

Customer.primaryColumn('cId').guid().as('id').default(null);
Customer.column('cName').string().as('name').default('default name');
Customer.column('cBalance').numeric().as('balance').default(2000);
Customer.column('cRegdate').date().as('registeredDate').default(() => new Date());
Customer.column('cIsActive').boolean().as('isActive').default(true);
Customer.column('cPicture').binary().as('picture').default(buf);
Customer.column('cDocument').json().as('document').default({foo: true});

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let customer = Customer.insert('abcdef02-0000-0000-0000-000000000000')
    console.log(await customer.toDto());
});

conventions

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid(); //property name will also be cId
Customer.column('cName').string(); //property name will also be cName

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let customer = Customer.insert('abcdef01-0000-0000-0000-000000000000')
    customer.cName = 'Paul';
    console.log(await customer.toDto());
});

update a join-relation

let orange = require('orange-orm');

let Customer = orange.table('_customer');
let Order = orange.table('_order');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').guid().as('customerId');

Order.join(Customer).by('oCustomerId').as('customer');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let order = await Order.getById('b0000000-b000-0000-0000-000000000000');
    let yokoId = '12345678-0000-0000-0000-000000000000';
    order.customerId = yokoId;
    let customer = await order.customer;
    console.log(customer.name);
});

update a hasOne-relation

let orange = require('orange-orm');

let Order = orange.table('_order');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

let deliveryAddress_order_relation = DeliveryAddress.hasone(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let address = DeliveryAddress.insert('eeeeeeee-0000-0000-0000-000000000000');
    address.orderId = 'a0000000-a000-0000-0000-000000000000';
    address.name = 'Sgt. Pepper';
    address.street = 'L18 Penny Lane';
    let order = await address.order;
    console.log((await order.deliveryAddress).street);
});

update a hasMany-relation

let orange = require('orange-orm');

let Order = orange.table('_order');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://postgres:postgres@localhost/test');
let orderIdWithNoLines = 'c0000000-c000-0000-0000-000000000000';

await db.transaction(async () => {
    let orderIdWithNoLines = 'c0000000-c000-0000-0000-000000000000';

    let line = OrderLine.insert('eeeeeeee-0001-0000-0000-000000000000');
    line.orderId = orderIdWithNoLines;
    line.product = 'Roller blades';

    let line2 = OrderLine.insert('eeeeeeee-0002-0000-0000-000000000000');
    line2.orderId = orderIdWithNoLines;
    line2.product = 'Helmet';

    let order = await line.order;
    let lines = await order.lines;
    console.log('Number of lines: ' + lines.length);
});

row lock
(not in sqlite)

let orange = require('orange-orm');

let Customer = orange.table('_customer');
Customer.primaryColumn('cId').guid().as('id');
Customer.column('cBalance').numeric().as('balance');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await showBalance();
await updateConcurrently();
await showBalance();

function showBalance() {
    return db.transaction(async () => {
        let customer = await Customer.getById.exclusive('a0000000-0000-0000-0000-000000000000');
        console.log('Balance: ' + customer.balance);
    });
}

function updateConcurrently() {
    let concurrent1 = db.transaction(async () => {
        let customer = await Customer.getById.exclusive('a0000000-0000-0000-0000-000000000000');
        customer.balance += 100;
    });

    let concurrent2 = db.transaction(async () => {
        let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
        customer.balance += 100;
    });

    return Promise.all([concurrent1, concurrent2]);
}

transaction lock
(postgres only)

let orange = require('orange-orm');

let Customer = orange.table('_customer');
Customer.primaryColumn('cId').guid().as('id');
Customer.column('cBalance').numeric().as('balance');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await showBalance();
await updateConcurrently();
await showBalance();

function showBalance() {
    return db.transaction(async () => {
        let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
        console.log('Balance: ' + customer.balance);
    });
}

function updateConcurrently() {
    let concurrent1 = db.transaction(async () => {
        await db.lock("12345");
        let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
        customer.balance += 100;
    });

    let concurrent2 = db.transaction(async () => {
        await db.lock("12345");
        let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
        customer.balance += 100;
    });

    return Promise.all([concurrent1, concurrent2]);
}

session lock
(postgres only) The function will only be run when a lock has been acquired. The function is awaited for any return value, then lock is released. The function will then forward the return value from the supplied function.

let orange = require('orange-orm');

let Customer = orange.table('_customer');
Customer.primaryColumn('cId').guid().as('id');
Customer.column('cBalance').numeric().as('balance');

let db = orange('postgres://orange:orange@localhost/rdbdemo');

await showBalance();
await updateConcurrently();
await showBalance();

function showBalance() {
    return db.transaction(async () => {
        let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
        console.log('Balance: ' + customer.balance);
    });
}

function updateConcurrently() {
    let concurrent1 = db.transaction(async () => {
        await db.lock("12345", runInLock);
        //lock released
        //do other stuff..
    });

    let concurrent2 = db.transaction(async () => {
        await db.lock("12345", runInLock);
        //lock released
        //do other stuff..
    });

    async function runInLock() {
        let customer = await Customer.getById('a0000000-0000-0000-0000-000000000000');
        customer.balance += 100;
    }

    return Promise.all([concurrent1, concurrent2]);
}

equal

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.equal('John');
    //same as Customer.name.eq('John');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

notEqual

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.notEqual('John');
    //same as Customer.name.ne('John');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

not

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.equal('John').not();
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

lessThan

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.balance.lessThan(5000);
    //same as Customer.balance.lt(5000);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

lessThanOrEqual

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.balance.lessThanOrEqual(8123);
    //same as Customer.balance.le(8123);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

greaterThan

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.balance.greaterThan(5000);
    //same as Customer.balance.gt(5000);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

greaterThanOrEqual

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.balance.greaterThanOrEqual(8123);
    //same as Customer.balance.ge(8123);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

between

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.balance.between(3000, 8123);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

in

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.in(['John', 'Yoko']);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

startsWith

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.startsWith('Jo');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

endsWith

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.endsWith('nny');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

contains

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.contains('ohn');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

iEqual
(postgres only)

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.iEqual('jOhN');
    //same as Customer.name.iEq('jOhN');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

iStartsWith
(postgres only)

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.iStartsWith('jo');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

iEndsWith
(postgres only)

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.iEndsWith('nNy');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

iContains
(postgres only)

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Customer.name.iContains('oHn');
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

exists

let orange = require('orange-orm');

let Order = orange.table('_order');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Order.deliveryAddress.exists();
    let orders = await Order.getMany(filter);
    console.log(await orders.toDto());
});

or

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let john = Customer.name.equal('John');
    let yoko = Customer.name.equal('Yoko');
    let filter = john.or(yoko);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

and

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cIsActive').boolean().as('isActive');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let isActive = Customer.isActive.equal(true);
    let highBalance = Customer.balance.greaterThan(8000);
    let filter = isActive.and(highBalance);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

or alternative syntax

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let john = Customer.name.equal('John');
    let yoko = Customer.name.equal('Yoko');
    let filter = orange.filter.or(john).or(yoko);
    //alternatively orange.filter.and(john).or(yoko);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

and alternative syntax

let orange = require('orange-orm');

let Customer = orange.table('_customer');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cIsActive').boolean().as('isActive');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cName').string().as('name');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let isActive = Customer.isActive.equal(true);
    let highBalance = Customer.balance.greaterThan(8000);
    let filter = orange.filter.and(isActive).and(highBalance);
    //alternatively orange.filter.or(isActive).and(highBalance);
    let customers = await Customer.getMany(filter);
    console.log(await customers.toDto());
});

any filter

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('id').guid();
Order.column('customerId').guid();
Order.column('orderNo').string();

Customer.primaryColumn('id').guid();
Customer.column('isActive').boolean();
Customer.column('balance').numeric();
Customer.column('name').string();

OrderLine.primaryColumn('id').guid();
OrderLine.column('orderId').guid();
OrderLine.column('product').string();

Order.join(Customer).by('customerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('orderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange.sqlite(__dirname + '/../db/rdbDemo');

await db.transaction(async () => {
    let filter = Order.lines.any( line => {
        let aFilter = line.product.contains('a');
        let cFilter = line.product.startsWith('c');
        return aFilter.and(cFilter);
    });
    let orders = await Order.getMany(filter);
    console.log(inspect(await orders.toDto(), false, 10));
});

all filter

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('id').guid();
Order.column('customerId').guid();
Order.column('orderNo').string();

Customer.primaryColumn('id').guid();
Customer.column('isActive').boolean();
Customer.column('balance').numeric();
Customer.column('name').string();

OrderLine.primaryColumn('id').guid();
OrderLine.column('orderId').guid();
OrderLine.column('product').string();

Order.join(Customer).by('customerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('orderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange.sqlite(__dirname + '/../db/rdbDemo');

await db.transaction(async () => {
    let filter = Order.lines.all( line => {
        return line.product.contains('sub');
    });
    let orders = await Order.getMany(filter);
    console.log(inspect(await orders.toDto(), false, 10));
});

none filter

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('id').guid();
Order.column('customerId').guid();
Order.column('orderNo').string();

Customer.primaryColumn('id').guid();
Customer.column('isActive').boolean();
Customer.column('balance').numeric();
Customer.column('name').string();

OrderLine.primaryColumn('id').guid();
OrderLine.column('orderId').guid();
OrderLine.column('product').string();

Order.join(Customer).by('customerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('orderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange.sqlite(__dirname + '/../db/rdbDemo');

await db.transaction(async () => {
    let filter = Order.lines.none( line => {
        return line.product.contains('sub');
    });
    //alternatively: Order.lines.product.contains('sub').not();
    let orders = await Order.getMany(filter);
    console.log(inspect(await orders.toDto(), false, 10));
});

sub filter

let orange = require('orange-orm');

let Order = orange.table('_order');
let DeliveryAddress = orange.table('_deliveryAddress');

Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');

DeliveryAddress.primaryColumn('dId').guid().as('id');
DeliveryAddress.column('dOrderId').string().as('orderId');
DeliveryAddress.column('dName').string().as('name');
DeliveryAddress.column('dStreet').string().as('street');

let deliveryAddress_order_relation = DeliveryAddress.join(Order).by('dOrderId').as('order');
Order.hasOne(deliveryAddress_order_relation).as('deliveryAddress');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = Order.deliveryAddress.street.startsWith('Node');
    let orders = await Order.getMany(filter);
    console.log(await orders.toDto());
});

composite filter

let orange = require('orange-orm');

let Order = orange.table('_order');
let Customer = orange.table('_customer');
let OrderLine = orange.table('_orderLine');

Order.primaryColumn('oId').guid().as('id');
Order.column('oCustomerId').guid().as('customerId');
Order.column('oOrderNo').string().as('orderNo');

Customer.primaryColumn('cId').guid().as('id');
Customer.column('cIsActive').boolean().as('isActive');
Customer.column('cBalance').numeric().as('balance');
Customer.column('cName').string().as('name');

OrderLine.primaryColumn('lId').guid().as('id');
OrderLine.column('lOrderId').guid().as('orderId');
OrderLine.column('lProduct').string().as('product');

Order.join(Customer).by('oCustomerId').as('customer');

let line_order_relation = OrderLine.join(Order).by('lOrderId').as('order');
Order.hasMany(line_order_relation).as('lines');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let isActive = Order.customer.isActive.eq(true);
    let didOrderCar = Order.lines.product.contains('car');
    let filter = isActive.and(didOrderCar);
    //alternatively orange.filter.and(isActive).and(didOrderCar);
    let orders = await Order.getMany(filter);
    console.log(inspect(await orders.toDto(), false, 10));
});

raw sql filter

let inspect = require('util').inspect;
let orange = require('orange-orm');

let Order = orange.table('_order');
Order.primaryColumn('oId').guid().as('id');
Order.column('oOrderNo').string().as('orderNo');
Order.column('oCustomerId').guid().as('customerId');

let Customer = orange.table('_customer');
Customer.primaryColumn('cid').guid().as('id');
Customer.column('cName').string().as('name');
Customer.column('cBalance').string().as('balance');
Customer.column('cIsActive').boolean().as('isActive');

let orderCustomerJoin = Order.join(Customer).by('oCustomerId').as('customer');
Customer.hasMany(orderCustomerJoin).as('orders');

let db = orange('postgres://postgres:postgres@localhost/test');

await db.transaction(async () => {
    let filter = {
        sql: 'exists (select 1 from _customer where _customer.cId = oCustomerId and _customer.cBalance > 3000 and _customer.cName LIKE ?)',
        parameters: ['%o%']
    };
    let orders = await Order.getMany(filter);
    let strategy = { customer: null }
    console.log(await orders.toDto(strategy));
});

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

docs.md

docs.md

All examples below are found at npmjs.org/package/rdb-demo.
This is the Classic Documentation. Are you looking for the Modern Typescript Documentation ?

Contents

Files

docs.md

Latest commit

History

docs.md

File metadata and controls

All examples below are found at npmjs.org/package/rdb-demo. This is the Classic Documentation. Are you looking for the Modern Typescript Documentation ?

Contents

All examples below are found at npmjs.org/package/rdb-demo.
This is the Classic Documentation. Are you looking for the Modern Typescript Documentation ?