Want to Watch a Video Instead?
Configuring the MySQL and IBM DB2 APIsFor 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 MeshAfter generating your APIs, enter DreamFactory’s
Schemacomponent and select the API and table that will serve as the relationship parent. In the following screenshot I’ve chosen the
MySQLAPI and the
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 Relationshipbutton you can create new relationships where they didn’t previously exist, including relationships between two databases. Click on the
Add Virtual Relationshipbutton 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 from
Has Many, and
Many to Many.
Reference Service: This field identifies the related service. It’s set to
DB2because the relationship pertains to the previously configured IBM DB2 API.
Reference Table: This field identifies the related table. Recall we selected the
employeestable, so we’re going to relate the
employeestable to the
Reference Field: This field identifies the foreign key field found in the related
After defining these fields, save the changes and you’re ready to begin using the new relationship!
Querying the RelationshipNow 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 the
employeestable looks like prior to configuring Data Mesh:
After querying Data Mesh, the results look like this:
ConclusionDreamFactory’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!
Resources that represent something in your system (orders, customers, accounts, etc)
Actions against them, represented using existing HTTP verbs (GET, POST, PUT, DELETE).
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.
Which to Choose?To select the best database solution for the task at hand, one must weigh several factors, including:
- Operating System
- Cloud Support
- Tool Support
Operating SystemMost 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:
MySQLMySQL 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 ServerSQL 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 DependsHonestly, 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.
CostCost 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:
MySQLMySQL’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 ServerSQL 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 DependsOnce 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 SupportIn 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:
MySQLAll 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 ServerSimilar 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.
PerformanceDatabase 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 ServerWhile not a slam dunk, SQL Server’s slightly better numbers, and "flagship" status give it a slight advantage here.
Tool SupportIn 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.
MySQLMySQL’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 ServerSQL 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 ServerBoth 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 SupportBoth 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.
DreamFactoryWith 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