Connect an Application to an External Database

You can connect your C3 AI application to a third-party database to integrate data from external sources. The connection uses a Java Database Connectivity (JDBC) connector. The C3 Agentic AI Platform supports the JDBC connectors listed in the Connector Reference Table.

Copy link to this section

Connect to an external database

To connect your C3 AI application to an external database, use the following C3 AI Types:

• SqlSourceSystem: Represents a connection to an external SQL database system.
• JdbcCredentials: Stores the credentials that establish the connection to the external database.
• SqlSourceCollection: Models an external table in your C3 AI application.
• External: Tells the platform that an external source manages data persistence and schema.

With these Types, complete the following steps:

1. Model the source system
2. Configure credentials to authorize the JDBC connection
3. Model the table that contains the data
4. Model the table schema
5. Validate the configuration

Copy link to this section

Model the source system

Define a source system with the SqlSourceSystem Type. Set the name field to the identifier for the external database system.

Add the following MySourceSystem.json file to the \metadata\SqlSourceSystem directory of your application package:

{
    "name": "MySourceSystem"
}

Copy link to this section

Configure credentials to authorize the JDBC connection

Create an instance of the JdbcCredentials Type to configure the connection to the external source, then set it on the source system. Run the following code in the C3 AI Console of your application.

For a database with a native JDBC driver, such as SQL Server, set the connection URL, database, user name, password, and datastore:

creds = JdbcCredentials.make({
  "url": "<JDBC URL>",              // for example, "jdbc:sqlserver://<host>:1433"
  "database": "<database name>",
  "username": "<user name>",
  "password": "<password>",
  "datastore": DatastoreType.MSSQL
});

SqlSourceSystem.forName("MySourceSystem").setCredentials(creds);

For a connector that uses a CData driver, build the credentials from the driver's connection properties. Put properties that hold secrets, such as passwords and tokens, in secretProperties:

connectorName = "<datastore value>"; // for example, DatastoreType.DATABRICKS or "databricks"
creds = JdbcCredentials.builder().datastore(connectorName)
                                 .properties({"<property name>": "<value>"})
                                 .secretProperties({"<secret property name>": "<secret value>"})
                                 .build();

SqlSourceSystem.forName("MySourceSystem").setCredentials(creds);

Set datastore to a DatastoreType value, such as DatastoreType.DATABRICKS, or to its lowercase string, such as "databricks". The value is case-sensitive: a display name such as "Databricks" does not identify a driver.

CData Driver is a software connector that lets applications work with many data sources through JDBC. Property names depend on the driver. The following lists contain common CData Driver properties that map across JDBC connectors.

Authentication properties:

• userName or user: Username for authentication
• password or pwd: Password for authentication
• token: Access token (for cloud services)
• apiKey: API key for service authentication
• secretKey: Secret key for OAuth flows

Connection properties:

• server or host: Server host name or endpoint
• port: Connection port number
• database or db: Database name
• schema: Schema name
• useSSL or ssl: Enable SSL or TLS encryption
• sslmode: SSL mode (require, prefer, allow, disable)

Performance properties:

• connectionTimeout: Connection timeout in milliseconds
• socketTimeout: Socket timeout in milliseconds
• maxPoolSize: Maximum connection pool size
• minPoolSize: Minimum connection pool size
• prepareThreshold: Prepared statement threshold

Copy link to this section

Model the table that contains the data

To model the external table in your application, create a SqlSourceCollection and set the following fields:

• name: Identifier for the table.
• source: Name of the Type that mixes the External Type and models the schema of the external table. You create this Type in the following step.
• sourceSystem: The SqlSourceSystem Type and its identifier.

For example, to model a table called MyTable, add the following MyTable.json file to the \metadata\SqlSourceCollection directory of your application package:

{
    "name" : "MyTable",
    "source" : "MyTable",
    "sourceSystem" : {
      "type" : "SqlSourceSystem",
      "name" : "MySourceSystem"
    }
}

Copy link to this section

Model the table schema

To model the schema of the table in your application, create an entity Type that mixes the External Type. Define a schema name that matches the name of the table in the external database. The schema name is case sensitive.

For example, add the following MyTable.c3typ file to the \src directory of your application package:

entity type MyTable mixes External, NoSystemCols schema name '<schema_name.table_name>'

Use the inferSourceType() method to infer the C3 AI data Type that matches the structure of each external column. For example, run the following code in the C3 AI Console of your application:

var schema = SqlSourceCollection.forName("MyTable").inferSourceType().declaredFieldTypes;

var myObject = {};
for (let i = 0; i < schema.length; i++) {
        schemaName = schema[i].schemaName;
        myObject[schemaName] = schema[i].valueType.name;
    }

The platform maps external source data types to C3 AI Types according to the following table. See PrimitiveType for more information on data types in the C3 Agentic AI Platform.

To validate the C3 AI data Type inference against the external source data types, run the following code:

SqlSourceCollection.forName("MyTable").connect().columns;

Complete the definition of the MyTable Type that mixes the External Type. The following code contains example fields:

entity type MyTable mixes External, NoSystemCols schema name 'schema_name.table_name' {
  
  id: ~ schema name "Id"

  field1: string schema name "Field1"

  field2: double schema name "Field2"

  field3: datetime schema name "Field3"
}

The id field is required. If the external table has no column named id, add the following annotation to the MyTable Type. The annotation maps the id field to the schema name of another column:

@db(dataTypeOverride="<ID_FIELD_DATA_TYPE>")
id: ~ schema name "<ID_FIELD>"

Copy link to this section

Validate the configuration

After you set the configuration, fetch the MyTable Type data from the external table. Run the following code in the C3 AI Console of your application:

c3Grid(MyTable.fetch());

After successful configuration, the code returns a table for the MyTable Type.

Copy link to this section

See also

To choose and configure a SQL source in Data Fusion, see Configure SQL Sources.