LambdaORM is an intermediary between the business model and the persistence of the data. Completely decoupling the business model from the data layer. For this use:

Query Language written in javascript lambda expressions.
Schema Configuration.

Features

Query Language
- Simple query language based on javascript lambda expressions.
- Can write the expression as javascript code or as a string
- Crud clauses
- Implicit joins and group by
- Eager loading using the Include() method.
- Metadata from query expression
Schema Configuration
- Decoupling the business model from phisical model
- Configuration in json or yml formats
- Definition of mappins to map the business model with the physical model
- Extends entities
- Environment variables
- define index, unique key, constraints
CLI
- Init and update commands
- Run expressions
- Sync and drop schema
- Imports and exports
Repositories
Transactions
Using multiple database connections

Lambda expressions

The lambda expressions are written based on the programming language itself, referring to the business model, completely abstracting from the database language and its structure.

The purpose is to use javascript syntax to write query expressions. Which will be translated into the SQL statement corresponding to the database engine.

Example:

Countries.page(1,10).include(p => p.states.map(p=> [p.name,p.latitude,p.longitude] ))

The engine also allows us to write the expressions in a string.

'Countries.page(1,10).include(p => p.states.map(p=> [p.name,p.latitude,p.longitude] ))'

Advantage:

Use of the same programming language.
It is not necessary to learn a new language.
Easy to write and understand expressions.
Use of the intellisense offered by the IDE to write the expressions.
Avoid syntax errors.

Expressions:

To write the expressions we use methods, operators and functions.

Methods:

Starting from the entity we have the following methods.

Method	Description	SQL Equivalent
filter	To filter the records.	WHERE	more info
having	To filter on groupings.	HAVING	more info
map	To specify the fields to return.	SELECT	more info
distinct	to specify the fields to return by sending duplicate records.		more info
first	returns the first record	SELECT + ORDER BY + LIMIT	more info
last	returns the last record	SELECT + ORDER BY DESC + LIMIT	more info
take	returns one record	SELECT + LIMIT	more info
sort	To specify the order in which the records are returned.	ORDER BY	more info
page	To paginate.	LIMIT (MySQL)	more info
include	To get records of related entities		more info
insert	To insert records	INSERT	more info
update	To update records always including a filter	UPDATE with WHERE	more info
updateAll	to be able to update all the records of an entity	UPDATE without WHERE	more info
delete	To delete records always including a filter	DELETE with WHERE	more info
deleteAll	To be able to delete all records of an entity	DELETE without WHERE	more info
bulkinsert	to insert records in bulk	INSERT	more info

There are no methods for the INNER JOIN clause since it is deduced when navigating through the relations of a property.

There are no methods for the GROUP BY clause since this is deduced when grouping methods are used.

Operators

The operators used are the same as those of javascript.

below access to their documentation:

Category	Operators
Arithmectic	-, +, , /, *, //, %	more info
Bitwise	~,&,^,<<,>>	more info
Comparison	==, ===, !=, !==, >, <, >=, <=	more info
Logical	!, &&	more info
Array	[]	more info

Functions

In the case of functions, some correspond to javascript functions and others are specific to sql

below access to their documentation:

Category	functions
Numeric	abs,ceil,cos,exp,ln,log,remainder,round,sign,sin,tan,trunc...	more info
String	chr,lower,lpad,ltrim,replace,rpad,rtrim,substr,trim,upper,concat...	more info
Datetime	curtime,today,now,time,date,datetime,year,month,day,weekday,hours...	more info
Convert	toString,toJson,toNumber	more info
Nullable	nvl,nvl2,isNull,isNotNull	more info
General	as,distinct	more info
Sort	asc,desc	more info
Conditionals	between,includes	more info
Group	avg,count,first,last,max,min,sum	more info
Metadata	user,source	more info

Usage

To work with the orm we can do it using the singleton object called "orm" or using repositories.

Objeto orm

This orm object acts as a facade and from this we access all the functionalities.

To execute a query we have two methods

Lambda method:

This method receives the expression as a javascript lambda function.

If we are going to write the expression in the code, we must do it with the lambda function, since in this way we will have the help of intellisense and we will make sure that the expression does not have syntax errors.

import { orm } from 'lambdaorm'

(async () => {
	await orm.init()	
	const exp = (country:string)=>
				Products.filter(p => (p.price > 5 && p.supplier.country == country) || (p.inStock < 3))
						.having(p => max(p.price) > 50)
						.map(p => ({ category: p.category.name, largestPrice: max(p.price) }))
						.sort(p => desc(p.largestPrice))

	const result = await orm.lambda(exp).execute({ country: 'USA' },'mydb')
	console.log(JSON.stringify(result, null, 2))
	await orm.end()
})()

where the SQL equivalent of the expression is:

SELECT c.CategoryName AS `category`, MAX(p.UnitPrice) AS `largestPrice` 
FROM Products p 
INNER JOIN Suppliers s ON s.SupplierID = p.SupplierID 
INNER JOIN Categories c ON c.CategoryID = p.CategoryID 
WHERE ((p.UnitPrice > 5 AND s.Country = ?) OR p.UnitsInStock < 3) 
GROUP BY c.CategoryName 
HAVING MAX(p.UnitPrice) > 50 
ORDER BY `largestPrice` desc

Expression method:

This method receives the expression as a text string.

if the expression comes from somewhere else, UI, CLI command, persisted, etc, in this case we will use the expression in a string

import { orm } from 'lambdaorm'

