API Cost Calculator: How Much Does it Cost to Build an API?

.imagedropshadow {
padding: 5px;
border: solid 1px #EFEFEF;
}

You need to save money, deploy projects faster, and spend more time on developing application experiences that enthrall users. It is practically a certainty that APIs will play a critical role in your software development process, but how much does it cost to build an API?

It’s important to understand that there is more to an API than just coding an interface to some data source. Whether you are planning on hiring a contractor or assigning a new project to your team, an API calculator can help you understand the time and cost required to develop a mission-critical part of your next project.

Continue reading “API Cost Calculator: How Much Does it Cost to Build an API?”

MySQL vs MS SQL Server – Which Database Reigns Supreme?

Relational database management systems (RDBMS) form the backbone of enterprise IT. Data-driven business intelligence, analytics, and reporting workloads simply couldn’t exist without the support of a high-powered, high-performance relational database. Good databases can bring your enterprise IT to the next level—while bad databases can bring it to its knees.

The crucial question is then: which RDBMS is right for your business needs and objectives? When comparing relational database solutions, two names seem to come up more than any others: MySQL and Microsoft SQL Server.

MySQL is an open-source RDBMS solution that was purchased by Oracle in 2008. As a component of the popular LAMP web application development stack, MySQL powers some of the world’s most highly visited websites, including Facebook, Twitter, and YouTube.

Microsoft SQL Server is an RDBMS solution developed and maintained by Microsoft. First released in 1989, SQL Server is now available in many different versions with different feature sets, including Enterprise, Standard, and Express versions.

The question of MySQL vs. SQL Server is a tough one, and there’s no right answer for every organization. Below, we’ll go over the most important factors to consider when choosing between SQL Server and MySQL.

Continue reading “MySQL vs MS SQL Server – Which Database Reigns Supreme?”

Creating a Geocoder Service Using DreamFactory and the Google Maps Geocoding API

There is no question DreamFactory’s native connectors have saved IT teams countless hours of development time. Yet these are almost incidental when one takes into consideration the platform’s ability to integrate with thousands of third-party REST and SOAP services, not to mention create entirely new APIs through the scripted service interface. Further, thanks to DreamFactory’s ability to leverage third-party libraries, new APIs can often be created in just a few dozen lines of code. In this tutorial you’ll learn how to create a DreamFactory-managed geocoding service using Spatie’s popular open source geocoder package. Using this service, you’ll be able to easily convert addresses into latitudinal and longitudinal coordinates, as well as perform reverse geocoding (convert coordinates into an address). Once complete, you’ll be able to use a JavaScript library such as Axios or a PHP library like Guzzle to add geocoding to your application with no additional coding required! Continue reading “Creating a Geocoder Service Using DreamFactory and the Google Maps Geocoding API”

DreamFactory and GitHub – Event Scripting Bliss

DreamFactory’s Event and Platform Scripting

The DreamFactory scripting objects enable you to create a custom delivery of data through a unified URL structure. Ok, this is a pretty well-known fact if you have spent some time digging around DreamFactory. DreamFactory allows you to link your event scripts and custom scripts to a file that is managed in your GitHub account. This eliminates the need to manually update your DreamFactory scripts when you update those scripts in your source control repo.

Continue reading “DreamFactory and GitHub – Event Scripting Bliss”

Extend Your Twitter Integration With Third-Party Libraries

DreamFactory and Twitter
DreamFactory and Twitter

Integrating Third-Party Libraries With DreamFactory Scripting

Being able to script business logic into your endpoints to further extend and customize the data response(s) and request(s) is one of the best features inside of DreamFactory. As a form of ETL data integration, DreamFactory can help you create a data lake or data warehouse, especially when combined with a service such as Xplenty. Sometimes cases arise where you need to take advantage of the robust third-party library community to help extend your API. In the example below, we are going to use a third-party library to help us connect our DreamFactory instance to a Twitter Account to post updates. We also bring in the conecpt of connecting the Twitter service to our Rapsberry Pi IoT device to post temperature information to show you how can connect multiple services through DreamFactory to manage your data needs in a simple, robust method that makes API mangement a snap.

