Schema
The DreamFactory database schema resource provides a way of managing the database table layout, usable fields, their storage types and requirements. By "schema", we mean in its traditional SQL database sense, i.e. a set of properties that define the layout of tables and their fields including relationships between them, not the schema namespaces used by Microsoft and other database vendors. However, we have extended this meaning to also encompass the properties that define tables on NoSQL database, i.e. any table key configuration, etc., some of which are database-type dependent.
Contents
Common Parameters & Formatting
All database services have a root-level resource called _schema that allows API access to the schema knowable by the database connection provisioned. All calls to this resource take the form of...
http[s]://<host_name>/api/v2/<service_name>/_schema/[<table_name>/[<field_name>]]
Starting in release 2.4.0, this API has been expanded to include field level manipulation...
http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>/_field/[<field_name>]
and relationship level manipulation...
http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>/_related/[<relationship_name>]
where table_name, field_name and relationship_name are optional and control what level of resource the client is acting on. All posted and returned data, when not using the table_name, field_name or relationship_name path parameter must be an array of resource-defining property sets (tables, fields, or relationships), possibly wrapped with a resource element based on system configuration. The wrapper label itself can be optional or modified, see configuration options.
{ "resource": [ <resource_info>, <resource_info>, ... ] }
In addition to discovering existing schema and allowing provisioning of schema, DreamFactory adds what we call extensions to our blended REST API for databases. The REST API supports a hierarchy of resources to help manage the database schema, centered around the table and field levels. For more detail see the operations section below.
There are no additional parameters or headers required for Database Schema API.
Extensions
While there are plenty of "standard" elements that you can expect to be present in most databases, particularly SQL-based ones, there are also plenty of vendor-specific types, commands, formats, etc. that make it bothersome, and sometimes difficult for client application development. After dealing with that for several years, we decided to try to make things easier by providing the following extensions.
- a client-friendly JSON input and output layout
- consolidated and simplified data types
- table, field and relationship aliasing to hide database details
- server-side validations for helping clients control data sent to the database
- Virtual fields using database functions
- Virtual relationships between non-related datasets
- table, field and relationship labels for client usage
- field-level client settings for aiding clients in form building
If you know your favorite database flavored SQL, or NoSQL, like the back of your hand, we will not hinder you (though you might find some of this useful), but if you are like most application developers, the last thing you want to do is go find your database documentation, load up some workbench environment, and/or beg a friend or a perfect stranger for help just to define a table to store your application's data.
Layout Format
Below is a JSON schema layout we came up with (pooled from various other platforms and environments over the years) to help define your table configuration, including our extensions, with each element defined below. All elements may not be available for all database, table, or field types.
<table_info> =
{ "name": "<table_name>", "alias": "<table_alias>", "label": "<table_label>", "plural": "<table_plural_label>", "description": "<table_description>", "primary_key": "<primary_key_field_names>", "name_field": "<naming_field_name>", "native": [ <platform_native_info>, ... ], "field": [ <field_info>, ... ], "related": [ <relationship_info>, ... ], "options": "<SQL_fragments>", "access": <verb_mask>, "raw": "<raw_vendor_specific_table_definition>" }
- name: String. Required. The database vendor compatible table name used to designate a table via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed table names. If schema namespaces apply for tables, then the names must use the <schema_name>.<table_name> convention commonly used by those vendors.
- alias: String. Optional. The user defined table name alias to be used to designate a table via the table records (_table) resource. The alias will also be used in Role Service Access on the table records (_table/<alias>) assignments. The name value above is always used to designate a table via the _schema resource.
- label: String. Optional. See labels section.
- plural: String. Optional. See labels section.
- description: String. Optional. See labels section.
- native: Array. Optional. Any platform-specific information discovered from the table. Information available varies by platform.
- field: Array. Required for SQL DB service types. A wrapper for an array of field definitions. See below for definition specifics. Each field consists of properties defining that field and its usage. A minimum of one field is required to create a table.
- related: Array. Optional. A wrapper for an array of relationship definitions. See below for definition specifics. Currently only supported on SQL DB service types. Each relationship consists of properties defining that relationship and its usage. Relationships can be detected automatically by the application or provisioned by the user, see virtual relationships.
- options: String. Optional. Request Only. These are database-specific create or alter fragments for things not generally supported otherwise when defining the table or fields, such as defining complicated multi-field keys, or the MySQL engine version, i.e. "ENGINE=InnoDB".
- access: Integer. Read-Only. If the include_access URL parameter is set to true, then this returns the HTTP verb mask of allowed access.
- raw: String. Optional. Request-Only. Allows a pure SQL-compatible definition for a table. If this property is defined, no other properties besides the name, alias, label, and description are looked at. The definition must be supported directly by the underlying database as no translations are done, i.e. "(sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb)));".
<field_info>
{ "name": "<field_name>", "alias": "<field_alias>", "label": "<field_label>", "description": "<field_description>", "client_info": {<user_defined_client_information>}, "native": {<database_platform_specific_information>}, "type": "<simplified_type>", "db_type": "<database_specific_type>", "length": <max_length>, "precision": <decimal_precision>, "scale": <decimal_scale>, "default": <default_value_or_expression>, "required": [true|false], "allow_null": [true|false], "fixed_length": [true|false], "supports_multibyte": [true|false], "auto_increment": [true|false], "is_index": [true|false], "is_unique": [true|false], "is_primary_key": [true|false], "is_foreign_key": [true|false], "ref_table": "<foreign_key_referenced_table>", "ref_fields": "<foreign_key_referenced_fields>", "ref_on_update": "[RESTRICT|CASCADE|SET NULL|NO ACTION]", "ref_on_delete": "[RESTRICT|CASCADE|SET NULL|NO ACTION]", "validation": { "<validation_name>" : { <validation_info> }, ... }, "db_function": [ { "use": [SELECT|FILTER|INSERT|UPDATE], "function": <database_function_syntax>, "function_type": "database" } ], "picklist": [ <value>, ... ], "is_virtual": [true|false], "is_aggregate": [true|false], "raw": "<raw_vendor_specific_field_definition>" }
- name: String. Required. The database vendor compatible field name used to designate a field via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed field names.
- alias: String. Optional. The user defined field name alias to be used to designate a field via the table records (_table) resource, i.e. in the fields URL parameter option or posted or retrieved records. The name value above is always used to designate a field via the _schema resource.
- label: String. Optional. See labels section.
- plural: String. Optional. See labels section.
- description: String. Optional. See labels section.
- client_info: JSON String. Optional. User-defined JSON object allows client to define persisted settings for building forms, etc.
- native: JSON String. Optional. Database platform-defined JSON object containing platform specific settings discovered about the field, retrieve and sometimes update settings possible.
- type: String. Required if raw property is not defined. In server responses, the value for this element is closest derived DreamFactory simplified type, if none apply, then it will be set to "string". In requests to the server, the value can be one of the simplified types (i.e. "string”, see the types section for more info), or other types supported directly by the underlying database. If using the latter (db-specific types), make sure you include any additional parameters in the type string, such as length, i.e. "nvarchar(128)", as the length, precision, scale, fixed_length and supports_multibyte parameters are not considered in this case.
- db_type: String. Read-Only. In server responses, the value for this element is the type as defined directly by the underlying database.
- fixed_length: Boolean. Optional for string type. Set to true to represent string types as fixed length (i.e. ‘char’), false for variable length (i.e. ‘varchar’) which is the default.
- supports_multibyte: Boolean. Optional for string type. Set to true to represent string types that support multibyte characters, i.e. "national" in most database vernacular, (i.e. "utf8"); use false for single byte character support only (i.e. ‘ascii’) which is the default.
- length: Integer. Optional. Used to define the max length of strings and number fields. For strings, if length is not defined, the default is 255.
- precision: Integer. Optional. Used by the "decimal" and "money", and in some cases the "double" and "float", types. It represents the total length of the number allowed minus the ‘.’, and can be set either by the length property or the precision property, length takes precedence.
- scale: Integer. Optional. Used by the "decimal" and "money" types, where it defines the number of decimal places to the right of the decimal. Only applies if the length or precision property is given. If scale is not given, the default is 0, i.e. no decimal places.
- default: Mixed. Optional. Use to define the SQL DEFAULT parameter of a field, supported values are dependent on the "type" designation, but may include null, strings, numbers, and SQL expressions, i.e. "NOW()" for datetime, etc.
- required: Boolean. Read-Only. Defines whether or not a value is required for this field when creating a record, it is set to the equivalent of if required = !(allowNull || defaultValue || autoIncrement).
- allow_null: Boolean. Optional. Defines whether or not the NULL value is allowed to be set for the field, ie. false = "NOT NULL". If not defined, the default is allow NULL (true).
- auto_increment: Boolean. Optional. Set to true to allow auto-incrementing of an integer or primary key. If not defined, the default is false.
- is_primary_key: Boolean. Optional. Set to true to define non-trivial primary keys, use once per table definition. If not defined, the default is false, except for the "id” type, see below.
- is_foreign_key: Boolean. Optional. Set to true to define foreign keys that are not stored as integers or point to multiple primary keys. If not defined, the default is false, except for the "reference" type, see below.
- ref_table: String. Required when type is "reference" or when is_foreign_key is true. Defines the table name that a reference field points to.
- ref_fields: String. Required when type is "reference" or when is_foreign_key is true. Defines the field(s) of the referenced table defined by ref_table property that the field is referencing. If multiple fields are part of the key, separate the fields by a comma.
- ref_on_update: String. Optional when type is "reference" or when is_foreign_key is true. defines the operation to take when the referenced record is updated. May not be supported by all database types, see ref_on_delete.
- ref_on_delete: String. Optional when type is "reference" or when is_foreign_key is true. Defines the operation to take when the referenced record is deleted. Options are typically "RESTRICT","CASCADE", "SET NULL", "NO ACTION", but may be database dependent. Default is database dependent.
- is_unique: Boolean. Optional. Set to true to require that each row has a unique value for this field. This is typically implemented as a unique index on the table.
- is_index: Boolean. Optional. Set to true to set the field as a table index to speed up common searches that use this field. Typically this isn't supported for large fields, like "text".
- picklist: Array of strings or integers. Optional. Designates values allowed for the field defined, type of the field must match the values given, i.e. string for an array of string values. Use the "picklist" and "multi_picklist" validations to restrict changes to this field's value via the API to only these values, otherwise these serve as suggestions to the client.
- validation: Array of settings. Optional. See validations for more info.
- db_function: Object. Optional. See database functions for more info.
- is_virtual: Boolean. Optional. Set to true to designate a new field as virtual, see virtual fields. If not defined, the default is false.
- is_aggregate: Boolean. Optional. Applicable only is is_virtual is true. Set to true to designate that this virtual field uses an aggregate function. Aggregate fields are not included in GET requests by default. If not defined, the default is false.
- raw: String. Optional. Request-Only. Allows a pure SQL-compatible definition for a field. If this property is defined, no other properties besides the name, alias, label, description, and client_info are looked at. SQL types in the definition must be supported directly by the underlying database as no translations are done, i.e. "int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY".
<relationship_info>
{ "name": "<relationship_name>", "alias": "<relationship_alias>", "label": "<user_defined_label>", "description": "<user_defined_description>", "always_fetch": [true|false], "type": [belongs_to|has_many|many_many], "field": "<field_of_this_table>", "is_virtual": [true|false], "ref_service_id": <service_id_of_foreign_table>, "ref_table": "<foreign_table>", "ref_fields": "<field_of_foreign_table>", "ref_on_update": "[RESTRICT|CASCADE|SET NULL|NO ACTION]", "ref_on_delete": "[RESTRICT|CASCADE|SET NULL|NO ACTION]", "junction_service_id": <foreign_service_id>, "junction_table": "<foreign_table>", "junction_field": "<field_of_foreign_table>", "junction_ref_field": "<field_of_foreign_table>" }
- name: String. Required. The database-type compatible table or field name used to designate a table or field via the database connection. DreamFactory prefers alpha-numeric, lowercase singular nouns, using underscore for separating words, but should support most vendor-allowed formats. Check with your database vendor documentation for allowed table or field names. If schema namespaces are used for tables, then the names must use the <schema_name>.<table_name> convention commonly used by those vendors.
- alias: String. Optional. The user defined relation name alias to be used to designate a relation via the table records (_table) resource, i.e. in the related URL parameter option or posted or retrieved records. The name value above is always used to designate a relation via the _schema resource.
- label: String. Optional. See labels section.
- native: JSON String. Optional. Database platform-defined JSON object containing platform specific settings discovered about the relationship.
- description: String. Optional. See labels section.
- type: String. Required. The type of relationship represented.
- is_virtual: Boolean. Optional. Set to true if the relationship is due to arbitrary foreign keys, see virtual relationships.
- ref_service_id: Integer. This defines the service by its id value that a reference points to.
- ref_table: String. Defines the table name that a reference field points to.
- ref_fields: String. Defines the field(s) of the referenced table defined by ref_table property that the field is referencing. If multiple fields are part of the key, separate the fields by a comma.
- ref_on_update: String. Optional when type is "reference" or when is_foreign_key is true. defines the operation to take when the referenced record is updated. May not be supported by all database types, see ref_on_delete.
- ref_on_delete: String. Optional when type is "reference" or when is_foreign_key is true. Defines the operation to take when the referenced record is deleted. Options are typically "RESTRICT","CASCADE", "SET NULL", "NO ACTION", but may be database dependent. Default is database dependent.
- junction_service_id: Integer. When type is many_many, this defines the service where the junction table exists.
- junction_table: String. Defines the name of the junction table.
- junction_field: String. Defines the field of the junction table that refers to primary table.
- junction_ref_field: String. Defines the field of the junction table that refers to the referenced table defined by ref_table
Aliases
Whether it is for security reasons or because you just don't care for the names in the database, DreamFactory allows you to provision and use aliases for the table names, field names and relationship names. The aliases must be used when accessing any of the three elements via the Table (_table) resource. For example if I provision an alias for a table named 'todo' to 'task', when I access the table through the Schema (_schema) resource, the client must use /api/v2/_schema/todo, however to query records, the client must use /api/v2/_table/task. Likewise for any field names used in records or query parameters, like fields or filter.
Labels
Labels in singular and plural form, as well as, a description value are tracked in a system table and are relayed to the client along with the schema. They are not used in API calls. They can contain UTF-8 characters, including spaces, but no other whitespace characters for label and plural. These are useful in client applications so that form labels don't have to be hard coded, see our admin applications, like the Data Manager, for example. Labels are available for table,field and relationship names. Plural labels are only available for table names. Labels can be sent and saved on the server along with other properties when creating or updating table schema. When none are specified, labels are auto-generated based on commonly used techniques, i.e. -y becomes -ies, etc.
Simplified Types
The supported simple types are defined as follows.
- id or pk: defines a typical table identifier, translates to
"INT NOT NULL AUTO_INCREMENT PRIMARY KEY"
. This type requires no other properties to define the field. It presumes a type of int with allow_null set to false, the auto_increment and is_primary_key are set to true. It can only be used once in a table definition.
- reference: defines a typical foreign key, presumes the type of int and requires the ref_table and ref_fields properties to be defined as well. Optional defining properties are ref_on_delete, ref_on_update, allow_null and default. Similar to SQL fragment
"FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE”
.
- string: defines a string field (i.e. varchar or char), length defaults to 255, but can be set using the length property. Set the fixed_length property to true for fixed length (i.e. char) behavior. Set supports_multibyte for multi-byte, national (i.e. nvarchar) behavior. Other optional properties are allow_null, default, values, and validation.
- text: defines a large string field (i.e. MySQL's text or MSSQL's varchar[max]), defaults to the largest length string allowed by the underlying database. Optional properties are allow_null and default.
- binary: defines a binary string field (i.e. varbinary or binary), defaults to a length of 255, but can be set using the length property. Set the fixed_length property to true for fixed length (i.e. binary) behavior. Optional properties are allow_null and default.
- boolean: defines a boolean field, which may be represented by int of length 1, using 0 and 1 values, if a true and false boolean type is not supported by the underlying database. Optional properties are allow_null and default.
- integer: defines an integer field. Use length to set the displayable length of the integer, i.e. int(11) in MySQL. Optional properties are allow_null, default, values, and validation.
- float: defines a standard single-precision float field. Some databases (i.e. MySQL) allow length or precision settings. Optional properties are allow_null and default.
- double: defines a standard double-precision float field. Some databases (i.e. MySQL) allow length or precision settings. Optional properties are allow_null and default.
- decimal: defines a standard decimal field. Use scale to set the number of desired decimal places to the right of the decimal point. Use length or precision to set the total number of digit positions. Optional properties are allow_null and default.
- datetime: a datetime field. Optional properties are allow_null, default, values, and validation.
- date: a date field. Optional properties are allow_null, default, values, and validation.
- time: a time field. Optional properties are allow_null, default, values, and validation.
- timestamp: a date and time stamp with timezone awareness where applicable, i.e. MySQL’s timestamp not null default 0 or MSSQL’s datetimeoffset.
- timestamp_on_create: a timestamp as documented above that will be automatically set on record creation and not updated again unless set by the client via the API. See "api_read_only" validation for keeping this from being set by API.
- timestamp_on_update: a timestamp as documented above that will be automatically set on record creation and again on every update. See "api_read_only" validation for keeping this from being set by API.
- user_id: a reference to the current user, assuming a valid session has been established. On the native database, this is implemented as a reference field pointing to the system's user table, on other databases, it is implemented as an integer. Optional properties are allow_null and default.
- user_id_on_create: a user_id as documented above that will be automatically set on record creation and not updated again unless set by the client via the API. See "api_read_only" validation for keeping this from being set by API.
- user_id_on_update: a user_id as documented above that will be automatically set on record creation and again on every update. See "api_read_only" validation for keeping this from being set by API.
Validations
Some server-side validations can be defined for each field by setting this property to one or more of the following options in the given format below. Note that additional validation will result in additional processing which may slow response times. Validations are configured in the following format...
{ "validation": { "<validation_name>": { "on_fail" : "[ignore_field | <error_msg>]" <other_config>, ... }, ... } }
where on_fail is a configuration option that takes either the value ignore_field, dictating if the field should be ignored if it does not pass validation, or a specified error message to overwrite the generic exception thrown. If this configuration is missing, a generic exception will be thrown stating the validation has failed.
Other validation configuration options are documented below for each validation type.
Possible validations settings are as follows...
- picklist - Supported for string type only. It requires the field value to be set to one of the values listed in the values property. Any error is returned if the values property is empty or does not contain the value being set. Values are checked only on create and update record API calls; data integrity is kept for existing values even when the picklist value list is modified. Behaves similar to MySQL "enum" type.
- multi_picklist - similar to picklist but allows multiple values to be selected and stored. Behaves similar to MySQL "set" type.
- delimiter - Optional. Defaults to comma, ",". Parameter allows setting the delimiter character used to separate each value being set from what is being stored in the field value.
- min - Optional. Integer. Defaults to 1. Number of minimum required selections from the list.
- max - Optional. Integer. If set, it designates the maximum required selections from the list.
- api_read_only - sets this field as read only through the API. Use "default” property to set values, i.e. useful for creating timestamps, etc. Supported for all types.
- create_only - sets this field to only allow values to be set on record creation. Supported for all types.
- not_null - validates that the field value to be set is not null. Supported for all types,
- not_empty - validates that the field value to be set is not empty string. Supported for string and text types. Null is not checked.
- not_zero - validates that the field value to be set is not zero (0). Supported for integers, decimals and floats. Null is not checked.
- int - validates that the value to be set is between the min and max integer values designated. Supported for integers.
- range - Optional. Defines the min and max range allowed for the integer.
- min - Optional. If set, it designates the minimum value allowed.
- max - Optional. If set, it designates the maximum value allowed.
- formats - Optional. Selects other allowable formats for the integers, "hex" and "octal" currently available.
- float - validates that the value to be set is a valid float.
- decimal - Optional. Defaults to '.'. Designates the character used as the decimal separator, which is sometimes ','.
- boolean - validates that the value to be set is one of the generally accepted values for true or false, i.e. true = 1, "1", "true", "on" and "yes". false = 0, "0", "false", "off", "no", and "".
- email - validates that this field is an email, i.e. "[email protected]”. Supported for string type only. See FILTER_VALIDATE_EMAIL.
- url - validates that this field is a url, i.e. starts with "http(s)://”. Supported for string type only.
- sections - Optional. Array of Strings. Designates required sections of the URL, "path" and "query" currently available.
- match - for strings matching a regular expression designated by regex. Supported for string type only.
- regex - Required. String. Exception thrown if not defined or empty. Due to special characters in regular expressions, the regex value must be encoded as base64 before being provisioned.
Virtual Fields
A virtual field is a field defined by using the is_virtual field schema designation that doesn't actually exist in the database but is used in fields (i.e. SELECT clause) and filter (i.e. WHERE clause) URL parameters. Virtual fields MUST define the db_function parameter below. Virtual fields included in POST, PUT, or PATCH request are ignored.
Database Functions
A database function can be defined for a field, virtual or real, by setting this property to any function supported by the database, i.e aggregate functions like MAX() for SELECT statements. If the db_function is applied to an existing database field, the actual value of the field in the database is not returned, but the function's value is returned instead. For example, if you wanted a certain string field, say "title" to be returned upper-case, regardless of its actual database value, we could add a db_function with function set to UPPER(title). Everywhere "title" is used the value will be uppercased.
As of 2.4.2, you can now designate a database function to be used for each of the following specific API uses...
- SELECT - Used for GET methods and all internal use of fields values, i.e. behavior of the SQL SELECT clause.
- FILTER - Used for all filter uses where the field part of the filter is replaced with the designated function, i.e. behavior of the SQL WHERE clause.
- INSERT - Used for all insert clauses for the field, where the value is replaced with the function call, i.e. behavior of the SQL INSERT request.
- UPDATE - Used for all set clauses for the field, where the value is replaced with the function call, i.e. behavior of the SQL UPDATE request.
Database functions are configured in the following format...
{ "db_function": [ { "use": [ "SELECT" ], "function": "TO_BASE64(bdata)", "function_type": "database" }, { "use": [ "INSERT", "UPDATE" ], "function": "FROM_BASE64({value})", "function_type": "database" } ], }
where use is an array of the use cases where this function is to be used, and function is the full raw syntax of the supported function call. 'function_type currently only supports "database", meaning the database itself is the one executing the function.
Note: For SELECT and FILTER functions, use the field name, not alias, where the field is supposed to be used. For INSERT and UPDATE functions, use the lookup syntax "{value}" which will perform the function on the value of the field passed in from the payload.
Database functions are database vendor dependent, but some common functions are listed below...
- Aggregate Functions such as AVG, COUNT, MAX, MIN, SUM, etc. Set is_aggregate to true for these, so that API usage of fields=* doesn't attempt to return these fields.
- ex. Max(salary) - Returns the maximum value of an existing column in the table named "salary", numerically for numbers or alphabetically for strings.
- Scalar Functions such as UCASE, LCASE, MID, LEN
- ex. LEN(address) - Returns the length of an existing value of the column in the table.
- Vendor-specific Functions such as SQL Server's CONVERT() or <field_name>.ToString().
- User-defined Functions if supported by the database vendor.
Virtual Relationships
Relationships in the schema are determined by scanning the foreign key constraints in the database. These relationships are then exposed in the related portion of the table schema. A virtual relationship is one whose foreign key constraint isn't discovered in the database but is provisioned by the user. These virtual foreign keys are provisioned in the field schema and stored locally as part of the system tables and applied to the discovered schema and used by table record operations for data retrieval and provisioning.
A foreign key as a real database constraint:
{ "name": "account_id", "label": "Account Id", "type": "reference", "ref_table": "account", "ref_fields": "id" }
A virtual foreign key pointing to the same service, different table:
{ "name": "reports_to_id", "label": "Reports To Id", "type": "reference", "is_virtual_foreign_key": true, "ref_table": "contact", "ref_fields": "id" }
A virtual foreign key pointing to a different service with id '6':
{ "name": "mapped_email", "type": "string", "is_foreign_key": true, "is_virtual_foreign_key": true, "is_foreign_service": true, "ref_service_id": 6, "ref_table": "contact", "ref_fields": "id" }
Example Field Declarations
{ { "name": "id", "type": "id" }, { "name": "first_name", "type": "string", "is_index": true }, { "name": "last_name", "label": "Last Name", "type": "string", "length": 40, "default": "", "allow_null": false, "is_index": true }, { "name": "display_name", "label": "Display Name", "type": "string", "length": 255, "allow_null": false, "supports_multibyte": true, "is_unique": true, "validation": {"not_empty": {"on_fail": "Display name must not be empty."}} }, { "name": "description", "label": "What’s the deal...", "type": "text" }, { "name": "email", "label": "Email", "type": "string", "length": 320, "default": null, "allow_null": true, "validation": {"email": {"on_fail": "Please enter a valid email address."}} }, { "name": "phone", "label": "Phone", "type": "string", "length": 12, "default": "", "allow_null": false, "fixed_length": true, "validation": "match(nnn-nnn-nnnn)" }, { "name": "Rating", "label": "Rating", "type": "string", "length": 40, "values": [ "Unknown","Cold","Warm","Hot" ], "default": "Unknown", "allow_null": false, "validation": {"picklist": {"on_fail": "Please select one of the valid options."}} }, { "name": "website", "label": "Website", "type": "string", "validation": {"url": {"on_fail": "Invalid URL given."}} }, { "description": "A foreign key as a real database constraint.", "name": "account_id", "label": "Account Id", "type": "reference", "ref_table": "account", "ref_fields": "id" }, { "description": "A virtual foreign key pointing to the same service, different table.", "name": "reports_to_id", "label": "Reports To Id", "type": "reference", "is_virtual_foreign_key": true, "ref_table": "contact", "ref_fields": "id" }, { "description": "A virtual foreign key pointing to a different service.", "name": "mapped_email", "type": "string", "is_foreign_key": true, "is_virtual_foreign_key": true, "is_foreign_service": true, "ref_service_id": 6, "ref_table": "contact", "ref_fields": "id" }, { "name": "created_date", "label": "Created Date", "type": "timestamp_on_create", "validation": {"api_read_only": {"on_fail": "ignore_field"}} }, { "name": "last_modified_date", "label": "Last Modified Date", "type": "timestamp_on_update", "validation": {"api_read_only": {"on_fail": "ignore_field"}} }, { "name": "created_by_id", "label": "Created By Id", "type": "user_id_on_create", "validation": {"api_read_only": {"on_fail": "ignore_field"}} }, { "name": "last_modified_by_id", "label": "Last Modified By Id", "type": "user_id_on_update", "validation": {"api_read_only": {"on_fail": "ignore_field"}} }, { "description": "An aggregate virtual field, used in grouping statements.", "name": "max_salary", "type": "double", "is_virtual": true, "is_aggregate": true, "db_function": [ { "use": [ "SELECT" ], "function": "MAX(salary)", } ] }, { "description": "A virtual field returning the concatenation of two other columns in the table.", "name": "full_name", "type": "string", "is_virtual": true, "db_function": [ { "use": [ "SELECT" ], "function": "Concat(first_name, ' ', last_name)", } ] }, { "description": "A real binary field being represented in the API as base64 encoded for transmission in JSON payloads.", "name": "bdata", "type": "binary", "db_type": "blob", "allow_null": true, "db_function": [ { "use": [ "SELECT" ], "function": "TO_BASE64(bdata)", "function_type": "database" }, { "use": [ "insert", "update" ], "function": "FROM_BASE64({value})", "function_type": "database" } ] } }
Schema Operations
See the following sections for more detail on CRUD operations for database schema...
Retrieving Schema
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.
List of Tables
Description: Return schema data for the database, listing all accessible tables based on role access.
URI: GET http://<server_name>/api/v2/<service_name>/_schema
Request >GET http://demo.cloud.dreamfactory.com/api/v2/db/_schema/ HTTP/1.1
Response
{ "resource": [ { "name": "account", "label": "Account", "plural": "Accounts", "access": [ "GET", ... ] }, { "name": "contact", "label": "Contact", "plural": "Contacts", "access": [ "GET", ... ] }, ... ] }
Multiple Tables By Name
Description: Retrieve all available properties of one or more tables by name.
URI: GET http[s]://<host_name>/api/v2/<service_name>/_schema/?names=<table_names>
Request
None
Response
{ "resource": [ { "name": "account", "label": "Account", "plural": "Accounts", "primary_key": "id", "name_field": null, "field": [ { "name": "id", "label": "Id", "type": "id", "db_type": "int(11)", ... }, ... ], "access": [ "GET", "POST", "PUT", "PATCH", "DELETE" ] }, { "name": "contact", "label": "Contact", "plural": "Contacts", "primary_key": "id", "name_field": null, "field": [ { "name": "id", "label": "Id", "type": "id", "db_type": "int(11)", ... }, ... ], "access": [ "GET", "POST", "PUT", "PATCH", "DELETE" ] } ] }
Single Table
Description: Return all schema data for a single db table.
URI: GET http://<server_name>/api/v2/<service_name>/_schema/<table_name>
Request
None
Response
{ "name": "account", "label": "Account", "plural": "Accounts", "primary_key": "id", "name_field": "", "field": [ { "name": "id", "label": "Account ID", "type": "id", "db_type": "int(11)", ... }, ... ], "related": [ { "name": "Contacts_by_AccountId", "type": "has_many", "ref_table": "Contact", "ref_field": "AccountId", "field": "Id" }, ... ], "access": [ "GET", "POST", "PUT", "PATCH", "MERGE", "DELETE", "ADMIN" ] }
Single Field
Description: Return schema data for a single field of a db table, including the name, label, and defining properties.
URI: GET http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>
Prior to 2.4.0: (Deprecated) URI: GET http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>
Request
None
Response
{ "name": "name", "label": "Name", "type": "string", "db_type": "varchar(40)", "length": 40, "precision": 40, "scale": 0, "default": null, "required": true, "allow_null": false, "fixed_length": false, "supports_multibyte": false, "auto_increment": false, "is_primary_key": false, "is_foreign_key": false, "ref_table": "", "ref_fields": "", "validation": "not_empty", "values": [] }
Creating Schema
The following operations are typically available for creating tables on all DreamFactory Database Services. However, these operations may vary significantly in the properties that are necessary to actually create a table entity for each specific database vendor. For instance, some tables may automatically create the identifier field(s) with no input from the client, like in the case of MongoDB. Be sure to check the specific database service type for any differences documented in other pages in this section.
Multiple Tables
Description: Create one or more tables in the database. Note that the request and response should be an array of table-defining property sets wrapped with a resource element.
URI: POST http[s]://<host_name>/api/v2/<service_name>/_schema/
Request
{ "resource": [ { "name": "account", "label": "Account", "plural": "Accounts", "field": [ { "name": "id", "label": "Account ID", "type": "id" }, … ] }, { "name": "contact", "label": "Contact", "plural": "Contacts", "field": [ { "name": "id", "label": "Contact ID", "type": "id" }, … ] } ] }
Response
{ "resource": [ { "name": "account" }, { "name": "contact" } ] }
Single Table
Description: Create a single table in the database. Note that the table name is part of the URL and must be properly encoded. Posted data must for a single table and not wrapped with the resource wrapper, like above.
URI: POST http[s]://<host_name>/api/v2/<service_name>/_schema/<table_name>
Request
{ "name": "todo", "label": "Todo", "plural": "Todos", "field": [ { "name": "id", "label": "Todo ID", "type": "id" }, { "name": "name", "label": "Title", "type": "string" }, { "name": "complete", "label": "Completed?", "type": "boolean", "allow_null": false, "default": false } ] }
Response
{ "name": "todo" }
Adding a Single Field
Description: Create a single field in a db table. The table and field name are part of the URL and must be properly encoded. For adding multiple fields at once, see [Updating Schema](Database-Updating-Schema) or [Patching Schema](Database-Patching-Schema).
URI: POST http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>
Prior to 2.4.0: (Deprecated) URI: POST http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>
Request
{ "name": "created_date", "label": "Created Date", "type": "timestamp_on_create" }
Response
{ "name": "created_date" }
Updating or Replacing Schema
The following operations are typically available for all DreamFactory Database Services. Refer to the specifics of your database type documented in other pages in this section. The PUT verb on most points of our API is meant to completely replace an existing resource. The same holds for the schema resource. If you wish to just add new fields or modify existing fields, PATCH is a simpler and preferred option, as it will not change anything in the schema except what is requested. Updating a table definition with PUT allows you to add or modify existing fields, as well as, delete unnecessary fields, but requires you to post all of the fields that you wish to remain in the schema. Any fields that are left out, will be deleted from the schema.
Multiple Tables
Description: Update multiple tables in the database by replacing the schema with updated schema.
URI: PUT http://<server_name>/api/v2/<service_name>/_schema/
Request
{ "resource": [ { "name": "account", "label": "Account", "plural": "Accounts", "field": [ { "name": "new_field", "label": "My New Field", "type": "string" }, … ] }, { "name": "contact", "field": [ { "name": "old_field", "label": "New Type", "type": "integer" }, … ] } ] }
Response
{ "resource": [ { "name": "account" }, { "name": "contact" } ] }
Single Table
Description: Update a table in the database by replacing.
URI: PUT http://<server_name>/api/v2/<service_name>/_schema/<table_name>
Request
{ "name": "contact", "label": "Contact", "field": [ { "name": "new_field", "label": "My New Field", "type": "string" }, … ] }
Response
{ "name": "contact" }
Single Field
Description: Update a single field in a db table. Here for completeness, this call behaves the same as using PATCH on a single field. For updating multiple fields at once, use patching single table below.
URI: PUT http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>
Prior to 2.4.0: (Deprecated) URI: PUT http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>
Request
{ "label": "My New Label", "default": "" }
Response
{ "name": "contact" }
Patching or Merging Schema
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.
Multiple Tables
Description: Update multiple tables in the database by adding new fields or altering existing fields.
URI: PATCH http://<server_name>/api/v2/<service_name>/_schema/
Request
{ "resource": [ { "name": "account", "label": "Account", "plural": "Accounts", "field": [ { "name": "new_field", "label": "My New Field", "type": "string" }, … ] }, { "name": "contact", "field": [ { "name": "old_field", "label": "New Type", "type": "integer" }, … ] } ] }
Response
{ "resource": [ { "name": "account" }, { "name": "contact" } ] }
Single Table
Description: Update a table in the database by adding new fields or altering existing fields.
URI: PATCH http://<server_name>/api/v2/<service_name>/_schema/<table_name>
Request
{ "name": "contact", "label": "Contact", "field": [ { "name": "new_field", "label": "My New Field", "type": "string" }, … ] }
Response
{ "name": "contact" }
Update Field
Description: Update a single field in a db table. For updating multiple fields at once, use table format above.
URI: PATCH http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>
Prior to 2.4.0: (Deprecated) URI: PATCH http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>
Request
{ "label": "My New Label", "default": "" }
Response
{ "name": "contact" }
Deleting Schema
The following operations are typically available for all DreamFactory Database Services. Refer to the specifics of your database type documented in other pages in this section.
Single Table
Description: Delete (aka drop) a single table from the database.
URI: DELETE http://<server_name>/api/v2/<service_name>/_schema/<table_name>
Single Field
Description: Delete (aka drop) a single field on a db table.
URI: DELETE http://<server_name>/api/v2/<service_name>/_schema/<table_name>/_field/<field_name>
Prior to 2.4.0: (Deprecated) URI: DELETE http://<server_name>/api/v2/<service_name>/_schema/<table_name>/<field_name>
