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.
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.