Creating APIs‎ > ‎

Database Connectivity

Each API has a list of one or more registered databases and one or more SQL database locations. This list may include only the database connections that were created when you created your API.

You can:

  • Add database entries, such as test databases, integration databases, and a production database.
  • Define cross-database relationships.

API Creator update-processing relies on database services for locking and transaction management. Retrieval-processing leverages the database engine's services for query optimization (index selection, join strategy).

You can:

  • Use multiple databases in the same API.
  • Combine resources, build rules between databases, and access them by defining multiple databases.
For more information about specifying database prefixes and multiple databases, see Multiple Databases.

Connect to your Database

You can connect to multiple databases, so these options are not restrictive. For example, you can click New empty Database and then connect your API to multiple databases.

For more information about connecting your API to multiple databases, see Integrate Multiple Databases.

Your database can be in the cloud or on-premise. API Server requires access to your database, whether it be stored in the cloud or within your firewall. Use this procedure to connect to your on-premise databases.

Use the following scenarios to connect to your database when creating new APIs:

  • Example Northwind database (Provided in some Live API Creator configurations). This option illustrates how to create an API using a pre-defined database. The Northwind database operates in conjunction with the Tour, which provides a brief introduction to Live API Creator's functions.
For more information about the Northwind database, see Northwind API Project Sample.
  • Target database. Use this option if you want to create an API for an existing database.
  • JNDI data source. Select this option if your connection information is not stored in API Server's admin database or if you want greater flexibility in leveraging database management system (DBMS)-specific options, such as SSL access and mirroring.

Note: Firewalls require accommodations.

  • New empty database. This option creates an API for a new managed database using API Creator and extended in Data Explorer.
    Prerequisite: Your managed data server must be defined and active.
For more information about creating a new database using API Creator, see Managed Data Background.
  • No database. This option creates an API without an initial database connection. API without databases leverage base API Server for message listening and response generation. For an example, you can download the JSON sample file.
  • A new database using your current tools. In this scenario, you create your database using your current tools then connect to that database when you create your API.
For more information about connecting to a database when creating new APIs, see Create your API Project.

