NoSQL vs SQL: Understand the Differences and Make the Best Choice

nosql vs sql
NoSQL vs SQL
The tough choice of NoSQL vs SQL. An important decision for any business that may pay the price for down the road if you don’t plan accordingly. Here, we break down the important distinctions and discuss NoSQL vs SQL for you to make an informed decision.

What is NoSQL

NoSQL or Not Only SQL is an alternative to relational databases such as MySQL, Postgres, and SQL Server. Typically NoSQL databases are popular for working with large sets of data as data is stored in the form of flat collections so reading or writing operations is much faster. There is also less management for NoSQL databases as they support automatic repair, data distribution, and simpler data models. MongoDB, Couchbase, and Cassandra are popular NoSQL databases, but what do their queries look like? If NoSQL is all about leaving SQL behind, then what happens? These are fair questions, for example MongoDB uses its own syntax that kind of resembles JavaScript. Let’s take a look:
db.inventory.find( { status: "D" } )
In the above query the example selects from the inventory collection all documents where the status equals “D”. It is not too difficult to follow, but does feel a little foreign if you are used to SQL.

What is SQL

SQL or Structured Query Language is a language used in programming and designed for managing data held in a relational database management system. Some examples of popular SQL databases are Microsoft SQL Server, MySQL, and Postgres. One of many reasons for its popularity is how portable it is. SQL can be used in the program in PCs, servers, laptops, and even some of the mobile phones. Unlike NoSQL databases, SQL databases have an established, well defined standard. The language is also very straightforward, let’s take a look at it:
SELECT * FROM inventory WHERE status = "D"
In the above query the example selects from the inventory collection all documents where the status equals “D”. Sound familiar? That is because it is doing the same thing the MongoDB query is doing.

Which is Better?

This all depends on what your goals are. SQL databases tend to be beneficial for companies that have a clear business structure and do not plan on making many changes to their database structure. If your company has rapid growth or no clear schema definitions then a NoSQL database is the right choice for you. If you’re uncertain about which type of database is best for your business, you can schedule a consultation with one of our experts today.

What if you want to use a SQL database but not learn SQL or the opposite, use a NoSQL database without learning how to handle the data? That is where DreamFactory comes into play, interact with your data without having to learn SQL. In minutes generate an API from nearly any datasource that is fully documented and secure without code. Contact an expert today to learn how to cut development cost and time in half.

Improved Data Security with MySQL Privileges and DreamFactory

DreamFactory and MySQL

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

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

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

Learning About The Bitnami System Database

Database Code Lines

The Elusive Bitnami System Database

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

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”

Create a MySQL REST API in Minutes – A Complete Tutorial

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 I’ll show you just how easy it is to build, secure, and deploy a REST API for your MySQL database.

Generating the MySQL REST API

DreamFactory can generate REST APIs for 18 databases, among them MySQL, Microsoft SQL Server, Oracle, PostgreSQL, and MongoDB. To do so, you’ll login to the DreamFactory administration interface, navigate to Services and then enter the service creation interface by clicking on the Create button located to the left of the screen. From there you’ll select the MySQL service type by navigating to Database > MySQL (see below screenshot).

Next you’ll be prompted to provide a name, label, and description (below screenshot). The latter two are used just for reference purposes within the administration interface, however the name value is particularly important because as you’ll soon see it will comprise part of the API URL.

Finally, click on the Config tab. Here you’ll be prompted to provide the database connection credentials (see below screenshot). This should really be nothing new; you’ll supply a host name, username, password, and database. Additionally, you can optionally specify other configuration characteristics such as driver options, the timezone, and caching preferences. For the purpose of this tutorial I’ll stick to the required fields and leave the optional features untouched.

With the credentials in place, just press the Save button at the bottom of the screen, and believe it or not the REST API has been generated!

Viewing the Swagger Documentation

Along with the API, DreamFactory will also auto-generate an extensive set of interactive Swagger documentation. You can access it by clicking on the API Docs tab located at the top of the administration interface, and then selecting the newly generated service by name. You’ll be presented with 44 endpoints useful for executing stored procedures, carrying out CRUD operations, querying views, and much more. For instance the following screenshot presents just a small subset of newly generated MySQL REST API endpoints!

Creating a Role and API Key

All DreamFactory-generated APIs are automatically protected by (at minimum) an API key. You can optionally authenticate users using basic authentication, SSO, or Directory Services (LDAP and Active Directory). Furthermore, you can associate each API key and/or user with a *role* which determines exactly what services the user is allowed to access. Not only that, you can restrict interactions to a specific database table or set of tables, a specific endpoint(s), and even restrict which HTTP methods are allowed.

As an example, let’s create a new role which restricts the associated API key to interacting with a single table in a read-only fashion within the newly created MySQL API. To do so, navigate to the Roles tab, and click the Create button. You’ll be presented with the interface found in the below screenshot. In the screenshot you’ll see I’ve already assigned a name and description for the role, and made it active by selecting the Active checkbox.

Next, click the Access tab. This is where you’ll define what the role can do. In the below screenshot you’ll see I’ve limited the role to interacting with the MySQL service, and within that service the role can only interact with the _table/employees* endpoint via the GET method. We’re on lockdown baby!

Save the role by clicking the Save button. Now we’ll create a new API key and associate the key with this role. To do so, click on the Apps tab located at the top of the screen, and then click the Create button. Assign your new App a name and description, ensure it is set to Active, and then assign it the default role of MySQL just as I’ve done in the below screenshot. Regarding the App Location setting, presuming you plan on interacting with the API via a web or mobile application, or via another web service, then you’ll want to select “No storage required”.

Press the Save button and you’ll be returned to the Apps index screen where the new API key can be copied! Copy the key into a text file for later reference.

Configuring CORS

We have one final configuration step before being able to test the API from outside the DreamFactory administration interface. You’ll need to enable CORS (Cross-Origin Resource Sharing) for the new API. For purposes of demonstration, you can set the default CORS setting as I’ve done in the below screenshot, which will allow API-restricted traffic from all network addresses:

Testing the REST API

With the API generated, API key and associated role created, and CORS configured, you’re ready to begin interacting with the API via a client! I like to use Insomnia for HTTP testing on MacOS, however another popular solution is Postman.

In the following screenshot I’m using Insomnia to contact the /api/v2/_table/employees endpoint using a GET request.

Recall that we’ve locked down this API key to only interact with the /api/v2/_table/employees/* endpoints using the GET method. So what happens if we try to POST to this table? A 401 (Unauthorized) status code is returned, as depicted in the following screenshot:

Alternative Approaches

Obviously DreamFactory isn’t the only solution available. Check out these other popular tutorials for different perspectives on the topic:

Where to From Here?

Believe it or not, we’ve only scratched the surface in terms of what DreamFactory can do for you. If you’d like to see our SQL Server, Oracle, or MongoDB connectors in action, or would like to watch how easy it is to convert a SOAP service to REST without writing any code, why not schedule a demo with our engineering team! Head over to https://www.dreamfactory.com/products and schedule a demo today!

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”

Community Spotlight: Crystal Taggart pens new book Build My App!

We were really excited to interview DreamFactory superuser and renaissance woman, Crystal Taggart. Crystal is the author of the upcoming book called Build My App!, which teaches people how to build an app using low cost and open source platforms. We are honored to be featured as one of the main solutions. Continue reading “Community Spotlight: Crystal Taggart pens new book Build My App!”

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!