Microsoft Server 2012 R2, SQL Server 2016 and DreamFactory – A Match Made in Heaven

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 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 cross over platforms and create Microsoft based solutions or Linux based solutions on the other’s platform.  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 our trusty Web Platform Installer friend, 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 🙂

Why You Shouldn’t Build Your Own REST API

 
BenBusseWhat’s the story behind the DreamFactory Services Platform? We make applications ourselves on cloud platforms like Salesforce, Windows Azure, and AWS. Every new application we created for our customers required the same manual steps:
  • Set up backend databases, schema, and file storage
  • Create a user management system with secure authentication
  • Design and create backend services for data, files, and external APIs
  • Write our own REST API to access all these services
  • Integrate the frontend application with these backend services
  • Test all of that integration end-to-end
Ouch! All that time spent creating the backend services and API took away valuable time creating the actual application that customers would be using everyday. We searched for an open source solution that could solve this problem. Alas, it didn’t exist. So we decided to build it. We realized that other app developers faced the exact same problems and could benefit from our work. So it made perfect sense to open source it. Many of our customers are large enterprises with sophisticated requirements, especially around security. The platform had to satisfy several goals:
  1. Dramatically simplify life for frontend developers. The platform should eliminate the need to write any server-side code.
  2. Support HTML5 and native mobile applications running on performance and bandwidth-constrained phones and tablets.
  3. Provide a comprehensive palette of backend services and a unified REST API to power sophisticated, data-driven applications at scale.
  4. Provide world-class security that large enterprises could adopt.
  5. Provide open source flexibility. A developer or sys admin should be able to install the DreamFactory software package in the cloud or on premise.
The unified REST API mentioned in goal 3 above is a key feature of the platform. Now you don’t have to write your own REST API. It’s automatically created for every backend service that your application needs. The API includes 123 standard GET, POST, PUT, and DELETE calls for:
  • /user – 11 API calls for user authentication, registration, profiles, and sessions
  • /system – 45 API calls for managing apps, app groups, email, roles, services, and users
  • /app – 16 API calls for application containers, files, and folders
  • /db – 8 API calls for database CRUD operations
  • /doc – 16 API calls for document containers, files, and folders
  • /email – 1 API call to send email
  • /lib – 16 API calls for lib containers, files, and folders
  • /schema – 10 API calls for managing schema
And every time you add a new service, the corresponding REST API for that service is automatically created and documented. For example, say you connect to a MongoDB database with DreamFactory. updateservice The new API ‘/mongo’ is automatically created and documented. Presto, now you have a REST API to access your remote MongoDB database from the client! mongodb Before you start building anything, spend 5 minutes browsing the API. The API is documented with an awesome tool called Swagger. Swagger lets you try out live API calls right in your browser.  It’s interactive, so you can quickly learn the capabilities of the API without writing a line of application code. Try it out! You can browse the API in two places: on our website and in the API Documentation tab of the admin console. apidocumentationtab Also check out Jason’s recent blog post on getting started with the DreamFactory API. Have fun with the API and let us know what you think!

NoSQL, No Problem! – Operation Specifics

 
LeeHicksAs a continuation to my initial blog on NoSQL support on the DreamFactory Services Platform (DSP), I would like to give you a little more information on how to use the NoSQL service operations. Designed to be flexible and powerful, yet still adhere to simple REST principles, there are several options for CRUD operations available in the DSP REST API for NoSQL services. Once you have configured your NoSQL service, you can click the document icon to the right of the service listing on the admin console and get the Swagger UI interface for documentation and testing as seen below. DreamFactory MongoDB Rest API Notes about this blog:
  1. I am using MongoDB as our example service but all supported NoSQL types behave similarly, with some exceptions.
  2. Also, I am using JSON format everywhere here, but XML is also supported.
  3. In the cURL request, not all headers are included in request, i.e. authentication, app name, etc. You can use the Swagger UI to perform many of these same calls much easier.
  4. See my notes in the earlier blog about how the HTTP verbs function.

