Wrangling Your Data with Database Functions

by Terence Bennett • July 6, 2018

One of the fundamental goals of the DreamFactory platform is to make it easy to get the data you want, in the format you want, from the sources you have available. Sometimes the type and/or format of the data in the database isn't easy to pass to and from a client RESTfully, particularly using JSON or XML payloads. DreamFactory's database functions support solves this problem.

The Solution

To do this sort of special handling when using SQL directly, you'd typically use an existing function supported by the database to convert or transform the native data type or format into something more manageable in your application. These conversion functions differ by database vendor, but most support the basics in one syntax or another. An example of using a database function from SQL Server would look something like:SELECT (field_name.ToString()) as field_name from table_name; DreamFactory has supported database functions for SELECT statements like these on virtual fields for several releases now.

Starting with 2.4.2, DreamFactory now supports the use of database functions on all SQL database fields, and not only for SELECT statements, but for filter uses (think SQL WHERE clauses), and INSERT and UPDATE requests.

Back to our original example with the binary data. Our client can handle Base64 encoding, so we'll use that for our transport. The first thing we need to do is add the correct functions to our existing schema for the field. DreamFactory will then use this configuration and apply it to the designated use cases during database access.

Go to the Schema tab on the administration application, select your database service and the table where this data exists. In the table view on the right, select the field representing the binary data column, in this case, a BLOB column called “bdata”. Once selected, scroll down to the “Database Function Use” section of the configuration. Here you can provision your database functions to apply. In this case, we want to encode the data in the “bdata” column in our SELECT usage, i.e. an API GET request. We also want to decode the data coming from the client when we insert the data back into the database. MySQL supports TO_BASE64() and FROM_BASE64() functions (syntax will vary by database vendor), so we will use them to do the encoding.

db-function-use.png

Notice that the column name is used as a parameter to the TO_BASE64 function. **Note:** If the column name is special for the database, it may need to be quoted (double quotes or other characters depending on the database type) to avoid errors. On the FROM_BASE64 call, we need the actual value from the payload passed in from the client, in this case, the encoded data for field “bdata”. The way DreamFactory knows to put the value in the function is to use the lookup replacement syntax “{value}”. Saving this configuration results in an update call to the schema API to update the field schema to store these use cases, which looks like…

PATCH https://df.example.com/api/v2/mysql/_schema/mytable/_field/bdata

 {

 	"name": "bdata",

 	"type": "binary",

 	"db_type": "blob",

 	"db_function": [{

 			"use": ["SELECT"],

 			"function": "TO_BASE64(bdata)",

 			"function_type": "database"

 		},

 		{

 			"use": ["INSERT", "UPDATE"],

 			"function": "FROM_BASE64({value})",

 			"function_type": "database"

 		}

 	]

 }

Once this configuration is applied, API requests to this database service to retrieve or insert data for this field will result internally in SQL calls similar to the following...

GET https://df.example.com/api/v2/mysql/_table/mytable/

Resulting in an internal call like this...

SELECT id, name, TO_BASE64(bdata) as bdata from mytable; And returning data that looks like this...

{

 	"resource": [{

 			"id": 1,

 			"name": "example",

 			"bdata": "iVBORw0KGgoAAAANSUhEUgAAAVoAAAAvCAYAAACiyQKwAAAACXBIWXMAAAsTAAALEwEAmpwYAAA7\ncWlvufzLsvzSYuO7ivI6mYszQIAARw9MbfY71BJix/i/AQAIlLRHhGty\nBAAAAABJRU5ErkJggg==",

 			"year": 2016

 		},

 		{

 			"id": 2,

 			"name": "test",

 			"bdata": "MQvufzLsvzSYuO7ivI6mYszQIAARw9MbfY71BJix==",

 			"year": 2017

 		}

 	]

 }

Similarly, posting the same dataset back to...

PATCH https://df.example.com/api/v2/mysql/_table/mytable/2

{      

 "bdata": "MQvufzLsvzSYuO7ivI6mYszQIAARw9MbfY71BJix==",

  "year": 2017  

  }

Results in an internal call like this...

UPDATE mytable SET bdata = FROM_BASE64(“MQvufzLsvzSYuO7ivI6mYszQIAARw9MbfY71BJix==”),
year = 2017 WHERE id = 2;

That’s it! Now data is as easily transportable and usable by clients as other simple types. This same concept can be applied to any database column type that needs special handling. Below are a few that we have used for some of the interesting SQL Server data types...

Type Suggested Function
image (CONVERT(varbinary(max), field_name))
rowversion CAST(field_name AS BIGINT)
geometry, geography, hierarchyid (field_name.ToString())
uniqueidentifier (CONVERT(varchar(255), field_name))

Database functions can help DreamFactory users wrangle their data that isn’t so compliant for the RESTful world we now live in. Head on over to the community forum to discuss or let us know what you think in the comments!

Related reading: Synchronizing Database Data Using an API