Continue reading “Extend Your Twitter Integration With Third-Party Libraries”

Improved Data Security with MySQL Privileges and DreamFactory

DreamFactory and MySQL

All MySQL installations naturally include a root account and offer the ability to create restricted user accounts. However, otherwise sane developers will often use these root accounts for application-level communication, dramatically raising the likelihood of data theft, data exfiltration, and other security issues. For that reason the DreamFactory team always recommends users take care to create restricted MySQL users before using the platform to generate APIs.

In this tutorial, you’ll learn how to create a non-root MySQL user and then further restrict this user’s privileges to a specific database and even table subset. You’ll also learn how to subsequently revoke a user’s privileges to reflect changing requirements.

Continue reading “Improved Data Security with MySQL Privileges and DreamFactory”

Learning About The Bitnami System Database

Database Code Lines

The Elusive Bitnami System Database

If you want to spin up a fast API solution, DreamFactory is a great way to do that with a Bitnami install. Within minutes you can have a fully documented and secure REST API to utilize. Just like any program bundle, there are lots of features to learn and interact with.  Outside of a Docker Swarm or AWS ELB setup, it is pretty hard to find a way to spin up a DreamFactory instance faster. We are going to dive in a bit further to find out how to interact with the system database. Continue reading “Learning About The Bitnami System Database”

AWS Redshift – SQL Functionality on Planet-Scale Hardware

The Problem

Your manager’s peers have been bragging a lot lately about their data warehouses, analytics, and charts, and now a steady stream of data-related questions are being sent your way.  Your department maintains several databases, and the data they contain has the potential to answer everything management is asking for. But the databases are needed for day-to-day operations, and can’t scale to answer these often highly specific questions such as, “How many asparaguses were consumed by men named Fonzie in Cleveland on Tuesdays in 2013?”. How to unlock the potential of this data?

You’ve probably heard of data warehouses, which are tailor-made for this sort of witchcraft. They make it possible to unlock every bit of value from data, and find answers wickedly fast. In the past, creating and maintaining data warehouses meant large, ongoing investments in hardware, software, and people to run them. This would be a hard sell – isn’t the company already spending enough?! Good news, however! In this day of cloud computing, it’s incredibly simple to create, load, and query data warehouses. They typically charge on a usage basis, meaning you don’t need the initial upfront capital investment to get off the ground. And they are super fast – far more powerful than anything you could run in-house.

This post will focus on Amazon Web Services Redshift (Amazon Web Services = AWS). And as a bonus, I’ll demonstrate the incredible Dreamfactory, which automatically builds a slick REST API interface for AWS Redshift over the top. From there, you’re a GUI away from giving management everything they could ask for, and wowing them with extras they hadn’t even thought of. They can now stand tall amongst their fellow executives, knowing you have their back.

AWS Redshift

AWS Redshift is built upon PostgreSQL, but has been dramatically enhanced to run at “cloud scale” within AWS. There are a few ingredients to this secret sauce:

Column-oriented storage

While you don’t need a deep understanding of what’s happening under the hood to use it, Redshift employs a fascinating approach to achieve it’s mind-boggling performance.

Let’s say you have data that looks like the following:

ID NAME CREATED DESCRIPTION AMOUNT

1 Harold 2018/01/01 Membership 10.00

2 Susan 2017/11/15 Penalty 5.00

3 Thomas 2016/10/01 Membership 8.00

Most SQL databases you’ve probably used in the past are row-based, which means they store their data something like this:

1:Harold,2018/01/01,Membership,10.00;

2:Susan,2017/11/15,Penalty,5.00;

3:Thomas,2016/10/01,Membership,8.00;

This is the efficient way to maximize storage, and works well for retrieving data in the “traditional fashion” (rows at a time). But when you want to slice and dice this data, it doesn’t scale very well. If you’ve got large (business-scale) volumes of data, and a variety of ways you want to query it, you can really start to strain your database.

