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 the 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 below. 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 any 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 in the specified sections.
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, along with individual table listings, 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. Most connections are dependent on the correct drivers being installed for that server. If installing DreamFactory from Bitnami pre-built packages, most drivers are already installed. See the specific database vendor links below for more information.
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
- Views Support
- 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
NoSQL databases vary widely in features and function. All support some forms of CRUD operations for records. Most have their own specific way of dealing with identifying fields (i.e. primary keys in SQL lingo), so the standard CRUD operations may be different in practice for each vendor. Most, except CouchDB which uses a views interface, support some native filtering capability. Where applicable DreamFactory provides a SQL-like filtering language that gets converted to the native filtering to make these databases easy to work with.
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.
