Create an extended connector

Extended connectors allow you to connect Synthetic Data to relational databases that are not supported natively by uploading a JDBC driver.

This workflow requires driver registration, which is currently supported only through the Synthetic Data APIs.

Use this task to create a new extended connector for a database such as PostgreSQL or IBM DB2.

Prerequisites

Before creating an extended connector:

  • A valid Type 4 JDBC driver for the target database must be packaged as a ZIP file and uploaded. The ZIP file can include the JDBC driver JAR along with any accompanying JARs or other required files, such as a license file (if applicable).

  • Database connection details, including the JDBC URL and credentials, must be available.

  • You must use the fully-qualified driver class name (e.g., org.postgresql.Driver).

Steps

Upload a JDBC driver

Before creating an Extended Connector, upload and register the JDBC driver that will be used to connect to the target database.

  1. Upload the JDBC driver ZIP file using the Synthetic file upload API.

    Copy 
    POST /dct/v3/synthetic/file-uploads 
    Content-Type: multipart/form-data
    file=@my-driver.zip

  2. Review the response.

  3. Note the value of the returned id field. This value is the file_upload_id and is required when registering the JDBC driver.

Register the JDBC driver

  1. Register the uploaded JDBC driver by using the JDBC Driver API.

    Copy 
    POST /dct/v3/synthetic/jdbc-drivers
    Content-Type: application/json
    Copy 
    { 
      "name": "Postgres JDBC Driver-42.7.10", 
      "description": "Payment Gateway Postgres DB", 
      "version": "42.7.10", 
      "driver_class_name": "org.postgresql.Driver", 
      "file_upload_id": "0a2bf14e-48bc-4ea7-a39d-15fdaf87dbe7", 
      "metadata_queries": [ 
        { 
          "query_type": "CHECK_CONSTRAINTS", 
          "sql_query": "SELECT tc.constraint_name, ccu.column_name, cc.check_clause AS search_condition, CASE WHEN tc.is_deferrable = 'NO' THEN 'ENABLED' ELSE 'DISABLED' END AS status, 'VALIDATED' AS validated, tc.is_deferrable AS deferrable FROM information_schema.table_constraints tc LEFT JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.constraint_schema AND ccu.table_name = tc.table_name LEFT JOIN information_schema.check_constraints cc ON cc.constraint_name = tc.constraint_name AND cc.constraint_schema = tc.constraint_schema WHERE tc.constraint_schema = :schema AND tc.table_name = :table AND tc.constraint_type = 'CHECK' AND cc.check_clause NOT LIKE '%IS NOT NULL%'", 
          "description": "Metadata query for check constraints" 
        }, 
        { 
          "query_type": "IDENTITY_COLUMNS", 
          "sql_query": "SELECT c.column_name, c.identity_generation AS generation_type, CONCAT('START WITH: ', c.identity_start, ', INCREMENT BY: ', c.identity_increment) AS identity_options, c.is_nullable AS default_on_null FROM information_schema.columns c WHERE c.table_schema = :schema AND c.table_name = :table AND c.is_identity = 'YES'", 
          "description": "Metadata query for identity columns" 
        }, 
        { 
          "query_type": "TRUNCATE_TABLE", 
          "sql_query": "TRUNCATE TABLE :schema.:table CASCADE", 
          "description": "Metadata query for truncate tables" 
        } 
      ] 
    }

    Response:

    Copy 
    { 
      "id": "9a27428b-3efb-4870-8620-481404ef08ff", 
      "job": { 
        "id": "14a0605ce23446919d1c70a71b2d7820", 
        "status": "COMPLETED", 
        "is_waiting_for_telemetry": false, 
        "target_id": "9a27428b-3efb-4870-8620-481404ef08ff", 
        "target_name": "Postgres JDBC Driver-42.7.10", 
        ... 
        ... 
      } 
    } 

  2. Note the value of the returned id field. This value is the jdbc_driver_id and is required when creating the extended connector.

Configure custom metadata queries

Some metadata, such as identity columns and check constraints, cannot always be discovered through the standard JDBC interface.

