Database

Latest revision as of 07:36, 19 July 2022

The DreamFactory REST API supports several types of database services. There are SQL database services (MySQL, etc.), 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. Start with the common features sections below to learn the database service basics. There are some features that are unique to each service type, for example, using the native filtering language in MongoDB. See the individual type sections below for more specifics.

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

• 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
• Amazon's Redshift

Other Database Services

DreamFactory database services support connections to most of the popular databases not classified as SQL databases. These 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. The following topics cover the ones currently supported. If you would like to see others, please let us know.

Vendor Specifics

• Apache Cassandra
• Apache CouchDB
• AWS DynamoDB
• Couchbase
• Microsoft Azure DocumentDB
• Microsoft Azure Tables
• MongoDB
• Salesforce Services - a DreamFactory "blended" access to the Salesforce REST API.

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 service (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
• Retrieving and Managing Related Data Along With 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,

Default Settings

All database services will by default return a maximum of 1,000 records. If you'd like to change this default on a per-database service setting, enter your service configuration and change the Maximum Records field accordingly. If you'd like to change this default globally, open your .env file and update the DB_MAX_RECORDS_RETURNED setting (make sure you also uncomment this setting). Once DB_MAX_RECORDS_RETURNED has been updated and the .env file saved, you'll need to clear your application cache by running:

$ php artisan config:clear

Once done, you'll need to additionally enter each database service's configuration and clear out the Maximum Records field, otherwise, the database-specific 1,000 record maximum will override the global setting.

You then migrate over to the Services tab in the admin interface and change the value of the database you want to increase the number of records to be retrieved.