What's an ORM?

The database translator app

The TL;DR

An ORM is software that lets developers interact with their database in their programming language of choice – instead of SQL.

• Most apps are built on relational databases like MySQL or PostgreSQL, which you talk to using SQL

• SQL is great, but can get complicated and unwieldy when building web apps

• ORMs translate SQL into languages developers are building in, like JavaScript or Python

• Popular ORMs include Django, SQLAlchemy, Sequelize, and ActiveRecord

Almost every developer uses an ORM of some sort; and a new wave of databases are popping up with ORMs built in. You might not have heard of them, but they’re an under appreciated part of the backend stack.

Working with relational databases

You’re probably heard of SQL (and if you haven’t, read this). It’s the main way that developers and data teams interact with relational databases, which is the most popular type of database for building apps and data warehouses. SQL gets written in “statements” or little groups of instructions that look like this:

SELECT
  username,
  email,
  user_created_at
FROM users
WHERE user_created_at > ‘2022-01-01’

This statement tells the database to go to the users table – where all of the data about our users is stored – and give us the user name, email, and date the user was created for all users created after January first of this year. The result from this query would be a bunch of data, usually organized in a table format. Developers also use SQL to insert data into the database, update existing data, delete records, and do admin tasks like creating new tables and handling user permissions.

---

🗣️ Speaking of SQL…if your team needs an admin panel, check out this week’s sponsor.

Basedash is the admin panel you don't have to build. Basedash works with minimal set up, so your team can carry out tasks like issuing Stripe refunds, updating profile information or letting your support reps search orders, all without having to waste your engineers' time on building custom internal tools.

---

Building web apps is basically death by a million cuts. The kinds of SQL statements that give you the data you need are usually pretty small. For example, if you’re trying to load your profile page on Twitter, the Twitter backend needs to fetch some information about you, via a SQL statement that might look like this:

SELECT
  username,
  number_of_followers,
  last_5_tweets
FROM users
WHERE username = ‘itunpredictable’

Similarly, when a new user gets created, the SQL statement might look like:

INSERT INTO users (username, user_created_at)
VALUES (“itupredictable”, “2021-01-01”)

Unlike analytics and data warehouses, where queries are usually very long and involve joining multiple tables together, these more transactional queries that power apps are usually short and sweet. A query in Snowflake could be hundreds of lines long, while transactional queries are usually only a few lines long.

Object oriented programming and databases

SQL is great. In fact, it’s probably the “language” I’m best at (although this may not mean much). The issue is that when you’re building an application, you’re primarily writing in an object oriented programming language (more on this term later) like JavaScript or Python. So if you want to issue SQL statements against your database from your web app, you’d need to create these large, unwieldy strings:

var database = database.connect()
var sql_statement = “””
  SELECT 
  …
  …
“””
database.execute(sql_statement)

When statements get sufficiently complex – especially when you need to interpolate custom data into them before you send them to the database – this makes for a frustrating developer experience. 

Beyond the awkwardness of writing SQL as a bunch of text inside variables, there’s another problem: the programming languages that developers use are object oriented, and SQL is not. Object oriented means that these languages center around reusable units of code called objects that represent important concepts, like a user or a product. Each object has properties – or things that object can be – and methods, or things that objects can do. 

// Create a new instance of an object
var justin = new Person(name=”justin”)

// Set the object’s age to 27
justin.age = 27

// Call the object’s laugh() method
justin.laugh()

This paradigm of properties and objects is how most developers build apps. And it’s very different from issuing SQL statements, which is more of a declarative, ask for what you want pattern.

😰 Don’t sweat the details 😰

Object orientation can take a bit of time to sink in. For the purposes of this post, just keep in mind that the ways that developers write code, and the ways that SQL is structured, don’t mesh very well.

😰 Don’t sweat the details 😰

What an ORM does is bridge the gap between these two things – SQL and object oriented programming – by wrapping around your database with nice, ergonomic functions that more closely map to how developers build apps.  

Working with ORMs

In short, using an ORM is the difference between this:

SELECT 
  username, 
  email 
FROM users 
WHERE user_created_at = ‘01-01-2022’

And this:

var user  = users.where({ user_created_at: ‘01-01-2021’ })

The former is plain SQL; the latter is a user object with a `.where` method that a dictionary is passed into as a filter. It’s exactly the same “request” from the database, but the ORM lets you write it like you would a line of normal code. Behind the scenes, the ORM is taking that code and transpiling it into the SQL statement that your database knows and loves.

A nice diagram from Bill Tran

A useful parallel is the client side libraries that you see associated with NoSQL databases like MongoDB. Since MongoDB isn’t relational, the way you query it is by using object oriented languages directly. For example, here’s how you’d run the above query in MongoDB using their Node.js (JavaScript) driver:

var users = db.collection.find({ user_created_at: ‘01-01-2022’ })

(This is part of why NoSQL is getting so popular; the ORM is basically built in.)

There are benefits to an ORM beyond just translating SQL to object oriented languages. ORMs are an abstraction on top of a database, so in many cases you can easily switch out one database for another if your team wants to make a change. You also get some useful utilities built in, like migrations (changing the schema of your database) and transactions (making sure queries don’t lead to bad data).

There are some outsized-ly popular ORMs, but most tend to be built for specific programming languages:

• SQLAlchemy for Python

• Sequelize or Knex or Prisma for JavaScript

• ActiveRecord for Ruby

Additionally, some popular web frameworks like Django for Python and Laravel for PHP have ORMs built in, so if you build your web app with them, you have an ORM at your fingertips without installing and configuring it directly.

🖇 Workplace Example 🖇

A startup I worked for used Postgres as their database, with Sequelize on top so they can write JavaScript to access the database. They rarely write SQL queries directly against the database.

🖇 Workplace Example 🖇

Almost all ORMs are open source and free, given how fundamental they are to the backend toolchain. But newer database companies are learning lessons from the most recent NoSQL wave and attaching built in ORMs to their offerings. A good example is Fauna, a serverless database (i.e. no configuring servers needed) that you access via a native API. The same is true for Hasura, which will provision you a Postgres database with an automatic GraphQL API on top. These are decently popular: keep an eye out for where things go.

---

A kind reminder that this week’s post is sponsored by Basedash.

Basedash is the admin panel you don't have to build. Basedash works with minimal set up, so your team can carry out tasks like issuing Stripe refunds, updating profile information or letting your support reps search orders, all without having to waste your engineers' time on building custom internal tools.

---