To enable Synthetic Data to retrieve this information, provide custom metadata queries during JDBC driver registration.

Supported query types

Query type Purpose Placeholders available
IDENTITY_COLUMNS Detect auto-increment / identity columns in a table :schema, :table
CHECK_CONSTRAINTS Discover check constraints on a table :schema, :table
TRUNCATE_TABLE Custom TRUNCATE syntax (e.g., for PostgreSQL:  TRUNCATE TABLE :table CASCADE) :schema, :table
 
  • Column name lookup is case-insensitive.
  • Use :schema and :table as placeholders wherever the schema and table name need to be substituted in the query — for example, in the WHERE clause.
  • PostgreSQL and DB2 iSeries platforms include built-in queries to retrieve IDENTITY_COLUMNS and CHECK_CONSTRAINTS metadata. If required, custom queries can be provided.
  • Default query for TRUNCATE_TABLE is TRUNCATE TABLE <schema_name>.<table_name>.

Required columns for IDENTITY_COLUMNS custom query

The query must return one row per identity column.

Column name Required Notes
COLUMN_NAME Required Name of the identity column. Rows where this doesn’t match a known column in the table are skipped.
GENERATION_TYPE Required How the identity value is generated. Typical values: ALWAYS, BY DEFAULT, BY DEFAULT ON NULL. SQL NULL is accepted and leaves the field unset.
IDENTITY_OPTIONS Required Comma-separated sequence options in KEY:VALUE form. SQL NULL is accepted (sequence options are left unset). Recognised keys: START WITH, INCREMENT BY, MIN_VALUE, MAX_VALUE. Unknown keys are ignored. Example: START WITH:1, INCREMENT BY:1, MIN_VALUE:1, MAX_VALUE:9999999999.

Required columns for CHECK_CONSTRAINTS custom query

Column name Required Notes
COLUMN_NAME Required Name of the column the constraint applies to. Rows where this doesn’t match a known column in the table are skipped.
CONSTRAINT_NAME Required Name of the check constraint.
SEARCH_CONDITION Required The constraint condition expression (e.g., AGE > 0).
STATUS Optional Whether the constraint is enabled. Accepted values: ENABLED / DISABLED, YES / NO, true / false, SQL NULL.
VALIDATED Optional Whether existing data has been validated against the constraint. Accepted values: VALIDATED / NOT VALIDATED, YES / NO, SQL NULL.
DEFERRABLE Optional Whether the constraint can be deferred until the end of the transaction. Accepted values: DEFERRABLE / NOT DEFERRABLE, YES / NO, SQL NULL.

Create an extended connector

  1. From the top navigation bar, click Synthetic, then click Applications in the left navigation pane.

  2. On the Applications page, click View for the application.

  3. Click the Connectors tab.

  4. In the Connector Mappings section, click + Add Reference Connector.

    The Add Synthetic Connector wizard opens.

  5. On the Details page, provide the following information:

    1. Name: Enter a name to identify the connector.

    2. Description (optional): Enter a description of the connector.

    3. Under Connector Capabilities, ensure Reference is selected. Optionally, select Target if this connector will also be used as a destination for generated synthetic data.

    4. Click Next.

  6. On the Connection Details page:

    1. From Source Type, select the registered JDBC driver that you want to use for the extended connector. The drop-down list displays the names of the JDBC drivers that have been uploaded and registered in the system.

      Ensure that all JDBC driver JAR files are obtained from authentic and trusted sources. Verify that the files have not been modified or tampered with before use.

    2. Under Connection Method, JDBC URL (Advanced) is selected by default. Enter a value in the following fields:

      1. Schema Name — the schema to discover within the source database.

      2. JDBC URL — Enter the full JDBC connection string used to connect to the source database. This option allows you to specify advanced connection parameters that are not available in the basic hostname‑based configuration.

      3. Authentication Method — Select the method used to authenticate to the database (for example, Username/Password).

      4. Username — The database username used for authentication.

      5. Password — The password for the specified username.

    3. (Optional) Click Test Connection to verify the connection details.

    4. Click Next.

  7. On the Summary page, review the connector details.

  8. Click Submit.