Do all the things like ++ or -- rants, post your own rants, comment on others' rants and build your customized dev avatarSign Up
Voxera672511dStored procedures are usually used to separate data fetching logic from the rest of backend or as an extra security layer to limit what backend can do.
And sometimes for performance but that’s less and less.
At least as far as I have seen.
For a local db like sqlite it feels completely unnecessary.
groxx47211dsqlite runs in your process in most cases, stored procedures would be pointless. Every operation is a stored procedure, effectively.
Though prepared statements can be a fairly significant speedup if you do lots of small operations.
monkeyboy107410dPlaying devil's advocate here, but isn't the argument always that "it depends ...".
netikras1662410dI'm amazed. I'm truly amazed and disappointed by the fact that people do that.
@Voxera Having stored procedures in mobile app is no better than sending SQLs from web frontend to backend via ajax calls. They are a part of backend's repo layer. Not even service. Repo. Having them at frontend is like making a pop-up "Please enter a SQL query to run at the backend or leave empty to run default."
Voxera672510d@netikras that was my point, with local db or in this case client side db, I cannot see any benefit of stored procs.
And even in backend I have rarely found one necessary since prepared statements has most of the same performance benefits and keep all logic in one place.
Only if you really want to separate the db logic from the rest of the backend I can se the use, for example if the BE developers should not have free reign in the db.
netikras1662410d@Voxera For a moment I thought you were trying to justify stored procedures in a mobile app :D Sorry. Must have misread smth.
I've come across stored procs in one of our projects and they do make sense if either you do not have access to DB schemas OR project is relatively new and DB is still not fully formed (performance tuning taking place, updates, upgrades, renames, etc.). This way it's easier to decouple code from DB. DB could implement entities in views or just plain tables, change this implementation on-the-go, etc., and code does not have to worry about any of those. Code knows that the procedure will return an entity User, no matter how that model is implemented in DB layer.
We've come across this when running perf tests. DB guy was free to make DB adjustments w/o requiring code changes/recompilation.
At first I was against stored procedures but now I'm starting to consider them as a viable possibility.