Creating a Microsoft SQL Server API in Less Than 5 minutes with DreamFactory

DreamFactory and Microsoft SQL Server
DreamFactory and Microsoft SQL Server

Do you have a ton of data sources and do not know how to expose them? Do you know you need a Microsoft SQL Server API but don’t know where to begin to build it? Look no further, DreamFactory can take any database and generate a fully documented and secure REST API faster than making a sandwich. All you need is your database credentials and DreamFactory will handle the rest, instantly generating Swagger documentation and securing your API by way of API keys. Follow along with the blog or our video below!

Installing DreamFactory

In order to get started you must install DreamFactory, which is Open Source and gives you the ability to try out a numerous amount of popular databases. To spin up your own hosted environment for free click here.

Generating the SQL Server API

Now the fun part. Once you have DreamFactory up and running you will be on the Admin panel. From there you will navigate to the Services tab to connect your database for your API. For example I have selected a Microsoft SQL Server database but it is nearly identical for any database you will be connecting to. Connecting your database typically only requires filling out these 5 fields as shown below.

SQL Server API

Upon saving there will be a success window pop-up saying “Service saved successfully”. What it doesn’t tell you is all the magic it just did behind the scenes. In just that short amount of time, it generated your REST API. So now if you navigate to the API Docs tab you can see your new documentation for the API and actually interact with it via the “Try it out” button.

SQL Server API

Securing and Interacting with the API

I can end it here now that you have generated your API, but where is the fun in that? Now let’s actually see the API in action! First things first, let’s generate an API key to be paired with the API for security purposes. DreamFactory does not allow access to the API without being authenticated. Let’s navigate to the Roles tab and create a Role for our API. This Role with correspond with the API key so different users can have different privileges based off different keys.

SQL Server API

For my Role I have pointed it to the SQL Server Service we just created and told it to only allow GET calls on the endpoints. This will ensure anyone using this API key will not be able to, for example delete data from the database. We must now link this Role to an API key. If we now go to the Apps tab we can create a new API key with the corresponding Role.

SQL Server API

Once we hit save we are able to see the API key generated for our use. Just to show how it works I will be using Insomnia, a popular HTTP service, to call our DreamFactory API. I will call the customers table, passing the API key in the headers for authentication.

SQL Server API

Conclusion

As you can see I have access to our new API and how much time did that take? Way less time than building this API yourself! If you would like to find out exactly how much time and money DreamFactory can save you, check out our API calculator. Otherwise what are you waiting for? Go build your next application using DreamFactory already!

If you have any questions about the platform, or just APIs in general, we’d love to hear from you! Contact us.

Creating an Almost No Code Database-backed Web Interface with DreamFactory and Tabulator

No matter the size or industry, companies everywhere grapple with solving a deceptively simple problem: displaying database data on the web in a table. This problem seems to be universal in the sense that all teams have experienced the inconvenience of passing around Excel spreadsheets, and want to instead move this data to the web. Yet these same teams often lack the programming experience required to securely retrieve the data and present it within a paginated, filterable and often searchable web interface. Fortunately, using DreamFactory and off-the-shelf software such as Tabulator or DataTables, it’s possible to create a powerful database-backed web interface such as the one presented in the following screenshot:

Example dashboard interface

If you’d like to interact with a live example head over to http://tabulator.demo.dreamfactory.com/ and get your hands dirty! In this blog post I’ll show you how easy it is to create your own database-backed web interface using DreamFactory and Tabulator with almost no code required.

Creating the Database API

DreamFactory is an API generation and management platform used by thousands of organizations around the globe. You can download our open source version from DreamFactory.com or contact us to start an on premise or hosted trial of our commercial version. The open source edition includes support for several popular database, including MySQL and PostgreSQL, whereas the commercial edition additionally supports Microsoft SQL Server and Oracle. Once installed, you can generate a database-backed API in literally minutes, secured by (at minimum) an API and role-based access controls. If you’re not familiar with this process head over to DreamFactory Academy and watch the following video, “Creating Your First Database API with DreamFactory”:
You’ll also find dozens of other videos on our Youtube channel. Once the API created, you can query the database using a standardized API URL structure such as:
https://example.com/api/v2/corporate/_table/employees
Of course, for security reasons you’ll need to additionally pass along an API key. This process is described in the aforementioned video.

Integrating the API with Tabulator

