in telecom resporgs query somos by directly connecting to their oracle database. it's pretty nice compared to say some rest api especially if you're integrating it into your own db as a linked server or remote table.
Does anyone integrate with their customers' DB directly?
41–48 of 48 posts
Re: Does anyone integrate with their customers' DB directly?
#42We’ve done this for an SME customer of ours. We were essentially building a new module on top of their existing in-house systems. We laid out our query patterns in advance so they could index properly, and only query specific views. Write-out is only in tables dedicated for our product.
It works surprisingly well, and is pretty resilient since it essentially acts as a message queue too. Then again, this is all low traffic stuff in a SME / B2B setting, with zero multitenancy involved.
I’ll still take a proper API or message queue any day though.
Edit: I suppose our biggest benefit is that our customer can actually change the interface with us fairly quickly. Then have database experts in-house, but devs who could do APIs. So the collaboration has been mostly smooth an account of that, and that is a huge advantage compared to them having to outsource any API work. Technically speaking I’m not a fan, but the non-technical results have been useful.
Re: Does anyone integrate with their customers' DB directly?
#43Even if you're only doing reads, you'll be relying on interfaces that can change at any time with no notice. That's a kind of tech debt time bomb waiting to blow up your service and make a big pager party!
If you're doing writes, this is potentially harmful to the customer, too, not just data exfiltration but also potentially software breaking and that could be lawsuit territory.
Even doing this with your own services' data stores is bad practice. Direct reads/writes to a service's data store without going through its defined interfaces means unexpected, often unmonitored changes happening. I strongly advise against this pattern of "secret APIs," as opposed to overt ones.
Also, please note that this applies to any data store and not just a DB.
Re: Does anyone integrate with their customers' DB directly?
#44I'm thinking that much of the architecture around building APIs in front of databases stems from the religious approach to SOA over HTTP/REST. I don't see much wrong with this as to me the database is still a sort of an API with a different protocol.
At Scramjet we've built an Platform+Agent engine and put it to use and in many cases we do integrate into the DB's directly without APIs through long-lived integration functions.
Let me address some of the comments below:
- hard coupling of DB is bad - I really don't see how adding an additional middleware results in lessening this, you're hard coupling into something else. While it might help if a database structure is changed on a whim but, from my experience, most of the schema changes reflect changed requirements.
- hard couping is bad in general - is it though? As a platform engineer I went into a pitfall of making everything reusable only to learn that 90% of the stack has never changed and the XML-based protocol projects developed in 2004 are still being developed without change to the protocol at all. A reactive approach with a separated client/data model is much better in my opinion and quite frankly it's mostly impossible to write a system these days without one.
- schema changes are unavoidable - but hey, as someone pointed out, there are views and materialised views. The RDBs do offer a good framework for keeping legacy schemas during the development cycle and this framework will save you the most money as it affects the least amount of moving parts in your system. It's there since the 90s at least, so why not use it?
- the security mumbo jumbo - this seems to be the most uninformed opinion - all leading RDBMS have an extremely robust and granual way to grant access to the data. They are battle tested for nearly half of a century. They are well known and easily tested. And, unlike the REST frameworks, HTTP servers, relays, caches and what not, those are contained in a single piece of software, limitting the attack vectors.
If you can deploy the client to the DB remotely, near the database then I'd say - you're doing this right and not only that, you're limitting the amount of code you need to deploy, maintain and depend on. If you separate the data, the access code and the logic from each other, you have achieved decoupling. But that does not mean that REST API's are the "one correct way".
Such an approach was the underlying idea of our Scramjet Platform and we've taken this approach not only in integrating into DBs, but also file storage, direct protocols and even GPIOs in some cases. And though we have a trick up our sleve of being able to split the code across distributed environments, even in general terms I'd say: it works, it's maintainable and way less expensive.
Re: Does anyone integrate with their customers' DB directly?
#45Data visualization products do this regularly
Are there any good open source/free ones? I tried Apache Superset a few months ago which seemed promising but was appalling bad when it came to trying to use it.
R is the gold standard
Microsoft’s PowerBI and Google has some tools included in G Suite
Tableau has a free tier I think
Re: Does anyone integrate with their customers' DB directly?
#46The only reasonable way to do this safely is by querying a read replica. You could take down your customer's systems very, very easily by running queries without proper indexes. Dealing with that is probably trickier than it sounds, because a DB of appreciable size just won't be queryable. You might go live quicker. But the integration will break every time your customer makes a change or needs to upgrade. Pray there…
> The only reasonable way to do this safely is by querying a read replica.
I agree - this is a very good approach with the most amount of control around how often you need fresh data, how to trigger that etc.
Although, not every integration is customer-facing and does not have to be infinitely scalable. Scalability = cost, often a heavy cost. If you query the DB once a day, the customer can come back to you and say: our db's are a little hot at 3AM and you do your queries then - can you add some indexes to the view or run a DESCRIBE query and optimize this a bit?
And let's agree that the above point does not change whether you use an API layer or query directly.
Re: Does anyone integrate with their customers' DB directly?
#47The only reasonable way to do this safely is by querying a read replica. You could take down your customer's systems very, very easily by running queries without proper indexes. Dealing with that is probably trickier than it sounds, because a DB of appreciable size just won't be queryable. You might go live quicker. But the integration will break every time your customer makes a change or needs to upgrade. Pray there…
> The only reasonable way to do this safely is by querying a read replica. I agree - this is a very good approach with the most amount of control around how often you need fresh data, how to trigger that etc. Although, not every integration is customer-facing and does not have to be infinitely scalable. Scalability = cost, often a heavy cost. If you query the DB once a day, the customer can come back to you and say:…
> Although, not every integration is customer-facing and does not have to be infinitely scalable. Scalability = cost, often a heavy cost.
You're either putting that cost on the client or paying to replicate their DB yourself. If that's too expensive, you shouldn't be using this approach in the first place! Running arbitrary queries on your customer's database primary is simply never safe, nor can it be made safe.
> If you query the DB once a day, the customer can come back to you and say: our db's are a little hot at 3AM and you do your queries then - can you add some indexes to the view or run a DESCRIBE query and optimize this a bit?
It's not a question of the DB running a little hot. It's accidentally triggering one or more full table scans on a table with millions of records. You have no idea what time your customer experiences load, or when their own batch jobs run. Or whether the index you built against with EXPLAIN six months ago got dropped because there's no queries in your customer's codebase that could possibly reference it.
And it's not just about their system staying up. What if you trigger so many IOPS that your customer has a massive bill? Or if you run a query that needs more space to prepare the results, which scales up the instance automatically, resulting in unexpected costs? You're gonna pay for that, right?
You also might hit a performance cliff: the query planner might choose a bad plan for a query that's otherwise satisfiable with an index only, because the stats suggest (for instance) that the visibility map is stale. There's nothing you can do here to fix this short of tuning settings on the DB itself.
> And let's agree that the above point does not change whether you use an API layer or query directly.
It does! Your customer (hopefully) knows their own DB. You don't. Have your customer build the API and they'll support your integration. You can't possibly have a better coupling with the data in your customer's database than your customer does.
Re: Does anyone integrate with their customers' DB directly?
#48Earlier quoted context omitted.
> The only reasonable way to do this safely is by querying a read replica. I agree - this is a very good approach with the most amount of control around how often you need fresh data, how to trigger that etc. Although, not every integration is customer-facing and does not have to be infinitely scalable. Scalability = cost, often a heavy cost. If you query the DB once a day, the customer can come back to you and say:…
> Although, not every integration is customer-facing and does not have to be infinitely scalable. Scalability = cost, often a heavy cost. You're either putting that cost on the client or paying to replicate their DB yourself. If that's too expensive, you shouldn't be using this approach in the first place! Running arbitrary queries on your customer's database primary is simply never safe, nor can it be made safe. > I…
> Running arbitrary queries on your customer's database primary is simply never safe, nor can it be made safe
Look, the only thing that's never safe is speaking in absolutes.
> It's not a question of the DB running a little hot. It's accidentally triggering one or more full table scans on a table with millions of records.
Does the table have a million records? And I mean come on, a million records and a FTS once a day on modern machine - for the sake of inserts I actually would go with it... Besides may I remind you: materialised views.
> It does! Your customer (hopefully) knows their own DB. You don't. Have your customer build the API and they'll support your integration.
I envy you. You live in a fantasy land where your customers don't make mistakes and are full pros. I mean that, or you don't care...