Database
The DreamFactory REST API supports several types of database services. There are SQL database services (supporting connections to relational databases like MySQL), NoSQL database services (MongoDB, etc.), and others like Salesforce database service that tend not to fit in either category. DreamFactory 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 (aka a "blended" 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.
Contents
Common Features
Database record CRUD (Create, Read, Update and Delete) operations and some table-level operations are available for all 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 or Replacing Schema
- Patching or 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 and Usage
- Retrieving Records
- Creating Records
- Updating/Replacing Records
- Patching/Merging Records
- Deleting Records
Events
Basic events fired by all database services. See the specific sections for additional events.
- db.get,
- db._schema.get,
- db._schema.post,
- db._schema.put,
- db._schema.patch,
- db._schema.{table_name}.get,
- db._schema.{table_name}.post,
- db._schema.{table_name}.put,
- db._schema.{table_name}.patch,
- db._schema.{table_name}.delete,
- db._schema.{table_name}.{field_name}.get,
- db._schema.{table_name}.{field_name}.put,
- db._schema.{table_name}.{field_name}.patch,
- db._schema.{table_name}.{field_name}.delete,
- db._table.get,
- db._table.{table_name}.get,
- db._table.{table_name}.post,
- db._table.{table_name}.put,
- db._table.{table_name}.patch,
- db._table.{table_name}.delete,
- db._table.{table_name}.{id}.get,
- db._table.{table_name}.{id}.put,
- db._table.{table_name}.{id}.patch,
- db._table.{table_name}.{id}.delete,
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 Bitnami pre-built packages, most drivers are already installed.
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.
Features
- Retrieving and Managing Related Data Along With Records
- Accessing Stored Procedures & Functions
- Using Database Expressions
Vendor Specifics
- SQLite
- MySQL, MariaDB, Percona, or Amazon's Aurora
- PostgreSQL
- Microsoft SQL Server
- SAP SQL Anywhere
- Oracle Database
- IBM DB2
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.
= Features
Vendor 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.
