How to insert data to multiple tables in one db request with sequelize?

Question

I have the following relation:

Association is:

  users.belongsToMany("roles", {
    through: "userRoles",
  });

When creating a user I do this:

register: async (obj, args, context, info) => {
  const PasswordHash = await bcrypt.hash(args.input.Password, 12);

  const user = db.users.create({
    ...args.input,
    Password: PasswordHash,
  });

  db.userroles.create({
    UserId: user.Id,
    RoleId: args.input.roleId,
  });
},

How to do this in one request? Right now Im doing one when creating the user and then getting the Id of the newly created user and adding a role to that user (request 2).

A followup, lets say I would have more then one junction tabel tied to users table, again how would I insert to all tables in one db request?

What's the backing DB? You'd need to create a stored proc and income that via Sequelize. — James
– James, Commented May 5, 2020 at 21:25
I could, is your user ID auto-generated by the DB? FWIW last comment has a typo "income" should be "call"... — James
– James, Commented May 5, 2020 at 21:53

James · Accepted Answer · 2020-05-05 23:04:40Z

1

Create a stored proc in your MySQL DB to create both records e.g.

drop procedure if exists createUser
DELIMITER //
CREATE PROCEDURE createUser
(
    IN email VARCHAR(200),
    IN password BINARY(60),
    IN roleId INT
)
BEGIN
    DECLARE userId BINARY(16);

    INSERT INTO Users (Email, Password)
    VALUES (email, password);   

    # Assuming your user ID is auto-generated, pull ID of last insert
    SET userId = LAST_INSERT_ID()

    INSERT INTO UserRoles (UserId, RoleId)
    VALUES (userId, roleId);

END //

DELIMITER ;

Then you can call that stored proc using Sequelize

const { email, roleId } = args.input;
const result = await sequelize
  .query('CALL createUser (:email, :password, :roleId)', 
        {replacements: { email, password: PasswordHash, roleId }});

You can tweak to suit in terms of the relevant parameters, but you get the idea.

edited May 5, 2020 at 23:04

answered May 5, 2020 at 22:58

James

82.4k19 gold badges172 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ali Khalid · Accepted Answer · 2020-05-05 21:56:58Z

There is no way to create a user and add the roles at the same time unless you want to create a custom stored procedure in MySQL.

Lets try to simplify your problem. You do not know the USER ID before you create the user. Therefore it is impossible to create the role before you create the user. One way to solve this issue, is to generate the user ID in your code.

You can add an extra column in your "Users" table called GeneratedId and convert the ID column to AUTO_INCREMENT, then use a package such as https://www.npmjs.com/package/uuid to generate a unique ID before you insert the user.

Use the same generated Id to associate the role to the user (The "UserId" column in the "UserRole" table will contain the Id you generated in code).

Now you can add the user and all the associated data without waiting for the answer from the server.

Collectives™ on Stack Overflow

How to insert data to multiple tables in one db request with sequelize?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related