Database

From DreamFactory
Jump to: navigation, search
(SQL Database Services)
(Default Settings)
 
(17 intermediate revisions by 5 users not shown)
Line 1: Line 1:
The 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. DreamFactory database services can connect to databases installed locally on the same server 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|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.
+
== 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.
  
== 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]]
 +
 
 +
== 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 ===
 +
* [[DreamFactory/Features/Database/Cassandra|Apache Cassandra]]
 +
* [[DreamFactory/Features/Database/CouchDB|Apache CouchDB]]
 +
* [[DreamFactory/Features/Database/DynamoDb|AWS DynamoDB]]
 +
* [[DreamFactory/Features/Database/Couchbase|Couchbase]]
 +
* [[DreamFactory/Features/Database/Azure_DocumentDB|Microsoft Azure DocumentDB]]
 +
* [[DreamFactory/Features/Database/Azure_Tables|Microsoft Azure Tables]]
 +
* [[DreamFactory/Features/Database/MongoDB|MongoDB]]
 +
* [[DreamFactory/Features/Database/Salesforce|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 ===
 
=== Database Resources ===
Every database service provides a way of getting the supported resources of that service. The REST API call looks like the following...
+
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>
 
<pre>GET http[s]://<server>/api/v2/<service>/?[as_access_list=true | as_list=true]</pre>
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 [[DreamFactory/Features/Roles|Role Service Access]] assignments so access can be controlled based on the resource, or by table.  
+
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.  
  
=== Table Schema (_schema) ===
+
==== 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.  
 
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|Common Parameters & Formatting]]
Line 26: Line 54:
 
* [[DreamFactory/Features/Database/Schema#Deleting Schema|Deleting Schema]]
 
* [[DreamFactory/Features/Database/Schema#Deleting Schema|Deleting Schema]]
  
=== Table Records (_table) ===
+
==== 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.
 
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#Common Parameters and Usage|Common Parameters and Usage]]
Line 34: Line 62:
 
* [[DreamFactory/Features/Database/Records#Patching/Merging Records|Patching/Merging Records]]
 
* [[DreamFactory/Features/Database/Records#Patching/Merging Records|Patching/Merging Records]]
 
* [[DreamFactory/Features/Database/Records#Deleting Records|Deleting Records]]
 
* [[DreamFactory/Features/Database/Records#Deleting Records|Deleting Records]]
 +
* [[DreamFactory/Features/Database/Related_Data|Retrieving and Managing Related Data Along With Records]]
  
== SQL Database Services ==
+
=== 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,
  
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.
+
== Default Settings ==
  
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 [https://github.com/dreamfactorysoftware/dreamfactory/blob/master/config/database.php database configuration file]. This SQL connection is used by the application internally and exposed via the REST API as the 'system' service.  
+
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:
  
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.
+
<pre>$ php artisan config:clear</pre>
  
* [[DreamFactory/Features/Database/SQL|SQL DB Service Specifics]]
+
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.
* [[DreamFactory/Features/Database/SQL#Drivers and Connection Strings|Drivers and Connection Strings for each SQL Database]]
+
* [[DreamFactory/Features/Database/SQL#related|Retrieving and Managing Related Data Along With Records]]
+
* [[DreamFactory/Features/Database/SQL/Stored_Procedures_And_Functions|Accessing Stored Procedures & Functions]]
+
* [[DreamFactory/Features/Database/SQL-Field-Expressions|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.
+
[[File:DB Max Records.png|center|850px|DB_MAX_RECORDS value .env|alt=DB_MAX_RECORDS value]]
  
* [[DreamFactory/Features/Database/NoSQL|NoSQL DB Service Specifics]]
+
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.
* [[DreamFactory/Features/Database/MongoDB|MongoDB Specifics]]
+
* [[DreamFactory/Features/Database/DynamoDb|AWS DynamoDB Specifics]]
+
* [[DreamFactory/Features/Database/Azure_Tables|Microsoft Azure Tables Specifics]]
+
* [[DreamFactory/Features/Database/CouchDB|CouchDB Specifics]]
+
  
== Other Database Services ==
+
[[File:DB Service Records.png|center|850px|database Service max records Value|alt=Database Service max records value]]
 
+
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.
+
 
+
* [[DreamFactory/Features/Database/Salesforce|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