Docs‎ > ‎Integrate Menu‎ > ‎Data Sources‎ > ‎

Multiple Databases

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.
  • Define Resources spanning databases.
  • Declare rules between databases.
  • Issue APIs that against any of your databases.

Define Multiple Databases

You can define multiple databases. Assign each a Database prefix, which helps identify the database objects as shown in the schema, above.

Change 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. Renaming a database prefix breaks Rules, Resource Definitions. Choose your database prefix name carefully.
  1. Export your API project.
  2. Change the database prefix in a text editor.
  3. Import your API project.

Define Relationships between databases

You can define relationships that cross databases (aka Virtual Foreign Key), as shown below:
  1. Select Data Sources, Relationships tab.
  2. Click Add, define your relationship, and mark the relationship as active.
  3. View the data. The following image shows Customers and Orders from the same (demo) database:

    The following image shows Customers and Orders from different databases:

Define Resources between Databases

You can define Resources that combine data from different databases. If you define the relationship as shown in the previous example, the join is defaulted.

Multi-Database Performance

Live API Creator 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.

APIs for Database Access

Use the same APIs to interact with data, whether your project is single or multi-database. Best Practice: Include the database prefix, for example main:Customer, in multi-database references. If your project later adds a second database, including a database prefix avoids requiring that you revise these table references.