Avoid Using a Database as an API Integration Point

0 comments suggest edit

Before I begin, I should clarify what I mean by using a database as an API integration point.

In another life in a distant galaxy far far away, I worked on a project in which we needed to integrate a partner’s system with our system. The method of integration required that when a particular event occurred, they would write some data to a particular table in our database, which would then fire a trigger to perform whatever actions were necessary on our side (vague enough for ya?).

In this case, the data model and the related stored procedures made up the API used by the partner to integrate into our system.

So what’s the problem?

I always felt this was ugly in a few ways, I’m sure you’ll think of more.

  1. First, we have to make our database directly accessible to a third party, exposing ourselves to all the security risk that entails.
  2. We’re not really free to make schema changes as we have no abstraction layer between the database and any clients to the system.
  3. How exactly do you define a contract in SQL? With Web Services, you have XSD. With code, you have interfaces.

Personally, I’d like to have some sort of abstraction layer for my integration points so that I am free to change the underlying implementation.

Why am I bringing this up?

A little while ago, I was having a chat with a member of the Subtext team, telling him about the custom MembershipProvider we’re implementing for Subtext 2.0 to fit in with our data model. His initial reaction was that developer-users are going to grumble that we’re not using the “Standard” Membership Provider.

The “Standard”?

I question this notion of “The Standard Membership Provider”? Which provider is the standard? Is it the ActiveDirectoryMembershipProvider?

It is in anticipation of developer grumblings that I write this post to plead my case and perhaps rail against the wind.

The point of the Provider Model

You see, it seems that the whole point of the Provider Model is lost if you require a specific data model. The whole point of the provider model is to provide an abstraction to the underlying physical data store.

For example, Rob Howard, one of the authors of the Provider Pattern wrote this in the second part of his introduction to the Provider Pattern (emphasis mine).

A point brought up in the previous article discussed the conundrum the ASP.NET team faced while building the Personalization system used for ASP.NET 2.0. The problem was choosing the right data model: standard SQL tables versus a schema approach. Someone pointed out that the provider pattern doesn’t solve this, which is 100% correct. What it does allow is the flexibility to choose which data model makes the most sense for your organization. An important note about the pattern: it doesn’t solve how you store your data, but it does abstract that decision out of your programming interface.

What Rob and Microsoft realized is that no one data model fits all. Many applications will already have a data model for storing users and roles.

The idea is that if you write code and controls against the provider API, the underlying data model doesn’t matter. This is emphasized by the goals of the provider model according to the MSDN introduction…

The ASP.NET 2.0 provider model was designed with the following goals in mind:

  • To make ASP.NET state storage both flexible and extensible \
  • To insulate application-level code and code in the ASP.NET run-time from the physical storage media where state is stored, and to isolate the changes required to use alternative media types to a single well-defined layer with minimal surface area
  • To make writing custom providers as simple as possible by providing a robust and well-documented set of base classes from which developers can derive provider classes of their own

It is expected that developers who wish to pair ASP.NET 2.0 with data sources for which off-the-shelf providers are not available can, with a reasonable amount of effort, write custom providers to do the job.

Of course, Microsoft made it easy for all of us developers by shipping a full featured SqlMembershipProvider complete with database schema and stored procedures. When building a new implementation from scratch, it makes a lot of sense to use this implementation. If your needs fit within the implementation, then that is a lot of work that you don’t have to do.

Unfortunately, many developers took it to be the gospel truth and standard in how the the data model should be implemented. This is really only one possible database implementation of a Membership Provider.

An Example Gone Wrong

There is one particular open source application that I recall that already had a fantastic user and roles implementation at the time that the Membership Provder Model was released. Their existing implementation was in all respects, a superset of the features of the Membership Provider.

Naturally there was a lot of pressure to implement the Membership Provider API, so they chose to simply implement the SqlMembershipProvider’s tables side by side with their own user tables.

Stepping through the code in a debugger one day, I watched in disbelief when upon logging in as a user, the code started copying all users from the SqlMembershipProvider’s stock aspnet_* tables to the application’s internal user tables and vice versa. They were essentially keeping two separate user databases in synch on every login.

In my view, this was the wrong approach to take. It would’ve been much better to simply implement a custom MembershipProvider class that read from and wrote to their existing user database tables.

For the features of their existing users and roles implementation that the Membership Provider did not support, they could have been exposed via their existing API.

Yes, I’m armchair quarterbacking at this point as there may have been some extenuating circumstances I am not aware of. But I can’t imagine doing a full multi-table synch on every login being a good choice, especially for a large database of users. I’m not aware of the status of this implementation detail at this point in time.

The Big But

Someone somewhere is reading this thinking I’m being a bit overly dogmatic. They might be thinking

But, but I have three apps in my organization which communicate with each other via the database just fine. This is a workable solution for our scenario, thank you very much. You’re full of it.

I totally agree on all three counts.

For a set of internal applications within an organization, it may well make sense to integrate at the database layer, since all communications between apps occurs within the security boundary of your internal network and you have full control over the implementation details for all of the applications.

