Creating an API and using an API server of some sort has become a very common way of working with modern software, especially distributed systems. Whether developers move to microservices or something else, they often make more use of APIs than ever before. At Redgate, I’ve watched us work on new products, often beginning with a basic API at the command line and evolving from there.
However, does the database need an API? This article thinks so, though I don’t love the piece. It seems to wander a bit amongst a few topics and doesn’t always fit together. It’s also trying to promote the Stargate API gateway for Cassandra. I don’t know if this is a good idea, or if it really helps make developers more productive, but the idea of having an API for developers makes sense.
In fact, when I work with clients I often try to get them to think of their tables as an API of sorts. With APIs, we want to rev them, but carefully. After all, we know there are dependencies from various other pieces of software, including other items inside the database. We want to be sure we don’t break anything. At least not for long, which is why DevOps and being able to release work quickly matters.
Communicating changes and coordinating work is a challenge. I was surprised by a client who used SQL Doc for this reason, to ensure everyone had up-to-date knowledge of what their schema meant. It’s also a reason I’ve taken some of the work I do with clients and built my Architecting Zero Downtime talk. Many developers think narrowly about the requirement in front of them and not about all the other dependencies and impacts. I think a bit more unit testing in databases would help here. In fact, I demonstrate sometimes how to write a test that treats your table like an API to prevent breakage.
I do agree that databases, especially relational ones can be complex. If we can simplify things for developers, especially developers new to our system, we can get more work done and hopefully, raise code quality. I don’t know that Stargate or GraphQL or anything else is better, though I have started to think LINQ is a better implementation than SQL for working with relational databases. That’s not going to change anytime soon, and plus, I need SQL to remain popular until I retire đŸ˜‰
Whether you formally build an API or not, it is helpful to treat your schema like an API and make changes in the same way that you would if there were other teams depending on your interface. That’s because there likely are others who depend on what you build.
Steve Jones
Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.
Voice of the DBA 
1) SQL isn’t going to become unpopular anytime before either of us retire, possibly even before our kids retire. There’s a very good reason SQL has persisted for as long as it has.
2) With an API I always thought the goal was to provide external connections with a black box like approach. The API only requires that the requestor provide criteria (and of course credentials) and it takes care of deciding how to gather and return the data where as a direct connection to the DB and at least read level access allows the requestor to decide what to get and how and possibly not in the best way, not an efficient way. Even with only READ access they can cause you headaches. Our primary system is now in the vendors cloud so we don’t have to deal with a lot of security and performance related aspects of SQL Server like before. To expand B2B options for 3rd parties who work in the same industry (property management) the vendor has created a numbers of these. If a company who specializes in finding potential renters wants to work with us we can provide them the ability to directly query our DB for any necessary data (ie availability, pricing). I would never want any of these 3rd parties to have direct access to the DB itself and let them decide what to get and how.
LikeLike
yeah, SQL isn’t leaving anytime soon.
The goal is a black box, but that’s part of my point. You ought to consider your table the API. Change that carefully, which means rev’ing it, not just treating it like an internal code structure that you can alter without considering dependencies. Ideally you’d use views here as an API, but so many things with SQL get cumbersome with views.
LikeLiked by 1 person