My Journey with Stored Procedures: Lessons and Challenges

Thrown into the Deep End

I had just started my journey as an intern, excited but nervous. One of my initial tasks? Work on a project using Sequelize ORM with MySQL. Sounds simple, right? But then my senior told me:

"Write all the business logic inside stored procedures instead of writing it in the code."

This caught me off guard. Although I had learned about stored procedures before, I had never actually used them. Suddenly, I was thrown into a world where every logic change meant rewriting SQL, debugging felt like an endless maze, and I didn't understand why we couldn't just use ORM like before.

The Real Struggle Begins

Just when I was getting used to stored procedures, I got assigned another task building an entire role based access control system. This meant handling roles, rights, users, and their mappings. Each role had specific rights, and each user could be assigned roles or custom permissions.

At first, I did what I knew best handled everything in the controller using Sequelize ORM. But soon, I noticed something alarming:

• Every user-role or right mapping required multiple database calls.

• Deleting or updating one record meant adjusting several others, leading to huge loops in my code.

• The sheer amount of data being retrieved from the database was unnecessary and insecure.

Before (Problem with ORM Loops in JS)

// ❌ Bad Practice: Fetching all rights from `custom_rights` and looping in JS
// - Performance Issue: If `custom_rights` is large, `.map()` in JS is slow.
// - Security Risk: Data manipulation happens outside the DB, risking inconsistencies.
// - Transaction Overhead: Bulk insertions should be handled efficiently in SQL.
// ✅ Solution: Use a **stored procedure** to handle this logic inside the database 
//   - Ensures atomicity and consistency within a single SQL execution.
//   - Optimized for bulk insertions, reducing application-layer processing.

// ❌ Bad Practice: Fetching all rights and looping in JS
const rightMappings = custom_rights.map(right => ({
  user_id: id,
  role_id: role_id || user.role_id,
  right_id: right.id,
  Read_Access: right.Read_Access || false,
  Write_Access: right.Write_Access || false,
  Update_Access: right.Update_Access || false,
  Delete_Access: right.Delete_Access || false,
  createdBy: req.userId,
  status: 'active'
}));


// ❌ Instead of JS `.map()`, use a stored procedure:
// `CALL insert_user_rights(:user_id, :role_id, :custom_rights_json, :createdBy)`

This approach retrieved too much data and involved too many database calls a clear performance issue.

After (Optimized with a Stored Procedure)

DELIMITER //

CREATE PROCEDURE insert_user_rights(
    IN p_user_id INT,
    IN p_role_id INT,
    IN p_custom_rights JSON,
    IN p_created_by INT
)
BEGIN
    -- Insert rights efficiently in a single transaction
    INSERT INTO user_rights (user_id, role_id, right_id, Read_Access, Write_Access, 
                             Update_Access, Delete_Access, createdBy, status)
    SELECT 
        p_user_id, 
        p_role_id, 
        JSON_UNQUOTE(JSON_EXTRACT(rights.value, '$.id')), 
        JSON_UNQUOTE(JSON_EXTRACT(rights.value, '$.Read_Access')),
        JSON_UNQUOTE(JSON_EXTRACT(rights.value, '$.Write_Access')),
        JSON_UNQUOTE(JSON_EXTRACT(rights.value, '$.Update_Access')),
        JSON_UNQUOTE(JSON_EXTRACT(rights.value, '$.Delete_Access')),
        p_created_by, 
        'active'
    FROM JSON_TABLE(p_custom_rights, '$[*]') AS rights;

END //

DELIMITER ;

The "Aha!" Moment

That’s when it hit me this is exactly what stored procedures are meant for!

Instead of looping in the controller, I could write a stored procedure that efficiently handled all the mapping in the database itself. One stored procedure call, and everything would be mapped in an optimized way.

I revisited stored procedures not as a hassle but as a powerful tool. Suddenly, everything made sense.

Why Stored Procedures Made Sense

Switching to stored procedures brought huge improvements:

✅ Performance Optimization

• Stored procedures are precompiled and cached by the database engine, leading to faster execution compared to dynamic SQL.

• They reduce the overhead of multiple database calls by handling complex logic in a single execution.

✅ Reduced Network Load

• Instead of multiple queries sent from the application, a single stored procedure call reduces the number of database round trips.

✅ Security Enhancements

• Execution privileges can be restricted to stored procedures instead of direct table access, reducing SQL injection risks.

✅ Atomic Transactions

• Stored procedures can ensure that multiple related operations execute as a single transaction, improving consistency.

✅ Encapsulation of Business Logic

• Business rules stay in the database, making it easier to enforce constraints and policies consistently.

When Not to Use Stored Procedures

❌ For Simple Queries

• If a query is straightforward (like fetching user details by ID), using an ORM or direct SQL query might be more readable and maintainable.

❌ Frequent Schema Changes

• If your database schema evolves often, updating stored procedures can be time-consuming compared to modifying ORM models.

❌ Limited Debugging & Version Control

• Debugging stored procedures is harder compared to application-layer code. Also, managing version control is more complex since they exist inside the database rather than in the application repository.

❌ Cross-Database Compatibility Issues

• Stored procedures are written in specific database languages (T-SQL, PL/SQL, etc.), making it harder to migrate between different database systems.

❌ Increased Development Overhead

• Writing and maintaining stored procedures require SQL expertise, and not all developers may be comfortable working with them.

Final Thoughts

This journey taught me the importance of choosing the right tool for the task at hand and reinforced the value of stored procedures as a powerful asset in the developer's toolkit.