Records

From DreamFactory
Jump to: navigation, search

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.

    1. <a name="layout"></a>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](Database/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",
      ...
    },
    ...
  ]
}


    1. <a name="common"></a>Common Parameters & Usage

In addition to the [Common Headers and Parameters](/DreamFactory/API#common), 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 ],
  ...
}
      1. <a name="fields"></a>Selecting Returned Fields
  • `fields` - 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.

      1. <a name="metadata"></a>Selecting Returned Metadata
  • `include_count` - 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` - Set to true to include any viewable table [schema](Schema) in the returned meta information.
{
	"resource": [
		{
			"id": 352,
			...
		},
		...
	],
	"meta": {
		"count": 8,
		"next": 1,
		"schema": {
			"name": "table_name",
			...
		}
	}
}
      1. <a name="identifiers"></a>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](#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` - 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` - 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` - 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.


      1. <a name="filters"></a>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 broken it down into optional parameters for our REST API.

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

  • `filter` - URL-encoded filter string. If this parameter is empty or missing all records will be returned, subject to the 'limit' and 'offset' parameters, or the maximum allowed by the system settings.

Filter String Examples: 

 * 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


  • `params` - An array of name-value pairs used as filter replacement parameters. To use with GET requests, use [tunneling](Common-Headers-Parameters#tunneling) via POST.
  • `limit` - Max number of records to return. If this parameter is empty or missing all matching records will be returned, subject to the 'offset' parameter.
  • `offset` - Index of first record to return, e.g., to get records 91-100 set offset to 90 and limit to 10. (Note that `order` must be set for `offset` to work properly with Microsoft SQL Server.)
  • `order` - Field to order results by. Also supports sort direction ASC or DESC such as 'Name ASC'. Default direction is ASC.
  • `group` - Field to group results by. Also supports sort direction ASC or DESC such as 'Name ASC'. Default direction is ASC.


      1. <a name="batching"></a>Batching Records
  • `continue` - In batch scenarios, where supported, continue processing even after one record fails. Default behavior is to halt and return results up to the first point of failure.
  • `rollback` - In batch scenarios, where supported, rollback all changes if any record of the batch fails. Default behavior is to halt and return results up to the first point of failure, leaving any changes.
    1. <a name="list"></a>List of Tables

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

URI: **GET** `http://<server_name>/api/v2/<service_name>/_table`

Live API : [getTablesList](https://df-wiki.enterprise.dreamfactory.com/swagger/index.html#!/db/getTablesList)

    1. <a name="operations"></a>Record Operations

The following operations are typically available for all DreamFactory Database Services. See the following sections for more detail on CRUD operations for database table records...

      1. <a name="retrieving"></a>Retrieving Records

The examples given below use a single simple identifier field named "id" which is, in this case, an auto-incrementing primary key. 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.

The following operations are typically available for all DreamFactory Database Services. The examples given below use a single simple identifier field named "id" which is, in this case, an auto-incrementing primary key. 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.

        1. <a name="get-filter"></a>By Filter

Description: Filter records for a db table using a SQL-like WHERE clause (or native filter or certain NoSQL types). Server-side replacement lookups are 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}'


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

Live API : [getTablesList](https://df-wiki.enterprise.dreamfactory.com/swagger/index.html#!/db/getRecordsByFilter)

Examples

    • Scenario A:** You have a database with a table called Clients with fields ID, Firstname, Surname and want to find all your clients who are called "Smith".

URI: **GET** `http[s]://<host_name>/api/v2/<service_name>/Clients?filter=surname%3D'Smith'`

    • Scenario B:** You have a database with a table called Clients with fields ID, Firstname, Surname and want to find all you clients with names beginning with Sm

URI: **GET** `http[s]://<host_name>/api/v2/<service_name>/Clients?filter=surname%20LIKE%20'Sm%25'`

Scenario B is equivalent to SQL: SELECT * FROM Clients WHERE Surname LIKE 'Sm%' So DreamFactory supports mySQL wildcards. In this case % matches an arbitrary number of characters (including zero characters) See: http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html

        1. <a name="get-ids"></a>By a List of Identifiers

Description: Retrieve one or more records for a db table by id.

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

Live API : [getTablesList](https://df-wiki.enterprise.dreamfactory.com/swagger/index.html#!/db/getRecordsByIds)

        1. <a name="get-id"></a>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_name>/<id>`

Live API : [getTablesList](https://df-wiki.enterprise.dreamfactory.com/swagger/index.html#!/db/getRecord)

        1. <a name="get-records"></a>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_name>`

Live API : N/A

Request 

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

Response 

{
  "resource": [
    {
      "id": 1,
      "name": "Check out DF REST API",
      "complete": true
    },
    {
      "id": 2,
      "name": "Create a cool app of my own",
      "complete": false
    }
  ]
}
        1. <a name="get-post-filter"></a>By Posting a Filter with Replacement Parameters

Description: Just like using <a name="get-filter">"By Filter"</a> above but this allows placing complex or non-string filters in the payload along with any replacement parameters, including lookups, required for the filter. Note that the filter can still be passed as part of the URL as before. Also note that filter strings passed in the payload MUST NOT be encoded. Also note the quotes around lookup name usage.

  • filter => filter=complete%20%3D%20%3Acomplete (decoded: complete=:my_complete)
  • params without lookups => { "params" : { ":my_complete" : false } }
  • params with lookups => { "params" : { ":my_complete" : "{lookup_name}" } }

URI: **POST** `http[s]://<host_name>/api/v2/<service_name>/<table_name>`

Live API : N/A

Request 

{
  "filter": "complete=:my_complete",
  "params":
    {
      ":my_complete": "{my_lookup_name}"
    }
  ]
}

Response 

{
  "resource": [
    {
      "id": 2,
      "name": "Create a cool app of my own",
      "complete": false
    }
  ]
}
        1. <a name="get-records"></a>By Posting Ids

Description: Just like using <a name="get-ids">"By Ids"</a> 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_name>`

Live API : N/A


Request 

{
  "ids": "1,2"
}

or... 

{
  "ids": [1,2]
}

Response 

{
  "resource": [
    {
      "id": 1,
      "name": "Check out DF REST API",
      "complete": true
    },
    {
      "id": 2,
      "name": "Create a cool app of my own",
      "complete": false
    }
  ]
}
      1. Creating Records

The following operations are typically available for creating records on all DreamFactory Database Services. 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.

        1. <a name="post-records"></a>Multiple Records

Description: Create one or more new records for a db table. Server-side lookups may be used as field values, and will be replaced on the server with the correct lookup value.

  • without lookups => { "name": "my new task", "complete": false }
  • with lookups => { "name": "my new task", "complete": "{lookup_name}" }


URI: **POST** `http[s]://<host_name>/api/v2/<service_name>/<table_name>`

Live API : [getTablesList](https://df-wiki.enterprise.dreamfactory.com/swagger/index.html#!/db/createRecords) Request 

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


Response 

{
  "resource": [
    {
      "id": "3"
    }
  ]
}
        1. <a name="post-record"></a>Single Record

Description: Create a new record for a db table, without using the batching notation ('record' wrapper). Notice that if no wrapper is used in the request, only a single record is allowed, and only a single record response will be returned without a wrapper.

URI: **POST** `http[s]://<host_name>/api/v2/<service_name>/<table_name>`

Request: 

{
  "name": "Test my cool app",
  "complete": false
}


Response: 

{
  "id": "1"
}
      1. <a name="replacing"></a>Updating/Replacing Records

The following operations are typically available for all DreamFactory Database Services. The examples given below use a single simple identifier field named "id" which is in this case an auto-incrementing primary key, 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.

        1. <a name="put-id"></a>Single record


        1. Single Record By Identifier


          1. Multiple Records


        1. <a name="put-ids"></a>Multiple Records By Identifier List
        1. <a name="put-filter"></a>By Filter
      1. Patching/Merging Records

The following operations are typically available for all DreamFactory Database Services. The examples given below use a single simple identifier field named "id" which is in this case an auto-incrementing primary key, 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.

        1. <a name="patch-id"></a>A single record


        1. Single Record By Identifier


        1. Multiple Records


        1. <a name="patch-ids"></a>Multiple Records By Identifier List
        1. <a name="patch-filter"></a>By Filter
      1. Deleting Records

The following operations are typically available for all DreamFactory Database Services. The examples given below use a single simple identifier field named "id" which is in this case an auto-incrementing primary key, 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.

        1. <a name="delete-records"></a>Multiple Records

Description: Delete one or more existing records for a db or system table.

Request HTTP Method: POST (requires additional header, see Request Headers below)

Headers: No additional headers required, See [Common Headers and Parameters](Common-Headers-Parameters).

URI: `http[s]://<host_name>/api/v2/<service_name>/<table_name>/<Id>


        1. <a name="delete-ids"></a>Multiple Records By Identifier List

Description: Delete one or more existing records for a db or system table.

Request HTTP Method: POST (requires additional header, see Request Headers below)

Headers: No additional headers required, See [Common Headers and Parameters](Common-Headers-Parameters).

URI: `http[s]://<host_name>/api/v2/<service_name>/<table_name>/<Id>


        1. <a name="delete-filter"></a>By Filter

Description: Delete one or more existing records for a db or system table.

Request HTTP Method: POST (requires additional header, see Request Headers below)

Headers: No additional headers required, See [Common Headers and Parameters](Common-Headers-Parameters).

URI: `http[s]://<host_name>/api/v2/<service_name>/<table_name>/<Id>`

        1. <a name="delete-id"></a>Single Record By Identifier

Description: Delete one or more existing records for a db table.

URI: **DELETE** `http[s]://<host_name>/api/v2/<service_name>/<table_name>/<Id>`

Go [here](https://df-wiki.enterprise.dreamfactory.com/swagger/#!/db/getRecords_get_2) to see this in action in our [Live API](Admin-Console-api-sdk).

Retrieved from "https://wiki.dreamfactory.com/index.php?title=DreamFactory/Features/Database/Records&oldid=31251"