Add a Connection to a Database

  1. Go to the Integrate, Data Sources, Connection tab.
  2. Complete the following fields and save your changes:
    Database name
    The name for your database connection.
    Database prefix
    The prefix for your database connection. If you have more than one active database in your API, the prefix specifies which database to use, for instance when defining rules and resource definitions.
    Important! Choose your database prefix name carefully, since renaming the prefix breaks rules and resource definitions. For more information about how to rename a database prefix, see Integrate Multiple Databases.
    Best Practice: Specify a database prefix using a memorable word like "demo" or "customers".
    Maximum length: 20 characters
    Case Sensitive: Yes
    Database type
    Specifies what type of database you want to use. For more information about which database to use, consult your database administrator or system administrator.
    Values: MySQL, Oracle, SQLServer, AzureSQL, NuoDB (beta), PostgreSQL, Pervasive PSQL (beta), Salesforce, Derby, DB2 for z/OS, DB2 for LUW
    Database URL
    The URL that points to your database. This URL is heavily database-dependent. For more information, see the relevant documentation. The following is an example MySQL database URL:
    jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]
    Schema/Owner name
    The database-dependent name that points to your schema on the database server.
    Case Sensitive:
    • Derby, DB2, Oracle, DB2 for zOS, DB2 for LUW: Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes, for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
    • PostgreSQL: Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (foo). If the value uses mixed case, enclose the name in double quotes, for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
    • SQL Server: Is case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose unusual characters, the value with the correct case, and unusual characters using double-quotes (").
    • MySQL: Its behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and unusual characters using back-ticks (`).
    User name
    The user name under which you access your database. Your database administrator assigns you a user name.
    Case Sensitive:
    • Derby, DB2, Oracle, DB2 for zOS, DB2 for LUW: Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes, for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
    • PostgreSQL: Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (foo). If the value uses mixed case, enclose the name in double quotes, for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").
    • SQL Server: Is case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose unusual characters, the value with the correct case, and unusual characters using double-quotes (").
    • MySQL: Its behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and unusual characters using back-ticks (`).
    Password
    The password for the database user specified in the User name field.
    Active
    Select this checkbox if you want to use this database as the current database for your API. API Creator reads the database metadata for active data source connections and caches it in the API Server admin database. This metadata is shared between nodes.

    Note: When you mark the database connection as active and save your connection settings, API Creator tests the database connection. If API Creator does not reach the database successfully, this checkbox is cleared and the database is marked inactive.
    Log errors
    Select this checkbox if you want to record database-level errors for this data source in the error log. Use the error log for debugging database issues. You can view this error log on the Integrate, Data Sources, Error log tab.

    Note: Selecting this checkbox can impact performance.
    Schema is Editable
    Select this checkbox to designate that you can change any schema of any supported server in your API.
The database connection is added.

Schema as Data Model

API Creator reads your database schema when you create an API and connect to your database. API Creator references the database objects and determines the names (table names, column names, and foreign key names) from the database catalog. You can view the schema on the Integrate, Schema, Tables tab. The following image shows this tab:

API Creator uses the database schema to create resources and the JavaScript object model.

Note: Table references are prefixed with the data source name.

For more information about the JavaScrip object model, see Customize your API.

Foreign Key-based Relationships

API Creator requires references to foreign key relationships to express logic (for example, sum/count and parent references), retrieval joins, Data Explorer support for master/detail, lookups, and automatic joins. Within a relationship, the table containing the primary key on the "one" side. For example, Purchaseorder is a parent to the child called Lineitem.

Two tables in a relational database have a one-to-many relationship. Relationships are discovered by foreign keys that you define in your database. The Logic Sample database includes examples of multiple different kinds of relationships.

Note: Some databases, such as SQL/Server, support foreign keys across schemas. Schema discovery deals with only one schema at a time, so these are not discovered. You can add them with virtual foreign keys.

For more information:

Database Administration

The start-up process creates the Demo, Sample, and Your Database test database. This test database is a blank placeholder.

Configure Your Data Source to Access your Database Administration Web Tool

You manage your schema using your existing tools and procedures. As the schema changes, you must reload it. You can configure your data source to access your database administration web tool using the fields on the Integrate, Data Sources, Details tab.

Manage your Schema using Data Explorer

In addition to your usual database tools, you can create new databases, and their APIs, using API Creator. You can perform many database functions (such as adding tables, columns, and relationships) in a simpler manner using managed databases.

For more information about using managed databases, see Database Creation.

Reload the Schema

When you first create an active data source connection (the Active checkbox is selected on the Integrate, Data Sources, Connection tab), API Creator reads the database metadata and caches it in the API Server admin database. This metadata is shared between nodes. As the schema evolves and you change the schema, you must reload it. When you reload the schema, API Creator throws away the API Server cache, rereads the database metadata, and caches it in the API Server admin database.

On the Integrate, Schema, Tables tab, click Reload Schema.

Important! The time it takes API Creator to rescan the database metadata and update the cache depends on the size of the database and the latency to the database server.

Reload the Browser from the Cache

When you reload the browser from cache, API Creator reloads the browser from the cache (possibly update by other users) by polling the @tables system REST endpoint for your API. This refreshes the local copy of schema from Live API Creator.

On the Integrate, Schema, Tables tab, click the Synch icon.

How to Verify your API Project

The JavaScript object model built using schema discovery is not a code generation process. It always matches your current schema (subject to Schema caching). Changes to your schema can make Rule Base objects (for example, resources) invalid, due to dangling references.

Complete the following process to verify your API:

  1. On the Integrate, Schema, Tables tab, reload the schema by clicking Reload Schema.
  2. On the Create, API Properties, Details tab, verify your API by clicking Verify.
    The resources that refer to objects that no longer exist (for example, because they were deleted or renamed) are identified and logged as issues.
  3. On the Analyze, Issues page, review the issues.
    If errors are detected, the Problems navigation are highlighted. You can click it to see/address the errors. The following image shows the Analyze, Issues page:

Define Virtual Foreign Keys

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

For more information about how to define relationships between databases, see Integrate Multiple Databases.

Create Relationships between Parent and Child Tables

You can create relationships between parent and child tables using the Relationship Editor on the Integrate, Data Sources, Relationships tab. Relationships are useful when creating resources that require 'joins' between tables. API Creator uses the existing relationships to create navigation, tab panels, and parent pick choices.

Note: When you create a relationship, API Creator reloads the schema. The time it takes API Creator to read the metadata depends on the size of the database and the latency to the database server.

Display Sequences

Databases that support sequences display the column and sequence name. The following image image shows the Integrate, Data Sources, Sequences tab:

Extend your Data Model to Include Non-persistent Attributes

You can extend your data model to include attributes not in the schema. You can use such attributes in resources and rules. Just as with conventional systems, you must make design decisions about whether to persist derived data. If you decide to persist attributes, synchronize data with logic.

For more information:

Synchronize Data with Logic

Logic processing interacts with values currently stored in the database.

For more information about strategies to synchronize data with logic, see Synchronize Data with Logic.

Data Sources Best Practices

We recommend explicit foreign key definitions. Define foreign keys by way of validations, including a validation name.

For more information about defining foreign keys by way of validations, see Validation.