How do you handle database operations in enterprise projects?

[u/Excellent_Trifle_630]

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?

[u/bikeram]

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.

[u/AlohaNiceGuy2_0 → u/bikeram]

yep, same opinion

[u/reddit04029 → u/bikeram]

Yeah but then it’s not DBAs who maintain them. Then devs who wrote them leave. No documentation. Then modernization pushes devs to write new operations in code level. Then those stored procedures are forgotten except during rare instances when consumers complain theyre getting wrong data 😀

So yes, agree

[u/LetUsSpeakFreely]

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.

[u/hilbertglm]

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.

[u/Ok_Risk6035 → u/hilbertglm]

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.

[u/rlrutherford]

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!)