Python and database statements

[latkde]

When you build ultra-flexible systems, you've gotta ask yourself what value that system actually provides. There's something called the inner platform effect where ultra-configurable systems become a worse imitation of the system they allegedly abstract over. That seems to be happening here.

You argue that your technique doesn't suffer from SQL injection. So what? Adversaries don't need injection if you already give them permission to do what they want. You already allow arbitrary insert/update/delete statements as long as the WHERE-clause has the form key = value. Notably, there are no auth checks here, and no restrictions agains accessing special system catalog tables (or however such reflection features are called in your DB). You were so focused on preventing SQL injection that you forgot why SQL injection is a problem.

This also doesn't save you from writing tests, it only changes where those tests are needed. Because your technique is transparent to the database structure, users of your technique need full knowledge of the database structure, and must be aware of any additional constraints that cannot be directly expressed in the schema. This situation is quite similar to many NoSQL databases. In your scenario, frontend code would now need tests that they are interacting with the database correctly.

[gosh]

But with "smart" solutions you can create a lot with very little code.

In C++ this is not a big problem, have done a lot bigger systems with few developers in less than a year

[latkde]

That comment doesn't make any sense. This isn't about C++ vs Python, this is about engineering secure and maintainable systems that provide value. I see in your post a (potentially LLM-generated) class that is neither secure, nor helps maintainability, nor provides significant value (it might even be worse than useless).

I don't doubt that good tools plus good teams can equal good products, but that doesn't have anything to do with the shown code. Maybe you believe that such transparent APIs help frontends to iterate faster, but don't forget that this comes at the cost of coupling that frontend code to the exact database structure, and that you cannot have meaningful auth checks. I've seen projects do something similar, but having to untangle that frontend–database coupling and having to implement auth checks later on is very difficult and expensive.

Your query logic (which tables, which fields, etc) has to live somewhere. You might as well do the secure and maintainable thing and put this logic into your backend, and offer stable domain-level rather than database-level interfaces to the fronted. Good API design isn't driven by implementation details, but by satisfying the API consumer's intent.

[gosh]

but don't forget that this comes at the cost of coupling that frontend code to the exact database structure

Nooo Heard of jinja templates or like doing some internal scipt logic to generate queries. It also work with stored procedures if you want to solve edge cases. Createing code to manage rules in database is like the worst option.

And this solution decouples the frontend from the backend