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 using 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 credentials to establish connection to external database.
• SqlSourceCollection: Models an external table in your C3 AI application
• External: Informs 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

See the following sections to learn how to complete each step.

Copy link to this section

Model the source system

Define a source system using the SqlSourceSystem Type and set the name field as 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.

For example, run the following code from your application C3 AI Console to configure the JdbcCredentials Type:

    map = {
        "userName":"myName",
        "password": "myPassword"
    };
    connectorName = '<YOUR_CONNECTOR>' // For example, Databricks
    sqlSourceSystemName = 'MySqlSourceSystemName'
    creds = JdbcCredentials.builder().datastore(connectorName)
                                            .properties(map)
                                            .build();
        
    JdbcStore.forName(sqlSourceSystemName).setCredentials(creds, ConfigOverride.APP);
    JdbcStore.forName(sqlSourceSystemName).setExternal(ConfigOverride.APP);

This code example uses the CData Driver authentication properties userName and password. CData Driver is a software connector that enables applications to interact with many data sources using JDBC. The following lists contain commonly used CData Driver properties that you can map across different 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 analyze the external data source, infer the data type, and determine the appropriate C3 AI data Type that matches the structure of the data. For example, run the following code in your application C3 AI Console:

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;
    }

This code 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.

You can also access the external source data types to validate the C3 AI data Type inference by running 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 does not have a column named id, add the following annotation to the MyTable Type to change the schema name for the corresponding id field:

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

Copy link to this section

Validate the configuration

After setting the configuration to connect an external data source to your application, fetch the MyTable Type data from the external table. Run the following code in the application C3 AI Console:

c3Grid(MyTable.fetch());

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