# Interacting with the API

We'll conclude this chapter with a series of examples intended to help you become familiar with the many ways in which you can interact with a database-backed API. For these examples we'll be using the Insomnia HTTP client (introduced in chapter 2) however you can use any similar client or even cURL to achieve the same results.

# Retrieving All Records

Let's begin by retrieving all of a particular table's records just as was done within the API Docs example. Open your client and in the address bar set the URL to /api/v2/{service_name}/{table_name}, replacing {service_name} with the name of your API and {table_name} with the name of a table found within the database (and to which your API key's associated role has access). For the remainder of this chapter we'll use mysql as the service nam, and in this particular example the table we're querying is called employees so the URL will look like this:

http://localhost/api/v2/_table/employees

Also, because we're retrieving records the method will be set to GET.

Next, we'll need to set the header which defines the API key. This header should be named X-DreamFactory-Api-Key. You might have to hunt around for a moment within your HTTP client to figure out where this is placed, but we promise it is definitely there. In the case of Insomnia the header is added via a tab found directly below the address bar:

With the URL and header in place, request the URL and you should see the table records returned in JSON format:

The equivalent SQL query would look like this:

SELECT * FROM employees;

# Limiting Results

The previous example returns all records found in the employees table. But what if you only wanted to return five or 10 records? You can use the limit parameter to do so. Modify your URL to look like this:

http://localhost/api/v2/_table/employees?limit=10

The equivalent SQL query would look like this:

SELECT * FROM employees LIMIT 10;

# Offsetting Results

The above example will limit your results found in the employees table to 10, but what if you want to select records 11 - 21? You would use the offset parameter like this:

http://localhost/api/v2/_table/employees?limit=10&offset=10

The equivalent SQL query would look like this:

SELECT * FROM employees LIMIT 10 OFFSET 10;

# Ordering Results

You can order results by any column using the order parameter. For instance to order the employees tab by the emp_no field, modify your URL to look like this:

http://localhost/api/v2/_table/employees?order=emp_no

The equivalent SQL query looks like this:

SELECT * FROM employees ORDER BY emp_no;

To order in descending fashion, just append desc to the order string:

http://localhost/api/v2/_table/employees?order=emp_no%20desc

Note the space separating emp_no and desc has been HTML encoded. Most programming languages offer HTML encoding capabilities either natively or through a third-party library so there's no need for you to do this manually within your applications. The equivalent SQL query looks like this:

SELECT * FROM employees ORDER BY emp_no DESC;

# Selecting Specific Fields

It's often the case that you'll only require a few of the fields found in a table. To limit the fields returned, use the fields parameter:

http://localhost/api/v2/_table/employees?fields=emp_no%2Clast_name

The equivalent SQL query looks like this:

SELECT emp_no, last_name FROM employees;

# Filtering Records by Condition

You can filter records by a particular condition using the filter parameter. For instance to return only those records having a gender equal to M, set the filter parameter like so:

http://localhost/api/v2/_table/employees?filter=(gender=M)

The equivalent SQL query looks like this:

SELECT * FROM employees where gender='M';

You're free to use any of the typical comparison operators, such as LIKE:

http://localhost/api/v2/_table/employees?filter=(last_name%20like%20G%25)

The equivalent SQL query looks like this:

SELECT * FROM employees where last_name LIKE 'G%';

# Combining Parameters

The REST API's capabilities really begin to shine when combining multiple parameters together. For example, let's query the employees table to retrieve only those records having a last_name beginning with G, ordering the results by emp_no:

http://localhost/api/v2/_table/employees?filter=(last_name%20like%20G%25)&order=emp_no

The equivalent SQL query looks like this:

SELECT * FROM employees where last_name LIKE 'G%' ORDER BY emp_no;

# Querying by Primary Key

You'll often want to select a specific record using a column that uniquely defines it. Often (but not always) this unique value is the primary key. You can retrieve a record using its primary key by appending the value to the URL like so:

/api/v2/_table/supplies/45

The equivalent SQL query looks like this:

SELECT * FROM supplies where id = 5;

If you'd like to use this URL format to search for another unique value not defined as a primary key, you'll need to additionally pass along the id_field and id_type fields like so:

/api/v2/_table/employees/45abchdkd?id_field=guid&id_type=string

# Joining Tables

One of DreamFactory's most interesting database-related features is the automatic support for table joins. When DreamFactory creates a database-backed API, it parses all of the database tables, learning everything it can about the tables, including the column names, attributes, and relationships. The relationships are assigned aliases, and presented for referential purposes within DreamFactory's Schema tab. For instance, the following screenshot contains the list of relationship aliases associated with the employees table:

Using these aliases along with the related parameter we can easily return sets of joined records via the API. For instance, the following URI would be used to join the employees and departments tables together:

/api/v2/mysql/_table/employees?related=dept_emp_by_emp_no

The equivalent SQL query looks like this:

SELECT * FROM employees
  LEFT JOIN departments on employees.emp_no = departments.emp_no;

The joined results will be presented within a JSON array having a name matching that of the alias:

{
    "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"
        }
    ]
}

# Inserting Records

To insert a record, you'll send a POST request to the API, passing along a JSON-formatted payload. For instance, to add a new record to the supplies table, we'd send a POST request to the following URI:

/api/v2/mysql/_table/supplies

The body payload would look like this:

{
    "resource": [
        {
            "name": "Stapler"
        }
    ]
}

If the request is successful, DreamFactory will return a 200 status code and a response containing the record's primary key:

{
    "resource": [
      {
        "id": 9
      }
    ]
}

# Adding Records to Multiple Tables

