Records

For database clients, the primary database operations involve managing database table records. The database table records resource, signified by _table below, allows for CRUD operations of various permutations on table records.

Layout

All calls to this resource take the form of...

http[s]://<host_name>/api/v2/<service_name>/_table/[<table_name>/[<id>]?[<param-name>=<param-value>]...]

table_name - The table name or alias (see Schema Extensions) of the table that we want to access on the database service.
id - Optional. The value of a primary key field (or other fields in some circumstances). When present, the client is acting on a single record identified by a single value. Posted and returned data is always a single unwrapped record. Otherwise, all posted and returned data must be an array of records, and may be optionally wrapped with a resource element, see system configuration.

With id:

{
    "<field_name>": "value",
    ...
}

Without id:

{
    "resource": [
        {
            "<field_name>": "value",
            ...
        },
        ...
    ]
}

Common Parameters and Usage

In addition to the Common Headers and Parameters, many of the database service record API operations support the following parameters. To make things more flexible, most parameters can be passed as URI parameters in the form of name=value or included in the posted data itself. If passed as a URI parameter, the values must be encoded accordingly. If it is included in the posted data, and the parameter supports a list (i.e. comma-separated values) as a value, like the ids parameter, then it could also even be sent as an array of items.

For example, using the ids parameter as a URI parameter would look like...

http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>/?ids=1,2,3

and is equivalent to the following posted JSON data...

{
    "ids": "1,2,3",
    ...
}

or this...

{
    "ids": [1, 2, 3],
    ...
}

Selecting Returned Fields

fields - String or Array. The list of fields to return in response. If this parameter is ‘*’, or missing on a retrieve (GET) request, all field values will be returned. If it is missing or set to empty (‘’), just the primary key field(s) values will be returned.

To save the client additional round trip calls, the fields parameter can be used on all record operations to return the latest field values. When used on POST, PUT, or PATCH requests, the field values are fetched after the operation has been completed. When used on a DELETE request, it returns the values from the record before the deletion.

Note: In some situations, when used with verbs other than GET, additional calls may be incurred to the database to retrieve this information and thus may add additional processing time to a request.

Selecting Returned Metadata

include_count - Boolean. Set to true to include the total record count for the filter given. Not available by all database vendors. May incur additional calls to database to retrieve data. Returns the count value and optionally the next value to use if more data can be retrieved, i.e. a limit was applied or the max returnable records was hit, see system configuration.
include_schema - Boolean. Set to true to include as schema any viewable table schema in the returned meta information. Note: This schema listing will return aliases for table, field and relationship names where configured.

{
    "resource": [
        {
            "id": 352,
            ...
        },
        ...
    ],
    "meta": {
        "count": 8,
        "next": 1,
        "schema": {
            "name": "table_name",
            ...
        }
    }
}

Identifying Records

Working with a single record where a single identifier is available is most easily done using the id path option mentioned above. This can be used for all verbs except POST, see creating records below.

When attempting to identify multiple records for retrieval or update or delete, the ids parameter may be used instead of a filter. Using this parameter can be considered the equivalent of the SQL IN filter option. It takes precedence over the filter parameter mentioned below.

ids - String or Array. The list of identifier values to operate on, matching the default primary key or the identifier defined by the id_field parameter.

When using either the id path option or the ids parameter, or when posting record sets in a request, you may also provide one or both of the following two parameters when the primary keys are not well defined, or when using secondary indexes is desired.

id_field - String. A single or list of field(s) used as identifiers for the table, used to override defaults or provide identifiers when none are provisioned. In some scenarios, this parameter may be used to retrieve records using secondary indexes.
id_type - String. Requires id_field. A single or list of field type(s) for the identifiers of the table, used to override defaults, i.e. using integers for MongoDB.

Filtering Records

When you can not, or do no not desire to, identify records directly, you need to be able to filter the table for the correct record sets. Being able to filter records from a database table is one of the most powerful things you can do, and also one of the most complex. We have taken the typical SQL filtering and have broken it down into optional parameters for our REST API. Not all filtering options that are available in SQL clauses are available in the API, like JOIN, for security reasons.

Note: All of the following parameters are only useful if the id path option or the ids parameter are not provided.

