Design: Service Oriented Databases
With Visual Studio Orcas on the horizon, one of the big features that I’ll be looking forward to is LINQ. The ability to create queries and expression trees against objects in code, to delay execution of them, to parse and interpret the trees and to project the results with anonymous types has me hot under the collar. But there’s one feature in LINQ that I won’t be looking forward to, and that feature is called DLINQ.
DLINQ takes a standard LINQ query and interprets it and turns it into a dynamic SQL statement. You decorate your classes and properties with attributes, create a database context, and any standard LINQ query that you might write can be executed directly against the database. DLINQ handles the generation of the SQL and mapping it back to objects, and because it’s objects that you’re working with you get full intelligence and strong-typing. You can learn more about DLINQ here.
Sounds like a great concept, right?
Service Oriented Architecture
Not so long ago now, developers were hot under the collar about something else: web services. It wasn’t so much that you could invoke stuff on the other side of the internet using nothing but HTTP, though that alone was a tremendous breakthrough because it meant we didn’t have to involve infrastructure guys, which reduced the cost of I.T. projects by 98.32%. It was the idea of Service Oriented Architecture, in other words, programming to a contract.
Programming to a contract is such a great concept because it means “stuff can change” and you won’t be affected:
Of course, programming to a contract is not unique to service oriented architectures - it’s also one of the core object-oriented design principles. Page 11 of Head First Design Patterns puts it loud and clear: “Program to an interface, not to an implementation”. Programming to an implementation is considered an anti-pattern, because it introduces coupling:
When we connect our application to a database such as SQL Server, we have the option of programming to an interface, or programming to an implementation.
Like perhaps 50% of .NET developers, I’m a huge fan of stored procedures. Here’s a list of reasons that are NOT the reasons why I like stored procedures:
- I love T-SQL
- Stored procedures out-perform dynamic SQL
- Stored procedures are compiled
- Stored procedures can have schema binding so you won’t break things
- Stored procedures are more secure
All of these are personal things, vary widely depending on the scenario, or are debatable as to their truthfulness. No, the number one reason why I like stored procedures is this one, undeniable fact:
Stored procedures are a CONTRACT
They are your contract with those odd-ball DBA’s down the hall who spend their time writing Palindromes in T-SQL but still haven’t figured out how to use SQLCLR or how to expose stored procedures as web services (NB: I do count Rob as an odd-ball, but he does in fact know how to use SQLCLR and web services :)).
You: I want to list some customers and their total orders for the month, not including orders that haven’t been charged yet.
DBA: Fine, but to do that, you must supply me with the last name of the customers to search. And it must be a palindrome.
The header of the stored procedure defines the parameters that the developer must supply, and the body is where the DBA puts all of their voodoo magic to get the data you requested.
The reason stored procedures are a contract is because they insulate the database from the application. So long as the contract is still met, the DBA’s have full control over their database. They can drop tables, add tables, split tables into two, normalize them, put them in other databases, replace columns with XML, rename columns, even drop columns, and the application should not be affected. I think the ownership of the layers of an application should look similar to this:
So why is DLINQ bad?
The issue I have with DLINQ is the issue I have with anything that directly accesses the database without respecting a contract. Code generators that generate dynamic SQL, Ruby on Rails that generates SQL at runtime, DataSets with embedded “SELECT” statements, and ASP.NET’s SqlDataSource with embedded dynamic SQL all fall into this category. Even custom, hand-crafted domain models that generate dynamic SQL, by using pattern’s like Fowler’s Query Object, fall into this category.
Of course, DLINQ can be used to execute stored procedures. But if you do that, what advantage do you get from DLINQ? Without dynamic queries, which rely on dynamic SQL, DLINQ is nothing but an over-hyped object-relational mapper. It’s got nothing to offer over libraries we’ve had for years, such as NHibernate, except that it will come out of the box.
Service Oriented Databases
If you work in a team where you have direct table access, imagine this: One day, you arrive at work only to discover that the DBA’s have SELECTed themselves out from underneath their T-SQL rocks to discover that they can expose their stored procedures as web services.
Bad luck to you. Now those pesky DBA’s are regaining control. They can normalize their dirty schemas, fix naming conventions, apply some security, put tables in completely different databases altogether, and in general, do what they were hired to do. Meanwhile, after rewriting your application to call the web services, you have nothing to do. While the DBA’s are making all of these ground-breaking changes and increasing performance, you’re sitting idle, because none of their changes are affecting you. Poor, unfortunate you 
Just stop reading for a moment and really try to think of your database as a service, doing stuff for lots of people but always through contracts, always by encapsulating its internals.
Now, since it’s unlikely DBA’s are going to figure out what SOAP is (both the protocol and the bath toy) anytime soon, consider stored procedures as your “web service endpoints”.
But databases don’t need contracts! We have full control!
If you’ve read this far, the only possible reason that you can think DLINQ is useful is because you don’t see the need to put a contract in place between it and your application. Chances are, your developers are building the application as well as the database at the same time, and there isn’t a DBA within 15 miles. You’ve got no issues with the database schema changing, because if it does the developer who changes it can also change the application. DLINQ is a gift.
Of course, there’s always an “it depends”, and perhaps you are one of the very lucky few who will find dynamic database queries in LINQ useful. But perhaps things might not be as rosy as they seem.
Have a think about how many projects you’ve been on where you’ve written a completely new database for an existing application, without changing the application. Chances are it’s zero (I’d LOVE to hear otherwise).
Now, think about how many times you’ve written a new application, but you’ve had to pull data from an existing database. For most of us that number probably hovers around 50%.
How much fun was it to use that existing database? How many stupid schema designs, broken relationships and de-normalized objects did that thing have? If your experiences are anything like mine, it was probably hell to work with. You probably wanted to change it, but chances are likely that it was still being used by other applications, applications which used dynamic SQL, and so you can’t change it.
Does that sound familiar?
There are two generalizations we should make when we think about solution design:
- In general, a database is going to live longer than the applications using it, even when they’re built at the same time.
- In general, a database is going to eventually be consumed by multiple applications. It might not be on your requirements list, but it will happen.
The conclusion to this is that if you use dynamic SQL, you lock the schema of that database for all eternity. You may have moved on by then, but generations of applications will be built upon the schema you’ve designed, and there will be absolutely no room for change. Eventually the database will be migrated and rewritten, along with the applications, and the cycle will repeat.
Unfortunately, the majority of developers I meet seem to think that “architecture” means choosing a code generator or O/R mapper, and that SQL Server and T-SQL is below them or too complex to learn. So, no doubt, DLINQ is going to be amazingly popular.
Please, think of the children; program to interfaces.
Filed under: Architecture
[…] Posted in Coding by Will on May 4, 2007. Paul Stovell just wrote an[other] excellent article. Paul says that (basicly) you should be thinking of interfaces to your database as a contract. […]
Dang… there’s the money-shot right there. Great article!
Wouldn’t code generators be very useful if you’re programming to interfaces that are implemented as sproc wrappers?
You could implement the interface with DLINQ for all anyone cares - I gotta admit, DLINQ is pretty intellisexy.
I guess sprocs make it harder for the kids to screw up.
BTW, this is probably one of the best examples I’ve read of why you should program to interfaces. Plus that article on BOP, I think you ought to write a book, “Paul on Bloody Good Programming Practices”
2 cents
Chris
You don’t seem to mention the benefits of having a shared language that allows you to query databases and in memory objects (of for that matter XML) in the same language. Personally I’ve seen many systems that use SQL to do inappropriate things that would be much faster in .Net (SQL CLR intergration isn’t).
Nice diagram of the application being one big block :op I will always have a contract between the logical and business layers for all the reasons you mention… is just the boundaries between technologies that may move.
And why should DBA’s be exempt from learning new technologies? :o)
Nice post. I was talking exactly about this with Joel Pobar at CodeCampOz that DLINQ is completely broking any contract-first development.
However please take note that stored procedures are *not* contracts. Unfortunately they miss an [out] contract. They only define the in parameters but you never know what is going to come out of them. If that could be changed by the SQL team to allow an SP to define an [out] contract just like SQL Functions can do that would be truly great.
Corneliu.
Nice, Nice. Happy those numerous chats didn’t go to waste :op. You’re absolutely right. There are also other consideration regarding leveraging the power of SQL 2005 that using dynamic SQL just won’t give you.
Hi Corneliu,
Good point. Stored procedures certainly leave a lot to be desired as far as their suitability as a contract goes, but unless DBA’s adopt web service procedures they’re about the closest thing we’ll get. I suppose it’s more of a “verbal contract” in that you all know what the procedure is returning, but it’s not enforced as much as it could be from a technical perspective.
A feature I like about Oracle’s PL/SQL is that you can do most of your “selects” as [out] tables, meaning you know exactly how many tables of results are going to come back (and that they’ll be named). However I’m not sure if you can assign a schema to them? Oracle’s seperation between package headers and package bodies is also kinda neat in that it would allow the developer to define the header (contract) and the DBA to define the body (implementation).
Paul
So tell us what you really think…. actually this is one of the best discussions I’ve seen around Dlinq. I’m not sure I 100% share your disapproval but found this an interesting take on the subject.
What do you make of ADO.NET Entity framework, not to mention Jasper and Astoria? There must be 2 or 3 more good articles there
ODBC is not an API
ODBC is not an API
I still can’t see a point in the denial of DLINQ.
You’re quite rightly pointed out that one database can be used by a number of applications.
Imagine if every application puts logic in Stored Procs in the same database. What are you going to end up with? Loads of hardly manageable rubbish is my answer.
Following changes in the DB is a whole new animal. No, you don’t lock the schema by using dynamic query.
If the database schema is changes, then chances are you need to change stored procs. You might as well change your DLINQ code. What’s the difference? You can easily track changes in DB by unit tests in your C# code - if a test that generates query and subsequently calls the DB fails you fix it.
This, in fact, makes your application more robust and more testable - if all your logic in C# code, you can unit test it. It’s hell of a lot harder to unit test business logic buried in stored procs. It is hardly done and I’ve never seen it done properly. Most of the unit tests of stored procs make sure that it runs and doesn’t cause exceptions, while a C# code for the query can be easily tested with great results. But, unit testing is a whole new topic
I agree with Alex. Stored procs are better then executing queries directly on database but when business logic becomes complex, I think its better to move business logic out of database to some middle layer in c# code. Apart from being easier to unit test, you can also use mature exception handling and logging. Also in object oriented code,logic might be better organised and if business logic becomes very very complex you can always use various APIs and/or rule engines.
May be I am a bit biased but personally I prefer not to store any business logic in stored procs
Thanks for the thought-provoking post.
DBA’s renaming and dropping columns and tables? Everywhere I’ve worked, it’s either the developers doing that (because there are no DBA’s) or the DBA’s shoot anybody that so much as considers renaming something. Consequently I don’t appreciate the need for stored procedures as an insulation layer between code and schema, in my experience. When I have seen schemas change dramatically, it is in parallel to a big upheaval in application logic — so everything is changing anyway. Insulation between the server and the client is of no use when both are changing at the same time for the same reason, which I have found tends to be the case. Or put another way: schemas often change in ways that necessitate application-data interfaces changing — like it’s often the whole point of updating the schema: to expose some new data feature for the application to exploit.
I’m not saying the insulation would never be useful, just that I haven’t seen it be very useful very often on the projects that I’ve worked on.
I suspect that this issue, like many argued by software engineers on the Internet, is contextual and more relative to the project and the shop than seems intuitive at first.
I also appreciate Alex’s previous point that the DAL may as well be in C# rather than stored procedures, one or the other is not obviously better, both have strengths and weaknesses.
Ahh.. nice discussion! Well about that reusability of the data in other applications. That is where SOA comes in! The contract is the application together with its business logic. The database only purpose in life is to store data in a reliable and efficient manner to be retrieved in lightspeed seconds if possible.
So if other applications want to use the data then consume it through its transport neutral soap front-end and get security and business validation for free!
Well.. not for free but a damn good contract that is!
I agree with Alex Salamakha.
You are right when you tell that there always must be a ‘layer’ between the database structure and the application. The question is: where this layer is supposed to be?
You believe it must be in the database and Alex thinks it must be in application.
It would be very cool if you write another post with the answer to Salamakha.
I agree with Alex as well.
You should also consider other point: The ease of use of DLINQ. It is much easier to form a query in DLINQ query language than in TSQL, by using DLINQ you don’t need remeber how to join complex table hierachies. According to my experience with NHibernate and similar software, O/R mapping saves reasonable percentage of worktime, makes the application source code smaller, testable and easier to read. You can also achieve multidatabase support if needed.
Hmmm….so basically you’re moving a chunk of your application code into the database plus exposing the db’s stored procs. as endpoints. Leaving aside that stored procs are not the best place to develop any kind of code you’re actually doing several bad things here:
Tightly coupling yourself to your database - what if in the future the data provider for your service changes - maybe to another system/webservice or some other technology - not so SOA now are you.
Failing to take advantage of the productivity enhancements of DLINQ (or whatever ORM you choose - I don’t care that DLINQ is late to the party - it’s a non-issue).
If you want to have a contract just do it in your application data layer and expose this as a service. This leaves you free to modify the implementation as you please - stored procs, dlinq, nhibernate, files, post-it nots - whatever.
I don’t see an issue with ORM layers here at all, and most benefits of stored-procs (performance) tend to be wildly over stated.
Thanks for the thought provoking piece though…..you haven’t won me over though and I’m looking forward to using DLinq, or the best tool for the job as appropriate.
Ant
AN orm Like DLINQ isn’t just translating native queries to SQl, its providing you with alot more things such as the ability to defines units of work, caching, lazy loading, proxies and transactions. Stored procs may provide a contract, but not a very strongly typed one like you can expose using the CLR. The idea that SQL runs faster in a stored proc is just rubbish, unless your still using some old database like SQL Server 6.
If your building large scale enterprise systems you typically build a service layer to hide the fact a database is even there. You might not realise this but NOT everyone uses SQL Server.
Another point that I think you are missing is that you can write your DLINQ queries and NOT HAVE TO KNOW TSQL. That is the biggest benefit in my opinion. So you want to change from SQL Server to mySQL. Go ahead, you don’t need to rewrite your entire query system to do it.
DLINQ is an abstraction from SQL. You mess with your objects, and the COMPILER tells you if you have broken the type safety. Not a runtime error from a TSQL parser that is totally dependent on the version of that parser. You get a verification at compile time that you are not breaking your contracts of data types. How many times of you seen TSQL code blow up because of a failed CAST? I see them a lot. DLINQ prevents that because you are strongly typed during compile.
Alas it’s a religious war between OO coders and database coders! But the real struggle is about accountability and responsibility.
I think a lot of DBA’s would be fine with DLINQ if the OO coders who are writing DLINQ will be the ones responsible for said database performance. Unfortunately database perf responsibility is placed on the dba’s shoulders.
“You are right when you tell that there always must be a ‘layer’ between the database structure and the application. The question is: where this layer is supposed to be?”
Nail on the head. It will be demarked with whomoever is responsible for that part of the code.
Why should I as a dba suffer because you as an OO coder don’t understand databases and grind my server to a halt?
Why should I as an OO coder be forced into the crappy, unflexible, and stodgy interface you dba’s provide me?
I’ve seen this go back and forth during my 10 years as an IT professional, and don’t see DLINQ changing it.
[…] treffende Beschreibung stammt aus dem Post Service Oriented Databases auf […]