I am by no means an experienced developer--but the issue I always seem to have without using an ORM is that there are string literals containing different bits of SQL scattered all throughout my code. In addition to being hard to refactor when the database model changes, I think there is a fairly high performance cost to doing so many string concatenations every time the code runs. Is there a better way to manage the SQL command strings when you are sending the queries by hand?
That was my biggest issue with writing own SQL, until I started using PyCharm. A while ago they integrated DataGrip (I think it is only available in pro version) which makes the IDE also understand SQL code[1].
If you connect the IDE to a database it starts to recognize the SQL to the point it behaves like rest of the code (you have autocomplete etc). I am starting to think that this is the correct approach and ORMs were just a hack trying to achieve that.
A lot of times you don't need to change the SQL string at all. You just bind different parameter values before sending the same SQL string to the DB server over and over again.
The exceptions are SQL elements that cannot be (easily, or at all) parametrized, such as the column lists in SELECT, ORDER BY, GROUP BY, or changing the WHERE "shape" and so on.
In my experience, this tends to be a minority of queries, although an important minority.
----
P.S. If string concatenations are your bottleneck, then your database is screaming fast! The real-life bottlenecks are usually in excessive database round-trips and unoptimized query plans, and are orders-of-magnitude larger.
I haven't done DB stuff in a while as I've mostly been frontend, but I reckon the way I'd lay it out is in the same way that I have a "clients" or "services" folder (or repo) which contains things that return Promise<Data> (and I don't have to care whether their source is HTTP, Firebase, or anything really). I would probably do the same with my back-end application (or lambda). Directories (or repo) full of services which are sets of high level calls (e.g. getPotatoes()) which are async functions that return data. Inside would be (probably) SQL.
> I think there is a fairly high performance cost to doing so many string concatenations every time the code runs
String concatenation is extremely cheap compared to any sort of IO or computation.
