Thinking back to the first time I laid eyes on SQL (Structured Query Language), the thing I remember most of all is the instant panic I felt. All of the CAPITALIZATIONS, and the aggressive sounding DROP TABLE statements — accompanying dire warnings that ALL data would be lost, and I should be very, very careful… It was not an inspiring introduction. On top of it all was the intimidating task of remembering what all of the commands were, the specific order that they should go in, and how to join every table to every other table.
Plain and simple, I was coming in with the expectation that a database should simply hold my data, and give it back when I want — seems easy! However, the complex language of SQL made me feel like I had to learn a new summoning spell for every statement and query.
And then, in a pit of despair-googling, attempting to parse out how I was going to figure out this language, I stumbled upon what felt like a revelatory concept: Object-Relational Mapping, commonly referred to as ORM. This discovery changed everything about how I was feeling.
I will get into the details of this revelation, but first I want to reaffirm that learning basic SQL syntax is incredibly important. Even with my personal revelation, I still needed to understand what SQL was myself — and through continued hands-on practice through resources like SQLZoo, many of my initial fears were dissolved. But driving me to work through this practice was the knowledge that after understanding the basics of this language, I may never need to manually write a single query again. There was hope at the end of the tunnel.
And with my practice, something that was once intimidating and foreign came to feel like a second-language. I was a master of JOIN statements. A god of CREATE DATABASE. Sanitizing my database inputs immunized me to the terrors of Little Bobby Tables.
At this point I felt ready. I dove into some new work requiring the use of Ruby on Rails, and got my first exposure to Rail’s Active Record — which was to be my first ORM. And I was hooked. I immediately wanted to know how I could implement this in all of my work.
Clarke’s Third Law — that “any sufficiently advanced technology is indistinguishable from magic” — could very well be referring to ORM, for all the black magic that it accomplishes with databases. Even everyone’s favourite problem child JOIN, with all its INNER, OUTER, LEFT, RIGHT, and FULL permutations, is easily dealt with.
Coming from pure SQL to an ORM was a shock to my system — even though I was already aware of what an ORM was. For those of you that may not be aware, and are resonating with my experiences of initial fear, I want to lessen that shock. If you’re at all intrigued by my description of this magical technology so far, please read on for my experience of integrating one example ORM into Javascript development, and how you can do the same.
However, if you’re an SQL purist scoffing at my lack of ability and competence… also please read on, and you can continue to laugh at my ineptitude while you immediately DROP TABLE this entire post from your memory.
Object Relational Mapping
So I’ve buried the lede a bit. I’ve talked about my fears and how magical ORM is — but I haven’t really described it. So all you need to know for this post is that an ORM does all the background work of managing your database! It can create models and controllers, and manage the relationships between the items in your database in future updates. ORM abstracts all the work you’d have to do manually and acts as the translator for you and your database. With ORM you don’t have to worry so much about your fluency in SQL, and you don’t have to get mad at your database for refusing to speak to you in a real language.
Sequelize
Now, as I mentioned above, I wanted to integrate ORM into my work with Javascript — and unfortunately (or fortunately, depending on your feelings surrounding Ruby on Rails), Active Record is not available for Javascript. So what do we do?
One option that I discovered is Sequelize — a Javascript/Typescript, Node.JS ORM! This fantastic ORM provides support for common databases, including Postgres, MySQL, MariaDB, SQLite and SQL Server. And for everyone that hates callback hell — it’s promised-based too!
Just look at an example of my standard database creation compared to the marketing materials for Sequelize:
My basic SQL table creation:
CREATE TABLE assignments (
id SERIAL PRIMARY KEY NOT NULL,
name VARCHAR(255),
content TEXT,
day INTEGER,
chapter INTEGER,
duration INTEGER
);Sequelize’s slick styling:
const Wishlist = sequelize.define("Wishlist", {
title: DataTypes.STRING,
});
const Wish = sequelize.define("Wish", {
title: DataTypes.STRING,
quantity: DataTypes.NUMBER,
});// Automatically create all tables
await sequelize.sync();
Sequelize definitely makes this look less intimidating!
And implementing Sequelize in a project for the first time was so easy! I had some fear leaving behind my manual database creation, but Sequelize has thorough getting-started documentation that really helped me figure out what I was doing.
But the real powerful resource was the public support available.
In a review of its public GitHub history, Sequelize has been in iterative public development since 2014 — and the vast public community providing support for this ORM proves the power of choosing a tool that is tried, tested, and true.
In fact, there are so many great tutorials about implementing Sequelize in your own projects, with fantastic full video walkthroughs available on YouTube, that I’m not going to go into significant detail on how to implement it yourself here. Instead, here are a couple of videos that I found useful:
However, I will share my feelings about the experience, and how the steps felt in comparison to something like Active Record. For one — whereas Active Record comes default with Ruby on Rails, Sequelize was a separate installation. But, instead of this being a negative, I found this modularity to be an improvement. This need to be functional with the latest javascript — without holding it back from ongoing development — is a strength of this structure, and I greatly appreciated Sequelize’s up-to-date functionality.
Installation through npm is quick:
npm install sequelize sqlite3And setting up the database login information is straightforward and understandable when coming from pure SQL.
Now, compared to Rail’s Active Record, Sequelize does hold your hand slightly less. But as someone who likes to see more of what’s actually happening in my code, I appreciate this over the command-line based model and controller creation of Active Record. Though there is no ‘make everything’ button, creating models and controllers is super simple!
Here is a simple example of how I created a simple model for my Book objects:
Sequelize allows you to create a model in only a few lines, with clear language regarding the data type and characteristics! Creating relationships is also super straightforward. I can easily define a many-to-many relationship for books and tags:
Controllers and queries are fantastic too, with the ability to granularly apply basics using specific INSERT, SELECT, WHERE, UPDATE, and DELETE statements. So for individuals who have put the work into SQL, and are afraid of moving more fully into an ORM, Sequelize lets you use it as little as possible in filling, querying and modifying your database. Sequelize also has some utility methods that add additional functionality, like count, max, min, sum, increment and decrement.
Giving just one example, in my project I used Sequelize and its promise-based functionality under the CREATE function to introduce new Book objects into my database. I incorporated this with Node.JS and Express routing to manage the posting of this data through my web-based application. So simple!
I think, without a doubt, I have become a convert to the ORM-based programming lifestyle. The ease of use and the feeling of magical database organization is fantastic. It saves so much development time in my day-to-day programming tasks — and I haven’t even touched on its functionality regarding migrations and the Sequelize CLI (but maybe in the future?).
Sequelize, with its significant incorporation of SQL terms and its general ease-of-use, has definitely become a favourite of mine in this category. If this is your first time reading about ORM, I hope my shared experience of ORM programs has helped! If it’s not — is there another Javascript-compatible ORM out there that you’re using or that you think I should try out?