CODEWITHSUNDEEP
mysqlsqlitesequelize.js
Lu4
Lu4

Reputation: 15032

Sequelize special query that involves "last inserted row id"

I have the following database structure (expressed in SQLite dialect):

CREATE TABLE `Clocks` (`_id` INTEGER PRIMARY KEY AUTOINCREMENT, `time` DATETIME);
CREATE TABLE `Operations`
(
    `_id` UUID NOT NULL PRIMARY KEY,
    `finished` TINYINT(1) NOT NULL DEFAULT 0,
    `launchedOn` BIGINT REFERENCES `Clocks` (`_id`) ON DELETE SET NULL ON UPDATE CASCADE,
    `finishedOn` BIGINT REFERENCES `Clocks` (`_id`) ON DELETE SET NULL ON UPDATE CASCADE
);

Now what I would like to achieve in Sequelize.js looks like the following SQL Query in SQLite:

BEGIN TRANSACTION;
    INSERT INTO Clocks(time) VALUES (date('now'));
    INSERT INTO Operations(_id, finished, userId, launchedOn) VALUES ('00000000-0000-0000-0000-000000000001',  0, last_insert_rowid());
COMMIT;

and following:

BEGIN TRANSACTION;
    INSERT INTO Clocks(time) VALUES (date('now'));
    UPDATE Operations
    SET     finished = 1,
            finishedOn = last_insert_rowid()
    WHERE _id = '00000000-0000-0000-0000-000000000001';
COMMIT;

I've did some investigation with Sequelize.js, I've got idea on how to organize the above transactions but I have no idea on how to include last_insert_rowid() into list of inserted items. This function by the way is SQLite-specific is there cross-database alternative to it?

Thank you in advance!

Upvotes: 0

Views: 1108

Answers (1)

paolord
paolord

Reputation: 399

You can try these, you can return the resulting object created upon insert and use it on the next query.

the first query:

return sequelize.transaction(function (t) {
  return Clocks.sync().then(function(){
    return Clocks.create({
      time: Sequelize.fn('date', 'now')
    });
  }).then(function(clock){
    return Operations.sync().then(function(){
      return Operations.create({
        id: '00000000-0000-0000-0000-000000000001',
        finished: 0,
        launchedOn: clock.id 
      }, {transaction: t});
    });
  });
});

second query:

return sequelize.transaction(function (t) {
  return Clocks.sync().then(function(){
        return Clocks.create({
            time: Sequelize.fn('date', 'now')
        });
      }).then(function(clock){
        return Operations.sync().then(function(){
            return Operations.update({
                    finished: 1,
                    finishedOn: clock.id 
                }, 
        {where: { id: '00000000-0000-0000-0000-000000000001'}},
        {transaction: t});
        });
      });
});

I'm pretty sure you can do the same on other dialects.

Upvotes: 1

Related Questions