filter - String. This is a URL encoded filter string equivalent to the WHERE clause of a SQL statement. If this parameter is empty or missing all records will be returned, bounded by the 'limit' and 'offset' or group parameters if supplied, or the maximum allowed by the system settings. See Filter Strings section below for more information.
params - An object of name-value pairs used as filter replacement parameters. To use with GET requests, use tunneling via POST.
limit - Integer. Max number of records to return. If this parameter is empty or missing all matching records will be returned, subject to the 'offset' parameter or the maximum allowed by the system settings.
offset - Integer. Index of first record to return, e.g., to get records 91-100 set offset to 90 and limit to 10.
order - String. Field to order results by. Also supports sort direction ASC or DESC such as 'Name ASC'. Default direction is ASC.
group - String. Field to group results by. Also supports sort direction ASC or DESC such as 'Name ASC'. Default direction is ASC.

Filter Strings

While some of the string patterns and operators may be different across various database vendors, our API tries to standardize them to at least support ANSI SQL and the most common syntax.

Supported Logical Operators - Logical operators are used to combine or negate (i.e. NOT) multiple comparison clauses. Note: All clauses must be wrapped in parenthesis, i.e. (a=b) AND ((c=d) OR (e=f)), or NOT((a=b) OR (c=d)).

AND - Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE; otherwise returns UNKNOWN.
OR - Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise, returns UNKNOWN.
NOT - Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.

Supported Comparison Operators - In most cases, using character-based operators, i.e. 'EQ', require surrounding spaces, but symbol-based operators, i.e. '!=', surrounding spaces are optional.

'EQ' or '=' - Equality test.
'NE' or '!=' or '<>' - Inequality test.
'GT' or '>' - Greater than test.
'GTE' or '>=' - Greater than or equal to test.
'LT' or '<' - Less than test.
'LTE' or '<=' - Less than or equal to test.
'IN' - Checks for equality against any member in set provided, if found returns TRUE. Requires a comma-delimited set surrounded by parenthesis, i.e. a IN (1,2,3).
'NIN' or 'NOT IN' - Checks for equality against any member in set provided, if found return FALSE, otherwise TRUE. Requires a comma-delimited set surrounded by parenthesis, i.e. a NOT IN (1,2,3). 'NIN' is only supported on MongoDB currently.
'LIKE' - Loose equality test using wildcard '%' at the beginning, end or both. Most database vendors support 'ab%' for "starts with string 'ab'", '%ed' for "ends with string 'ed'", and '%mid%' for "contains string 'mid'".
'CONTAINS' - (>=2.1.1) Internally converted to equivalent of LIKE '%mid%' for "contains string 'mid'".
'STARTS WITH' - (>=2.1.1) Internally converted to equivalent of LIKE 'ab%' for "starts with string 'ab'".
'ENDS WITH' - (>=2.1.1) Internally converted to equivalent of LIKE %ed' for "ends with string 'ed'".

Example Filters

(first_name='John') AND (last_name='Smith')
(first_name='John') OR (first_name='Jane')
first_name!='John'
first_name like 'J%'
email like '%@mycompany.com'
(Age >= 30) AND (Age < 40)

Using Replacement Parameters

Internally, most filters received from the API are parsed and turned into parameterized queries using replacement parameters where possible to protect against SQL injection. To make things even simpler for the API clients, we also allow for parameter replacement in the API itself. Just replace your filter comparison values with a unique string starting with ":" and pass the real value in the params area of the posted payload. Note: Since we are using a POST here, the filter parameter can also be passed as part of the payload, but must not be URL encoded in this case.

filter as parameter

filter=complete%20%3D%20%3Acomplete (decoded: complete=:my_complete)

payload including params

{ "params" : { ":my_complete" : false } }

payload including filter and params

{
    "filter": "complete=:my_complete",
    "params": {
        ":my_complete": false
    }
}

Using Lookups

Server-side lookups may be used as field values, and will be replaced on the server with the correct lookup value.

{
    "resource": [
        {
            "name": "Test my cool app",
            "complete": "{lookup_name}"
        },
        ...
    ]
}

Server-side replacement lookups are also allowed in the filter string.

without lookups

filter=name%20%3D%20%27value%27 (decoded: name = 'value')

with lookups