Tabulator is an open source JavaScript library used for creating interactive HTML tables. It supports data loading via a JavaScript array, JSON formatted data, or an AJAX data source. We’ll use the latter approach to load data from the DreamFactory-powered database API. To begin though, you’ll first need to install the library. If you’re looking for the easiest solution, we recommend cloning our dreamfactory-tabulator repository, located on GitHub. Alternatively a number of other installation solutions are supported (Bower, NPM, Yarn, etc); check out the Tabulator documentation for more information. Once installed, you only need to add a few lines of boilerplate code to load the database data into the Tabulator table. The following example contains just 35 lines of JavaScript code and will recreate a basic version of the interface presented in the earlier screenshot:
<!DOCTYPE html>
<html>
    <head>
        <title>Tabulator example</title>
    </head>
    <body>
        <div id="example-table"></div>

        <script>

            var table = new Tabulator("#example-table", {
                    layout:"fitData",
                    height:"100%",
                    layout:"fitColumns",
                    pagination:"local",
                    paginationSize:20,
                    paginationSizeSelector:[20, 40, 60, 80],
                    placeholder:"No Data Set",
                    columns:[
                            {title:"Employee Number", field:"emp_no"},
                            {title:"First Name", field:"first_name"},
                            {title:"Last Name", field:"last_name"},
                            {title:"Birth Date", field:"birth_date"},
                            {title:"Hire Date", field:"hire_date"},
                    ],
            });

            $(document).ready(function(){
                    var ajaxConfig = {
                            dataType: 'json',
                            headers: {
                                    "X-DreamFactory-Api-Key": '123456qwerttasdf' 
                            },
                    };

                    table.setData(
                        'https://example.com/api/v2/corporate/_table/employees', 
                        {}, 
                        ajaxConfig
                    );
            });

        </script>
</body>
</html>
Let’s breakdown some of the key syntax found in this example:
  • The div identified by the example-table id will serve as the location where Tabulator will inject the table. When the Tabulator object is created, you’ll see this div ID (#example-table) is passed in as the first argument so Tabulator knows what div to use.
  • The columns defined in the columns array should match the fields returned within the DreamFactory JSON response. In this example, each record includes five fields: emp_no, first_name, last_name, birth_date, and hire_date.
  • The ajaxConfig object defines the X-DreamFactory-Api-Key header. You’ll assign the API key generated within DreamFactory to this header.
  • The setData method identifies the URL that will be contacted to retrieve the JSON data.
Check out a more involved example in our GitHub repository to learn how to add other features!

In Summary

If you’d like to learn more about DreamFactory, and how our platform can be used to quickly generate powerful web interfaces with almost no code required, contact us at DreamFactory.com!

MS SQL Server vs MySQL – Which Reigns Supreme?

RDBMS databases form the backbone of most business software solutions. When people discuss SQL (Structured Query Language), it’s in reference to an RDBMS system. Applications store all their important data there. The databases (usually) power all the searches. A good database can bring a system to a higher level. A bad database can bring a business to its knees. For any developer or enterprise embarking on a new software venture, one big question is “which database vendor should I use?”. In the early days of computing, database vendors such as IBM and Oracle reigned supreme. That has changed in recent years. MySQL (open source solution recently purchased by Oracle) and Microsoft’s SQL Server have gained market share. According to a 2018 StackOverflow survey, they hold the top two rankings in SQL database usage. But which one is best for YOUR business? MySQL vs SQL Server presents a tough and complicated decision!

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

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”

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.

How To Quickly Create a Simple REST API for SQL Server Database

Would you like to access SQL data from your mobile, web or IOT apps?

To have an easy and secure way to add a REST API to any SQL database in minutes, we are going to walkthrough using DreamFactory to create create just that. All you have to do is use the DreamFactory platform to connect your database, then use it to auto-generate a REST API for your database – it’s that simple!

In this blog post we’ll show how to REST-enable any SQL database, which is free forever for the databases and other services covered by our open source software. Then we’ll show some simple examples of how to use the REST API to manage your SQL schema and data.

Interested in a live demo with one of our engineers? We’ll be happy to show you how it’s done for your particular use case! If you’d rather watch a video, check out DreamFactory Academy. Alternatively, you might want to check out our new guide, Getting Started with DreamFactory. It offers a comprehensive walkthrough of generating a database-backed API. You’ll learn how to generate the API, define a role and API key, and then interact with the API using a variety of queries.

Continue reading “How To Quickly Create a Simple REST API for SQL Server Database”

How to Connect to a MySQL Database with JavaScript

The DreamFactory REST API enables database connections using a wide variety of front end scenarios. This simple sample app demonstrates how DreamFactory easily can be used as a backend for a JavaScript application. It’s a simple address book, where contacts can be created, shown, updated, deleted and grouped: basically, CRUD operations.

Continue reading “How to Connect to a MySQL Database with JavaScript”

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.