Learning the age of a MySQL database

Every MySQL database server includes a database called information_schema which aggregates metadata about the other databases managed on that server. Among other things you can query the information_schema database’s tables table to learn when a MySQL database was created. This timestamp is found in the tables create_time field. For instance if you wanted to know when a database named dreamfactory was created, you’d query the information_schema database like this:

Continue reading “Learning the age of a MySQL database”

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 Postgres, Microsoft SQL Server and MySQL. 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.

Related reading: How to Connect to a MySQL Database with JavaScript

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.

Instantly Create a SQL Server REST API

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, Microsoft SQL Server (check out our article that examines the differences between MySQL and SQL Server) and even Excel. We’ll also show some simple examples of how to use the REST API to manage your SQL schema and data.

Continue reading “Instantly Create a SQL Server REST API”

MySQL REST API Creation in Minutes – A Complete Tutorial

MySQL REST API

 

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 documentation, generation, 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.

Related Reading: Learn how to generate a REST API for your SQL database

Continue reading “MySQL REST API Creation in Minutes – A Complete Tutorial”

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!”