It's often the case that you'll want to create a new record and associate it with another table. Believe it or not this is possible via a single HTTP request. Consider the following two tables. The first, supplies, manages a list of company supplies (staplers, brooms, etc). The company requires that all supply whereabouts be closely tracked in the corporate database, and so another table, locations, was created for this purpose. Each record in the locations table includes a location name and foreign key reference to a record found in the supplies table.

::: note We know in the real world the location names would be managed in a separate table and then a join table would relate locations and supplies together; just trying to keep things simple for the purposes of demonstration. :::

The table schemas look like this:

CREATE TABLE `supplies` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

CREATE TABLE `locations` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `supply_id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `supply_id` (`supply_id`),
  CONSTRAINT `locations_ibfk_1` FOREIGN KEY (`supply_id`) REFERENCES `supplies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Remember from the last example that DreamFactory will create convenient join aliases which can be used in conjunction with the related parameter. In this case, that alias would be locations_by_supply_id. To create the relationship alongside the new supplies record, we'll use that alias to nest the location name within the payload, as demonstrated here:

{
    "resource": [
        {
            "name": "Broom",
            "locations_by_supply_id": [
                {
                    "name": "Broom Closet"
                }
            ]
        }
    ]
}

With the payload sorted out, all that remains is to make a request to the supplies table endpoint:

/api/v2/mysql/_table/supplies

If the nested insert is successful, you'll receive a 200 status code in return along with the primary key ID of the newly inserted supplies record:

{
    "resource": [
        {
            "id": 15
        }
    ]
}

# Updating Records

Updating database records is a straightforward matter in DreamFactory. However to do so you'll first need to determine which type of REST update you'd like to perform. Two are supported:

  • PUT: The PUT request replaces an existing resource in its entirety. This means you need to pass along all of the resource attributes regardless of whether the attribute value is actually being modified.
  • PATCH: The PATCH request updates only part of the existing resource, meaning you only need to supply the resource primary key and the attributes you'd like to update. This is typically a much more convenient update approach than PUT, although to be sure both have their advantages.

Let's work through update examples involving each method.

# Updating Records with PUT

When updating records with PUT you'll need to send along all of the record attributes within the request payload:

{
    "resource": [
        {
            "emp_no": 500015,
            "birth_date": "1900-12-15",
            "first_name": "Johnny",
            "last_name": "Football",
            "gender": "m",
            "hire_date": "2007-01-01"
        }
    ]
}

With the payload in place, you'll send a PUT request to the employees table endpoint:

/api/v2/mysql/_table/employees

If successful, DreamFactory will return a 200 status code and a response body containing the primary key of the updated record:

{
    "resource": [
        {
            "emp_no": 500015
        }
    ]
}

The equivalent SQL query looks like this:

UPDATE supplies SET first_name = 'Johnny', last_name = 'Football', birthdate = '1900-12-15', gender = 'm', hire_date = '2007-01-01' WHERE emp_no = 500015;

# Updating Records with PATCH

To update one or more (but not all) attributes associated with a particular record found in the supplies table, you'll send a PATCH request to the supplies table endpoint, accompanied by the primary key:

/api/v2/mysql/_table/supplies/8

Suppose the supplies table includes attributes such as name, description, and purchase_date, but we only want to modify the name value. The JSON request body would look like this:

{
  "name": "Silver Stapler"
}

If successful, DreamFactory will return a 200 status code and a response body containing the primary key of the updated record:

{
  "id": 8
}

The equivalent SQL query looks like this:

UPDATE supplies SET name = 'Silver Stapler' WHERE id = 8;

# Deleting Records

To delete a record, you'll send a DELETE request to the table endpoint associated with the record you'd like to delete. For instance, to delete a record from the employees table you'll reference this URL:

/api/v2/mysql/_table/employees/500016

If deletion is successful, DreamFactory will return a 200 status code with a response body containing the deleted record's primary key:

{
    "resource": [
        {
            "emp_no": 500016
        }
    ]
}

The equivalent SQL query looks like this:

DELETE FROM employees WHERE emp_no = 500016;

# Synchronizing Records Between Two Databases

You can easily synchronize records between two databases by adding a pre_process event script to the database API endpoint for which the originating data is found. To do so, navigate to the Scripts tab, select the desired database API, and then drill down to the desired endpoint. For instance, if we wanted to retrieve a record from a table named employees found within database API named mysql and send it to another database API (MySQL, SQL Server, etc.) named contacts and possessing a table named names, we would drill down to the following endpoint within the Scripts interface:

mysql > mysql._table.{table_name} > mysql._table.{table_name}.get.post_process  mysql._table.employees.get.post_process

Once there, you'll choose the desired scripting language. We've chosen PHP for this example, but you can learn more about other available scripting engines within our wiki documentation. Enable the Active checkbox, and add the following script to the glorified code editor:

// Assign the $platform['api'] array value to a convenient variable
$api = $platform['api'];

// Declare a few arrays for later use
$options = [];
$record = [];

// Retrieve the response body. This contains the returned records.
$responseBody = $event['response']['content'];

// Peel off just the first (and possibly only) record
$employee = $responseBody["resource"][0];

// Peel the employee record's first_name and last_name values,
// and assign them to two array keys named first and last, respectively.
$record["resource"] = [
    [
        'first' => $employee["first_name"],
        'last' => $employee["last_name"],
    ]
];

// Identify the location to which $record will be POSTed
// and execute an API POST call.
$url = "contacts/_table/names";
$post = $api->post;
$result = $post($url, $record, $options);

Save the changes, making sure the script's Active checkbox is enabled. Then make a call to the employees table which will result in the return of a single record, such as:

/api/v2/mysql/_table/employees?filter=emp_no=10001

Of course, there's nothing stopping you from modifying the script logic to iterate over an array of returned records.