Column-based databases, on the other hand, flip this idea on its head, and store the information in a column-based format, with the *data* serving as the *key*. So the above might look something like this:

Harold:1;Susan:2;Thomas:3;

2018/01/01:1;2017/11/15:2;2016/10/01:3;

Membership:1,3;Penalty:2;

10.00:1;5.00:2;8.00:3;

This drastically improves query performance. For example, when searching for “DESCRIPTION == ‘Membership'”, the query only needs to make one database call (“give me the items with a ‘DESCRIPTION’ of ‘Membership'”), instead of inspecting each row individually (as it would have to do in a traditional, row-based database). Very cool, very fast!

Massive Parallelization

When I picture what the AWS cloud must look like, I usually conjure something up from the Matrix (except it’s full of regular computers, rather than, well, humans). Or maybe Star Trek’s “Borg”, a ridiculous planet-cube flying through space, sucking up other civilizations. I guess both of those images are a little disturbing. A safer mental image is this – data centers spanning the globe, loaded with racks and racks of computers, all connected and working together.

For most computing tasks, throwing more hardware at the problem doesn’t automatically increase performance. There are bottlenecks that remain in place no matter how many processors are churning away. In our “traditional database” example, this bottleneck is typically disk I/O – the processors are all trying to grab data from the same place. To overcome this, the architecture and storage have to be arranged in a way that can benefit from parallelization.

Which is exactly the case with AWS Redshift. Due to the column-based design described above, Redshift is able to take full advantage of adding processors, and it’s almost linearly scalable. This means if you double the number of computers (“nodes”, in Redshift-speak), the performance doubles. And so on. Combine this scalability with the ridiculous number of computers AWS has at it’s disposal (specifically, several Borgs-worth), and it’s like staring out at a starry night. It goes on forever in all directions.

How this works for you

If you’re sold on the power of AWS Redshift, then you’ll be pleased to learn that setup is incredibly simple. AWS documentation is top notch, a crucial thing in this brave new world. When writing this post, I followed their tutorial, and it all went smoothly. Probably took me 15 minutes, and I had the example up and running.

If you already have SQL expertise, you won’t have any problem picking up Redshift syntax. There are some differences and nuances, but the standard “things” (joins, where clauses, etc) all work as expected. I typically use Microsoft’s SQL Server Management Studio (SSMS), and was able to connect to Redshift with no problem (after setting it up as a linked server). Your favorite SQL client will presumably work here as well (anything that supports JDBC or ODBC drivers).

Once you get your feet wet, there are myriad tools that will load your business data into Redshift. If you’ve got SQL chops in house, I’d start with the AWS documentation, and go from there. If you need a little (or a lot) of help, a whole ecosystem of companies and tools have sprung up around Redshift. A quick Google search will introduce you to them.

When you’re up and running, and growing more comfortable demanding more from the system, AWS makes it incredibly simple to add capacity. Thanks to the brilliant Redshift architecture, you just add nodes, and AWS takes care of the rest. Their billing dashboard will show you what it’s costing in real time, with no hidden or creeping costs of data centers, hardware upgrades, things going bump in the night, etc. So much magic happening under the covers, and you get the credit. The joys of cloud computing!

My Humble Example

When writing this, I used the example AWS provides (it consists of a few tables containing some fake Sales data). With everything in place, I can query from SSMS (with a little bit of “linked server” glue syntax):

