Battle of the Node.js ORMs: Objection vs. Prisma vs. Sequelize

You know them, you love them, but when you have 3,524 packages with the tag ORM available at NPM, deciding on one ORM may be a little bit overwhelming. Comparing three of the most-used ORMs will help you decide which is tool is best for your project. Whether it's Objection, Prisma, or Sequelize, read this post to determine your champion ORM!

What Are the Top 3 ORMs?

Objection

Objection is a Node.JS ORM with over one hundred thousand weekly downloads. Built on the query builder Knex, Objection supports all databases supported by Knex. Objection’s main goal is to allow users to use the full power of SQL and your database’s storage engine. Objection may also be called a relational query builder.

Prisma

Prisma is an open-source Node.js and TypeScript ORM with half of a million weekly downloads. Prisma prounivides support for PostgreSQL, MySQL, SQL Server, SQLite, MongoDB, and CockroachDB. Centered around a GraphQL-like DSL schema, Prisma lets you cleanly define your database structure.

Sequelize

The most popular option, Sequelize is an open-source Node.js ORM with millions of weekly downloads. Sequelize provides support for Postgres, MySQL, MariaDB, SQLite, SQL Server, and more. Featuring solid transaction support, relations, eager- and lazy-loading, and read replication, Sequelize is a powerful tool. Sequelize also supports migrations and has a CLI tool for generating and seeding data.

Strengths of Each ORM

Objection

Very readable, intuitive syntax.
Allows chained operations
Great for migrations as it’s not tied to a changing model
Excellent support for RAW queries
Allows for a great deal of control over database operations
Makes joins straightforward

Prisma

Good CLI, capable of generating migrations and boilerplate files
Excellent documentation
Nice DSL for schema
Offers support for special keywords like “contains”, “includes”, “startsWith”
Support for RAW queries
Painless to import in existing project
Allows mapping column to custom properties name
Very popular

Sequelize

Strong CLI, capable of generating migrations, helping with seeds and boilerplate files
Getting started is straightforward
Support for RAW queries
Makes joins painless
Immensely popular

Weaknesses of Each ORM

Objection

Since models change over time, they shouldn’t be used for migrations
Requires a greater SQL knowledge in order to avoid non-performant queries
Less popular
Knex (its base) is too simple, it's not even considered an ORM, but a query-builder

Prisma

It lacks a DBAL (Database Abstraction Layer)
Can be intimidating to beginners

Sequelize

Lack of high quality documentation
More advanced joining options are tricky
Little support for full text index searching

Code examples

Nothing helps you learn about ORM options more than code samples—especially when it comes to showing how these three compare. These code examples demonstrate main features of Objection, Prisma, and Sequelize.

Objection

Configuration

Objection is unique because it needs to be used on top of Knex. The configuration file for an Objection + Knex project will be called knexfile.js, and it's going to look similar to this:

// .knexfile.js

export default {
  client: 'pg',
  useNullAsDefault: true,
  connection: process.env.DATABASE_CONNECTION_STRING ||
    'postgres://dbuser:dbpassword@localhost:5432/pet_store_objection',
  migrations: {
    tableName: 'knex_migrations',
    directory: 'migrations',
  },
  seeds: {
    directory: 'seeds',
  },
};

In this file you can set basic configurations like your connection, your folders and your database of choice.

Models

Though we are using Objection, the CLI is going to be Knex’s. Run npm install knex -g to globally install it.

Your model’s structure is going to be based on a JSON Schema, like this:

const { Model } = require('objection');

module.exports = class User extends Model {
  static get tableName() {
    return 'users';
  }

  static get jsonSchema() {
    return {
      type: 'object',
      required: ['id', 'firstName', 'lastName', 'email'],
      properties: {
        id: { type: 'string' },
        firstName: { type: 'string' },
        lastName: { type: 'string' },
        email: {
          type: 'string',
          minLength: 1,
          maxLength: 70,
        },
        created_at: {
          type: 'string', // datetimetz
        },
        updated_at: {
          type: 'string', // datetimetz
        },
      },
      additionalProperties: false,
    };
  }

  $beforeInsert() {
    this.created_at = new Date();
  }

  $beforeUpdate() {
    this.updated_at = new Date();
  }
};

