A knowledge gap in development

Development Software Development
1

I think this is the right category, if it’s not I’m sorry.

TL;DR: I don’t how to connect two different databases to each other when it’s not an ODBC connection to On premises data and need help.

//Intro:

So this is my first post on the forum and instead of just saying hello, I’m going to admit some faults and the fact that I have a significant knowledge gap that needs help.

Back in College (2004) I took a course in database development and learned some basic SQL, how to connect databases via existing ODBC with on premises data, but not build a new connection. In any case, that was in 2004 and I only worked one job between then and now that required me to use SQL (I got an unrelated engineering degree and moved on to different things).

Now I work for a small company and develop on the Quickbase platform (not a fan and opinions aside, it was this or unemployment, so I stayed employed). I’m the lead backend database developer and a major gap in my knowledge surfacing.

//The Problem:

We have two web-based software tools that we want to merge into a suite (because they’re related). Quickbase is easier to learn, we have a 3rd party partner who does our web development and we’re happier with their work than the developer of the “other” tool, so the management decision is to pull “other” data into Quickbase that is then pass it to our new web-based tool.

“Other” Database is all SQL and they’ve provided us a copy of the data, but we need our users to be able to pull that data into quickbase and push to “Other” database, but there isn’t a Quickbase “Channel” to do that directly. QB supports ODBC at a level above our current package, but with limitations that don’t appear to be acceptable for us.

“Other” provider says they will build or integrate any API we need or build out compatibility for pipelines if we wish, but I don’t know what to specify and I can’t just say make it compatible with Quickbase. I am completely lost now. I’ve spent 2 weeks trying to fill in my knowledge gap and have woefully insufficient progress.

So how do you connect two databases to each other with the ability to create, modify and audit records when your other data is not on your LAN and ODBC is not an option?

//Possible Solution?

I know what JSON is, but I don’t know how it really works. What I do know is that Quickbase has a JSON channel for their pipelines and I can specify a URL for the file, either through the JSON Channel Directly or through a webhook. But it doesn’t look like I can push data back to that JSON file. I’m assuming we would have to build a pipeline on the other side to pull changes back over, but I don’t know how to do that either.

//Conclusion

I know I have a knowledge gap, but I don’t know how big it is or where to start filling in the gap and I need some help. --Thanks

1 Like
2

What is quickbase, is this some kind of modern/web based reinterpretation of ms access?

… and why not?

What APIs does quickbase expose for its data?

I think what you’re asking for is done sort of “replication”.
In traditional databases this feature is typically used to achieve high availability… or to exfiltrate data into other forms of storage that are cheaper or more scalable but without transactional support… so that you can run apache spark and similar on top without crushing your transactional workloads.

Which DBMS are your SQL devs using? Can they write some software that’d hook up to the replication stream and would automatically apply changes to quickbase as they’re made to SQL? … and vice versa?

3

What is Quickbase?

Quickbase is a low code web based database development platform with a look and feel more akin to PowerBI in it’s built-in functionality and target market than MS Access. What I have been told by their reps is that it’s SQL under the hood with an interpreter layer on top of it.

What API does QB Expose for its data?

According to their documentation, they have two API’s they support, an HTTP API and a JSON RESTful API supporting an application/json header only (doubt that would be a problem though).

Which DBMS are our SQL Devs using?

Unknown, we don’t employ them directly, they are a 3rd party company and I know nothing about what they do. They just insist they will build anything we need to our specification.

Is it theoretically possible for me to hand them the Quickbase JSON REST API documentation and say build that?
Reading the surface level documentation (all I’ve had time to read through so far) it doesn’t even imply that QB will push data to another server, only that records can be queried from it and modified in QB, it doesn’t look like it will replicate to anywhere else. Maybe this understanding is part of my knowledge gap.

4

Yes. That is the route that is probably going to be the least resistant. it also adds some functionality if you decide to go to another DBMS. but there are security concerns that you will want to address with the CISO/CIO of your company.

Their DBMS system in the backend probably supports replication but I am sure they will not want to expose that to the outside world because the implementation may be h4xx0r central.

Since you don’t have a direct way to replicate from QB to an external DBMS, is it possible on your end to build a universal front end that would then push the data to both databases. I ask this, because if you let the other DBMS query QB, you are going to run into performance issues and it also puts me at paranoia state 1 to allow another off-premises system just hoover up all of your data.

5

Hi, are you sure that you need to fill this gap yourself and not turn to specialists?