ORM in Node.js

In my last post (a long time ago) in this series, I described how to solve one of the most basic problems in web development with Node, user authentication.  In this post I’m going to talk about another common web development problem, database access.  Specifically, using an ORM library to facilitate (and accelerate  database access.) Since I first understood the concept of ORM (Object Relational Mapping) and used it in a professional setting, I have been a huge fan.  My first exposure to ORM (where I truly understood what as going on) was with Linq to SQL while building a WCF service in C#.  It was super empowering when I realized that I could query the database writing C# code!  No more string contamination to build queries (which is a major risk to SQL injection attacks) and no more dependencies on other teams to build stored procedures.  As a developer, this was extremely liberating!  Naturally, when exploring a new framework I want to learn what libraries and tools are available to provide the ORM.  For Node, I have chosen sequelize.  I’m going to go over defining a model (a table) and querying the database.

The Model

In ORM, the model is the representation of your database table.  It defines what columns there are, the data they they are, relationships to other models (tables) and, in more advanced cases, things like indexes and constraints.  In my experience, there is no ORM library that give you the full functionality on SQL in defining your tables, so the question is, why bother with defining models?  The answer is portability.  Sequelize (like any good ORM tool) can map to different SQL dialects.  It supports MySQL, SQLite, and SQL Server.  If you are running automated tests, no problem, sequelize will use the in memory SQLite database.  You are deploying a Linux cloud or an on prem Microsoft environment, again, no problem because the library will generate SQL for the targeted environment.  An ORM can also generate the database for you and update the schema. This concept is called ‘Migrations’ and is a much more involved topic and I will probably devote an entire post to in the future.  Below is a very basic model that describes a database table called ‘WeatherObservation.’  I have defied a few columns to store some data from a Raspberry PI and a DHT 11 sensor.


Once you have the model defined, inserting, updating and querying data out of your database now becomes native code.  It is the same as all the other code you are writing for you application.  Below are two examples, first is a simple GET operation that returns all records in a database table.  Notice that you don’t have to write any SQL to parse out any fields from the database query, the ORM library does all that for you and you get back you data in a format that is easy for you to manipulate and work with.  You are also using the “all” method from sequelize so you are simply writing JavaScript.  The second example is a POST operation that generates an “INSERT” statement using the “create” method from sequelize.