Get the ID of the Last Inserted Row in Sequelize.js

Introduction

Sequelize is a popular promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It boasts solid transaction support, relations, eager and lazy loading, read replication, and more. One common requirement when working with databases is the need to retrieve the unique identifier of the last inserted record, commonly for use in subsequent queries or logic. In this article, we’ll explore how to accomplish this in Sequelize.js by going through step-by-step examples that increase in complexity.

This tutorial is intended for developers with a basic understanding of Sequelize and Node.js. By the end of the article, you will have a comprehensive understanding of different ways to get the ID of the last inserted row in Sequelize.js.

Basic Example

When you insert a new record into a database using Sequelize, the resulting instance contains the `id` of the newly created row. Here’s the simplest example:

const User = sequelize.define('user', {
  username: Sequelize.STRING
});

// Add a new user
User.create({
  username: 'johndoe'
}).then(function(newUser) {
  console.log(newUser.id); // Logs the ID of the new user
});

In this case, Sequelize’s `create` method returns a promise that resolves with the newly created instance, from which we can directly access the `id` property.

Handling Auto-Incremented IDs

If the primary key of your table is set to auto-increment, Sequelize will automatically fill in the `id` field with the newly generated value. The method for doing this is the same as the basic example above. However, it’s important to understand that Sequelize handles this behind the scenes:

// Assuming `userId` is an auto-incrementing field
const User = sequelize.define('user', {
  userId: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true
  },
  username: Sequelize.STRING
});

User.create({
  username: 'janedoe'
}).then(function(newUser) {
  console.log(newUser.userId); // Logs the auto-incremented ID
});

Working with UUIDs

If your table uses UUIDs (Universally Unique Identifiers) instead of numeric auto-incrementing IDs, Sequelize can also handle this. Below is how you would retrieve the UUID of the last inserted row:

const User = sequelize.define('user', {
  uuid: {
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV4,
    primaryKey: true
  },
  username: Sequelize.STRING
});

User.create({
  username: 'alexdoe'
}).then(function(newUser) {
  console.log(newUser.uuid); // Logs the UUID
});

This is possible because Sequelize is allowing you to use a default value of `UUIDV4`, which generates a new UUID whenever a row is inserted.

Transactions and Last Inserted ID

When dealing with transactions, you may want to ensure the insert operation is successful before attempting to use the new `id`. Here’s an example incorporating transactions:

// Start a transaction
sequelize.transaction(function(t) {
  // Within the transaction, create a new user
  return User.create({
    username: 'janedoe'
  }, {transaction: t}).then(function(newUser) {
    console.log(newUser.id); // Logs the ID, but only if the transaction commits
    return t.commit();
  }).catch(function(error) {
    console.error('Transaction failed: ', error);
    return t.rollback();
  });
});

Using Hooks for Complex Logic

Sometimes, you might want to perform actions immediately after a new row is inserted. This can be done using hooks:

User.afterCreate((newUser, options) => {
  console.log('New user ID:', newUser.id);
});

// The console.log in the afterCreate hook will print out after each User.create
User.create({
  username: 'janedoe'
});

The `afterCreate` hook is triggered once a new instance is created, giving you access to the new user’s `id`.

Bulk Inserts and Retrieving IDs

When doing bulk inserts, Sequelize, by default, does not return the inserted instances due to performance reasons. If you need the IDs of the inserted rows, you would have to manage this with an additional step:

User.bulkCreate([
  { username: 'user1' },
  { username: 'user2' }
]).then(() => {
  // You must now query the Users to get their IDs
  User.findAll({}).then(users => {
    users.forEach(user => {
      console.log(user.id);
    });
  });
});

To conclude, obtaining the ID of the last inserted row in Sequelize.js is a straightforward task that can be slightly nuanced depending on your use case. Remember to consider the type of primary key your table uses and whether you’re working within transactions or handling bulk inserts. With Sequelize.js, you’re equipped with various options to suit virtually any requirement related to database operations, including retrieving the latest insertion ID.

Final Words

In this tutorial, we have explored the methods provided by Sequelize.js to retrieve the ID of the last inserted row. Starting from simple individual inserts to complex scenarios involving transactions and hooks, Sequelize.js provides the necessary tools to handle these operations effectively. It’s essential to understand and correctly implement these techniques to ensure the integrity of your application’s data management practices. Whether you’re dealing with auto-incrementing IDs, UUIDs, transactions, or bulk operations, Sequelize.js has got you covered.

Happy coding!