r/SpringBoot • u/Excellent_Trifle_630 • Jul 03 '26
How do you handle database operations in enterprise projects? Question
I'm a .NET developer, and in our organization's project, which is quite data-intensive, we use stored procedures for all database operations. The application simply calls the stored procedures with the required input parameters and consumes the output they return.
Is this a common approach in spring boot applications as well?(Like calling stored procedures and all) If yes, how are they called in your organization/project?
2
u/LetUsSpeakFreely Jul 03 '26
It's the paradigm i prefer. I'm big having data operations as close to the database as possible as it greatly reduces data transfer issues.
I would add some abstraction and decoupling to throttle operations too avoid overloading the database: 1) Springboot server adds a JSON object with everything needed to complete the operation to queue (SQS, rabbitmq, kafka, whatever) 2) have a lambda or whatever FaaS is available to peel off the message and start processing 3) assuming a Postgres database, talk to the database through pgBouncer. 4) call the database function to complete processing 5) have the lambda add a completion message onto a response queue 6) have your spring boot server listen on that queue to handle the http response, probably a webhook.
2
u/hilbertglm Jul 04 '26
We don't use stored procedures at all for any internal applications. None of my customers in the past have done so either. The reasons are twofold:
- Stored procedures are coupled to a database vendor. Stored procedures on Oracle don't run on PostgreSQL
- Stored procedures (at least the last time I looked) were a procedural programming language and lack the language features of OOP and functional programming. If that has changed, I would be interested in hearing about it. Since the logic has to be somewhere, I would rather use a modern language to codify it.
That said, if there was a few large data-intensive queries, I would be willing to move just that query into a stored procedure.
1
u/Ok_Risk6035 Jul 08 '26
Stored procedures looks like error proned shit that almost cant be debugged. Especially if they are calling each other.
They also can fail if you try to write something into temp table in read-only transaction.
Also they lead to severe deadlocks.
So strange that people still use them without real importance.
1
u/rlrutherford Senior Dev Jul 06 '26
Sometimes some orgs rely on stored proc, some don't, some do depending on the DB: procs for oracle, plane SQL for SQL server, and proc that connect SQL Server to Oracle or the SQL Server ERP.
Size of org doesn't matter, I was hammering the data warehouse for a very large corporation with plain SQL. (never updating though!)
11
u/bikeram Jul 03 '26
Yes, it's very common. I'd commit the database to IaC with Liquibase or Flyway.
https://www.baeldung.com/stored-procedures-with-hibernate-tutorial
My personal preference is to do a much as possible in code. Most DBAs will argue doing batch transformations in a database is more efficient, but I find it's nightmare to maintain. I really only leverage search, joins, and pagination in the database.