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.

Did you know you can generate a full-featured, documented, and secure Microsoft SQL Server REST API in minutes using DreamFactory? Sign up for our free 14 day hosted trial to learn how! Our guided tour will show you how to create an API using an example database provided to you as part of the trial!

Create a Microsoft SQL Server REST API Now

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

Did you know you can generate a full-featured, documented, and secure Microsoft SQL Server REST API in minutes using DreamFactory? Sign up for our free 14 day hosted trial to learn how! Our guided tour will show you how to create an API using an example database provided to you as part of the trial!

Create a Microsoft SQL Server REST API Now

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!

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.

The Reusable REST API Platform Strategy

The engineering team at DreamFactory designed and built some of the very first applications that use web services. Over the years, we learned many lessons trying to create the perfect mobile backend for these applications. This article lays out some of the problems companies encounter when they build custom REST APIs from scratch, and after that, we look at some of the architectural advantages and technical characteristics of a reusable REST API platform. The REST API Complexity Problem When a company decides to start a new mobile project, the IT group first defines the business requirements, and then starts writing the actual software. Usually there is a client-side team that designs the application and a server-side team that builds the backend infrastructure. These two teams must work together to develop a REST API that connects the backend data sources to the client application. One of the most time-consuming and expensive aspects of the development process is the “interface negotiation” that occurs between these two groups. With the acceptance of BYOD and general proliferation of mobile devices, the modern enterprise may need hundreds or even thousands of mobile applications. New mobile projects typically have new requirements that were not anticipated by the existing services. You could go back and try to expand the scope of the old services, but they are already in production, and so in many situations a new REST API is created for each new project. And so the API building process continues over time with various developers, consultants, and contractors. The REST APIs are often written with different tools and developer frameworks. They run on different servers or in the cloud. They are tied to different databases and file storage systems. Each new service has different security mechanisms, credential strategies, user management systems, and API parameter names, as this diagram illustrates. Continue reading “The Reusable REST API Platform Strategy”

PHP 7 doubles performance for DreamFactory

speed-increase.png DreamFactory is a high performance request/response engine for REST API Services. You can hook up any SQL or NoSQL database and instantly get a comprehensive REST API for that data source. A big part of our platform is the support for JSON Web Tokens. JWT is an open standard for representing session information that is transferred between the client and server. JWT allows DreamFactory to run in a completely stateless manner, which makes the platform very easy to scale vertically with additional server capabilities or horizontally with multiple instances between a load balancer. Continue reading “PHP 7 doubles performance for DreamFactory”

How to integrate the Twilio API with DreamFactory to enable SMS for your app

twilio.png Twilio has a superb API for integrating SMS messages into your applications. It’s easy to add Twilio as a remote HTTP service to any application you’re building with DreamFactory. DreamFactory lets you securely store your Twilio authentication credentials, call the Twilio API directly from a DreamFactory session, and easily add role-based access control to any Twilio API endpoint. This brief tutorial shows you how to add Twilio to DreamFactory in five minutes.

Continue reading “How to integrate the Twilio API with DreamFactory to enable SMS for your app”

DreamFactory Cache Supports Redis, Memcached, and local storage

Redis
DreamFactory Redis
One of the most powerful features of DreamFactory is the ability to write server-side script in pre- and post-process events and use as a custom scripting service. This provides the ultimate flexibility and allows the platform to support all sorts of complex backend business logics. However, DreamFactory server-side scripts are stateless and their scope is gone once the original process is completed.

Supported Cache Services

To allow for more flexibility in server-side scripts as well as to add a quick way of storing key-value pair data, DreamFactory 2.3 introduces the ‘Cache’ service. The Cache service supports three different types of cache backends.

  • Local file based cache
  • Redis cache
  • Memcached cache
Once the services are configured, all three types of cache backends use the same REST API interface to read, write, update, and delete the key-value pair data. Cache Services are native services of DreamFactory and are supported by features like role-service-access, lookup usage, live API documentation etc.

You can configure and use a cache service in a standalone way to store any number of key-value pair and retrieve them as needed. This allows you to easily implement a server-side caching system for your client apps. But the real benefit of cache service can be realized in server-side scripting. In your DreamFactory server-side script, you can make API calls to your cache service just like you do that for any other services. This will allow you to temporarily or permanently store any data related to your script. This can later be used in another script/process or even inside your client application.

Configuring Your First Cache Service

Let’s take a closer look at this new Cache service and it’s usage. You can configure/provision a Cache service just like any other services using the DreamFactory Admin console -> Services tab.

To learn more about the configuration, please see the wiki page.

Once you configure a DreamFactory Cache service, head over to the swagger API Docs by selecting the ‘API Docs’ tab on the Admin console. Your newly provisioned Cache service should now show up on the list of services there. Select your cache service to expand it. This will show you all the available APIs for your service. You can expand each of this API to get further details on their usage.

Redis service in Swagger

Cache for Performance

If you are interested in getting your API calls in a timely manner you can leverage Redis for consistent speeds. To do so you just have to follow a few simple steps.

First you will want to enable Caching for the Service you would like cached and set the amount of time each cached response is allowed to last.

Redis

Then you will want to edit the Cache Settings section in your .env file. You will want to uncomment the following.

  • CACHE_DRIVER=redis
  • CACHE_DEFAULT_TTL=Cache TTL in minutes
  • CACHE_HOST=
  • CACHE_PORT=6379
  • CACHE_PASSWORD=LEAVE COMMENTED OUT IF YOU ARE NOT USING A PASSWORD
  • CACHE_DATABASE=2
  • REDIS_CLIENT=Which Redis client to use: predis or phpredis

Believe it or not, that Service is now being Cached. Confirm it in your Redis environment and never worry about slow response times again! Chat with an expert today or try out a free instance to see how easy it is yourself.

5 things every DreamFactory user should know

1. Where’s my stuff?

The location of the all powerful DreamFactory code will be located in a different place depending on what type of installation you did. We call this location the installation directory. In many cases, the version number is included as part of the path. In this example, we’ll assume you got verion 2.2.1-2 from Bitnami. Continue reading “5 things every DreamFactory user should know”