exec ('-- Find total sales on a given calendar date.

SELECT sum(qtysold)

FROM sales, date

WHERE sales.dateid = date.dateid

AND caldate = ''2008-01-05'';') at redshift

sum

--------------------

210

(1 row affected)

I get a thrill when a chain of systems, architectures, and networks all flow together nicely. Somewhere in a behemoth of a data center, a processor heard my cry, and spun out this result in response. Amazing.

DreamFactory

Now that the company has access to the data, and can gleefully ask any question, they are going to want the dashboards and pretty graphs. Typically you’d use a REST API to feed the data to some sort of UI, but how to do this with Redshift? While management is tickled with their new toy, they will cloud over with suspicion if you now propose a months-long project to make it shinier.

In keeping with the theme of “easy, automatic, and powerful”, I’d propose using DreamFactory. In a matter of minutes (literally), it will connect to a data store (both SQL or NoSQL), intelligently parse all the schema, and spin up a REST API layer for doing all the things (complete with attractive documentation). What used to take a team of developers months can now happen in an afternoon!

Here are some screenshots of my REST API, completely auto generated from the Redshift example above. It took me about 15 minutes (12 of those spent poking around the documentation) to get this done. For my simple example, I followed their Docker instructions, and in no time was playing with the REST API depicted below:

let’s get our rest on!

what pretty documentation you have!

Powerful stuff!

To Infinity and Beyond!

Now that you’ve witnessed how easily you can warehouse all your data, and bootstrap it into a REST API, it’s time to bring this to your organization. Play with it a little, get comfortable with the tools, then turn up the dials.   Want to learn more about how DreamFactory and Redshift can work together (or how to put a REST API on any database)?  Schedule a demo with us. The next time management comes calling for data, you can give it to them with a fire hose!

Microsoft Server 2012 R2, SQL Server 2016 and DreamFactory

Part 1: Running Microsoft Server 2012 and SQL Server on AWS, on my MacBook Pro

How do we get from here, hosting an AWS Microsoft SQL Server instance on my MacBook Pro?

AWS Microsoft Server 2012 R2 Desktop
AWS Microsoft Server 2012 R2 Desktop

To here using Microsoft Server, SQL Server, and DreamFactory, still on my MacBook Pro.

SQL Server Get Schema
SQL Server Get Schema

Some Background:

Let’s get to the nuts and bolts of this.  In the past, it was very difficult to create cross platform solutions.  With the advent of cloud computing, this has become increasingly easier to do.

When you have a robust piece of middleware software, such as DreamFactory which is for most intents and purposes language and platform agnostic, you really do have your choice of platforms to install it on.  Each has its advantages and disadvantages, which I am not going to go into detail in this article, but suffice it to say, there are a lot of enterprises that choose the Microsoft platform(s), and some of those advantages became apparent as I worked on this post.

First things first, make sure to grab all of the pre-requisites you need to make the install easy:

Required Software and Extensions

At a minimum, you will need the following software and extensions installed and enabled on your system in order to successfully clone and install DreamFactory 2.12.0+.

  • PHP 7+ – check and install the requirements below for your particular environment.
    • PHP required extensions: Curl, MBString, MongoDB, SQLite, and Zip. You may need to install other extensions depending upon DreamFactory usage requirements. If you don’t plan on using MongoDB, please remove the df-mongodb requirement from,composer.json or include the --ignore-platform-req option when running composer install.
  • Git
  •  Windows Git Client – Git Bash lets you run “Linux style” commands
  • A web server such as NGINX, Apache, or IIS. You may use PHP’s built-in server for development purposes.
  • One of four databases for storing configuration data: MS SQL Server, MySQL (MariaDB or Percona are also supported), PostgreSQL, or SQLite.
  • Composer – may require cURL to be installed from particular environment below.

Microsoft Server can be spun up almost anywhere now, as is evidenced by the photos above, and since DreamFactory is platform agnostic, we can install it on the Microsoft Server 2012 R2 instance with just a few bits of software installed to get up and running. There are multiple ways to grab and install PHP on a Microsoft platform, but an easy way is to utilize the Web Platform Installer (version 5.0 as of this post).

The Install:

You can download the Web Platform Installer for IIS here. Select a PHP version (7.0.x is required to run the current 2.13.0 version of DreamFactory), and different pieces of IIS, should you decide to utilize that as your production web server.  This post will not dive into the nitty-gritty of IIS, but you can see our documentation here.  We will be using PHP’s built-in development web server to just illustrate the connections.

Web Platform Installer 5.0
Web Platform Installer 5.0 Showing PHP installed

Once you have installed PHP and double checked your pre-requisites are installed, you can begin the install:

  • Perform a Git clone into this directory for Dreamfactory:
git clone https://github.com/dreamfactorysoftware/dreamfactory
Git Clone DeramFactory
Clone down the latest version

This will pull down the master branch of DreamFactory into a directory called ./dreamfactory.

  • Navigate to the dreamfactory directory and install dependencies using composer. For production environment, use --no-dev, otherwise discard that option for a development environment. If you are not running or plan to run MongoDB, add —ignore-platform-reqs:
composer update --ignore-platform-reqs --no-dev
composer update --ignore-platform-reqs --no-dev
composer update –ignore-platform-reqs –no-dev

Otherwise, run the following command to install the dependencies:

composer install --no-dev
  • Run DreamFactory setup command-line wizard. This will set up your configuration and prompt you for things like database settings, first admin user account, etc. It will also allow you to change environment settings midway and then run it again to complete the setup.
php artisan df:setup
DF:Setup
php artisan df:setup

Follow the on-screen prompts to complete the setup.

Prompts
Follow the prompts

You can then run php artisan serve and migrate over to the address and port you have set up. In this example, we are running off of http://127.0.0.1:8000

php artisan serve
php artisan serve

Part 2:  The SQL Server Reckoning

With our instance running now, we can finally delve into the “fun” part of this install.  The ease with which you can add a SQL Server instance is awesome.  It is the fastest install I have ever done from the driver install to DreamFactory connection, it was less than 5 minutes¹.

Using the Web Platform Installer, you can download a SQL Server driver package that is compatible with your PHP version and your O/S version.

SQL Server Driver Package, version 5.2
SQL Server Driver Package, version 5.2

Now you can head back over to your instance and create a SQL Server service.  Just select the service type, add in your credentials and then test it.  That’s it.  No muss, no fuss.  Take a look at the screenshots below to see the results.

Create your service
Create your service
Add your credentials
Add your credentials
SQL Server Get Schema
SQL Server Get Schema

We have now connected our SQL Server instance to our Microsoft Server 2012 R2 (both hosted on AWS) on my MacBook Pro.  Sometimes, it all falls into place.  Don’t forget to check out our wiki and community forums for more topics, information, and examples.

¹ I had my credentials on hand in a notepad text file for copy/paste quickness, but still, very fast 🙂

Filtering Related Columns within DreamFactory REST API Queries

Consider a query which joins employee records found in an employees table with information about their assigned department, the latter of which resides in a table named departments. The relationship is formalized using a key named emp_no. When DreamFactory parses the schema it will create aliases for each relationship, including one for the above-described named something like dept_emp_by_emp_no. The join query will therefore look like this:
/api/v2/mysql/_table/employees?related=dept_emp_by_emp_no
This would yield a JSON response containing records that look like this:
{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "1986-06-26",
  "birth_year": "1953",
  "dept_emp_by_emp_no": [
    {
      "emp_no": 10001,
      "dept_no": "d005",
      "from_date": "1986-06-26",
      "to_date": "9999-01-01"
    }
  ]
},
If you wanted to limit the related fields to just dept_no and from_date, you would add dept_emp_by_emp_no.fields to the parameter list:
/api/v2/mysql/_table/employees?related=dept_emp_by_emp_no&dept_emp_by_emp_no.fields=dept_no,from_date
This query would yield records with the following structure:
{
  "emp_no": 10001,
  "birth_date": "1953-09-02",
  "first_name": "Georgi",
  "last_name": "Facello",
  "gender": "M",
  "hire_date": "1986-06-26",
  "birth_year": "1953",
  "dept_emp_by_emp_no": [
    {
      "dept_no": "d005",
      "from_date": "1986-06-26"
    }
  ]
},
You can learn more about working with related data inside DreamFactory on our wiki: http://wiki.dreamfactory.com/DreamFactory/Features/Database/Related_Data#Getting_the_Related_Data.