What happened to stored procedure in modern db design

You've hit on a core tension in modern application development, especially with frameworks like Spring Boot and JPA/Hibernate. Let's break down the shift away from stored procedures and the evolving role of DBAs:

The Shift Away from Stored Procedures:

• Object-Relational Mapping (ORM) Paradigm:

  • JPA/Hibernate and similar ORMs prioritize an object-oriented view of data. Developers work with Java objects, and the ORM translates these objects into SQL queries. This abstraction aims to reduce the need for writing raw SQL and improve code maintainability.

  • The idea is that you manipulate objects, not database tables directly. This is a powerful abstraction when it works well.

• Application-Centric Logic:

  • Modern application architectures often push business logic into the application layer. This allows for greater flexibility and easier deployment of changes without requiring database updates.

  • This approach is often favored in cloud-native environments where application scaling and rapid iteration are paramount.

• Portability and Database Agnosticism:

  • ORMs strive to provide database portability. By abstracting SQL, developers can theoretically switch databases with minimal code changes.

  • Stored procedures are database-specific, making them less portable.

• Development Speed and Simplicity:

  • For many developers, writing JPA/Hibernate queries is faster and easier than writing complex stored procedures.

  • Spring boot is designed to allow developers to be very agile.

• Maintenance:

  • Many developers find java code easier to maintain than pl/sql or other stored procedure languages.

• Version Control:

  • Application code is more easily placed into version control systems like git, and is easier to incorporate into CI/CD pipelines.

The Persistence of Raw SQL (Named Queries):

• Performance Optimization:

  • Sometimes, JPA/Hibernate-generated SQL is inefficient. Developers resort to native SQL queries (e.g., @Query annotations) to optimize performance for complex queries.

  • In cases where the ORM's abstraction falls short, raw SQL remains essential.

• Complex Reporting and Data Analysis:

  • For complex reporting or data analysis tasks, raw SQL provides the necessary flexibility and control.

• Legacy Database Integration:

  • When integrating with legacy databases or systems, raw SQL may be required to interact with existing stored procedures or database structures.

The Evolving Role of DBAs:

• Automation and Cloud-Based Databases:

  • Cloud providers offer managed database services that automate many traditional DBA tasks, such as backups, patching, and scaling.

  • Infrastructure as Code (IaC) tools and automation have reduced the need for manual database administration.

• DevOps and Database Reliability Engineering (DBRE):

  • The DBA role has evolved into DBRE, focusing on database reliability, performance, and automation within a DevOps culture.

  • DBREs work closely with developers to ensure database performance and scalability.

• Data Modeling and Optimization:

  • DBAs/DBREs continue to play a crucial role in data modeling, query optimization, and database performance tuning.

  • They are essential for ensuring that databases can handle the demands of modern applications.

• Data Security and Compliance:

  • DBAs/DBREs are responsible for ensuring data security and compliance with regulations such as GDPR and HIPAA.

  • They are the gatekeepers of the data.

• Data Governance:

  • With the rise of data lakes and data warehouses, professionals that used to be DBAs are now heavily involved in data governance, ensuring data quality, and data lineage.

Why the "Monolith of Calls" Problem Persists:

• Developer Skill Gaps:

  • Not all developers have a deep understanding of database performance and optimization. This can lead to inefficient queries and data access patterns.

• Time Constraints and "Quick Fixes":

  • Developers often prioritize speed and functionality over optimal database design. This can result in code that works but is not scalable or maintainable.

• Microservices and Distributed Data:

  • While microservices offer many benefits, they can also introduce complexity in data management. Ensuring data consistency and performance across multiple services can be challenging.

In summary:

The shift away from heavy reliance on stored procedures is driven by the desire for application-centric logic, portability, and development speed. However, raw SQL remains essential for performance optimization and complex data operations. The DBA role has evolved into DBRE, focusing on automation, reliability, and data governance in a cloud-native world. The "monolith of calls" problem persists due to developer skill gaps, time constraints, and the complexities of distributed data.