How to Insert Record and Return ID in Sequelize

Overview

Sequelize is a popular Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It’s robust and provides an easy way to map object syntax onto database schemas. One of the most common tasks when working with an ORM is inserting records into a database and then performing operations with the newly inserted record’s identifier. In this tutorial, we will explore how to insert a record into a database using Sequelize and retrieve the record’s ID after the operation has been executed. This is crucial for scenarios where the ID is needed for subsequent operations, such as creating related records or sending the ID back in a response object in an API call.

Prerequisites

• Basic knowledge of Node.js and npm.
• A Sequelize setup with a configured database connection.
• A model defined in Sequelize that you will be inserting records into.

Basic Insertion and ID Return

To begin with, let’s look at how to insert a single record and get its ID. Assume you have a model User and you want to insert a new user into your database:

const { User } = require('./models');

async function createUser(userData) {
    const user = await User.create(userData);
    return user.id;
}

The User.create() method is a Sequelize built-in method that inserts a new record into the database using the JSON object passed to it as an argument. The promise returned by create() resolves with the newly created object, which includes the ID value assigned by the database.

Advanced Usage: Insert Record with Associations

Suppose our User model is associated with a Profile model, and we want to insert both user and profile information in one go. Sequelize associations can be used:

const { User, Profile } = require('./models');

async function createUserWithProfile(userData, profileData) {
    const user = await User.create({
        ...userData,
        Profile: profileData
    }, {
        include: [ Profile ]
    });

    // The user variable now contains the User instance
    // with the Profile accessible via user.Profile

    return {
        userId: user.id,
        profileId: user.Profile.id
    };
}

Here, we use User.create() and pass in an options object with include, which specifies the associated model we want to create alongside our User. Sequelize then inserts the User and Profile records transactionally, ensuring both are created successfully or neither if an error occurs.

Batch Inserts and IDs Retrieval

What if you need to insert multiple records at once and retrieve all their IDs? Sequelize can do batch inserts using the bulkCreate method:

const { User } = require('./models');

async function createMultipleUsers(usersData) {
    const users = await User.bulkCreate(usersData);
    return users.map(user => user.id);
}

This method takes an array of plain objects, inserts them into the database in a single call, and returns an array of instances. From these instances, you can extract the IDs. It’s a more performance-efficient approach when you need to insert multiple records simultaneously.

Handling Errors and Transactions

When inserting records, it’s important to handle errors appropriately. Additionally, you might want to use transactions to ensure data consistency. Sequelize provides support for transactions, which allows us to revert changes in case an error occurs:

const { User, sequelize } = require('./models');

async function createUserSafely(userData) {
    const t = await sequelize.transaction();

    try {
        const user = await User.create(userData, { transaction: t });
        await t.commit();
        return user.id;
    } catch (error) {
        await t.rollback();
        throw error;
    }
}

In this example, we use sequelize.transaction() to initiate a transaction. We then pass this transaction as an option to User.create(). If an error occurs, we roll back the transaction with t.rollback(), reverting any changes made in the database.

Conclusion

This tutorial has provided an overview of how to insert records and retrieve their IDs in Sequelize. Using these practices, you can effectively manage data insertion tasks in your Node.js applications with robust error handling, relational inserts, and transactional consistency. Sequelize’s API makes these tasks quite straightforward, enabling developers to write concise and maintainable code. We hope this guide helps you with your future Sequelize projects!