Creating APIs‎ > ‎Integration‎ > ‎

Integrate Multiple Databases

You can integrate multiple databases as part of your API. Integrate multiple databases so that you can:

  • Define relationships between databases.
  • View data from multiple databases.
  • Define resources spanning databases.
  • Declare rules between databases.
  • Issue APIs that against any of your databases.

Foreign keys are useful for defining multi-table resources, object model accessors, and rules. Many schemas do not define these. If your schema does not have foreign keys, you can define virtual foreign keys using API Creator. Related tables can be in the same database or different databases.

The following image shows the Integrate, Data Sources, Relationships page:

Rename a Database Prefix

Database prefixes help identify the database objects. Resources, rules, and JavaScript use database prefixes to reference multiple data sources.

References to tables (for example, in rules, resource definitions, and role permissions) include the database prefix. This is represented as text, so you can swap test/production databases.

Important! Choose your database prefix name carefully. Renaming a database prefix breaks rules and resource definitions.

  1. Export your API.
    For more information about exporting APIs, see Import and Export API Definitions.
  2. Change the database prefix in a text editor.
  3. Import your API.

Define Relationships between Databases

You can define virtual foreign keys across multiple databases by defining relationships between databases.

For more information about virtual foreign keys, see Virtual Foreign Keys.

  1. On the Integrate, Data Sources, Relationships tab, click Add.
  2. Define your relationship.
  3. View the data in Data Explorer. The following image shows customers and orders from the (same) Demo database:

    The following image shows Customers and Orders from the Sample databases:

Define Resources between Databases

You define resources that combine data from different databases by entering syntax in the Join field on the Create, Resources, Resource tab. The following image shows this field on the tab:

Note: Defining the relationship between the databases defaults the join and the key appears as the value in the Join field. You can change this value.

The resource's join condition defines how this resource is joined with the containing resource.

You can refer to columns in the table of the containing resource using the following notation in the Join field:

customer_number = [custnum] and region_ident = [regident]

In this example, customer_number and region_ident are columns of the current resource's table, and custnum and regident are columns of the table for the containing resource.

MongoDB resource syntax Example

If you are defining a MongoDB resource, use Mongo syntax in the Join field. For example:

{"$and": [{"CompID": "<CompanyID>"}, {"CustID": "<CustomerID>"}]}

Multi-Database Performance

Live API Creator efficiently optimizes resources that combine data from different databases. For example, Live API Creator retrieves customers and then retrieves the OrdersFromSample rows.

Define Rules between Databases

You can define rules between databases. Each database commits separately. One database may succeed while the other fails.

APIs for Database Access

You can use the same APIs to interact with data, whether your API is single or multi-database.

For more information about using the same APIs to interact with data in multiple databases, see LogicContext Object.