Database

From DreamFactory
Jump to: navigation, search
(Created page with "DreamFactory REST API supports several types of database services. There are SQL database services (supporting connections to MySQL, PostgreSQL, MS SQL Server, SAP SQL Anywher...")
 
(Default Settings)
 
(41 intermediate revisions by 5 users not shown)
Line 1: Line 1:
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 instance or remotely on other servers or cloud architectures.
+
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, 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.
+
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.
  
For how to access tables and records via the database services, start with the [Common Features](#common) 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.
+
== 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.
  
## <a name="common"></a>Common Features
+
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.
  
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.
+
=== Features ===
 +
* [[DreamFactory/Features/Database/SQL/Stored_Procedures_And_Functions|Accessing Stored Procedures & Functions]]
 +
* [[DreamFactory/Features/Database/SQL/Views|Views Support]]
 +
* [[DreamFactory/Features/Database/SQL/Field_Expressions|Using Database Expressions]]
  
The following topics document the common capabilities across all supported database service types, unless otherwise noted.
+
=== Vendor Specifics ===
 +
* [[DreamFactory/Features/Database/SQLite|SQLite]]
 +
* [[DreamFactory/Features/Database/MySQL|MySQL, MariaDB, Percona, or Amazon's Aurora]]
 +
* [[DreamFactory/Features/Database/PostgreSQL|PostgreSQL]]
 +
* [[DreamFactory/Features/Database/SQLServer|Microsoft SQL Server]]
 +
* [[DreamFactory/Features/Database/SQLAnywhere|SAP SQL Anywhere]]
 +
* [[DreamFactory/Features/Database/Oracle_DB|Oracle Database]]
 +
* [[DreamFactory/Features/Database/IBM_DB2|IBM DB2]]
 +
* [[DreamFactory/Features/Database/Redshift|Amazon's Redshift]]
  
* Database Resources
+
== Other Database Services ==
  * [Retrieving Resources](Database-Resources)
+
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.
  
* Table Schema
+
=== Vendor Specifics ===
  * [Common Parameters & Formatting](Database-Schema)
+
* [[DreamFactory/Features/Database/Cassandra|Apache Cassandra]]
  * [DreamFactory Extensions](Database-Schema#extensions)
+
* [[DreamFactory/Features/Database/CouchDB|Apache CouchDB]]
  * [Retrieving Schema](Database-Retrieving-Schema)
+
* [[DreamFactory/Features/Database/DynamoDb|AWS DynamoDB]]
  * [Creating Schema](Database-Creating-Schema)
+
* [[DreamFactory/Features/Database/Couchbase|Couchbase]]
  * [Updating/Replacing Schema](Database-Updating-Schema)
+
* [[DreamFactory/Features/Database/Azure_DocumentDB|Microsoft Azure DocumentDB]]
  * [Patching/Merging Schema](Database-Patching-Schema)
+
* [[DreamFactory/Features/Database/Azure_Tables|Microsoft Azure Tables]]
  * [Deleting Schema](Database-Deleting-Schema)
+
* [[DreamFactory/Features/Database/MongoDB|MongoDB]]
 +
* [[DreamFactory/Features/Database/Salesforce|Salesforce Services]] - a DreamFactory "blended" access to the Salesforce REST API.
  
* Table Records
+
== Common Features ==
  * [Common Parameters & Formatting](Database-Records)
+
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.
  * [Retrieving Records](Database-Retrieving-Records)
+
  * [Creating Records](Database-Creating-Records)
+
  * [Updating/Replacing Records](Database-Updating-Records)
+
  * [Patching/Merging Records](Database-Patching-Records)
+
  * [Deleting Records](Database-Deleting-Records)
+
  * Retrieving Schema Along With Records
+
  * [Date and Time Field Formats](Database-Date-Time-Formats)
+
  
 +
=== Database Resources ===
 +
Every database service provides a way of getting the supported resources of that service. The REST API call looks like the following:
 +
<pre>GET http[s]://<server>/api/v2/<service>/?[as_access_list=true | as_list=true]</pre>
 +
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 [[DreamFactory/Features/Roles|Role Service Access]] assignments, along with individual table listings, so access can be controlled based on the resource, or by table.
  
## <a name="sql"></a>SQL Database Services
+
==== 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.
 +
* [[DreamFactory/Features/Database/Schema|Common Parameters & Formatting]]
 +
* [[DreamFactory/Features/Database/Schema#Extensions|DreamFactory Extensions]]
 +
* [[DreamFactory/Features/Database/Schema#Retrieving Schema|Retrieving Schema]]
 +
* [[DreamFactory/Features/Database/Schema#Creating Schema|Creating Schema]]
 +
* [[DreamFactory/Features/Database/Schema#Updating or Replacing Schema|Updating or Replacing Schema]]
 +
* [[DreamFactory/Features/Database/Schema#Patching or Merging Schema|Patching or Merging Schema]]
 +
* [[DreamFactory/Features/Database/Schema#Deleting Schema|Deleting Schema]]
  
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 DSP from our pre-built packages, most drivers are already installed.
+
==== 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.
 +
* [[DreamFactory/Features/Database/Records#Common Parameters and Usage|Common Parameters and Usage]]
 +
* [[DreamFactory/Features/Database/Records#Retrieving Records|Retrieving Records]]
 +
* [[DreamFactory/Features/Database/Records#Creating Records|Creating Records]]
 +
* [[DreamFactory/Features/Database/Records#Updating/Replacing Records|Updating/Replacing Records]]
 +
* [[DreamFactory/Features/Database/Records#Patching/Merging Records|Patching/Merging Records]]
 +
* [[DreamFactory/Features/Database/Records#Deleting Records|Deleting Records]]
 +
* [[DreamFactory/Features/Database/Related_Data|Retrieving and Managing Related Data Along With Records]]
  
By default, each DSP package comes with a native, locally installed, SQL database service. In most packaged setups, this is connected to the local MySQL database running on the same server as the DSP, although other connections could be used simply by changing the appropriate DSP database configuration [distribution file](https://github.com/dreamfactorysoftware/dsp-core/blob/master/config/database.config.php-dist). This default service has a type of "Local SQL DB" with a name of "Database" and a API Name of 'db'. Consequently, this is the same physical database used by the [System Configuration](System-Configuration) REST service, but access to system tables is controlled by the server-side software and not allowed via this service.
+
=== 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,
  
To access other databases via your DSP, you can create more SQL DB services, see [System Configuration](System-Configuration) or the [Admin Console](Services) sections on how to accomplish this.
+
== Default Settings ==
  
Go [here](https://dsp-sandman1.cloud.dreamfactory.com/swagger/#!/db) to see this service type in action in our [Live API](Admin-Console-api-sdk).
+
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:
  
* [SQL DB Service Specifics](SQL-Database-Services)
+
<pre>$ php artisan config:clear</pre>
  * [Connection Strings for each SQL Database](SQL-Connections)
+
  * [Retrieving Related Data Along With Records](SQL-Related-Data)
+
  * [Creating and Managing Related Data With Records](SQL-Related-Data)
+
  * [Accessing Stored Procedures](SQL-Stored-Procedures)
+
  * [Using Expressions for Field Value](SQL-Field-Expressions)
+
  
 +
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.
  
## <a name="nosql"></a>NoSQL Database Services
+
[[File:DB Max Records.png|center|850px|DB_MAX_RECORDS value .env|alt=DB_MAX_RECORDS value]]
  
DreamFactory database services support connections to most of the popular NoSQL databases. To access these databases via your DSP, you can create a new NoSQL DB service, see [System Configuration](System-Configuration) or the [Admin Console](Services) sections on how to accomplish this. For more details on NoSQL services see the specific sections listed below.
+
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.
  
Go [here](https://dsp-sandman1.cloud.dreamfactory.com/swagger/#!/mongodb) to see this service type in action in our [Live API](Admin-Console-api-sdk).
+
[[File:DB Service Records.png|center|850px|database Service max records Value|alt=Database Service max records value]]
 
+
* [NoSQL DB Service Specifics](NoSQL-Database-Services)
+
  * MongoDB Specifics
+
  * AWS DynamoDB Specifics
+
  * Microsoft Azure Tables Specifics
+
  * CouchDB Specifics
+
 
+
 
+
## <a name="others"></a>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](Salesforce-Services) - a DreamFactory "blended" access to the Salesforce REST API.
+

Latest revision as of 20:13, 5 July 2018

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

Vendor Specifics

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

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.

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.

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.

DB_MAX_RECORDS value

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.

Database Service max records value