You can define Multiple Database as part of your API, as described below.
This enables you to
- define relationships between databases
- view data from multiple databases in the Live Browser
- define Resources spanning databases
- declare rules between databases
- Issue APIs that against any of your databases
Defining Multiple Databases
You can define multiple databases as shown below. Observe you assign each a Database prefix, which helps identify the database objects as shown in the schema, above.
Changing Database Prefix Names
References to tables (e.g., in Rules, Resource Definitions, Role Permissions) include the Database Prefix. This is represented as text, so you can swap test/production databases, etc.
This strategy does, however, mean that renaming a Database Prefix will break Rules, Resource Definitions, and so forth.
Choose your Database Prefix name carefully. If you feel you must change them, export
your project, alter them in a text editor, and import.
Defining Relationships between databases
You can define relationships that cross databases (aka Virtual Foreign Key), as shown below:
- Select Data Sources > Relationships tab
- Click Add, and fill in the fields to the right
- Be sure to define your relationship fields, and mark the relationship as active.
When this is complete, you can view the data in the Live Browser. The first screen shot shows Customers and Orders from the same (demo) database:
This screen shot shows Customers and Orders from different databases.
Defining Resources between databases
You can define Resources that combine data from different databases. If you define the relationship as shown above, the join is defaulted.
The system provides efficient optimizations for such resources. For example, the system retrieves a page full of Customers, and then issues a single retrieval to obtain the OrdersFromSample rows. It does not issue an OrdersFromSample query for each Customer row.
Defining Rules between databases
You can define rules between databases. Be aware, however, that each database commits separately, so it is possible one may succeed while the other fails. Future support is planned to log such events for manual reconciliation.
APIs for Database access
Use the same APIs
to interact with data, whether your project is single or multi-database. Multi-database references, however, must include the Database Prefix (e.g., main:Customer
Note this is not strictly necessary, but recommended. If your project later adds a second database, you will avoid having to revise these table references.