Lambda ORM
IMPORTANT: the library is in an Alpha version!!!
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:
- Queries written in lambda expressions
- Definition of the schema through configuration.
Features
- Expressions
- Simple expressions based on javascript lambda.
- String expressions
- Implicit joins and group by
- Eager loading using the Include() method.
- Metadata
- Schema
- Decoupling the business model from the data layer
- Configuration in json or yml formats
- mapping
- Extends entities and schemas
- Environment variables
- CLI
- Init and update commands
- Run expressions
- Sync and drop schema
- Imports and exports
- Repositories
- Indices
- 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.
Example:
User.map(p => {name: p.lastname + ', ' + p.firstname })
The engine also allows us to write the expressions in a string.
'User.map(p => {name: p.lastname + \', \' + p.firstname })'
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.
Schema
It is the nexus between the business model and the persistence of the data.
The classes that represent the business model are completely clean, without any attributes that link them to persistence.
All the configuration necessary to resolve the relationship between the business model and persistence is done in the schema, which is configuration.
This configuration can be done in a yaml, json file or passed as a parameter when initializing the ORM.
The purpose of this ORM is to use javascript syntax to write query expressions. Which will be translated into the SQL statement corresponding to the database engine.
Examples:
Simple
The schema defines how the entities of the model are mapped with the database tables.

lab
Extend entities
In this scheme we can see how to extend entities.

To understand an entity we use the extends attribute in the definition of the entity
entities:
- name: Positions
abstract: true
properties:
- name: latitude
length: 16
- name: longitude
length: 16
- name: Countries
extends: Positions
lab
Extend schemas
In this scheme we can see how to extend the schema.

We use the extends attribute in the definition of the schema to extend it.
schemas:
- name: countries
- name: countries2
extends: countries
lab
One schema related multiples databases
This schema has two entities that are in different databases.

The database attribute is used in the entity to be able to specify that an entity is in a database other than the default of the schema.
- name: States
database: mydb2
lab
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
Expressions:
To write the expressions we use methods, operators and functions.
Methods:
Starting from the entity we have the following methods.
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:
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:
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 |
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) => {
const orderId = await tr.lambda(() => Orders.insert().include(p => p.details), order)
const result = await tr.lambda((id:number) => Orders.filter(p => p.id === id).include(p => p.details), { id: orderId })
const order2 = result[0]
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)
const order3 = await tr.lambda((id:number) => Orders.filter(p => p.id === id).include(p => p.details), { id: orderId })
console.log(JSON.stringify(order3))
const deleteCount = await tr.lambda(() => Orders.delete().include(p => p.details), order3[0])
console.log(deleteCount)
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
Config
When the orm.init () method is invoked, the initialization of the orm will be executed from the configuration.
This configuration contains the main sections, paths, databases and schemas.
- In the app section, the general configuration of the application is set, such as the main paths, default database, etc.
- In the databases section the databases to which we are going to connect and which is the corresponding schema are defined
- In the section of diagrams, the entities, their relationships and their mapping with the database are defined.
Example:
{
"app:": { "src": "src", "data": "data" ,"models":"models","defaultDatabase": "mydb" },
"databases": [
{
"name": "mydb",
"dialect": "mysql",
"schema": "location",
"connection": "$CNN_MYSQL"
}
],
"schemas": [
{
"name": "location",
"enums": [],
"entities": [
{
"name": "Country",
"mapping": "COUNTRY",
"primaryKey": [ "id" ],
"uniqueKey": [ "name" ],
"properties": [
{ "name": "id", "mapping": "ID", "type": "integer","nullable": false },
{ "name": "name","mapping": "NAME", "nullable": false, "type": "string", "length": 127 },
{ "name": "alpha2","mapping": "ALPHA_2", "nullable": false,"type": "string","length": 2 },
{ "name": "alpha3", "mapping": "ALPHA_3", "nullable": false, "type": "string", "length": 3 }
]
}
]
}
]
}
There are the following options to define the settings.
-
Invoke the orm.init () method without the first argument and write this configuration in a file called lambdaorm.json or lambdaorm.yaml in the root of the project.
according to the lambdaorm extension you will know how to read it.
-
Invoke the orm.init () method, pass as an argument the path where the configuration file is located.
This path must include the extension .yaml or .json since this way we will know how to read it.
-
Invoke the orm.init () method passing the configuration as a json object as an argument
Example passing the path of the configuration file:
import { orm } from 'lambdaorm'
(async () => {
await orm.init('/home/my/db/book.yaml')
try {
const result = await orm.expression('Loan.map(p=>{user:p.reader.name,book:p.book.title,date:p.date})').execute('mydb')
console.log(result)
} catch (error) {
console.log(error)
} finally {
await orm.end()
}
})()
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.
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') |
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
Documentation
Labs
Lab northwind
In this laboratory we will see:
Creating the northwind sample database tables and loading it with sample data. This database presents several non-standard cases such as: - Name of tables and fields with spaces - Tables with composite primary keys - Tables with autonumeric ids and others with ids strings
Since this is the database that was used for many examples and unit tests, you can test the example queries that are in the documentation. We will also see some example queries to execute from CLI
source code
Lab 01
In this laboratory we will see:
- How to use the Lambdaorm-cli commands
- how to create a project that uses lambda ORM
- How to define a schema
- how to run a bulckInsert from a file
- how to export data from a schema
- how to import data into a schema from a previously generated export file
source code
Lab 02
In this laboratory we will see:
- how to create a project that uses lambda ORM
- How to define a schema
- how to extend entities using abstract entities
- How to insert data from a file.
- how to run queries from cli to perform different types of queries
source code
Lab 03
In this laboratory we will see:
- How to insert data from a file to more than one table.
- how to extend entities using abstract entities
- how to extend a schema to create a new one, overwriting the mapping
- how to work with two schemas and databases that share the same model
- how to use imported data from one database to import it into another
source code
Lab 04
In this laboratory we will see:
- How to insert data from a file to more than one table.
- how to extend entities using abstract entities
- how to define a schema that works with entities in different databases
- how to run a bulkinsert on entities in different databases
- how to export and import entity data in different databases
source code