26

After using Hibernate on most of my projects for about 8 years, I've landed on a company that discourages its use and wants applications to only interact with the DB through stored procedures.

After doing this for a couple of weeks, I haven't been able to create a rich domain model of the application I'm starting to build, and the application just looks like a (horrible) transactional script.

Some of the issues I've found are:

  • Cannot navigate object graph as the stored procedures just load the minimum amount of data, which means that sometimes we have similar objects with different fields. One example is: we have a stored procedure to retrieve all the data from a customer, and another to retrieve account information plus a few fields from the customer.
  • Lots of the logic ends up in helper classes, so the code becomes more structured (with entities used as old C structs).
  • More boring scaffolding code, as there's no framework that extracts result sets from a stored procedure and puts it in an entity.

My questions are:

  • has anyone been in a similar situation and didn't agree with the store procedure approach? what did you do?
  • Is there an actual benefit of using stored procedures? apart from the silly point of "no one can issue a drop table".
  • Is there a way to create a rich domain using stored procedures? I know that there's the possibility of using AOP to inject DAOs/Repositories into entities to be able to navigate the object graph. I don't like this option as it's very close to voodoo.

Conclusion

First, thank you all for your answers. The conclusion that I've arrived is that ORMs don't enable the creation of Rich Domain models (as some people mentioned), but it does simplify the the amount of (often repetitive) work. The following is a more detailed explanation of the conclusion, but is not based on any hard data.

Most applications request and send information to other systems. To do this, we create an abstraction in the model terms (e.g. a business event) and the domain model sends or receives the event. The event usually needs a small subset of information from the model, but not the whole model. For example in a online shop, a payment gateway requests some user information and the total to charge a user, but doesn't require the purchase history, available products, and all the customer base. So the event has a small and specific set of data.

If we take the database of an application as an external system, then we need to create an abstraction that allows us to map the Domain Model entities to the database (as NimChimpsky mentioned, using a data-mapper). The obvious difference, is that now we need to handcraft a mapping for each model entity to the database (either a legacy schema or stored procedures), with the extra pain that, since the two are not in sync, one domain entity might map partially to a database entity (e.g a UserCredentials class that only contains username and password is mapped to a Users table that has other columns), or one domain model entity might map to more than one database entity (for example if there's a one-to-one mapping on the table, but we want all the data in just one class).

In an application with a few entities, the amount of extra work might be small if there's no need to transverse the entities, but it increases when there's a conditional need to transverse the entities (and thus we might want to implement some kind of 'lazy loading'). As an application grows to have more entities, this work just increases (and I have the feeling that it increases non-linearly). My assumption here, is that we don't try to reinvent an ORM.

One benefit of treating the DB as an external system, is that we can code around situations in which we want 2 different versions of an application running, in which each application has a different mapping. This becomes more interesting in the scenario of continuous deliveries to production... but I think this is also possible with ORMs to a lesser extent.

I'm going to dismiss the security aspect, on the basis that a developer, even if he doesn't have access to the database, can obtain most if not all the information stored in a system, just by injecting malicious code (eg. I can't believe I forgot to remove the line that logs the credit card details of the customers, dear lord!).

Small update (6/6/2012)

Stored procedures (at least in Oracle) prevent doing anything like continuous delivery with Zero downtime, as any change to the structure of the tables will invalidate the procedures and triggers. So during the time that the DB is being updated, the application will be down too. Oracle provides a solution for this called Edition-Based Redefinition, but the few DBAs I've asked about this feature mentioned that it was poorly implmented and they wouldn't put it in a production DB.

Improve this question
9
  • Well, obviously you could do what Hibernate does and use inheritance for generating a dynamic proxy object, which allow you to retrieve the object graph. That's extremely hacky with SP though :D
    – Max
    Mar 21, 2012 at 10:52
  • So I would end up reinveinting half of hibernate, without the 10+ years of experience the hibernate team has :).
    – Augusto
    Mar 21, 2012 at 10:54
  • 1
    Any DBA should prevent the dropping of particular tables by certain users. It shouldn't matter how you attempt to do it.
    – JeffO
    Mar 21, 2012 at 12:22
  • 1
    You might take a look at Mybatis - it might provide the feature you need. It's less of an ORM than a mapping framework. You can write SQL however you like and tell Mybatis where to put it on your object model. It will handle large object graphs with multiple queries, which sounds like the situation you have (lots of thin stored procedures).
    – Michael K
    Mar 21, 2012 at 12:47
  • 1
    @Augusto: I have been in a similar situation, not due to use of SPs, but due to use of a proprietary mapping framework that did not support object relationships. We spent days writing code that could be written in minutes using a proper ORM. I never did get that problem solved.
    – kevin cline
    Mar 21, 2012 at 16:58

5 Answers 5

Reset to default
17

Your application should still be modelled from domain driven design principles. Whether you use an ORM, straight JDBC, calling SPs (or whatever) should not matter. Hopefully a thin layer abstracting your model from the SPs should do the trick in this case. As another poster stated, you should view the SPs and their results as a service and map the results to your domain model.

Improve this answer
7
  • Martijn, I agree that the app should be modelled using DDD principles, but the problem I'm facing (and please tell me if there's a solution!!) is that some stored procs return very little information to instantiante a DDD entity. Please see this comment where I explained a bit more about the information that the stored procedures return. I could circumvent this, by invoking more than one stored proc, and for example retrieving all the user details and then invoke another one to retrieve all the account info, but it feels wrong :).
    – Augusto
    Mar 21, 2012 at 12:17
  • 1
    @Augusto Well... you're app developer, so you have to decide whether it makes sense for certain object to exists with certain fields set to NULL. If it makes sense (for certain task for example) then let it be. If not, ask the author of SP to provide more data, so you can create your objects.
    – Jacek Prucia
    Mar 21, 2012 at 16:25
  • And adding to Jacek's comment - it's actually perfectly acceptable to call 2+ stored procs, again think of them as two remote services that you have to call in order to create your domain model, nothing wrong with that :-).
    – Martijn Verburg
    Mar 21, 2012 at 16:39
  • @Martijn: In my experience, a thin layer isn't sufficient. The mapping code may be considerably longer than the underlying business logic.
    – kevin cline
    Mar 21, 2012 at 17:00
  • @Kevin cline - Good point, have put 'hopefully' in the answer :-)
    – Martijn Verburg
    Mar 21, 2012 at 22:21
