Introducing moron.js – a new ORM for Node.js

A couple of weeks ago I released moron.js – the first public version of a new ORM for Node.js that Vincit has been using internally in various projects during the last year and a half. I started writing my own ORM after using basically all mature Node.js ORMs out there. While most of them do the basic stuff well, I repeatedly got frustrated with some things:

  • Writing more complex SQL queries is difficult, ugly or impossible.
  • Transaction support is either missing or error-prone. In most cases, you have to pass some kind of transaction object to each query you make. Forget one and bad things will happen to good queries.
  • Database schema is usually automatically created based on the models. Again we are at the mercy of the ORM and cannot do everything SQL or the underlying database engine allows.

Moron.js attempts to fix all this while still doing the most important job of an ORM, which is reducing boilerplate code.

I have also used various NoSQL databases in our projects. While NoSQL is sometimes a good match for Node.js and for the applications built with it, relational databases still have their place in the mix. I personally think the place is big.

More often than not, the data of a non-trivial application is relational. And if it isn't in the beginning of the project there is a good chance it will become relational.

Having said that, a flat object (table row) is rarely enough to represent a meaningful entity in an application. Usually, you want to use nested objects and arrays – documents. This is where NoSQL databases shine.

To fix this, moron.js has some interesting document features we have found very useful in our projects. One more opinion before we get to the good stuff: I love Promises! Everything asynchronous is handled with Promises in moron.js. Moron.js uses the bluebird library which also provides a callback interface through asCallback() method if you don't share my opinion about Promises.

The basics

Moron.js doesn't try to abstract away SQL. Actually, it attempts to do the opposite and make it as easy as possible to use the full power of SQL in all situations. There is no custom query DSL. I'm one of those weird people who think SQL is the best language for writing SQL queries.

Writing SQL query strings is error-prone and not that pretty. For that reason and many others, moron.js is built around the excellent query builder knex. Knex provides a way to build SQL queries using a clean, fun and easy-to-read syntax.

Knex hides the differences between SQL dialects, handles the different database clients and does stuff like connection pooling. While knex hides the dialect when possible, it has a lot of database specific methods. And if those aren't enough, you can always write raw SQL query strings.

Let's jump straight to examples. In the examples we assume that we have defined a Person model. Check out the moron docs if you want to know how to do this. We'll start with something simple. The first example makes a simple query to database and fetches a few person table rows as Person model instances.

Person
  .query()
  .where('age', '>', 12)
  .andWhere('age', '<', 20)
  .andWhere('gender', 'male')
  .then(function (maleTeenagers) {
    console.log(maleTeenagers[0] instanceof Person); // true
    console.log(maleTeenagers.length);
    console.log(maleTeenagers[0].firstName);
  })
  .catch(function (err) {
    console.log('oh noes', err.stack);
  });

The query() method of moron.js's model class returns a query builder object that has all methods of a knex QueryBuilder. The query is executed when the then() method is called. then() method returns a Promise that gets resolved with the result.

Now lets do something a little more complex to stretch our SQL muscles. The next example assumes Person has a one-to-many relation children. The query returns all Persons but also adds two extra attributes parentName and childrenAgeSum for each person using a join and a sub query respectively.

Person
  .query()
  .select([
    'Person.firstName',
    'Person.lastName',
    'Parent.firstName as parentName'
  ])
  .select(function () {
    // Calculate the sum of ages of all the person´s children
    // and select it as a childrenAgeSum property.
    this
      .sum('age')
      .from('Person as Child')
      .whereRef('Child.parentId', '=', 'Person.id')
      .as('childrenAgeSum')
  })
  .innerJoin('Person as Parent', 'Person.parentId', 'Parent.id')
  .orderBy('Person.firstName')
  .map(function (person) {
    console.log(person.firstName);
    console.log(person.childrenAgeSum);
    console.log(person.parentName);
  })
  .catch(function (err) {
    console.log('oh noes', err.stack);
  });

In this example we called map() instead of then() to execute the query. QueryBuilder has a bunch of Promise methods inherited from the bluebird promise library.

So far it looks like moron.js is just a query builder that returns model instances instead of plain JSON objects. This is where things get interesting. Model instances have a $relatedQuery() method that can be used to create queries to relations.

Instead of the whole table, the target of the query is the set of models related through a given relation. Just like the query() method, the $relatedQuery() method also returns an instance of QueryBuilder so again we have all the knex's methods available!

// jennifer is an instance of Person.
jennifer
  .$relatedQuery('children')
  .where('name', 'Bob')
  .first()
  .then(function (bob) {
    return bob
      .$relatedQuery('children')
      .insert({firstName: 'Bob junior', lastName: bob.lastName});
  })
  .then(function (bobJunior) {
    console.log('jennifer just got a grandchild', bobJunior.firstName);
    return bobJunior
      .$query()
      .patch({firstName: 'Justin'});
  })
  .then(function (justin) {
    console.log(justin.firstName);
  })
  .catch(function (err) {
    console.log('oh noes', err.stack);
  });

Every method that starts a query returns an instance of the same query builder class QueryBuilder. In moron.js, you always have a full control over the SQL that will be executed.

Documents