filter=name%20%3D%20%27%7Blookup_name%7D%27 (decoded: name = '{lookup_name})'

Or in filter replacement parameters, where filter is something like "complete = :complete".

{
    "params": [
        {
            ":complete": "{lookup_name}"
        }
    ]
}

Batching Records

When handling batches of records in a single call, whether creating, updating, deleting, or even retrieving, the default behavior is to halt and return results up to the first point of failure, leaving changes as they occur. To change this behavior we offer the following options as URL parameters.

continue - Boolean. In batch scenarios, where supported, continue processing even after one record fails, completing as many as possible and returning the results of success or failure.
rollback - Boolean. In batch scenarios, where supported, rollback all changes if any operation of the batch fails. Internally, this uses database transactions where available.

Date & Time Formatting

Database vendors accept and return date and time values in different formats in some cases. This makes it difficult to get consistent behavior on the client side when dealing with multiple vendors. Here are some of the default formats coming from some of the popular databases...

MySQL

{
    "lunch_time": "09:45:00",
    "birth_date": "2003-01-16",
    "last_contact": "2014-03-14 13:34:00",
    "last_modified_date": "2014-12-11 17:20:34"
}

MS SQL Server

{
    "lunch_time": "09:45:00.0000000",
    "birth_date": "2003-01-16",
    "last_contact": "2014-03-14 13:34:00.0000000",
    "last_modified_date": "2014-12-11T14:11:27.3012644Z"
}

While some of these differences may be configurable on the database itself, or can be modified during database transactions using vendor-specific conversion or casting alterations for each affected field in the SQL calls themselves, this is not simple, nor ideal when you may need different behavior for different clients. DreamFactory adds the ability to control the date and time formats for all connected database services through its "blending" API. This allows the clients of the API to utilize one consistent format for each data type across any and all database services accessed.

Configuration

The configuration settings for the date and time formats can be found in <install-directory>/config/df.php and include the following settings...

//-------------------------------------------------------------------------
//  Date and Time Format Options
//-------------------------------------------------------------------------
'dsp.db_time_format' => null,
'dsp.db_date_format' => null,
'dsp.db_datetime_format' => null,
'dsp.db_timestamp_format' => null,

The configuration options follow the conventions used in PHP for representing date and time formats. For options, see the following...

'format' section of http://php.net/manual/en/datetime.createfromformat.php
'format' section of http://php.net/manual/en/function.date.php.
'Predefined Constants' section of http://php.net/manual/en/class.datetime.php#datetime.constants.types

The following are example formats for each of the DreamFactory simple data types...

'dsp.db_date_format' => 'l jS \of F Y',
'dsp.db_time_format' => 'h:i A',
'dsp.db_datetime_format' => 'm/d/y h:i:s A',
'dsp.db_timestamp_format' => 'c',

and the same dataset from above reformatted using these settings.

{
    "lunch_time": "09:45 AM",
    "birth_date": "Thursday 16th of January 2003",
    "last_contact": "03/14/14 01:34:00 PM",
    "last_modified_date": "2014-12-10T14:42:18-05:00"
}

Note: When these values are changed, the platform cache must be reset before they will be used by the API, see the Admin panel "Tools" menu.

Client Usage

We have also included these settings in the system environment API response, making them available to all clients. This makes it simple for clients to ensure a common format is used when displaying and editing date and time field values, whether as a text field or via more complex date or time picker UI components.

URI:

GET http[s]://<server_name>/api/v2/system/environment

Request: None

Response:

{
    ...
    "db": {
        "date_format": "l jS \\of F Y",
        "time_format": "h:i A",
        "datetime_format": "m/d/y h:i:s A",
        "timestamp_format": "l, d-M-Y H:i:s T",
        "max_records_returned": 1000
    },
    ...
}

These format settings are used by database record retrieval and for stored procedure call response data when using the schema parameters for data formatting.

Note: Date and time field values must use the same format for sending data to the API as was received when retrieving the fields from the API. In other words, the client can not retrieve a date as 'yyyy/mm/dd' and then perform a record update using 'mm/dd/yyyy' format.

List Available Tables

Description: Return all accessible table names or aliases based on role access.

URI:

GET http://<server_name>/api/v2/<service_name>/_table[?refresh=[true|false]]

Live API : getDbTables

Record Operations

The following operations are typically available for all DreamFactory Database Services. The examples given below, where noted, use a single simple identifier field named "id" which is, in this case, an auto-incrementing primary key or a primary key created by the system. This may not be the case for all tables or all database service types. Refer to the specifics of your database type documented in other pages in this section.

See the following sections for more detail on CRUD operations for database table records...

Retrieving Records

By Filter

Description: Filter records for a db table using a SQL-like WHERE clause (or native filter or certain NoSQL types).

URI:

GET http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?filter=<filter_string>

Live API : getDbRecords

By Filter with Replacement Parameters

Description: Filter records for a db table using a SQL-like WHERE clause (or native filter or certain NoSQL types) utilizing replacement parameters. Must use POST verb with a payload including params object, and passing either 'method' query parameter or header set to GET, see tunneling.

URI:

POST http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?filter=<filter_string>?method=GET

Live API : N/A

Payload:

{
    "params": {
        ":my_complete": "{my_lookup_name}"
    }
}

By a List of Identifiers

Description: Retrieve one or more records for a db table by a single identifying field.

URI:

GET http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?ids=<id_list>

Live API : getDbRecords

By a Single Identifier

Description: Retrieve one record for a db table by id.

URI:

GET http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>/<id>

Live API : getDbRecord

By Posting Partial Records

Description: Refresh a client-side copy of old or partial records from a db table. Requires that at a minimum the old or partial records contain the identifying fields for the table.

URI:

POST http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?method=GET

Live API : N/A

Payload:

{
    "resource": [
        {
            "id": 1
        },
        {
            "id": 2
        }
    ]
}

By Posting Ids

Description: Just like using "By Ids" above, but this allows placing larger or more complex list or arrays of identifiers in the payload

URI:

POST http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?method=GET

Live API : N/A

Payload:

{
    "ids": "1,2"
}

or...

{
    "ids": [1,2]
}

Creating Records

These operations may vary significantly in handling of primary identifiers based on the database service type and sometimes based on the table definition itself. For instance, some tables may automatically create the identifier field(s) with no input from the client, like in the case of MongoDB or SQL databases with tables that have an auto-incrementing primary key. Others may require a single or multiple identifier fields to be sent with the rest of the record upon creation. Be sure to check the specific database service type for any differences documented in other pages in this section.

To create one or more new records for a db table, post the records desired to be created with all necessary fields for creation.

URI:

POST http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>

Payload:

{
    "resource": [
        {
            "name": "Test my cool app",
            "complete": "{lookup_name}"
        },
        ...
    ]
}

Live API : createDbRecords

Updating/Replacing Records

By Posted Records

Description: Replace one or more existing records identified by posted records for a database table.

URI:

PUT http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>

Live API : replaceDbRecords

By Identifier

Description: Replace one record from a database table by identifier.

URI:

PUT http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>/<Id>?[id_field=<id_field>]

Live API : replaceDbRecord

By Identifier List

Description: Replace one or more existing records identified by a list of identifying values for a database table.

URI:

PUT http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>/?ids=<comma-delimited ids>[&[id_field=<id_field>]]

Live API : replaceDbRecords

By Filter

Description: Replace one or more existing records by matching a filter for a database table.

URI:

PUT http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?filter=<filter>

Live API : replaceDbRecords

Patching/Merging Records

By Posted Records

Description: Patch one or more existing records identified by posted records for a database table.

URI:

PATCH http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>

Live API : updateDbRecords

By Identifier

Description: Patch one record from a database table by identifier.

URI:

PATCH http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>/<Id>?[id_field=<id_field>]

Live API : updateDbRecord

By Identifier List

Description: Patch one or more existing records identified by a list of identifying values for a database table.

URI:

PATCH http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>/?ids=<comma-delimited ids>[&[id_field=<id_field>]]

Live API : updateDbRecords

By Filter

Description: Patch one or more existing records by matching a filter for a database table.

URI:

PATCH http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?filter=<filter>

Live API : updateDbRecords

Deleting Records

By Identifier

Description: Delete one record from a database table by identifier.

URI:

DELETE http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>/<Id>?[id_field=<id_field>]

Live API : deleteDbRecord

By Identifier List

Description: Delete one or more existing records identified by a list of identifying values for a database table.

URI:

DELETE http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>/?ids=<comma-delimited ids>[&[id_field=<id_field>]]

Live API : deleteDbRecords

By Filter

Description: Delete one or more existing records by matching a filter for a database table.

URI:

DELETE http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?filter=<filter>

Live API : deleteDbRecords

By Posted Records

Description: Delete one or more existing records identified by posted records for a database table.

URI:

POST http[s]://<host_name>/api/v2/<service_name>/_table/<table_name>?method=DELETE

Live API: N/A

Records

Contents

Layout

Common Parameters and Usage

Selecting Returned Fields

Selecting Returned Metadata

Identifying Records

Filtering Records

Filter Strings

Using Replacement Parameters

Using Lookups

Batching Records

Date & Time Formatting

Configuration

Client Usage

List Available Tables

Record Operations

Retrieving Records

By Filter

By Filter with Replacement Parameters

By a List of Identifiers

By a Single Identifier

By Posting Partial Records

By Posting Ids

Creating Records

Updating/Replacing Records

By Posted Records

By Identifier

By Identifier List

By Filter

Patching/Merging Records

By Posted Records

By Identifier

By Identifier List

By Filter

Deleting Records

By Identifier

By Identifier List

By Filter

By Posted Records

Navigation menu

Search