Database

DreamFactory REST API supports several types of database services. There are SQL database services (supporting connections to MySQL, PostgreSQL, MS SQL Server, SAP SQL Anywhere, Oracle Database, IBM DB2, SQLite, etc.), NoSQL database services (supporting the likes of MongoDB, AWS DynamoDB, Azure Tables, CouchDB, etc.), and a Salesforce database service. DSP database services can connect to databases installed locally on the same server or remotely on other servers or cloud architectures.

DreamFactory makes accessing each of these back-end storage databases easy with a common REST interface, while still allowing most of the unique features of each underlying database type to be accessed. Each of these types of services are briefly discussed below.

For how to access tables and records via the database services, start with the Common Features section. Follow the links in each section for more detail. There are some features of each of these services that are unique to that service type, for example, relational queries in SQL databases, or using the native filtering language in MongoDB. See the individual type sections below for more specifics.

Common Features

Database record CRUD (Create, Read, Update and Delete) operations and some table-level operations are available for both SQL, NoSQL, and Salesforce database service types. This gives the API client the ability to write an application once with very little refactoring required to completely swap out the back-end database. It also makes the learning curve for adopting new databases very short.

The following topics document the common capabilities across all supported database service types, unless otherwise noted.

Database Resources

Every database service provides a way of getting the supported resources of that service. The REST API call looks like the following...

GET http[s]://<server>/api/v2/<service>/?[as_access_list=true | as_list=true]

Two resources are available with every database services (see the "Additional Resources" sections for the specific service types below for more), Table Schema (_schema) and Table Records (_table). These resources are available in the Role Service Access assignments so access can be controlled based on the resource, or by table.

Table Schema (_schema)

This resource is used to perform operations on the database schema, i.e. creating or dropping tables or fields, retrieving details about tables or fields, etc. The sections below describe the available operations in detail.

• Common Parameters & Formatting
• DreamFactory Extensions
• Retrieving Schema
• Creating Schema
• Updating/Replacing Schema
• Patching/Merging Schema
• Deleting Schema

Table Records (_table)

This resource is used to perform operations on the database table records, i.e. data. The sections below describe the available operations in detail.

• Common Parameters & Usage
  • Using Identifiers and Filtering
  • Using Batching
  • Using Lookups And Replacement Parameters
• Retrieving Records
• Creating Records
• Updating/Replacing Records
• Patching/Merging Records
• Deleting Records

SQL Database Services

DreamFactory database services support connections to most of the popular SQL databases. Currently all connections use PHP PDO connection strings and are dependent on the correct PDO drivers being installed for that server. If installing DreamFactory from our pre-built packages, most drivers are already installed.

Each instance requires at least one SQL database connection for system storage. In most pre-built packages, this is connected to a local MySQL (or equivalent) database running on the same server as the instance, although other connections could be used simply by changing the appropriate database configuration file. This SQL connection is used by the application internally and exposed via the REST API as the **system** service.

Any number of additional SQL database services can be added to the system. These services have a type of "SQL DB". Most instances are seeded with a default SQLite database, named 'db' by default. Bitnami installs typically come with an additional service connected to a local MySQL install (or equivalent), named 'mysql' by default. To access other databases via your instance, you can create more SQL DB services.

• SQL DB Service Specifics
• Drivers and Connection Strings for each SQL Database
• Retrieving and Managing Related Data Along With Records
• Accessing Stored Procedures & Functions
• Using Expressions for Field Value

NoSQL Database Services

DreamFactory database services support connections to most of the popular NoSQL databases. To access these databases via your instance, you can create a new NoSQL DB service. For more details on NoSQL services see the specific sections listed below.

• NoSQL DB Service Specifics
• MongoDB Specifics
• AWS DynamoDB Specifics
• Microsoft Azure Tables Specifics
• CouchDB Specifics

Other Database Services

Some of database storage types don't fit well into either of the above categories or have unique features that warrant their own service type. The following topics cover the ones we support so far. If you would like to see others, please let us know.

• Salesforce Services - a DreamFactory "blended" access to the Salesforce REST API.