This table will have the columns id, createdAt, and updateAt by default.

Migrations

The most common Knex commands for migration management are:

knex migrate:latest to apply the latest migration.
knex migrate:rollback to revert a migration.
knex migrate:make <migration_name> to generate a migration.

Example of migration file:

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.up = function (knex) {
  return knex.schema.createTable('users', (users) => {
    users.text('id').primary();
    users.text('firstName').notNullable();
    users.text('lastName').notNullable();
    users.text('email').notNullable();
    users.timestamps(true, true);
  });
};

/**
 * @param { import("knex").Knex } knex
 * @returns { Promise<void> }
 */
exports.down = function (knex) {
  return knex.schema.dropTableIfExists('users');
};

When you run your migrations, you’ll automatically get two tables with metadata about them. You can modify the name of those tables if you want.

Seeding

The two most useful commands when it comes to seeding are:

knex seed:make <seed_name> to generate a seed file.
knex seed:run to run your seeder.

Example of seeder file:

exports.seed = async function seed(knex) {
  const tableName = 'users';

  await knex(tableName).del();
  await knex(tableName).insert([
    {
      id: 1,
      firstName: 'Billie',
      lastName: 'Jean',
      email: 'girl@example.com',
    },
    {
      id: 2,
      firstName: 'Der',
      lastName: 'Erlkonig',
      email: 'poem@example.com',
    },
    {
      id: 3,
      firstName: 'Ophelia',
      lastName: 'Pseudochinensis',
      email: 'flower@example.com',
    },
  ]);
};

Simple queries

Simplified examples of common database operations

Finding all:

Model.query();

Create:

Model.query().insert({ firstName: "Britney", lastName: "Spears", email: "britney@pop.com" });

Passing where clauses to a find operation:

Model.query().where({ id: 1});

Find specific columns from table:

Model.query().select('id', 'lastName')

Update:

Model.query()
  .findById(1)
  .patch({ lastName: 'Jeans' });

Delete:

Model.query().deleteById(1);

Prisma

Configuration

Prisma’s configuration is centered around schema.prisma. This file is where you specify your data sources, your generators, and your data model definition (the app’s models and their relations).

// Default schema.prisma generated by the CLI

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Structure

Prisma has a great CLI. Run this to install Prisma and its client:

npm i prisma --save-dev && @prisma/client

Once Prisma is installed, run this to get your project started:

npx prisma init

The init command will automatically create a folder called Prisma, containing your schema.prisma. It will also create a .env file for you.

Models

In your schema.prisma, you can define your models like this:

// This is your Prisma schema file,
// learn more about it in the docs: <https://pris.ly/d/prisma-schema>

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id       Int @id @default(autoincrement())
  firstName String
  lastName String  
  email String  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Define properties of your columns using attributes "@id".

After adjusting your schema.prisma, run npx prisma generate to generate your Prisma client. You’ll need to re-run this command each time you modify your schema.prisma.

Migrations

When it comes to migrations, Prisma is considerably different from Objection and Sequelize. Remember: Prisma is centered around the schema.prisma.

To migrate your database, you'll first modify your schema.prisma, then re-run the generate command, and lastly run npx prisma migrate dev --name init. The command will automatically generate and apply the SQL script for you, but you'll notice, though, that the .sql generated has no "down" method:

-- CreateTable
CREATE TABLE "User" (
  "id" SERIAL NOT NULL,
  "firstName" TEXT NOT NULL,
  "lastName" TEXT NOT NULL,
  "email" TEXT NOT NULL,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,

  CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);

To reset your database manual changes and make its structure match the migrations, run npx prisma migrate reset. Be careful with this command, it's supposed to be used only in development mode.

When you run your migrations, Prisma creates a folder called migrations for you and a table in your database called _prisma_migrations that stores data about your migrations.

Seeding

Prisma also differs a lot from the other two when it comes to seeding. First, you need to add a prisma attribute with a seeding property in your package.json:

"prisma": {
  "seed": "node ./prisma/seed.js"
}

Then, create a file called seed.js in the same path specified in your package.json and fill it with your seed data:

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

