Stored procedures are a back-end/DB thing. Keep it off of mobile, please I am begging you. There's a reason there's no stored procedures in Android's SQLite. You really want front-end to crunch data? You already have Java or Kotlin to take care of that kind of thing for you.

Stored procs. Not happening on the mobile side of the fence. Ever.

Yes yes you have Palapa Web Server to set you up MySQL on your phone and get your stored procs up and running but hecc man you have a lot of issues. Why does it have to be done with stored procs?

  • 2
    Stored 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.
  • 0
    @Voxera it also doesn't help that the mobile user can edit and delete entries on the table through the Palapa Web Server app, (as in the APK! Not even the AAR!)

    Trying to argue against local stored procs is one of the most stressful times in my career 😣
  • 1
    sqlite 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.
  • 0
    Playing devil's advocate here, but isn't the argument always that "it depends ...".
  • 2
    I'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."

    Jesus Christ...
  • 3
    @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.
  • 1
    @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.
  • 0
    @netikras Thats valid options yes.
  • 2
    @netikras you can typically do that with views too, but they're far more composable. (tho sometimes they have a crippled feature-set)
  • 0
    @monkeyboy the "it depends" argument only goes as far as when the client insists on it. But even then I consider it whim-driven-development, and not based on actual business rules.
Add Comment