lambdaorm

Lambda ORM

IMPORTANT: the library is in an Alpha version!!!

Is an ORM that uses the syntax of lambda expressions in javascript to write the expressions, which are translated into sentences according to the dialect of the database.

Features

• Data mapper pattern
• Repositories
• Indices
• Transactions
• Using multiple database connections
• Expressions
  • Simple expressions based on javascript lambda.
  • String expressions
  • Implicit joins and group by
  • Eager loading using the Include() method.
• Configuration
  • Schema declaration in models or separate configuration files
  • Configuration in json or yml formats
  • Environment variables
• CLI
  • Init and update commands
  • Run expressions
  • Sync and drop schema
  • Imports and exports

Example:

import { orm } from 'lambdaorm'

(async () => {
	await orm.init()
	const expression = (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(expression).execute('mysql')
	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 

You could also write the expression to a string.

In this case you should use the orm.expression method instead of orm.lambda

import { orm } from 'lambdaorm'

(async () => {
	await orm.init()
	const expression = `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(expression).execute('mysql')
	console.log(JSON.stringify(result, null, 2))
	await orm.end()
})()

Queries:

Starting from the entity we have the following methods to assemble the sentences.

Operator	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:

• Arithmectic
• Assignment
• Bitwise
• Comparison
• Logical
• Array

Functions

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

below access to their documentation:

• Numeric
• String
• Datetime
• Convert
• Nullable
• General
• Sort
• Conditionals
• Group
• Metadata

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.

More info

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('mysql')
	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"	}	}
		}
	]
}
]]

Using the ORM

To work with the orm we do it through a singleton object called "orm".

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

to execute we have two methods, one lambda to which the expression is passed as a javascript lambda function and another expression to which we pass a string containing the expression.

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

But if the expression comes to us from another side, UI, CLI command, persisted, etc, in this case we will use the expression in a string

Example:

import { orm } from 'lambdaorm'

(async () => {
await orm.init()
try {
	//writing the statement as a lambda expression in javascript
	const query = (id: number) => Orders.filter(p => p.id === id).include(p => p.details)
	let result = await orm.lambda(query).execute('source',{ id: 10248 })
	console.log(JSON.stringify(result, null, 2))

	//writing the statement as a lambda expression to a text string
	const expression = 'Orders.filter(p => p.id === id).include(p => p.details)'
	result = await orm.expression(expression).execute('source',{ id: 10248 })
	console.log(JSON.stringify(result, null, 2))

} catch (error) {
	console.log(error)
} finally {
	await orm.end()
}
})()

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('source', 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)
}
})()

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 paths section the src and data paths are defined.
• 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:

{
  "paths": { "src": "src", "data": "data"  },
  "databases": [
    {
      "name": "lab_01",
      "dialect": "mysql",
      "schema": "lab_01",
      "connection": "$ORM_CNN_MYSQL"
    }
  ],
  "schemas": [
    {
      "name": "lab_01",
      "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()
	}
})()

• 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

install Lambda ORM

npm install lambdaorm 

And add the packages according to the databases to be used

npm install mysql2 
npm install mariadb
npm install pg # Postgres 
npm install tedious # Microsoft SQL Server
npm install oracledb

to use cli commands install package globally

npm install lambdaorm -g

CLI

Command	Description
version	Prints lambdaorm version this project uses.	more info
init	Generates lambdaorm project structure.	more info
updaye	update model, packages and project structure.	more info
sync	Syncronize database.	more info
run	Run an expression lambda or return information	more info
export	Export data from a database	more info
import	Import data from file to database	more info
drop	Removes all database objects but not the database.	more info

• Lab CLI

Documentation

• Wiki
• Source Code

Labs

• CLI