const load = async () => {
  try {
    const users = [
      {
        id: 1,
        firstName: 'Billie',
        lastName: 'Jean',
        email: 'girl@example.com',
      },
      {
        id: 2,
        firstName: 'Der',
        lastName: 'Erlkonig',
        email: 'poem@example.com',
      },
      {
        id: 3,
        firstName: 'Ophelia',
        lastName: 'Pseudochinensis',
        email: 'flower@example.com',
      },
    ]; 
    await prisma.user.createMany({ data: users });
  } catch (e) {
    console.error(e);
    process.exit(1);
  } finally {
    await prisma.$disconnect();
  }
};

load();

To run the seeds: npx prisma db seed

Simple queries

Simplified examples taken from https://sequelize.org/master/manual/model-querying-basics.html

Finding all:

prisma.model.findMany()

Create:

prisma.model.create({
  data: {
    id: 4,
    firstName: 'Britney',
    lastName: 'Spears',
    email: 'britney@pop.com',
  },
});

Passing where clauses to a find operation:

prisma.model.findUnique({
  where: { id: 1 }
});

Find specific columns from table:

prisma.model.findMany({
  select: {
    id: true,
    lastName: true,
  },
});

Update:

prisma.model.update({
  where: { id: 1 },
  data: {
    lastName: 'Jeans',
  },
});

Delete:

prisma.model.delete({
  where: { id: 1 }
});

Sequelize

Configuration

Sequelize we has a file called .sequelizerc, it is a special configuration that lets you specify some options that you normally would pass to the CLI, for example: env, url, and paths.

// .sequelizerc
require('babel-register');

const path = require('path');

module.exports = {
  config: path.resolve('config', 'config.json'),
  'models-path': path.resolve('models'),
  'seeders-path': path.resolve('seeders'),
  'migrations-path': path.resolve('migrations'),
};

Structure

Sequelize CLI can be used to create a great boilerplate structure. Run this to implement:

npm install --save-dev sequelize-cli && npx sequelize-cli init

Sequelize CLI will automatically create folders called models, config, seeders and migrations. All you need to do is fill it with relevant information.

Models

Use the command model:generate to create your models with the CLI. You can create a User's model and a migration that creates such a table by running:

npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string,email:string

This table will, by default, have the columns id, createdAt, and updateAt.

Migrations

When you create models with the CLI, it automatically creates the correct migration inside the “migration” folder.

npx sequelize-cli db:migrate to apply a migration.
npx sequelize-cli db:migrate:undo to revert a migration.
npx sequelize-cli migration:generate --name create-dogs-table to generate a migration.

Sequelize automatically creates a table called SequelizeMeta that stores an entry for the executed migrations. You can change this behavior in the configuration file, if needed.

Seeding

Following a similar pattern, you can easily generate seeds by running npx sequelize-cli seed:generate --name users.

This will generate a seeder boilerplate. In your example, it can be filled like this:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', [
      {
        firstName: 'Billie',
        lastName: 'Jean',
        email: 'girl@example.com',
        createdAt: new Date(),
        updatedAt: new Date(),
      },
      {
        firstName: 'Der',
        lastName: 'Erlkonig',
        email: 'poem@example.com',
        createdAt: new Date(),
        updatedAt: new Date(),
      },
      {
        firstName: 'Ophelia',
        lastName: 'Pseudochinensis',
        email: 'flower@example.com',
        createdAt: new Date(),
        updatedAt: new Date(),
      },
    ]);
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('Users', null, {});
  },
};

To run the seeds, run npx sequelize-cli db:seed:all.

To remove all previously seeded data from the database, run npx sequelize-cli db:seed:undo:all.

Simple queries

Simplified examples taken from https://sequelize.org/master/manual/model-querying-basics.html

Finding all:

Model.findAndCountAll({});

Create:

Model.create({ id: 4, firstName: "Britney", lastName: "Spears", email: "britney@pop.com" });

Passing where clauses to a find operation:

Model.findAll({
  where: {
    id: { [Op.eq]: 1 },
  },
});

Find specific columns from table:

Model.findAll({
  attributes: ["id", "lastName"],
});

Update:

Model.update(
  { lastName: 'Jeans' },
  { where: { id: 1 } }
);

Delete:

Model.destroy({
  where: { id: 1 }
});

Conclusion

Objection, Prisma, and Sequelize are all great ORM options with robust features. Now that you know the ORMs and their features, you can confidently choose the victor for your application.