One of the core design ideas of moron.js was to make working with documents as easy as possible. Moron.js does two things to make document handling easier:

  • Makes relation eager fetching easy and fun using relation expressions.
  • Automatically flattens deep documents into rows using Postgres' json and jsonb data types with fallback to JSON strings on other databases.

Relation expressions

There is nothing new about the possibility to fetch relations eagerly in an ORM. It is one of the most basic things you can do with them. Moron.js makes this super easy using a teeny-tiny DSL that I call relation expressions. Relation expression is a string that describes the relation tree that should be fetched for a model.

Fetch one relation pets:

Person
  .query()
  .eager('pets')
  .then(function (persons) {
    // Each person has the `.pets` property populated with
    // `Animal` objects related through `pets` relation.
    console.log(persons[0].pets[0].name);
    console.log(persons[0].pets[0] instanceof Animal); // true
  });

Fetch multiple relations on multiple levels:

Person
  .query()
  .eager('[pets, children.[pets, children.friends]]')
  .then(function (persons) {
    console.log(persons[0].pets[0].name);
    console.log(persons[0].children[0].pets[0].name);
    console.log(persons[0].children[0].children[0].firstName);
    console.log(persons[0].children[0].children[0].friends[0].firstName);
  });

Fetch one relation recursively:

Person
  .query()
  .eager('[pets, children.^]')
  .then(function (persons) {
    // The children relation is from Person to Person. If we want to
    // fetch the whole descendant tree of a person we can just say
    // "fetch this relation recursively" using the `.^` notation.
    console.log(persons[0].children[0].children[0].children[0].children[0].firstName);
  });

Because the relation expressions are strings, they can easily be sent for example as a query parameter of an HTTP request. Allowing a client to fetch an arbitrary set of relations is not very secure. Therefore, moron.js has the allowEager() method. allowEager can be used to limit the allowed relations to a certain subset.

expressApp.get('/persons', function (req, res, next) {
  Person
    .query()
    .allowEager('[pets, children.pets]')
    .eager(req.query.eager)
    .then(function (persons) { res.send(persons); })
    .catch(next);
});

The example above allows req.query.eager to be one of pets, children, children.pets, [pets, children] and [pets, children.pets]. Examples of failing eager expressions are movies, children.children and notEvenAnExistingRelation.

With relation expressions, you can decide in the client which relations to load for an entity.

Automatic document flattening

Sometimes, the data just isn't relational and you don't want to create a bunch of tables to represent a document. For these cases, moron.js offers another solution. It automatically flattens documents into rows by mapping the object and array properties into JSON. With the advent of Postgres' json and jsonb data types, we can now query these document columns. The mapping happens transparently:

Person
  .query()
  .insert({
    firstName: 'Jennifer',
    age: 24,
    address: {
      street: 'Somestreet 10',
      zipCode: '123456',
      city: 'Tampere'
    },
    employmentHistory: [
      {company: 'TUT', jobTitle: 'Research assistant'},
      {company: 'Vincit Oy', jobTitle: 'Passionate programmer'}
    ]
  })
  .then(function (jennifer) {
    console.log(jennifer.address.city);
    console.log(jennifer.employmentHistory[1].company);
    return Person.query().where('id', jennifer.id).first();
  })
  .then(function (jenniferFromDb) {
    console.log(jenniferFromDb.address.city);
    console.log(jenniferFromDb.employmentHistory[1].company);
  })
  .catch(function (err) {
    console.log('oh noes');
  });

Transactions

Transactions are started by calling the moron.transaction function. You give all the models you want to use in the transaction as parameters to the transaction function. The model classes are bound to a newly created transaction and passed to the callback function. Inside this callback, all queries started through the models (or query result model instances) take part in the same transaction.

The transaction is committed if the Promise returned from the callback is resolved successfully. If an exception is thrown anywhere inside the callback, including the async promise callbacks, the transaction is rolled back.

moron.transaction(Person, function (Person) {

  return Person
    .query()
    .insert({firstName: 'Jennifer'})
    .then(function (jennifer) {
      return jennifer
        .$relatedQuery('pets')
        .insert({name: 'Scrappy'});
    });

}).then(function (scrappy) {
  console.log('Jennifer and Scrappy were successfully inserted');
}).catch(function (err) {
  console.log('Neither Jennifer nor Scrappy were inserted');
});

Interested?

I've just scratched the surface of moron.js but this post is getting too long. If you're interested, check out our github page and API Documentation. Best way to get your hands dirty is to start playing with our example project.

Sami Koskimäki

Sami Koskimäki

6 kommenttia

Ozum Eldogan says:

Brilliant idea.

Eric Perez says:

Thank you for adding full support for Sqlite3! That is one feature that is severely lacking from most Node ORMs out there and is the most important piece of functionality for me.

ringobob says:

YES. Too many ORMs neuter SQL in order to “make things easier”, and thus make applications worse. When I can’t use an ORM to write a complicated query, I don’t learn how to use it at all, I just write SQL because I know it can do what I want. I have a strong interest in using moron.js if it won’t get in my way.

Twitchard says:

I see what you did there.
ORM-ON and MOR-ON

I actually had thought to myself the other day, that if I ever had time to write an ORM I would name it “morons” for exactly that reason. Props to you for actually doing it!

Join the conversation