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!
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.
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.
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.
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.
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.
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!
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.
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:
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:
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:
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!
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 2019 StackOverflow survey, they hold the #1 and #3 rankings respectively in most popular databases used by professional developers. But which one is best for YOUR business? MySQL vs SQL Server presents a tough and complicated decision!
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.
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”
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 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:
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:
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!
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
To here using Microsoft Server, SQL Server, and DreamFactory, still on my MacBook Pro.
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.
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 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:
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
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
php artisan df:setup
Follow the on-screen prompts to complete the setup.
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
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
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 serviceAdd your credentialsSQL 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 🙂
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:
Need access to SQL data from your mobile, web or IoT apps?
To have an easy and secure way to add a SQL Server REST API to any SQL database in minutes, we are going to show how you can use DreamFactory to do just that. Use the DreamFactory platform to connect your database, utilizing nothing more than your database credentials, then DreamFactory auto-generates a REST API for your SQL database – it’s that simple!
In this blog post we’ll show how to REST-enable any SQL database, including the creation of a SQL Server REST API. DreamFactory can REST-enable a huge range of databases and data sources including the most popular platforms like MySQL and even Excel. We’ll also show some simple examples of how to use the REST API to manage your SQL schema and data.
With 52% of professional developers rating it as the most popular database, wouldn’t it be ideal if you could create a MySQL REST API in minutes? Karl Hughes recently penned a blog post titled “The Bulk of Software Engineering in 2018 is Just Plumbing“. Notably he stated, “Just like plumbers, we are paid to know our tools and understand how they work together to make a usable piece of equipment, not to reinvent working technology…”. As programmers we should not be bothered with repeatedly writing code which is otherwise readily available, robust, and well-tested.
Yet this problem remains persistent in the REST API space, despite the implementation process being by this point in time rote, repetitive, and prone to error and oversight. This oversight is costly for several reasons:
End users just *do not care* how the API was implemented, meaning there is no competitive advantage to be had by hand-crafting a new API for each project.
Error and oversight in the API implementation and deployment phase can come at a very steep price due to security lapses and performance issues.
Repeatedly building one-off APIs means they can’t be managed via a single platform or interface; unless the team decides to devote even more time and effort to building a custom management solution.
Fortunately, the DreamFactory platform can easily absolve your team from all of these hassles and much more by offering a centralized solution for the API generation, documentation, and security. In this tutorial you’ll learn just how easy it is to build, secure, and deploy a REST API for your MySQL database.