Named Queries vs. queries in script library - Best Practices

Somewhere in the forums I remember reading that it was a better practice to write queries in a script library instead of using named queries. I can't find the topic that it was mentioned on. So, what are everyone's thoughts on this? Arguments for and against using named queries?

I use named queries all the time in place of scripted queries. It basically allows for resistance against SQL injection when using them properly. You can do prepared statements/queries in scripting also, but I like having a nice interface to test queries out and just call them from the script with whatever parameters I need.

3 Likes

So do the 'prep' scripting functions.

You can't create dynamic queries, perhaps obviously, using named queries. Named queries should be used for all static queries and dynamic ones must use scripted ones making sure to validate dynamic parts against an allow list

3 Likes

Generally, named queries are considered to be the better practice however, it is situational.

4 Likes

I always used named queries just until recently. I now use queries in scripts, mainly because my workflow changed so much in the past 2 years.

In our experience, when creating a new table in your database, you always want the same named queries and the same scripts (insert, update, upsert, delete, getById, getByfilter, ...).

So I made a AI-helper, where I just ask: 'Create me a table production_orders with fields 'no', 'line_id' -> FK to lines, 'capacity', ... and give me the jython functions.

This returns the sql command
'CREATE TABLE production_orders .... ' with the fields I want (plus extra fields like id, created_by, created_at, ...

And then it returns the jython code with all the prep queries (insert, update, upsert, delete, getById, getByfilter, ...).
I could have the AI return all the named queries, but just having 1 script makes the workflow 5 times faster, because it's just copy-paste.

We also extended this so it has function converting the query result to classes immediately, so class logic can be used.

1 Like

I can't find it at the moment but I recall making a post where I tested named vs prep query vs plain old runUpdateQuery with %s style, and named came out on top, prep second and plain was last. If performance matters named is definitely the best option.

3 Likes

Except for prepQueries (afaik) do not take advantage of caching, which will give Named Queries an edge over this approach. Unless the parameters are always changing, in which case I would expect there to be no real difference. In situations where the parameters are static or don't change often, I would lean on Named Queries, despite the hit to development speed.

5 Likes

And in the case where there might be multiple developers in the code with different levels of familiarity, I find that named queries are easier to read and find in the code.

5 Likes

@lrose @bschroeder Both valid points!

My only gripe with Named Queries is one that it seems like they could easily address:

1 Like