Where should SQL/DB Queries be encapsulated in a software system?
- by Stephen Bennet
I frequently write small applications (either web based or otherwise) that require heavy database usage. i've attempted various ways of handling where to put the actual sql queries (sort of ad-hoc ORM systems). These include:
Models that build themselves up - and only allowing SQL to be inside of a model.
A sort of factory style method where the models are built by a factory class that is allowed to know about SQL.
A third entity that maps models based on their fields/keys into the database and generates
SQL code on the fly based on this.
Is there a common knowledge of which method is best? Or another way I have missed?
Clearly a lot of it will be based on the context of the system itself, which for me is usually to produce lightweight tools or utility frameworks. In experimenting, I've never found any of them that feel intuitively "right" and not clunky, but I also do not want to go for a full framework such as Django or Ruby - both because the tools I create are in a variety of languages and because they usually do not warrant that level of surrounding footprint.
