Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Does anyone integrate with their customers' DB directly?

news.ycombinator.com

Post reply on HN
Print view View original

11–20 of 48 posts

Re: Does anyone integrate with their customers' DB directly?

#11

Post by wolpoli »

I would get the customer to create (and own) the API endpoint that talks to their database, and we talk to that API endpoint instead. Then it is up to them to test and maintain that endpoint.

This avoids a situation where we get an email out of the blue explaining that there is a database change and we need to dedicate engineering resources to make sure it is compatible by a certain date, or even worse and more likely, an urgent email explaining that the integration has broke and we need it back running last week.

Re: Does anyone integrate with their customers' DB directly?

#12

Post by testemailfordg2 »

Something boring but workable is using CSV files, dropped to a location on customers end through SFTP containing your transaction IDs that you are interested in. If its MS SQL Server on the other side, then a SQL agent on that server using some SSIS can read the CSV as input and prepare your required output as a CSV again for you to pickup from their SFTP Server.

Re: Does anyone integrate with their customers' DB directly?

#13

Post by BurningFrog »

Seems fine to me, provided some sanity constraints, like having read-only access, and some guarantee that the fields you're interested in will not be removed/changed.

I've worked on systems that use a SQL DB as a communication layer. A main advantage is that every language comes with good SQL read/write libraries, so things just work right away.

Re: Does anyone integrate with their customers' DB directly?

#14

Post by Nican »

Yes. SQL is a form of API, and it carries all the same challenges.

-> Permission control, making sure that the user of API can not see data they are not supposed to.

-> Auditability. Verify that the API is being used correctly.

-> Performance. Do not overload the endpoint. (Read from a read replica? And maybe you are not running hour-long analytics queries on the database)

-> Consistency. Are you using transactions to read your data? Could you be causing contention?

-> API versioning. How do you upgrade the underlying tables without breaking the users.

Re: Does anyone integrate with their customers' DB directly?

#15

Post by klysm »

BurningFrog wrote: post #13

Seems fine to me, provided some sanity constraints, like having read-only access, and some guarantee that the fields you're interested in will not be removed/changed. I've worked on systems that use a SQL DB as a communication layer. A main advantage is that every language comes with good SQL read/write libraries, so things just work right away.

A SQL database is an excellent integration tool that I think is very underutilized. You get strong semantics around transaction isolation, and can easily bake in permissions. Language support is nearly universal and you can avoid a ton of API decisions that are already made for you.

Re: Does anyone integrate with their customers' DB directly?

#17

Post by tbrownaw »

Congratulations, schema changes are now directly visible outside your organization.

For some of our outgoing files at $employer, there's a notice period of... I think I remember it being 30 days for additional code values and 90 days for layout changes. That sort of planning ahead becomes much harder if every single schema change is immediately visible to outsiders.

Re: Does anyone integrate with their customers' DB directly?

#18

Post by akira2501 »

drpossum wrote: post #4

> there's nothing wrong with querying the fintech's DB as long as we're treating infosec seriously This is an unfortunate attitude. From a software architecture point of view, you're hard-coupling your software to that database which can create brittleness: You're at the mercy of the original database schema which may not be optimal, carries it's own tech debt, may require translations to your own implementation, and…

> You're at the mercy of the original database schema

SQL has views. As long as you can represent the underlying data through a view you should be fine.

> and may cause mistakes in interpretation in anything of scale

All APIs have this concern.

> direct access to a database is an unnecessary security risk

You can constrain access to be read-only and to only access the particular view or views necessary.

> has found putting up abstractions and interfaces to address those issues

It could just be a crutch to avoid deep understanding of the underlying tools themselves. It's much easier to greenfield a new API. It's much easier to forget and accidentally abandon these APIs.

Re: Does anyone integrate with their customers' DB directly?

#19

Post by dbrueck »

testemailfordg2 wrote: post #12

Something boring but workable is using CSV files, dropped to a location on customers end through SFTP containing your transaction IDs that you are interested in. If its MS SQL Server on the other side, then a SQL agent on that server using some SSIS can read the CSV as input and prepare your required output as a CSV again for you to pickup from their SFTP Server.

Yes, we did this at a few places and it's boring and low tech and... works extremely well. Loose coupling, very small interface, and bad behavior on either side doesn't impact the performance of the actual DB, etc.

Re: Does anyone integrate with their customers' DB directly?

#20

Post by rukuu001 »

Yeah that's a blackops under the radar get it done quick solution that should be flagged like crazy for future spend on getting an API in place.

Of course, no one will dedicate time or money to that API until something breaks. Then you pull out all the emails, risk/assumption statements etc to highlight your due-diligence on informing the client. Then fix the problem, pushing the whole issue down the road again until the next time.

But seriously, expect to see this anywhere there are budget and time constraints. As soon as the fintech is big enough to get more worried about risk than growth, they should start taking this kind of thing seriously.

Post reply on HN