Table Administration

As you see from the list above, all table administration operations utilize the root of the service for the URL. All tables are identified by a ‘name’ field in the operations. All posted or returned data is transmitted as a single object representing a table containing a ‘name’ field at a minimum, or an array of table objects returned as a ‘table’ field value, except where noted. Retrieving Tables Every DSP service, when queried at its root with no extra parameters, returns an array of ‘resources’ available by that service. In the SQL and NoSQL case, these resources are table names. To get a list of currently available tables, just send a GET request to the service’s root, which, if you are using one of our hosted DSPs, looks like this.
curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo
The response looks like…
{
  "resource": [
    {
      "name": "test"
    },
    {
      "name": "zipcodes"
    }
  ]
}
To add vendor-specific details to the output, just add the query parameter ‘include_properties’ set to true. In this case, we get MongoDB-specific details about each collection.
curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo?include_properties=true
The response looks like…
{
    "table": [
        {
            "name": "test",
            "indexes": [
                {
                    "v": 1,
                    "key": {
                        "_id": 1
                    },
                    "ns": "test.test",
                    "name": "_id_"
                }
            ]
        },
        {
            "name": "zipcodes",
            "indexes": []
        }
    ]
}
Additionally, there is a ‘names’ query parameter accepting a comma-delimited list of table names that allows the client to selectively retrieve information about specific tables
curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo?include_properties=true&names=test,zipcodes
Creating Tables Use the POST operation to create one table or multiple tables at once. At a minimum, a value for the ‘name’ field is required to create a table. Additional vendor-specific fields may also be sent. Some vendors may require additional fields. Again, a single object or an array of objects is permitted in the posted data. The output will reflect the input, i.e. an array posted will result in an array received. To create a new table, use the following…
curl -X POST https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo \
     -H 'Content-Type: application/json' \
     -d '{ "table": [ { "name": "test", … } ] }'
The response looks like…
{
    "table": [
        {
            "name": "test",
            "indexes": [
                {
                    "v": 1,
                    "key": {
                        "_id": 1
                    },
                    "ns": "test.test",
                    "name": "_id_"
                }
            ]
        }
    ]
}
Updating Tables Most of the NoSQL vendors accept little to no updates on the meta-data part of the tables. Some do however, and those would be accessed as follows…
curl -X PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo \
     -H 'Content-Type: application/json' \
     -d '{ "table": [ { "name": "test", “indexes”: {…} } ] }'
Deleting Tables Obviously this should be approached with caution. You can use the ‘names’ query parameter in a delete request to delete existing tables.
curl -X DELETE https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo?names=test
An additional option for delete would be to post the same data format as the other operations.
curl -X DELETE https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo \
     -H 'Content-Type: application/json' \
     -d '{ "table": [ { "name": "test" } ] }'

Record Administration

The DSP REST API provides the following ways for your app to retrieve, create, update and delete data from the tables. The table name is sent as part of the URL, while the other options are sent as query parameters or as part of the posted data. Many of the query parameters can also be sent via the posted data. One special query parameter available on all requests is the ‘fields‘ parameter. This dictates which fields will be returned for the affected records of the operations. It accepts a comma-delimited string of field names when passed as a query parameter, or an array of field names when passed in posted data. For GET request, this parameter defaults to returning all fields, i.e. a ‘*’ value, while all other request types return only the record identifying fields by default. This saves the client from having to do an additional round-trip call to get things like updated or auto-filled field values, or to get a list of records changed when updating by a filter. Again, where applicable, a single object or an array of objects is permitted in the posted data. The output will reflect the input, i.e. an array posted will result in an array received. Creating Records To create a single record …
curl -X POST https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes \
     -d '{ "_id": "95008", "state": "CA", "pop": 39968 }'
To create multiple records…
curl -X POST https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes \
     -d '{ “record” [ { "_id": "95008", "state": "CA" }, { "_id": "30022", "state": "GA"} ] }'
