September 20, 2016 at 3:08 pm
I'm not a SQL newbie, but this is kind of a newbie question. I'm trying to understand the concept and technical workings of accessing a database via an API management system, rather than through traditional database client methods. My company is evaluating an API suite from Mulesoft, and apparently considering using APIs to centralize things like access control and logging for all data access. I'm not on that project and don't really understand it, but our data warehouse access would be controlled through these APIs as well.
What does that mean, exactly? How would a tool like Excel or Tableau work using APIs, rather than connecting through ODBC? Or could they work at all?
September 21, 2016 at 5:21 am
Your guess is as good as mine. I've not yet heard of such a thing. Do you know the name of the suite or have a source link that we might be able to look up?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 21, 2016 at 6:32 am
I suspect this is an extension on the concepts around an ORM (Object-Relational Mapping) tool. Instead of writing T-SQL, you just access the API and the API generates any and all T-SQL for you.
I haven't worked with an API tool like this, but I have worked with a couple of different ORM tools on projects that were successful and projects which failed horribly. The successful projects tested the approach, validated that both sides, database & app code, worked well and tried to take maintenance and longevity of the project into account. The failed projects tested everything from the app side only, ignored any other business needs, ignored standard relational storage best practices, and created total nightmares.
It should be possible to implement something like this, but only after appropriate testing and research. If they've already picked it and are actively implementing it without any testing on the database side, as well as exploring stuff like Tablaeu, I suspect you might be creating a nightmare. I'd suggest calling a meeting or three.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 21, 2016 at 7:05 am
What you're describing sounds like some type of middle tier or data access layer. However, SQL Server has access control and event logging capability baked into it; I wouldn't use a 3rd party product just for that. There are ORM tools intended to abstract application developers from the data model and SQL, ostensibly to save the development team time, but I don't believe any great product was ever built on top of ORM. It's database development tool for folks who essentially don't know databases.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 21, 2016 at 9:29 am
Interesting info and answers, thanks. This particular API system is from Mulesoft, but there are others. I'm getting the impression this is a "shiny new thing" that many of us will be facing before long. I'm concerned about all that middle tier overhead; I can imagine performance being a major concern (not to mention the vast gap between what's being marketed and what we'll end up with).
September 21, 2016 at 10:12 am
The bigger concern is going to be the lowest common denominator T-SQL that the API is going to generate. About 90% or so will be fine. That last 10% will be a horror show.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply