Looking for some guidance on best practices for querying/updating the database through Ignition.
Before Ignition, we did almost every database update through stored procedures. Named queries seem to be the native “Ignition way” of doing database updates.
I’m aware that you can call stored procedures from Named Queries, and similarly, through scripting. My question is: what is considered the best practice for updating the database.
Here are some benefits I can think of from each approach:
I tend to use named queries and gateway-scoped “Prep” queries (in script libraries). Mainly to keep everything in Ignition in as DB-brand-agnostic form as possible.
FWIW I don’t think it’s Ignition’s way, but pretty SOP for most web services to be DB agnostic and for the web server software to keep track of NamedQueries so that it knows what it’s allowed to run and not run. Not to mention it then also allows the server (not the db) to do some caching of results (which ignition does with named queries).
I’m with @pturmel - unless absolutely necessary, I keep everything in named queries or at the very least in a self contained that is accessible via gateway message handler.
One pro to this that you didn’t write but that I think is a big plus, is that python is (imo) about 10 times easier to read than SQL.
I’m currently working on a project where there’s a lot of business logic in these 500+ line stored procedures, I converted one of them to a function in Ignition and it’s about 100 lines, which to me is much more readable and therefore maintainable. Unless you’re really used to SQL in which case this probably doesn’t apply.
There is something “pure” about working in only SQL, and after enough days in a row working with it I sort of get it, but it’s definitely a perspective shift to go from python that reads like pseudo code and then SQL, especially going back and forth its like whiplash. Hence why I prefer to keep it all python if possible lol.