Easier Data Marts with DreamFactory Data Mesh
Want to Watch a Video Instead?
Configuring the MySQL and IBM DB2 APIs
For the purposes of this tutorial I’ll assume you’re already familiar with DreamFactory fundamentals, including how to generate database-backed REST APIs. If not, I suggest taking a few minutes to watch our introductory video at https://academy.dreamfactory.com.Configuring Data Mesh
After generating your APIs, enter DreamFactory’sSchema
component and select the API and table that will serve as the relationship parent. In the following screenshot I’ve chosen the MySQL
API and the employees
table:

Once selected, you can scroll down to the table’s “Relationships” section. This section warrants a bit of explanation. When DreamFactory generates a database API, it analyzes all tables, stored procedures, views, table columns and datatypes, and foreign key relationships. This section contains a list of join aliases that you can use to easily join tables via the API:

However you’re not limited to these aliases; by clicking the
Add Virtual Relationship
button you can create new relationships where they didn’t previously exist, including relationships between two databases. Click on the Add Virtual Relationship
button and you’ll be presented with an interface for defining the relationship between two databases. See the following screenshot:

In this screenshot, I’ve defined the fields as follows:
*
Always Fetch
: This field enables the virtual relationship. You can also optionally enable the relationship on demand via the API.
Type
: This field determines the relationship type. You can choose fromBelongs To
,Has One
,Has Many
, andMany to Many
.Reference Service
: This field identifies the related service. It’s set toDB2
because the relationship pertains to the previously configured IBM DB2 API.Reference Table
: This field identifies the related table. Recall we selected theMySQL
service’semployees
table, so we’re going to relate theemployees
table to theDB2
service’semployee_status
table.Reference Field
: This field identifies the foreign key field found in the relatedemployee_status
table.
After defining these fields, save the changes and you’re ready to begin using the new relationship!
Querying the Relationship
Now that the relationship has been defined, let’s execute a query and view the combined results. We’ll begin by showing what a query to theemployees
table looks like prior to configuring Data Mesh:

After querying Data Mesh, the results look like this:

Conclusion
DreamFactory’s Data Mesh feature offers an incredibly straightforward, point-and-click solution for creating sophisticated and transparent unified queries. You’re certainly not limited to meshing two databases together; try meshing two, three, or more databases together and marvel over the time and aggravation savings!API Integration – What is it? The Definitive Guide
History
-
Resources that represent something in your system (orders, customers, accounts, etc)
Integration
-
Internal data belonging to the business, both current and historical.
-
The data can “live” in modern systems that already feature APIs.
-
The data can exist buried and hidden in legacy databases. Important historical trends and details becomes available.
-
The data can also come from external sources. This can include:
-
Real-time data about financial markets.
-
Information about the weather in various geo-locations.
-
Traffic data from cities, highways, and rail lines.
-
Births and deaths. Marriages and divorces.
DreamFactory
MS SQL Server vs MySQL – Which Reigns Supreme?

