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.

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

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

Ingress for extended driver uploads (Kubernetes only)

If you plan to use an extended connector, you must configure a dedicated ingress so the JDBC driver ZIP file can be uploaded to the Syngen API service. Without this ingress setting, file uploads larger than 1 MB will fail.

Create a file named synthetic-file-upload-ingress.yaml with the content below:

Copy

apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
    name: synthetic-file-upload-ingress
    namespace: dct-services
    annotations:
        nginx.ingress.kubernetes.io/backend-protocol: "HTTPS"
        nginx.ingress.kubernetes.io/proxy-body-size: 1g
spec:
    rules:
    - http:
            paths:
            - path: /dct/v3/synthetic/file-uploads
                pathType: Prefix
                backend:
                    service:
                        name: proxy
                        port:
                            number: 443

Run the following command:
Copy
```
kubectl apply -f synthetic-file-upload-ingress.yaml
```
For other ingress controllers, configure the maximum body size according to the controller's documentation. Without this update, the maximum permitted driver ZIP file size is 1 MB.

Register the JDBC driver

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", 
    ... 
    ... 
  } 
}

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`.