Sequelize - Prisma — ContextQMD

This page compares the Prisma ORM and Sequelize APIs.

Sequelize vs Prisma ORM

While Prisma ORM and Sequelize solve similar problems, they work in very different ways.

Sequelize is a traditional ORM which maps tables to model classes. Instances of the model classes then provide an interface for CRUD queries to an application at runtime.

Prisma ORM is a new kind of ORM that mitigates many problems of traditional ORMs, such as bloated model instances, mixing business with storage logic, lack of type-safety or unpredictable queries caused e.g. by lazy loading.

It uses the Prisma schema to define application models in a declarative way. Prisma Migrate then allows to generate SQL migrations from the Prisma schema and executes them against the database. CRUD queries are provided by Prisma Client, a lightweight and entirely type-safe database client for Node.js and TypeScript.

API comparison

Fetching single objects

Prisma ORM

const user = await prisma.user.findUnique({
  where: {
    id: 1,
  },
});

Sequelize

const user = await User.findByPk(id);

Fetching selected scalars of single objects

Prisma ORM

const user = await prisma.user.findUnique({
  where: {
    id: 1,
  },
  select: {
    name: true,
  },
});

Sequelize

const user = await User.findByPk(1, { attributes: ["name"], raw: true });

:::tip

Use the raw: true query option to return plain JavaScript objects.

:::

Fetching relations

Prisma ORM

const posts = await prisma.user.findUnique({
  where: {
    id: 2,
  },
  include: {
    post: true,
  },
});

const posts = await prisma.user
  .findUnique({
    where: {
      id: 2,
    },
  })
  .post();

Note: select returns a user object that includes a post array, whereas the fluent API only returns a post array.

Sequelize

const user = await User.findByPk(id, {
  include: [
    {
      model: Post,
    },
  ],
});

:::tip

Use model: Post as "Post" if you used an alias to define the relationship between User and Post - for example: User.hasMany(Post, { as: "Post", foreignKey: "authorId" });

:::

Filtering for concrete values

Prisma ORM

const posts = await prisma.post.findMany({
  where: {
    title: {
      contains: "Hello",
    },
  },
});

Sequelize

const post = await Post.findAll({
  raw: true,
  where: {
    title: {
      [Op.like]: "%Hello%",
    },
  },
});

Other filter criteria

Prisma ORM

Prisma ORM generates many additional filters that are commonly used in modern application development.

Sequelize

Sequelize has an extensive set of operators.

Relation filters

Prisma ORM

Prisma ORM lets you filter a list based on a criteria that applies not only to the models of the list being retrieved, but to a relation of that model.

For example, the following query returns users with one or more posts with "Hello" in the title:

const posts = await prisma.user.findMany({
  where: {
    Post: {
      some: {
        title: {
          contains: "Hello",
        },
      },
    },
  },
});

Sequelize

Sequelize doesn't offer a dedicated API for relation filters. You can get similar functionality by sending a raw SQL query to the database.

Pagination

Prisma ORM

Cursor-style pagination:

const page = await prisma.post.findMany({
  before: {
    id: 242,
  },
  last: 20,
});

Offset pagination:

const cc = await prisma.post.findMany({
  skip: 200,
  first: 20,
});

Sequelize

Cursor pagination:

const posts = await Post.findAll({
  limit: 20,
  where: {
    id: {
      [Op.gt]: 242,
    },
  },
});

Note: Sequelize use the Sequelize operators to perform cursor pagination.

Offset pagination:

const posts = await Post.findAll({
  offset: 5,
  limit: 10,
});

Creating objects

Prisma ORM

const user = await prisma.user.create({
  data: {
    email: "[email protected]",
  },
});

Sequelize

const user = User.build({
  name: 'Alice',
  email: 'alice@prisma,io',
})
await user.save()

const user = await User.create({
  name: 'Alice',
  email: 'alice@prisma,io',
})

Updating objects

Prisma ORM

const user = await prisma.user.update({
  data: {
    name: "Alicia",
  },
  where: {
    id: 2,
  },
});

Sequelize

user.name = 'James'
user.email = ' [email protected]'
await user.save()

await User.update({
  name: 'James',
  email: '[email protected]',
})

Deleting objects

Prisma ORM

const user = await prisma.user.delete({
  where: {
    id: 10,
  },
});

Sequelize

await user.destroy();

Batch updates

Prisma ORM

const user = await prisma.user.updateMany({
  data: {
    name: "Published author!",
  },
  where: {
    email: {
      contains: "prisma.io",
    },
  },
});

Sequelize

const updatedUsers = await User.update({
  { role: "Admin" },
  where: {
    email: {
      [Op.like]: "%@prisma.io"
    }
  },
})

Batch deletes

Prisma ORM

const users = await prisma.user.deleteMany({
  where: {
    id: {
      in: [1, 2, 6, 6, 22, 21, 25],
    },
  },
});

Sequelize

await User.destroy({
  where: {
    id: {
      [Op.in]: [id1, id2, id3],
    },
  },
});

Transactions

Prisma ORM

const user = await prisma.user.create({
  data: {
    email: "[email protected]",
    name: "Bob Rufus",
    Post: {
      create: [{ title: "Working at Prisma" }, { title: "All about databases" }],
    },
  },
});

Sequelize

return sequelize.$transaction(async (t) => {
  const user = await User.create(
    {
      name: 'Alice',
      email: 'alice@prisma,io',
    },
    {
      transaction: t,
    }
  )
  const post1 = await Post.create(
    {
      title: 'Join us for GraphQL Conf in 2019',
    },
    {
      transaction: t,
    }
  )
  const post2 = await Post.create(
    {
      title: 'Subscribe to GraphQL Weekly for GraphQL news',
    },
    {
      transaction: t,
    }
  )
  await user.setPosts([post1, post2])
})

return sequelize.$transaction(async (transaction) => {
  try {
    const user = await User.create({
      name: 'Alice',
      email: 'alice@prisma,io',
    })
    const post1 = await Post.create({
      title: 'Join us for GraphQL Conf in 2019',
    })
    const post2 = await Post.create({
      title: 'Subscribe to GraphQL Weekly for GraphQL news',
    })
    await user.setPosts([post1, post2])
  } catch (e) {
    return transaction.rollback()
  }
})