Which to Choose?
To select the best database solution for the task at hand, one must weigh several factors, including:- Operating System
- Cost
- Cloud Support
- Performance
- Tool Support
Operating System
Most companies have already invested time, money, and expertise in their computing infrastructure. This includes their choice of Operating System (OS). Usually that consists of "Windows vs Linux" (although cloud computing is beginning to change that). When selecting a database to power your business, the OS your company is already is a big deciding factor. Here’s how that looks for MySQL vs SQL Server:MySQL
MySQL runs on virtually all major operating systems, including Linux, MacOS, and Windows. While traditionally associated with Linux (as part of the famed LAMP stack), it will run on Windows as well.SQL Server
SQL Server was originally written for the Microsoft Windows operating system. In recent years, Microsoft has made strides in embracing the open source community, and providing support for both Linux and Mac OS. The most recent versions of SQL Server run on Linux natively, and will run on Mac OS within a Docker container.Advantage – It Depends
Honestly, this one depends on what OS your company is already using. While both platforms support the two major operating systems, there are "home court advantages" to each. If you’re already a Windows and .Net shop, it probably makes sense to use SQL Server. If you’re a Linux and Python/Java/PHP shop, MySQL might be the better choice.Cost
Cost is always a factor when making decisions about software, and an enterprise-grade database can be one of the biggest expenses. Both solutions offer a "free" tier. From there, the price depends on how powerful a database you need, and what sort of support you’re looking for. It may be tempting to try and save money, and go for the free tier. But if the database is mission critical, paying for advanced monitoring, backup, and support is probably worth the cost. Here’s the breakdown:MySQL
MySQL’s free offering is the MySQL Community Edition. It boasts a decent number of the standard features. This would work fine for a developer learning the platform. It should also meet a smaller system’s needs. For a more complete feature set (as well as Oracle support), you need to shell out some bucks. According to recent pricing, this can run you anywhere from $2k-$10k per server, annually. There are 3 different tiers (Standard Edition, Enterprise Edition, and Cluster CGE). Choosing between them largely depends on the complexity and scale of your data needs.SQL Server
SQL Server’s free offering comes in two flavors – here’s how Microsoft describes them:- Developer – "Full-featured version of SQL Server software that allows developers to cost-effectively build, test, and demonstrate applications based on SQL Server software."
- Express – "Free entry-level database that’s ideal for learning, as well as building desktop and small server data-driven applications of up to 10 GB."
Advantage – It Depends
Once again, the best choice here depends on the needs of your business. Both solutions offer a free tier. Both have complicated pricing schemes beyond that. Consult with the sales department of each to get a final determination of what you need, and what you would end up paying.Cloud Support
In recent years the computing landscape has undergone a dramatic transformation. Cloud computing is all the rage. The "Big 3" providers are currently Amazon Web Services (AWS), Microsoft Azure, and Google Cloud. Each offer robust services, such as storage, computing, and yes, SQL Databases. This revolution has impacted the first two bullet points of this article (OS and Cost). The cloud provider manages the OS and server complications, and offer "pay as you go" plans to avoid the major up-front costs. In a way, this shift has diminished the importance of OS/Cost. Instead, other considerations such as performance, tool support, feature set are bigger factors. Here’s how the offerings stack up:MySQL
All 3 of the "Big 3" cloud providers support MySQL with the following offerings:- AWS offers MySQL on their Relational Database Service.
- Azure offers MySQL on their Azure Database for MySQL service.
- Google offers MySQL on their Cloud SQL offering.
SQL Server
Similar to MySQL, each major cloud provider has a SQL Server offering:- AWS offers Sql Server on their Relational Database Service.
- Azure offers SQL Server on their SQL Database service. While SQL Server runs under the covers, the SQL Database offering abstracts much of the server administration away from the end user.
- Google offers SQL Server on their Google Cloud Platform offering.
Advantage – SQL Server (SQL Database)
While either solution works as a cloud offering, the combination of Microsoft Azure and SQL Database is hard to beat. If you are ALREADY using another provider, or have ALREADY invested in MySQL, then that would still probably be your choice. However, coming into a green-field decision, the Azure/SQL Database choice is pretty compelling.Performance
Database performance is crucial to any software application. If the database doesn’t respond in an expedient fashion, the entire system bogs down. This leads to issues like poor user experience, delays in operations, and lost money. Database performance depends on an IMMENSE number of variables. Slight differences in workloads can skew advantages one way or another. Minor tweaks can improve results. A well-designed database is worth its weight in gold. MySQL and SQL Server both tout extensive performance and scaling capabilities. After scouring the web for comparisons between the two, SQL Server seems to have the advantage. Here are some hard numbers:- Comparative Performance Analysis of MySQL and SQL Server Relational Database Management Systems in Windows Environment
- Count Distinct Compared on Top 4 SQL Databases
Advantage – SQL Server
While not a slam dunk, SQL Server’s slightly better numbers, and "flagship" status give it a slight advantage here.Tool Support
In order to work with a database, one needs a good toolset. The database itself is a background process without a GUI. However, in order to develop and support the database, you need to interact with it. Both MySQL and SQL Server provide front end clients for this purpose.MySQL
MySQL’s client application is MySQL Workbench. Workbench has offerings that run on Windows, Linux, and MacOS. It offers several important database management tools, including:- Database connection and management
- SQL editor and execution
- Database and Schema modeling GUI
- Performance monitoring and query statistics
SQL Server
SQL Server’s client application is SQL Server Management Studio (SSMS). While SQL Server runs on Windows, Linux and MacOS (via Docker), SSMS is ONLY available on Windows machines. Note that Microsoft provides a Visual Studio Code extension to execute SQL from a Linux-based machine. SSMS has a more robust feature set than MySQL Workbench. This includes:- More extensive Database management tools. Includes a robust set of security, reporting, analysis, and mail services.
- A powerful execution plan visualizer. This allows easy and fast identification of performance bottlenecks.
- Integrated Source control.
- Real-time activity monitor with filtering and automatic refresh.
Advantage – SQL Server
Both offerings provide "the basics" (ability to execute SQL and view/manage databases), but the SSMS experience is far superior. Seasoned Database Administrators (DBAs) may wish to manage their databases with scripts and SQL. But many users want a simple GUI to perform these tasks. This is an area where SSMS shines. Also, the execution plan visualizer makes performance bottlenecks easy to fix. That can pay for itself time and time again.Language Support
Both platforms utilize SQL to interact with their schema and data (with some minor differences). However, they differ when it comes to runtime languages interfacing WITH the database. For example, in a typical server architecture, you might have:- Database – SQL reads/writes data
- App Server – C++/PHP/Perl/Python/.Net/Java provide business logic, and interface with database
- SQL Server supports T-SQL, a proprietary extension to SQL. This enables concepts such as Procedural Programming, local variables, string/data processing functions, and FROM clauses in UPDATE/DELETE statements. Basically, you can do more with your SQL.
- Runtime languages – both systems support connecting using the major programming languages (C#, Java, PHP, C++, Python, Ruby, Visual Basic, Delphi, Go, R). There are some articles on the web claiming that less-popular languages such as Eiffel are only supported on MySQL, but as long as you can connect using ODBC, both databases are available.
- If using a .Net language (C#, F#, Visual Basic, etc), once again Microsoft provides a "homecourt advantage". Microsoft wrote the ADO.Net library for SQL Server first. ADO.Net works with MySQL, but it really shines with SQL Server.
- SQL Server also provides the additional (and possibly controversial) mechanism of invoking .Net code FROM a stored procedure. This can be a powerful mechanism for injecting all sorts of functionality within your database. It also allows you to shoot yourself in the foot. Proceed with caution here.
So Which To Choose?
Obviously there is a great deal of information to unpack here. The "it depends" caveat still looms largely over the entire decision process. A general rule of thumb for approaching this decision might be:- If you are a Linux shop, already using pieces of the LAMP stack, MySQL would fit in with that nicely.
- If you are a Microsoft shop, already invested in .Net and the Windows ecosystem, SQLServer seems like the obvious choice.
- If you are completely green field, or looking to make a clean start, the evidence above leans towards SQL Server. Microsoft is building momentum in the cloud arena with Azure’s SQL Database. They are continuing to embrace other ecosystems (eg, Linux) and open source. And SQL Server features a better toolset, the more robust TSQL, and arguably better performance.
DreamFactory
With your Database decision made, what’s next? Most business applications consist of choices around the following rough architecture:- Database (hopefully we’ve advanced understanding of that here)
- Middle Application Programming Interface (API) layer (connects Database to front end GUI)
- GUI layer