Sequelize Transactions Implementation

Understanding the Problem

We need to implement two types of Sequelize transactions:

• Unmanaged transactions with manual commit/rollback
• Managed transactions with automatic commit/rollback

Devising a Plan

1. Setup database and initial state
2. Implement unmanaged transaction endpoint
3. Implement managed transaction endpoint (bonus)
4. Test validation and rollback behavior

Initial Database Setup

# Terminal commands
npm install
npx sequelize-cli db:migrate
npx sequelize-cli db:seed:all

Basic Solution: Unmanaged Transactions

File Location: app.js

app.get('/unmanaged', async (req, res, next) => {
    // Create the transaction
    const t = await sequelize.transaction();
    
    try {
        // Find Rose's account and update
        let rose = await Account.findOne({
            where: { 
                firstName: 'Rose', 
                lastName: 'Tyler' 
            },
            transaction: t
        });
        
        await rose.update({
            balance: rose.balance + 200
        }, {
            transaction: t
        });
        
        // Find Martha's account and update
        let martha = await Account.findOne({
            where: { 
                firstName: 'Martha', 
                lastName: 'Jones' 
            },
            transaction: t
        });
        
        await martha.update({
            balance: martha.balance - 200
        }, {
            transaction: t
        });

        // Commit the transaction
        await t.commit();

        // Return all accounts
        let allAccounts = await Account.findAll({ 
            order: [['firstName', 'ASC']]
        });
        res.json(allAccounts);
    } catch (error) {
        // Rollback on error
        await t.rollback();
        next(error);
    }
});

Advanced Solution: Managed Transactions

app.get('/managed', async (req, res, next) => {
    try {
        await sequelize.transaction(async (t) => {
            // Find Rose's account and update
            let rose = await Account.findOne({
                where: { 
                    firstName: 'Rose', 
                    lastName: 'Tyler' 
                },
                transaction: t
            });
            
            await rose.update({
                balance: rose.balance + 200
            }, {
                transaction: t
            });

            // Find Amy's account and update
            let amy = await Account.findOne({
                where: { 
                    firstName: 'Amy', 
                    lastName: 'Pond'
                },
                transaction: t
            });
            
            await amy.update({
                balance: amy.balance - 200
            }, {
                transaction: t
            });
        });

        // Return all accounts
        let allAccounts = await Account.findAll({ 
            order: [['firstName', 'ASC']]
        });
        res.json(allAccounts);
    } catch (error) {
        next(error);
    }
});

Step by Step Explanation

Unmanaged Transaction Steps

1. Create Transaction:
   • Use sequelize.transaction() to create transaction object
   • Store in variable for later use
2. Perform Operations:
   • Add transaction object to all queries
   • Include in both find and update operations
3. Handle Completion:
   • Call commit() on success
   • Call rollback() on error

Managed Transaction Steps

1. Define Transaction:
   • Pass callback to sequelize.transaction()
   • Callback receives transaction object
2. Perform Operations:
   • Use received transaction in all queries
   • No manual commit/rollback needed
3. Handle Completion:
   • Automatic commit on success
   • Automatic rollback on error

Expected Results

After Multiple Unmanaged Transactions

[
  {
     "firstName": "Amy",
     "lastName": "Pond",
     "balance": 900
  },
  {
     "firstName": "Donna",
     "lastName": "Noble",
     "balance": 200
  },
  {
     "firstName": "Martha",
     "lastName": "Jones",
     "balance": 0
  },
  {
     "firstName": "River",
     "lastName": "Song",
     "balance": 1200
  },
  {
     "firstName": "Rose",
     "lastName": "Tyler",
     "balance": 1400
  }
]

After Multiple Managed Transactions

[
  {
     "firstName": "Amy",
     "lastName": "Pond",
     "balance": 100
  },
  {
     "firstName": "Donna",
     "lastName": "Noble",
     "balance": 200
  },
  {
     "firstName": "Martha",
     "lastName": "Jones",
     "balance": 800
  },
  {
     "firstName": "River",
     "lastName": "Song",
     "balance": 1200
  },
  {
     "firstName": "Rose",
     "lastName": "Tyler",
     "balance": 1400
  }
]

Common Mistakes to Avoid

• Forgetting to add transaction to all queries
• Missing await on transaction operations
• Not handling rollback in catch block
• Mixing managed/unmanaged approaches

Testing the Implementation

1. Normal Operation:
   • Visit /unmanaged or /managed
   • Verify balance changes
2. Validation Testing:
   • Refresh until balance hits 0
   • Verify transaction rollback
3. State Verification:
   • Check /accounts endpoint
   • Verify correct final balances

Best Practices

• Always use transactions for related updates
• Include all related queries in transaction
• Handle errors appropriately
• Test both success and failure cases
• Verify final state after operations