Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

I would recommend you implement the gateway pattern. It provides a nice level of abstraction from your code (models ect) and your SQL.

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.

You know more about the requirements your projects than me (so feel free to ignore me) but I don't find any of your arguments for implementing your own system very persuasive. Have you actually benchmarked the response times of your system against a well established framework? The established frameworks have had lots of very skilled coders optimise them. Do you actually need that extra performance (assuming there is any?).

I'm also a little unsure of what advantage having your system in a variety of languages will give you. It sounds like a recipe for complex buggy code.

share|improve this answer
    
I didn't phrase it very well; this isn't one single system that I am building, just separate tools that I write from time to time which may be entirely unrelated from one another. Performance is not much of an issue, I'm focusing on maintainability, clairty and trying to have as few dependencies as possible in the code that I write. Where I said "footprint" I more meant not having to have any surrounding frameworks installed on the system in question, rather than any sort of speed impact. –  Stephen Bennet Apr 3 '12 at 11:05

That depends a lot on the actual technology you are using because that determines the set of tools and best practices you will get.

For instance if you work in Java you can use one or multiple properties files and store all the queries there, this has the advantage that you don't have them all over the place in your code and you don't need to recompile to make changes (also you can reimplement the properties class and make it look for changes in the file and reload if something is different that adds the flexibility where you can change queries without redeploying the app). But it has the downside that queries are exposed in plain text on the server so this could be considered a security flaw (easily solved buy reimplementing once more the properties file to read encrypted files). This can apply also if you are using PHP or ASP .NET. or C#.

But if you are working with Java but, let say, using Spring Framework you can have the queries defined as a map in an spring configuration file (XML), or in a bean defined in a config file.

  • Pros; is't elegant and flexible,
  • Cons; security considerations of having, once again, queries in plain text laying arround...

So, as you can see, the options are quite numerous limited only by the technology you are using.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.