AUL
Elegant database queries executor
Introduction
Because every web developer must implement his own template engine and orm, this package was born. ORM is recently the most
popular way of working with data. It essentially great, but why everyone should think that tables in database is objects
in the code - I don't know. Mapping going to be very complex procedure when you want to make something
not usual and not common for every database engine.
Quick example, if you worked with loopbackjs you probably know that you can't make joins, you can't use complex sql queries
and map results automatically. SailsJS also has no transaction support! You need to make workaround each time you need to
use SQL. So the question is - why do you ever need this abstraction if you limited with database common features. If you
want to switch to another database (postgresql -> mongo for example) you will definitely need to rewrite you model, and it's
usage. And if you need to change your orm provider - you will need to change a lot of code, because each orm has it's own
model lifecycle.
Complexity of ORM/ODM solves many problems to hide that DATA IS STORED IN DATABASE AS TABLE OR DOCUMENT. And each such
library makes it's own approach that usually it is hard to test queries, migrate from one orm to another, use SQL or
use your database engine's features. And I think DBA guys is not very happy with queries that orm usually builds.
Installation
npm install aul --save
or
yarn add aul
Usage
Let's make a quick example of how this yet another orm killer
can help you with your database logic.
NOTE 1: you should know - your data is stored in tables, documents or any other storage you use in your app. And you access
it by using it's natural syntax. If you need JOIN 2 tables, you may do this.
NOTE 2: Aul is general library with some basic abstractions around data queries. So you need to add some adapter
for your
database. (in the following example I will use knex adapter)
const {Criteria, Executor} = require("aul");
const KnexAdapter = require("aul-knex");
const MyModel = require("./my-model");
const criteria = new Criteria("table_name_or_collection_name");
criteria.addFilter("active", {active: true});
criteria.addMapper((result) => MyModel.from(result));
const executor = new Executor({path: "path/to/the/folder/with/filters"});
executor.setAdapter(new KnexAdapter({
client: "pg",
connection: process.env.DATABASE_URL,
searchPath: "public"
}));
executor.execute(criteria);
So in this little example, we built custom criteria to fetch data from table called table_name_or_collection_name
and
also we defined that we want to filter all records by passing active filter. Filter is a simple middleware function that
mutates query builder object in way it needs. For example, we have a query builder that holds SELECT * FROM table
and after proceeding
"active" filter, query will looks like SELECT * FROM table WHERE active = true
.
As you may see you should implement your filter by yourself and executor must know where you filters are located.
KnexAdapter object is created by passing the same options that knex library requires. So nothing special here.
Now let's see how your active filter should look like:
module.exports = ({active = true}) => {
return async (knex, next) => {
knex.andWhere('active', '=', !!active);
await next();
};
};
As you can see nothing special either. You just need to use knex query builder.
So what does it give to you?
- It gives you freedom. Now you do not need to think how to join or use some complex SQL, you simply use it.
- It gives you a way to abstract from SQL strings, and use query builder you want, you can use mongo client object if you want
- You can test each filter independently and use them like configuration parts of you criteria.
- You can migrate from one database to another by implementing filters for another adapter. But you do not need to change
criteria definition itself.
- It is very lightweight abstraction without any complex logic inside. Filters are running like regular middlewares one by one,
and they can be asynchronous.
What about objects? Do you need mapper?
Sure. You need mapper. But you can to choose. Aul has very limited approach to map your data into pure object.
You should provide your mapper in criteria.setMapper()
.
I will show example with mapping using muesli
library.
const Mapper = require("aul-muesli");
const Model = require("muesli");
class Author extends Model {
constructor() {
supper({
name: {
filter: "string",
constraints: [required("group1")]
},
lastname: {
filter: "string",
constraints: [required()]
},
fullname: {
filter: function (deps) {
return deps.join(" ");
},
computed: ["name", "lastname"]
}
});
}
}
class Book extends Model {
constructor() {
super({
title: {
filter: "string"
}
});
}
}
const map = {
"column": "field",
"column2": false,
"authors": (authors) => authors.map(a => Author.from(a))
};
criteria.addMapper(map);
criteria.addMapper((result) => Book.from(result));
let results = await executor.execute(criteria);
You can have any amount of mappers. But remember order matters!
What about update/insert queries?
As you can see objects that you get in the end is not the same magic objects that you get in any ORM. You can't make
save() or update(). Thus, you need to construct your own algorithm, where you can control batching, field passed etc.
Example,
class UpdateCriteria extends Criteria {
constructor() {
super("table_name");
this.addFilter("update", {active: true, status: "UPDATED"});
this.addFilter("active", {active: false});
}
}
So now you see that UpdateCriteria is nothing else than regular criteria, where you can use your common filters.
License
Aul is released under the MIT license.
Donate