SQL schema discovery

Last modified 14 Sep 2026 07:59 UTC
Since 4.11
This functionality is available since version 4.11.
Table of Contents

The SQL framework automatically discovers schemas from the connected database using standard JDBC metadata.

This article is part of the SQL connector development reference and guidance materials. See How to develop connectors using the SQL framework for the section introduction.

Automatic schema discovery

When the connector is initialized, the framework:

  1. Connects to the database using the configured JDBC connection

  2. Retrieves the list of tables (and/or views) via DatabaseMetaData, depending on the scanTables / scanViews configuration

  3. Applies the scanTableFilter / scanViewFilter / scanExcludeTables / scanExcludeViews regular expression filters

  4. Retrieves column metadata (type, size, nullability, primary key, unique, auto-increment) and foreign key constraints for each discovered table

  5. Maps discovered tables to ConnId object classes and columns to ConnId attributes

  6. Applies built-in mapping rules that adjust mutability flags and the UID (see below)

  7. Detects multitable relationships (child tables and junction tables)

System schemas information_schema, sys, system and xdb are skipped automatically.

If both scanTables and scanViews are false, no bulk discovery happens — only the tables explicitly referenced by your schema scripts are looked up (targeted discovery).

SQL to ConnId object class mapping

Each discovered table becomes a ConnId object class; the object class name defaults to the table name. Each column becomes a ConnId attribute; the attribute name defaults to the column name. Table and attribute correlation is case-insensitive, so app_user matches App_User in your scripts.

The SQL-to-ConnId type mapping of a column is resolved from its JDBC type:

SQL type family ConnId Java type Notes

VARCHAR, CHAR, NVARCHAR, CLOB

String

text columns, including the default fallback for unknown types

TINYINT, INTEGER

Integer

SMALLINT columns are exposed as Integer attributes; values are converted between Integer (ConnId side) and Short (SQL side) on both reads and writes

BIGINT

BigInteger

DECIMAL, NUMERIC

BigDecimal

FLOAT, DOUBLE

Double

FLOAT maps Double ⇄ Float

BOOLEAN, BIT

Boolean

DATE

ZonedDateTime

midnight of the column date in the system time zone

TIME

String

formatted LocalTime

TIMESTAMP (incl. with time zone)

ZonedDateTime

BLOB, BINARY, VARBINARY

byte[]

Types used for filter comparison in search are listed in SQL search and filter support.

UID detection

The framework determines which column (or columns) form the object __UID__:

  1. Single primary key — the single PK column becomes UID.

  2. Composite primary key — the first PK column becomes UID and the remaining PK columns are attached as additional UID columns; the composite UID value is the column values joined with a . separator (e.g. tenant-1.account-42).

  3. No primary key — falls back to the first unique column of the table as UID.

  4. No primary key and no unique column — the object class is still created, but without a UID mapping; no create/update/delete handler can be built for it. Exclude such tables with scanExcludeTables unless they are only needed read-only.

You can override the detected UID mapping in your schema script (see SQL schema customization).

UID and NAME are always exposed as String attributes, regardless of the underlying column type — numeric key columns are coerced transparently, and composite UID values are the key column values joined with a . separator (e.g. tenant-1.account-42).

Built-in mapping rules

After discovery, a fixed set of rules refines the schema:

Rule Effect

Nullable columns map as not required

A nullable column produces an attribute with required = false; non-nullable columns become required attributes.

Primary key columns are not updatable

Attributes mapped to primary key columns get updatable = false.

Auto-increment columns are not creatable

Attributes mapped to auto-increment/identity columns get creatable = false and updatable = false; the generated value is read back after create.

Large types are not returned by default

BLOB/CLOB/binary columns produce attributes with returnedByDefault = false, so they do not bloat search results unless explicitly requested.

Views are read-only

Object classes mapped to SQL views get creatable/updatable/removable = false and no write handlers are registered for them; search and incremental sync (which is itself a read-only operation) remain available.

These rules can be overridden in a schema customization script wherever the DSL exposes the corresponding flags — for example per-attribute creatable(false), updatable(false), removable(false) or returnedByDefault(false), or object-class-level readOnly(true).

Verify discovered schema

Set developmentMode to true while developing. The connector then registers an additional object class that lists every discovered SQL object class (with its table and schema protocol data), so you can inspect the discovered schema directly from the management system or the ConnId schema API before finalizing your scripts.

See SQL schema customization for customizing the discovered schema.

Was this page helpful?
YES NO
Thanks for your feedback