(async () => {
	await orm.init()
	const country = 'USA'
	const exp = `Products.filter(p => (p.price > 5 && p.supplier.country == country) || (p.inStock < 3))
						.having(p => max(p.price) > 50)
						.map(p => ({ category: p.category.name, largestPrice: max(p.price) }))
						.sort(p => desc(p.largestPrice))`

	const result = await orm.expression(exp).execute({ country: country },'mydb')
	console.log(JSON.stringify(result, null, 2))
	await orm.end()
})()

Repositories

Repositories are associated with an entity and have several methods to interact with it.

Example:

import { orm } from 'lambdaorm'
import { ProductRespository } from './models/northwind'

(async () => {
	await orm.init()
	const productRepository = new ProductRespository('mydb')
	const country = 'USA'
	const result = awaitproductRepository.query().filter(p => (p.price > 5 && p.supplier.country === country) || (p.inStock < 3))
			.having(p => max(p.price) > 50)
			.map(p => ({ category: p.category.name, largestPrice: max(p.price) }))
			.sort(p => desc(p.largestPrice))
			.execute({ country: country })
	
	console.log(JSON.stringify(result, null, 2))
	await orm.end()
})()

More info

Includes:

LambdaORM includes the Include method to load related entities, both for OnetoMany, manyToOne and oneToOne relationships.

We can also apply filters or bring us some fields from the related entities.

For each include, a statement is executed bringing all the necessary records, then the objects with relationships are assembled in memory. In this way, multiple executions are avoided, considerably improving performance.

Includes can be used in selects, insert, update, delete, and bulckinsert.

Example:

import { orm } from 'lambdaorm'
(async () => {
	await orm.init()
	const expression = (id:number) => Orders
		.filter(p => p.id === id)
		.include(p => [p.customer.map(p => ({ name: p.name, address: concat(p.address, ', ', p.city, ' (', p.postalCode, ')  ', p.country) })),
			p.details.include(p => p.product
				.include(p => p.category.map(p => p.name))
				.map(p => p.name))
				.map(p => [p.quantity, p.unitPrice])])
		.map(p => p.orderDate)

	const result = await orm.lambda(expression).execute('mydb')
	console.log(JSON.stringify(result, null, 2))
	await orm.end()
})()

The previous sentence will bring us the following result:

[[
{
	"orderDate": "1996-07-03T22:00:00.000Z",
	"customer": { "name": "Vins et alcools Chevalier", "address": "59 rue de l'Abbaye, Reims (51100)  France"
	},
	"details": [
		{
			"quantity": 12, "unitPrice": 14,
			"product": { "name": "Queso Cabrales", 	"category": { "name": "Dairy Products"}
			}
		},
		{
			"quantity": 10, "unitPrice": 9.8,
			"product": { "name": "Singaporean Hokkien Fried Mee",	"category": { "name": "Grains/Cereals" 	}}
		},
		{
			"quantity": 5, "unitPrice": 34.8,
			"product": { "name": "Mozzarella di Giovanni", "category": { "name": "Dairy Products"	}	}
		}
	]
}
]]

More info

Transactions

To work with transactions use the orm.transaction method.

This method receives the name of the database as the first argument and as the second it is a callback function that does not pass a Transaction object, in the example we name it tr.

We use the lambda or expression method to execute the sentence (as we found it written).

When we reach the end and return the callback, the orm will internally execute the COMMIT, if there is an exception, internally the ROLLBACK will be executed

Example

import { orm } from 'lambdaorm'

(async () => {

const order={customerId:"VINET",employeeId:5,orderDate:"1996-07-03T22:00:00.000Z",requiredDate:"1996-07-31T22:00:00.000Z",shippedDate:"1996-07-15T22:00:00.000Z",shipViaId:3,freight:32.38,name:"Vins et alcools Chevalier",address:"59 rue de l-Abbaye",city:"Reims",region:null,postalCode:"51100",country:"France",details:[{productId:11,unitPrice:14,quantity:12,discount:!1},{productId:42,unitPrice:9.8,quantity:10,discount:!1},{productId:72,unitPrice:34.8,quantity:5,discount:!1}]};

try {
orm.transaction('mydb', async (tr) => {
	// create order
	const orderId = await tr.lambda(() => Orders.insert().include(p => p.details), order)
	// get order
	const result = await tr.lambda((id:number) => Orders.filter(p => p.id === id).include(p => p.details), { id: orderId })
	const order2 = result[0]
	// updated order
	order2.address = 'changed 59 rue de l-Abbaye'
	order2.details[0].discount = true
	order2.details[1].unitPrice = 10
	order2.details[2].quantity = 7
	const updateCount = await tr.lambda(() => Orders.update().include(p => p.details), order2)
	console.log(updateCount)
	// get order
	const order3 = await tr.lambda((id:number) => Orders.filter(p => p.id === id).include(p => p.details), { id: orderId })
	console.log(JSON.stringify(order3))
	// delete
	const deleteCount = await tr.lambda(() => Orders.delete().include(p => p.details), order3[0])
	console.log(deleteCount)
	// get order
	const order4 = await tr.lambda((id:number) => Orders.filter(p => p.id === id).include(p => p.details), { id: orderId })
	console.log(JSON.stringify(order4))
})
} catch (error) {
	console.log(error)
}
})()

More info

Metadata

Lambda ORM has the following methods to extract metadata information from expressions.

To execute these methods it is not necessary to connect to the database.

method	Description	Path
parameters	returns the list of parameters in the expression	orm.lambda(query).parameters(schema)
model	returns the model of the result in an execution	orm.lambda(query).model(schema)
metadata	returns the metadata of the expression	orm.lambda(query).metadata(schema)
sentence	returns the sentence in the specified dialect	orm.lambda(query).sentence('mysql','northwind')

more info

Installation

npm install lambdaorm

CLI

Install the package globally to use the CLI commands to help you create and maintain projects

npm install lambdaorm-cli -g

CLI package