So while I still think even these apps could benefit from a well defined API or Web Service layer as the point of integration, I don’t think you should never consider the database as a potential integration point.

But when you’re considering integration for external applications outside of your control, especially applications that haven’t even been written yet, I think the database is a really poor choice and should be avoided.

Microsoft recognized this with the Provider Model, which is why controls written for the MembershipProvider are not supposed to assume anything about the underlying data store. For example, they don’t make direct queries against the “standard” Membership tables.

Instead, when you need to integrate with a membership database, use the API.

Hopefully future users and developers of Subtext will also recognize this when we unveil the Membership features in Subtext 2.0 and keep the grumbling to a minimum. Either that or point out how full of it I am and convince me to change my mind.

See also: Where the Provider Model Falls Short.

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.



18 responses

  1. Avatar for Jon Limjap
    Jon Limjap April 23rd, 2007

    Indeed, a lot of the abuse that is done using Microsoft technologies happen because of the abuse of the *samples* provided by Microsoft.
    I believe that the belief that there is such a thing as a "standard" membership provider comes with the fact that most examples are written using that standard -- usually the SqlMemberShip provider that connects, not to a SQL Server 2005 database, but a SQL Server Express 2005 database at that.
    Perhaps there's really a need to not only educate the Microsoft developer masses beyond Microsoft samples, but make them aware that there are ways of doing things outside of that "sample space".

  2. Avatar for StefanVE
    StefanVE April 23rd, 2007

    To Jon:
    I aggree there is no such thing as a standard membership provider and the purpose of the provider model is enabling pluggin in a custom implementation.
    I do not think however the sqlMembership provider is only meant as a 'sample' and if I do not have special requirements, I will use the sqlMemberShip provider. (The code is written, tested and has an admin website allready build. What is the benefit of writing my own ?) Also, it does work on sql2000 db if you modify the connectionstring and install the stp's. (there is a single script that does this).
    To Phil:
    I think I have seen the the OS app that does the copying. If it is the same app : They talk about the why's in a recent dnr-episode. (basically : they need cross site,multiple portal membership), but I also cannot understand why they have kept the sqlMembership tables. Maybe it's to help apps that are going to use the sqlmembership provider on the same database. Whatever the reason, I really did not like the end-result.

  3. Avatar for Sergio
    Sergio April 23rd, 2007

    Hmmmm... popular open source app in .net that uses the provider stufff.... Hmmm, there can't be many. Of course, popular doesn't mean good, and neither does open source. I had the very same feeling you had when I did the debugger step-through thing. By the way, if there's a reason for disliking the provider pattern or DI/IoC/Service Provider, it is the debugging experience. It sucks trying to figure out which code will be invoked when you step into a method from an interface or abstract class variable. The "popular open source app" in question made things even worse by being written in VB.NET (another clue) which is unbearably slow inside the IDE for a large project like that.

  4. Avatar for Steven Harman
    Steven Harman April 23rd, 2007

    @Jon: I agree completely that a lot of the sample code has been heavily abused by many developers. And when it comes to the Membership providers being abused, I think it was a huge mistake on their part to build the SqlMembershipProvider in a way such that it automagically connects to, creates, and uses a SQL Server 2005 Express db.
    IMO, this only leads to more abuse as it seems like the standard way to do things... I mean it works right out of the box. At least if the developer had set a connection string to get the SqlMembershipProvider to work they might realize that

    hey... there are other ways to do this!

    @Phil: Just for the record... I wasn't the member of the Subtext team you were referring to, was I? And if I was, it's OK for you to call me out directly - peer pressure is a powerful motivator.
    *prepares to hang head in shame... just in case*

  5. Avatar for Haacked
    Haacked April 23rd, 2007

    @Steve: No, it wasn't you. Not everything is about you Steve. ;)
    And I disagree, I think the the SqlMembershipProvider using SqlExpress is actually a great contribution. We have to keep in mind that ASP.NET has a huge and diverse user base.
    One of the ways to sell a platform is to make the "Out of box" experience" sublime by making it incredibly easy to get it up and running immediately, without requiring 20,000 steps. SQL Server Express is actually not a bad database and capable of handling the needs of a wide range of developers.
    However, there's also many developers who know immediately that Express won't meet their needs. It's incumbent on these developers to step beyond the defaults. I think Microsoft provides plenty of good documentation on the provider. The question is, is anyone reading them? Are they good enough?

  6. Avatar for Ryan Smith
    Ryan Smith April 23rd, 2007

    I built a CMS for a large client with ASP.NET 2.0 right as it came out. I decided to use the default membership provider for the user registration and everything else.
    It has turned out to be the bane of my existence due to the fact that anytime I want to do something unique, I'm forced in to hammering it into the Microsoft way of things.
    I would like to note that the Microsoft way of doing things isn't necessarily bad, but since I would approach it in a different way I end up with some really ugly hack code.
    I would love to go back and redo the entire user / membership tables in the way I would have done them, but I fear that it is far too late for that.
    The lesson that I learned from this is "Don't use a new programming pattern if you already know how to do it in a familiar way"

  7. Avatar for Evan
    Evan April 24th, 2007

    An abstraction between the database and application, brilliant!
    Now if only I could do that with my Entities..oh wait.. ;-)

  8. Avatar for Joe Brinkman
    Joe Brinkman April 24th, 2007

    I may have some insight into that .Net Open Source Application that shall not be named. You see one thing you forgot to mention regarding a membership provider, is that sometimes membership is meant to span more than one application. If you have 5 web applications with 5 custom membership implementations, then a user would need 5 separate sets of credentials - 1 for each app. However, if these 5 applications shared a common implementation then it would be possible for a user to login to all 5 apps using a single set of credentials.
    The application in question was/is part of a shared hosting program sponsored by a very large software company. This company laid out a goal for all web apps in the program to be able to share membership thereby allowing a user's site's to have a single login even though the site might actually consist of more than one application. Alas, for a number of reasons we won't discuss here, the actual sharing of the membership tables never occured, but by the time it became apparent that this would not happen, it was too late in the release cycle to really go and change things.
    Subsequent releases of "The app that shall not be named" have addressed some of the limitations encountered by the first implementation and we... err I mean they... expect that future releases will probably jettison the core membership tables altogether the same way the core profile and role providers were jettisoned in favor of custom implementations.
    One final note: In the future I think you will see problems like this handled by implementations that use a federated approach to membership. This would allow each application to maintain it's own custom membership data, while allowing users to maintain their "official" set of credentials in a common service. You can see this with Passport, Cardspace, OpenID and Liberty Alliance. I think ultimately, that relying on a built in db for sharing will go away and people will instead share this type of information through a trusted intermediary who provides the necessary abstraction between partner applications.
    PS: Sorry for hijacking your blog like this since it does seem in bad form for the comment to be almost as long as the post itself, but I have never been a big fan of answering a blog with a blog when the comment area seems so much more appropriate.

  9. Avatar for BigJimInDC
    BigJimInDC April 24th, 2007

    Just some food for thought, but is this kind of integration not what a VIEW with an INSTEAD OF trigger is supposed to be able to support. In other words, if you were hell bent on sticking with the SqlMembershipProvider and its data model, but didn't want to create a "hack" like the one described above that copied data, you could "mimic" the SqlMembershipProvider's data model with a series of VIEWs that even supported updating via INSTEAD OF triggers. And you could even accomplish a sense of "loose coupling" by placing these views in a separate SQL DB on the same MS SQL server that did cross DB querying. IMHO, for as much as I completely believe properly OO coded solutions are ideal, when it comes to "integration" tasks as this one, leveraging the RDBMS is your best bet.

  10. Avatar for Haacked
    Haacked April 24th, 2007

    @Joe, thanks for the insight. I'm glad to hear that the direction "The app that shall not be named" is towards a custom membership implementation.
    We still use and recommend "The app that shall not be named" on projects, hence I didn't want to be seen as bad mouthing it. I'm pretty sure I was using the first implementation that included the membership provider.

  11. Avatar for DotNetKicks.com
    DotNetKicks.com April 24th, 2007

    You've been kicked (a good thing) - Trackback from DotNetKicks.com

  12. Avatar for Chris
    Chris April 25th, 2007

    You brought up some points that I haven't really thought of. Thanks! The provider model makes even more sense to me now.

  13. Avatar for daniel
    daniel April 25th, 2007

    Some people have hinted at this, but it seems to me that custom MembershipProviders should not be the domain of application developers, but rather the developers implementing a solution. The main attraction of the provider model is that if an application uses it, and my organization uses it, then I can be pretty sure that I can use it within my organization's security infrastructure. I can drop in Active Directory or a custom provider against a legacy security database, and it just works.
    If the application developers start rolling their own "new improved SqlMembershipProvider", that effectively prohibits that level integration.
    Maybe I'm missing something, but I think the "Standard Membership Provider" is the one that the organization or individual says it is.

  14. Avatar for John Prado
    John Prado April 26th, 2007

    Smells like DNN to me!!!
    And it stink an entire quarter....
    Lot of bugs in that shit...

  15. Avatar for John Prado
    John Prado April 26th, 2007

    And about use the database as Provider for "membership"!!!
    It sucks too....
    MS need to see that what works in "Northwind" not mean it will works all around the world...
    Flexibility without "meaning and understanding" is not enough....

  16. Avatar for MikeSchinkel
    MikeSchinkel April 27th, 2007

    Hi Phil:
    This post (and probably your entire blog) prints on just the first page of the printout but the rest of the post doesn't print. The comments do print so something you are doing in your CSS or other formatting is causing the page not to print correctly.

  17. Avatar for Haacked
    Haacked April 27th, 2007

    Hi Mike, I think I fixed it. Please CTRL+F5 Refresh and retry printing.

  18. Avatar for Ayende @ Rahien
    Ayende @ Rahien May 3rd, 2007

    ODBC is not an API