If the DB vendor does not automatically create the identifying fields (primary key), it must be included in the POST request. The applicable identifying fields are always returned for successfully created records on a create request. Retrieving Records There are many ways in which an app can retrieve data through our API. Depending on your app you may want to use one or all of these. Note that the first three also make use of the optional query parameter ‘id_field’ which allows the client to state which field is used as the identifying field for the records in that table. Here is a quick look at the retrieval options.
  • By a single record identifier – In this case, the identifying field (think primary key) value is passed as part of the URL after the table name. This will return a single record or a 404 – Not Found error.
curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/95008
Returns this…
{
    "_id": "95008",
    "city": "CAMPBELL",
    "state": "CA",
    "pop": 39968
}
  • By a list of record identifiers – This case uses the ‘ids’ query parameter sent as a comma-delimited string of id values, or ‘ids’ field sent as comma-delimited string or an array of id values in posted data. If a large number or ids, or the id values are long or have special characters in them, it would be better to pass them as posted data.
curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/?ids=95008,30022
Returns this…
{
    "record": [
        {
            "_id": "95008",
            …
        },
        {
            "_id": "30022",
            …
        }
    ]
}
  • By record – This case allows you to post a single record or an array of records that at a minimum include the identifying field(s) and values. This is useful in retrieving records with more than one key, or for updating a stash of records that you have old or partial data for already. (Note: The below cURL call works for *nix and mac, for Windows, use -X POST -H “X-HTTP-Method: GET” instead of -X GET.)
curl -X GET https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes \
     -d '{ “record” [ { "_id": "95008", … }, { "_id": "30022", … } ] }'
  • By filter – This is the most powerful option for data retrieval. The NoSQL service allows the client to use a simple SQL-like filter string passed as a query parameter (url-encoded), or in some vendor cases, native filters in various formats (like MongoDB’s json format, more on this in later vendor-specific blogs).
For our simple SQL-like filter string, comparison operators supported for every vendor are =, !=, >, >=, <, <=.; Or given as their SQL short form ‘ eq ‘, ‘ ne ‘ (or ‘ <> ‘), ‘ gt ‘, ‘ ge ‘, ‘ lt ‘, ‘ le ‘. Depending on the db vendor, others such as contains, like, and begins-with may also be supported. Spaces surrounding the operator is required. String values on the right side of the comparison must be within single or double quotes. Depending on the db vendor, logical comparisons (AND, OR, NOT) are also supported in order to build out more complicated queries. Other ‘filter helper’ query parameters are also available.
  • limit – Defaults to return all, accepts an integer greater than 0 to limit the number of returned records.
  • order – Accepts a field name followed by space and then ASC or DESC to order the returned records.
  • offset – Accepts an integer greater than 0 to skip that many records in the response. This is useful for setting up paging through records, but may not be supported by all vendors.
For example, if we want to find the first 3 records in the zipcodes table information that have a population of over 20,000 people, returning only the city and state. If you are familiar with SQL, this would look like “SELECT _id,city,state from zipcodes WHERE pop > 20000;”. Here is what it looks like in cURL.
curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes?filter=pop+%3E+20000&limit=3&fields=city%2Cstate
And this is what gets returned…
{
    "record": [
        {
            "_id": "28659",
            "city": "NORTH WILKESBORO",
            "state": "NC"
        },
        {
            "_id": "31201",
            "city": "HUBER",
            "state": "GA"
        },
        {
            "_id": "71291",
            "city": "WEST MONROE",
            "state": "LA"
        }
    ]
}
Updating and Merging Into Records The same options available for retrieving data also apply to updating records. As mentioned in the earlier blog, the PUT HTTP verb is used when the whole record is to be replaced with the posted data. The PATCH HTTP verb is used when the client only wants to send the changing fields to the server. The same array of records or a single record format with changes is supported for updating records. To replace the whole record at id of 95008, send the request like…
curl -X PUT https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes \
     -d '{ "_id": "95008", "city": "Campbell", "state": "CA", "pop": 40102 }'
