DEV Community

Navicstein Rotciv
Navicstein Rotciv

Posted on

ORM's how they simply your queries.

ORM's how they simply your queries.

Spoiler Alert, this is directly dealing with waterline ORM

In this post am going to walk you through the concept of ORM's, what they do and how they can simply your database queries.
Before we take a deep dive into ORM, we might want to know what it stands for first, according to wikipedia.

Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages.

Unless you've worked with NoSQL databases like mongodb or ArangoDB, you've likely written your fair share of SQL queries, they look somehow like this:

SELECT * FROM users WHERE email = ''

Object-relational-mapping is the idea of being able to write queries like the one above, as well as much more complicated ones, using the object-oriented paradigm of your preferred programming language.

Here's how it'll look when writing with Waterlinejs

var users = await User.findOne({ email: '' })
// Array -> do something with users
Enter fullscreen mode Exit fullscreen mode

In a nutshell, we are trying to interact with our database using our language adapter of our choice,
As you can see, we are using the waterline ORM library to execute the exact same query, except we can write it in JavaScript (or whatever language you’re using). We can use the same languages we know and love this time javascript, and also abstract away some of the complexity of interfacing directly with a database.

Why use an Waterline ORM instead of writing native queries?

Database Agnosticism

In schemaful databases like Postgres, Oracle, and MySQL, models are represented by tables. In MongoDB, they're represented by Mongo "collections". In Redis, they're represented using key/value pairs. Each database has its own distinct query dialect, and in some cases even requires installing and compiling a specific native module to connect to the server. This involves a fair amount of overhead, and garners an unsettling level of vendor lock-in to a specific database; for example, if your app uses a bunch of SQL queries, it will be very hard to switch to Mongo later, or Redis, and vice versa.

Waterline query syntax floats above all that, focusing on business logic like creating new records, fetching/searching existing records, updating records, or destroying records. No matter what database you're contacting, the usage is exactly the same. Furthermore, Waterline allows you to .populate() associations between models, even if the data for each model lives in a different database. That means you can switch your app's models from Mongo, to Postgres, to MySQL, to Redis, and back again—all without changing any code. For the times when you need low-level, database-specific functionality, Waterline provides a query interface that allows you to talk directly to your models' underlying database driver


let's imagine you're building an e-commerce website, with an accompanying mobile app. Users browse products by category or search for products by keyword, then they buy them. That's it! Some parts of your app are quite ordinary: you have an API-driven flow for logging in, signing up, order/payment processing, resetting passwords, etc. However, you know there are a few mundane features lurking in your roadmap that will likely become more involved.

You ask the business what database they would like to use:

"Datab... what? Let's not be hasty, wouldn't want to make the wrong choice. I'll get ops/IT on it. Go ahead and get started though."

he traditional methodology of choosing one single database for a web application/API is actually prohibitive for many production use cases. Oftentimes the application needs to maintain compatibility with one or more existing data sets, or it is necessary to use a few different types of databases for performance reasons.


The product owner/stakeholder walks up to you and says:

"Oh hey by the way, the product's actually already live in our point of sale system. It's some ERP thing I guess, something like "DB2"? Anyway, I'm sure you'll figure it out. Sounds easy right?"

Many enterprise applications must integrate with an existing database. If you're lucky, a one-time data migration may be all that's necessary, but more commonly, the existing dataset is still being modified by other applications. In order to build your app, you might need to marry data from multiple legacy systems, or with a separate dataset stored elsewhere. These datasets could live on five different servers scattered across the world! One colocated database server might house a SQL database with relational data, while another cloud server might hold a handful of Mongo or Redis collections.

What are some great ORMs?

  • Mongoose
  • Waterline
  • sequelize
  • knexjs
  • .. etc

If I am a startup with a young developer team and a strict deadline, i'll prefer waterline, they are not paying me to say that, i've greatly worked with both mongoose and waterline, waterline seems to do a lot of work underneath, it tends to do a thing almost automatically without you bothering about setup cost, scaling and other factor one might consider, another thing is the adapters, i can easily use mysql and postgresql with mongodb in one big app [though the use case is rare], lemme say i want to use a mysql database in google cloud platform for only saving references of a file uploaded to s3, and a remote mongo database - say mongo atlas (see what i did there, { SQL with NoSQL } silly me :) )
you can easily accomplish that with waterline, also waterline have a great support for cloud databases like AWS dynamodb, GCP cloud, GCP firestore etc all maintained by the official team.

I'd also vouch for Mongoose, it was my first ORM love as it solves all my problem quite very well, but i wanted more, as at the time i stopped using mongoose i had a problem of populating from children nodes to parents which was already available in sails, but if am already a startup with an old codebase that just wants to keep things on the go, i'll use mongoose.

Closing thoughts

Though some people think of the configuration process of an ORM as a pain point, I have loved the ease with which I can configure different databases for different environments using a consistent interface.

I have greatly enjoyed using an ORM. As a full-stack developer working on small teams where we don’t have a dedicated database guru, it has greatly simplified working with the data layer.

Overall, I prefer working with an ORM to not. That being said, I haven’t had to work at the scale where SQL tuning becomes more important, so I’m sure my opinion is very biased.

If you haven’t tried using one, I would suggest trying it out and seeing how it works for your development system, but ultimately somebody who is really strong with databases will always give you better performance than the best ORM system.

I hope that you have learned something new today! I would appreciate it if you could drop some 👏 or leave a comment below! Also, this is my first post ever, don't bite me if you come across stuff that's displeasing to you :) .

Top comments (1)

molamk profile image

Great points! I think that ORMs are to raw queries, what Python is to C++.

They help you get things done fast without worrying about the details. When performance/resources become an issue though, it might be a good idea to switch to a more controlled/fine tuned strategy