Not trying to bash your ORM as it could have a great translation layer to make optimal queries.

my experience optimizing SQL has been removing the ORM layer via hand rolling SQL queries.

Most of my peers also seem to agree that ORM is great for knocking a feature out, but you’ll inevitably run into performance concerns at scale and need to write your own optimized queries yourself.

I feel like with SQLite having limited resources, you’d likely want to optimize your queries as best you can without an ORM? Is that not the case?

A well thought out ORM would consider for performance, obviously if you're just retrieving a particular string then manual SQL might have a few times speed lead.

The fundamental issue with both handrolled SQL and ORM is inefficient queries leading to linearly increasing time consumption. for instance usage of `LIMIT` and accidentally querying on non-indexed fields. But I don't see how this differentiate the 2 as I have seen it happen in both situations professionally.

A good ORM enforces certain behaviors, specifically querying by index and constant time pagination. This guards against accidental querying behaviors.

My experience is the exact opposite.

ORM's never scale and you end up with hand written SQL long term. Unless it is really just a CRUD app then ORM's really work fairly well.

On a personal and professional level it has been the opposite case for me.

I get the impression that OP's project is not aiming for scale.