If you only want to merge changes into that record without having to resend everything else, then send the request like…
curl -X PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes \
     -d '{ "_id": "95008", "pop": 40102 }'
If you only want to update or merge data for one record, and it can be identified by a single key field, then you could also add the id to the end of the URL, and pass only the fields that require change.
curl -X PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/29684
     -d '{ "pop": 40102 }'
Two other methods of merging data are by id list or filter (using “ids” or “filter” url parameters mentioned above for retrieving records). In these cases, if not natively supported, the server will query the table for the filtering results, merge in the record changes and push the changes back in an update request. This is an easy way of updating multiple records with the same field-value changes. Deleting Records Deleting supports the same options as updating. The only difference is, when using the id, ids or filter options, no posted data is required. Obvious, right? Using the ‘fields’ query parameter, the client can request the full or partial records before they are deleted from the system. What Next? Stay tuned for more specifics on each NoSQL vendor, next up more on MongoDB.

Four Big Problems DreamFactory Solves

 
BillAppletonThe DreamFactory Services Platform (DSP) solves four big problems that plague mobile application developers. First, we provide an easy to use REST API that covers a wide variety of backend services. Many server side assets like SQL databases don’t have a client addressable API. In cases like this we build out the entire API for the backend system. Other server side assets might have an API, but they don’t support JSON data, so we translate everything into JSON for ease of use. Lastly, different systems from different vendors tend to have wildly different or technically challenging network protocols and authentication methods. Go look at the interfaces for MongoDB, S3, Azure Tables, SimpleDB, SendGrid, MySQL, and CloudFiles. They all work differently. So we simplify all those systems into a single REST API that can be used from the client. Second, we hide the master credentials for all these different systems and expose their functionality through single sign-on with user roles and permissions. This means you never have to store or use a master credential from the client device where it can be stolen by a hacker. Instead you have a single user authenticate in to our service architecture where they are given limited capabilities to read, write, create, or update the various data sources. All of this is controlled by their user role. And if they lose their mobile device the system admin can instantly kill their session, protecting the backend systems. Third, we solve the originating host problem for HTML5 developers. Modern browsers limit web application communication to the originating server. This is a security measure designed to protect private networks behind the firewall. There are some exceptions to the rule, but in general HTML5 applications can always communicate with their originating host, while talking to other networks is problematic. So the DSP provides a single point of communication that combines data sources from other servers. By the way, your DSP also provides extensive application hosting options. Your CSS, HTML, and JavaScript files are stored right on the same platform as the other services for originating host access. If you need to use the REST API from another domain we can also do that with programmable CORs access that can be set up by the administrator. The DSP also makes life easier for native client technologies. When a native client attempts to communicate with a whole bunch of different servers bad things tend to happen. The client must open network communications with each backend system, handle a bunch of authentication protocols, load and manage all the data, handle network errors and latency, etc. This adds a lot of complexity to the application, especially on a mobile device with intermittent connectivity and limited bandwidth. But with a DSP your native client can communicate to a single endpoint and consume a single REST API that combines everything needed for application runtime. Lastly, the DSP provides administration for all of the different users, applications, and services that are needed. You can control which users see which applications, and which applications can use which services. You can use the DSP Admin Panel to create sophisticated user role and permission systems. For example, the sales, marketing, and executive personnel at a company might each log in and see a very different view of the applications and data. For a simpler scenario without a log in, you could create a “read only” user role that delivers all the various documents and database information that the application uses. The only actual user log in would be for the system administrator who curates the media stored on the server. Taken together, these four capabilities form the powerful backbone of the DreamFactory Services Platform. We are working hard to solve these backend problems so that you can focus on the front end. The result is high performance mobile applications written by much smaller development teams with reduced time to value.
http://www.dreamfactory.com

Try our Free Edition right now, or install our Apache License software package on your cloud or data center. Manage your applications with AWS, Azure, Rackspace, or any VM.