The DreamFactory Services Platform (DSP) can connect to any SQL database including MySQL, DB2, Oracle, SQL Server, and PostgreSQL. The database can be on the local server, a remote server, or in the cloud. The platform works with both new and existing databases. The Admin Console allows you to edit the data in any table on any connected database. There is also a metadata interface for creating and deleting tables and fields.
To create a new database service, first select the "Remote SQL DB" option from the menu in the "Services" section of the Admin Console. Enter a name for the service, the username and password, and then the connection string. After provisioning, the service shows up in the Live API, and is available to be called from the Client SDK as well. You can also add a "Remote SQL DB Schema" service. This allows tables and fields to be created and deleted on a remote database if necessary.
The username and password that you specify will govern all access to the remote database. 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. We also 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. In that situation, the permissions for the REST API will mimic the permissions on the remote database.
Every DSP also has a local SQL database. This is where we store users, roles, and other information that the DSP needs to operate. The local SQL database is listed by default as the "Database" service. There is an additional service called "Schema" that provides a metadata interface to the local database. Go to the "Schema" section of the Admin Console to create the tables and fields that you will need for your application.
Connect to any SQL database
The master credentials for remote SQL databases are encrypted and stored on the DreamFactory platform. 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.
We also support record-level database permissions with Server-Side Filters. This feature provides "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.
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 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.
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. The total number of records returned can be limited, and you can also order the records by any field. Record paging is provided for very large responses. All of the standard SQL data types are supported, including text, double, integer, date, time, email, phone, and relationship.
The SQL service can also 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 native 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.
More Information
Read our documentation about the SQL Services.
Read this blog with basic information on using SQL services.
And this blog with detailed information on working with related objects.
