The Enterprise-Grade REST API for SQL | Dreamfactory

DreamFactory supports a wide variety of backend systems including SQL, NoSQL, BLOB, email, users, roles, security, and integration. But in this blog post, I wanted to focus on our support for SQL databases. The vast majority of enterprise data is stored in a SQL database somewhere, and there is a huge need to access this information for mobile application development. So DreamFactory has really focused on building a comprehensive and enterprise-grade REST API for SQL.

Local Or Remote

You can install DreamFactory on any server, in the cloud or on-premises. Each instance comes pre-installed with a local MySQL database that we use for platform operations. In addition to the local database, you can hook up any number of external SQL databases. They might be located on the DreamFactory server, on another server, or in the cloud. In either case, we provide a comprehensive REST API for both local and remote databases.

Legacy Or Greenfield

The local database has various system tables that are used by the DreamFactory platform. For example, these tables store users, roles, and services. Normally the system objects are hidden from the REST API, although they can be accessed with system services. You can add your own schema and data to the local database for application development. A remote database will probably have some existing schema, data, and user roles. When you hook up a remote database, DreamFactory reads the schema and provides a REST API for the data.

Wide Support

DreamFactory can connect to any ANSI SQL database including MySQL, SQL Server, and PostgreSQL. As of this writing DB2 and Oracle have a few issues we are resolving. At the client, our API provides an abstraction layer that hides the system specific differences between all of the various backend SQL databases. This allows an administrator to "swap out" a database without disrupting the client applications. This is very helpful when moving from development to testing and on to production. In many cases, you can use the same client code for both SQL and NoSQL data sources as well.

Data Security

When you hook up an external database, you will need to provide a username, password, and connection string. The credentials that you specify will connect to an existing role and govern all access to the remote database. These permissions are reflected by the REST API. For example, an administrator could connect to an existing database with read-only credentials. In this case, the REST API will reflect the read-only permissions.

Lookup Keys

Sometimes you want the credentials for a remote database to be different depending on the current user or role. We provide a feature called Lookup Keys that allows the DreamFactory users and roles to connect to the corresponding users and roles in a remote database. This is extremely useful in situation where you want the REST API to mimic the security permissions on the remote database.

Here is a blog post with more information about lookup keys.

Master Credentials

For security, all of the master credentials for remote SQL databases are encrypted and stored on the DreamFactory platform. This capability removes the need to store master credentials in a client application where they could be compromised. When a user comes in through single sign-on, they are granted access to any number of databases depending on their assigned role. The role specifies detailed access rights for each database table. In this manner, different roles can be granted secure access to any table on any connected database.

Record-Level Access

We also support record-level database permissions with Server-Side Filters. This feature grants "fine grained" control of the records inside a given table. For example, you might want a user to edit only records that they have created. Or you might want members of a sales team to edit accounts, but only in their region. You can partition or limit records by user, role, application or any field value.

Here is a blog post with more information about server-side filters.

Query Filters

Our SQL database interface supports filter strings. For example, you could get all records within a certain date range, or created by a certain user, or with a wildcard match on a certain string. The filters are carefully rewritten to prevent SQL injection errors or attacks. Any number of logical operators and parenthesis can be used. All of the standard SQL data types are supported, including text, double, integer, date, time, email, phone, and relationship.

Related Objects

The REST API will deliver an array of objects along with related objects in a sub-array. This is a very powerful feature for HTML5 applications because large database documents can be downloaded and used immediately without any additional processing as a JSON object. Any changes made to the array can be committed back to the database with a single transaction. All parent-child relationships and many-to-many junction relationships are automatically updated. This capability provides a document oriented interface to any SQL database.

Here is a blog post with more information about related objects.

Paging And Sorting

Our REST API has some handy URL parameters that really help when working with large SQL databases. First, you can limit the number of records returned. This is a good idea when dealing with large tables to prevent out of memory errors at the client. When you hit the limit, you will receive an offset to the next batch of data to load, which makes handling pages of data easy. Lastly you can ask the server to sort the data by any field before delivering the records. All of these flags are visible in the Live API interface.

Continue Or Rollback

You might want to create, read, update, or delete an array of objects. By default, DreamFactory will process this operation until the array is finished or an error is encountered. But the REST API has flags to change this behaviour. For example, you might want to continue processing the array even if there is an error. You can also "roll back" the entire operation if any error is encountered. These flags are also visible in the Live API interface.

Schema Support

You can use the REST API to return information about what tables are available on any database. For example, you could fetch a list of all the tables visible from the current user role. Given any table, you can look into all of the fields that are available, along with other information, such as field type, text length, numeric scale, and field permissions. The access rights for each table and field are also listed. This is very useful for client applications that browse data or that need to know what objects are available.

Metadata Support

We have an entirely different set of services that allow Administrators to create, read, update, or delete the database schema. So for example, they could create a new database table, or add a new field to an existing table. These rights can also be granted to regular users if needed. This provides an automated way to move schema information between databases, or to set up tables dynamically. Our Admin Console uses these services to provide an interactive schema editor.

In Conclusion

Well, there you have it. We have gone to great lengths to build an open, reliable, scalable, and secure REST API for SQL. All you have to do is hook up a database and we provide a comprehensive palette of data, metadata, and schema services. This approach really reduces the need for building your own server side software, and rebuilding the server every time a new REST API is required. Under the circumstances, DreamFactory is much more secure and much less expensive than developing a new platform from scratch.

Related reading: API Trends