Using Lucid vs. Database Raw in AdonisJS

Lucid ORM, the official ORM of AdonisJS, is based on knex.js. It is one of the friendlier ORMs that provides the ease of using a readable syntax along with a better performance than some of the feature-heavy ORMs.

But, I have been recently torn between using one or the other.

Consider the below query that fetches a bunch of related fields -

const statusByParSub = await Database.select("sub.sub_num", "sub.status_cd")
  .from("sub")
  .innerJoin("par", "sub.par_id", "par.id")
  .innerJoin("users", "par.owner_id", "users.id")
  .where("par.start_date", ">", queryFilter)
  .groupBy("sub.sub_num")
  .groupBy("sub.status_cd")
  .count();

While the above query is certainly more readable than using a SQL, I doubt whether it can achieve the same level of performance as a raw SQL. The main reasons for the thought process being the layers involved in ORM, the end-query built from 1:M and M:M relationships, and the time that I personally take in creating an efficient ORM vs. writing a raw SQL.

The other big advantage of easy query building is not quite applicable in these situations. A lot of data fetching is happening in the same transaction (and all in the backend) and is not quite difficult to comprehend when written in a single statement.

Perceived limitations aside, I like the approachability of server end frameworks like Adonis (and ORMs that come with it), as compared to going more barebones using Express or going to technologies like Hasura or PostGraphile.

Since I do not have enough time to compare the two methods at this time :), I am trying to follow a middle ground -

  • Use ORM for all transactional queries that are “not heavy”
  • Revert to Database.raw(<sql>) for all the heavier queries where I “feel” that granular control is advantageous

Time will tell if I (or a poor sod in the future) will suffer from maintenance nightmares, or thanking stars for the right decision made at the right time.

comments powered by Disqus