Best Practice: Working with External SQL Database

1
Hi Forum, I am moving my Mendix Application from Mendix Cloud to Mendix Local. In the cloud I was limited to using API calls to retrieve data from our local SQL Database.  I was running nightly and on-demand API calls to retrieve and store the SQL data in Mendix and then perform actions on the Mendix data.  Now that I will be on our local environment, I will be able to use the Database Connector to run local queries on our SQL database.  I am curious if I should   1. Use the database connector to sync data to my mendix database as I did with the API 2. Use the connector to run a query on the SQL database each time that action is needed (I will need to rebuild much of my Microflow, but that is OK if it is best practice) 3. Some other methods that you recommend? Thank You, Jeff
asked
2 answers
1

My recommendation in most cases would be to stick with using your API, i.e., do not use a direct database connection. This SO thread does a decent job discussing why: https://stackoverflow.com/questions/2142070/why-should-a-developer-use-web-services-instead-of-direct-connections-to-a-db

The main concern I have with direct database integrations is the maintenance of them. Take these 2 scenarios. Assume in both that there are 2 separate teams developing and maintaining App A and App B.

Scenario 1 - Direct DB Integration

App B reads (and sometimes writes) data directly to App A's database. The SQL queries are written and stored in App B to do this.

App A determines they have a need to make a modification to a table and it involves renaming one or more columns. In this case, they need to (a) somehow know if App B uses this column, and then (b) coordinate a release with changes to App B. Or in the more likely scenario, they don't realize that App B uses this column (how would they know?), then they release their change and it breaks the integration in App B.

Now, App B is broken, and the users of App B are mad at the developers and support team for App B. But this was caused by a change in App A that they didn't know about. Now App B support is in trouble for something that's totally not their fault, and has to make an emergency fix to App B.

Scenario 2 - API

App B reads (and sometimes writes) data directly to App A via a web service. The web service is exposed by App A, and represents a contract, or functionality guaranteed by App A.

App A determines they have a need to make a modification to a table, and say it involves renaming one or more columns. In this case, they need to make their changes, and ensure the API is still functioning on the same spec as before. If they decide to make a change to the way the API works, they are responsible for communicating that change to the users of the API. If they fail to do that, the team will be blamed and forced to fix the API so it conforms to spec again.

Conclusion

In these scenarios, the key difference is the existence of a contract between App A and App B. The contract puts the onus on the App A team to ensure their API continues to work through ongoing development or new releases. It avoids tightly coupling the 2 systems in a way that can lead to less flexibility, wrongly placed blame, and emergency fixes.

answered
0

Seems most likely that you only need the data for reading. Database replication will do just fine.

answered