Maybe I’m just showing my age here, but has use of SQL really fallen so far out of favour that very few developers know it well? I’m honestly surprised.
I think that the fast majority of movement away from relational databases is pure folly. With JSON columns and horizontal scaling, relational DBs can handle virtually every workload, and give devs wonderful things like joins, aggregates, window functions, views, foreign data wrappers, user defined functions and all of this other stuff the have to build themselves on top of their "easy" NoSQL DB. There are many, many good use cases for document DBs, where they win on latency and scale. I've just seen a lot of NoSQL by default designs, or NeverSQL attitude, and those end up being very expensive to actually develop and own.
At my current work nobody talks about SQL, literally every discussion starts with "We will create this new Rails model". Indexes are not created deliberately - we are adding them only when the need is obvious, because some functionality is plain slow. No one talks about SQL, plain SQL queries in code are considered really bad practice - in cases where the query generated by Rails is complex and slow and a plain SQL query is PR-ed, the code reviews discussions are pretty long.
I am not saying this is good or bad, in fact all looks OK, but I was really really surprised at the beginning.
Because you're leaving an unmaintainable mess. Nobody else can rationalise the code aside himself. It's harder to debug because even he cannot account for every edge case his regex might throw up -- which also makes it less secure. Also regex is comparatively slow so transforming an SQL query using multiple regex search and replace patterns is not the way to write hot code hit by literally millions of visitors each hour.
> understanding his code generator is not (necessarily) a pre-requisite for understanding the resulting code.
Oh it absolutely is if you want maintainable and secure code. I get some situations call for complexity but when you're generating SQL on a public facing web application with a centralised database backend, you want to be damn sure you can rationalise the SQL being generated. The best ways of doing that is to either not to try to be too clever with your generation (KISS) or to put your confidence into an established and well tested existing ORM or equivalent.
Is there anything wrong with this? My thinking is that it could be seen as a premature optimization if an index is created without knowing if it will be used.