5

Is there an actual benefit of using stored procedures?

In the financial world (and places where Sarbanes-Oxley compliance is required), you need to be able to audit systems to ensure that they do what they are supposed to do. In these cases, it is much easier to ensure compliance when all data access is through stored procedures. And when all ad-hoc SQL is removed, it is much harder to hide things. For an example of why this would be a "good thing", I refer you to Ken Thompson's classic paper Reflections on Trusting Trust.

Improve this answer
4
  • yes a million times yes! You also need to make sure that users cannot do anythign they aren't supposed to do including not having direct rights to tables and stored procs help that tremendously.
    – HLGEM
    Mar 21, 2012 at 17:12
  • 2
    I work for a public company and we are SOX sompliant. It might be my poor knowledge of auditing, but I don't see the difference between doing the auditing at the DB level (via stored procs) or at the application level. Each application should have its own DB schema and that schema is only accessible from the application, rather than shared between different applications.
    – Augusto
    Mar 22, 2012 at 10:38
  • broken link ...
    – Alex R
    Apr 20, 2019 at 21:46
  • @AlexR, fixed link
    – Tangurena
    Apr 23, 2019 at 20:58
3

What often happens is that developers incorrectly use their ORM objects as their domain models.

This is incorrect, and ties your domain directly to your DB schema.

What should really have is seperate domain models as rich as you like and use the ORM layer seperately.

This means you will need mapping between each set of objects.

Improve this answer
8
  • 2
    This is a good idea but for smaller projects it really starts to feel like overkill. This approach also requires a translation layer between the ORM persistence layer and the domain model.
    – maple_shaft ♦
    Mar 21, 2012 at 11:39
  • @maple_shaft agreed, and that's what I meant by "mapping" :-)
    – ozz
    Mar 21, 2012 at 11:42
  • @Ozz, the way I've worked is exactly that, the entity classes ARE the domain model (and I might add with quite lots of success). I agree that it ties the domain model to the schema, but that's exactly what I want, as I use convention over configuration, and the nice side effect is that if I see a field on an entity, I don't need to think hard about the name of the table and column where that information is stored.
    – Augusto
    Mar 21, 2012 at 11:54
  • @Augusto I've done it too! and as maple_shaft says, it's fine for small CRUD style apps, but there are many issues as the OP is finding out. One example might be where you have a many to many mapping table eg: StudentClasses, that maps Students to their classes and just contains StudentID and classID, you would not necessarily want to map this in your domain. That's just a quick off the top of my head example.
    – ozz
    Mar 21, 2012 at 12:07
  • 3
    @Ozz: Your comment seems to contradict the very idea of an ORM. An ORM doesn't "tie your domain directly to your DB schema". The ORM maps your domain to a DB schema, without the need for a separate DAO layer. That's the whole point of an ORM. And most ORMs handle many-to-many mappings just fine, with no domain model needed for the mapping table.
    – kevin cline
    Mar 21, 2012 at 16:54
2

Stored procedures are very much more efficient than and client-side SQL code. They pre-compile SQL in the DB which also allows it to perform some optimisations.

Architecturally, a SP will return the minimum data required for a task, which is good as it means less data is being transferred. If you've got such an architecture, you need to think of the DB as a service (think of it as a web service and each SP is a method to call). It shouldn't be a problem to work with it like this, whereas an ORM guides you into working with remote data as if it was local, thus tricking you into introducing performance issues if you're not careful.

I've been in situations where we used SPs completely, the DB provided a data API and we used it. That particular app was very large scale and performed amazingly well. I won't have anything bad said about SPs after that!

There's another advantage - the DBAs will write all your SQL queries for you, and will happily handle all the relational hierarchy in the DB, so you don't have to.

Improve this answer
7
  • 3
    gbjbaanb, most of what you said is true for older databases. Most newer databases recompile queries quite often to decide which new optimizations to use (even if they are in stored produced). I agree with what you said about using the DB as an external system, but I also see that as a lot of work, as the application owns the database and both should be in sync as much as possible. For example with the naming of table/classes and fields/columns. Also, the approach of letting the DBAs write the procedures smells like development silos, rather than a having a multidisciplinary team.
    – Augusto
    Mar 21, 2012 at 11:39
  • 7
    SPs aren't necessarily always more efficient and I think handing off SQL to DBAs is a bad way to go. As a domain expert the developer should know what data they want to get and how to get it
    – Martijn Verburg
    Mar 21, 2012 at 11:40
  • 2
    This is a good answer, but in my experience most clients don't actually need the performance gains for controlling data access through stored procedures versus the inconvenience of making full use of your ORM tools on the application layer. More often than not I see these architectural decisions made in software shops where they have a need to justify the bloated salaries of "grey beard" stored procedure programmers that have no other skills.
    – maple_shaft ♦
    Mar 21, 2012 at 11:48
  • 1
    @Augusto the approach of letting the DBAs write the procedures smells like development silos +100 internets to you for this gem of truth. I have always seen this as the case where data access was controlled through stored procedures.
    – maple_shaft ♦
    Mar 21, 2012 at 11:50
  • 1
    @maple_shaft: why, aren't the DBAs who write SPs considered part of the developer team? Where it works, they are specialist coders who know this aspect of the system really well, much better than most general-purpose developers. This could be the problem that's given rise to ORMs popularity. I mean, no-one would think twice about having a designer do the GUI, so why the hate for a data architect doing the schema?
    – gbjbaanb
    Mar 21, 2012 at 23:55
1

Your domain objects can be populated however you please, its not neccesary to use Hibernate. I think the proper term is data-mapper. Its very possible that your persisted data will be completely different structure to your domain objects.

Improve this answer
4
  • We're currently using data mappers, but the problem is that they stored procs return a minimal set of data, which sometimes is not enough to fill an object (maybe we should allow the stored procedures to return more information). For example, one store proc might return a user email, forename, surname; while another one adds userid and an address. Since the data is different we're using different objects to store the data, which means that we have different 'User' classes. I'm trying to avoid using inheritance here, because I think it's a wrong use of it.
    – Augusto
    Mar 21, 2012 at 11:45
  • @Augusto: Interfaces?
    – Kramii
    Mar 21, 2012 at 12:17
  • @Karmii, I don't think the interfaces help here, as we then would need to duplicate the logic in different classes. Or we could use interfaces and then delegate the processing to a helper class, but that's not really OO :(.
    – Augusto
    Mar 21, 2012 at 12:22
  • 1
    @Augusto I don't understand the problem : "stored procs return a minimal set of data, which sometimes is not enough to fill an object" So you alter the sproc or create another one, and then let the data mapper do the mapping
    – NimChimpsky
    Mar 21, 2012 at 12:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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