SQL framework design and functionality overview

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

An overview of the SQL framework architecture aimed to help you understand how to create SQL connectors for midPoint that operate via the ConnID layer.

SQL framework functionality overview

Connection & pooling

  • HikariCP-based JDBC connection pool with lazy initialization (the pool is created on the first connection request, not at connector startup), per-connector configuration (pool size, timeouts).

  • JDBC driver class is resolved automatically from the jdbcUrl scheme (PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, H2, Oracle).

  • SqlConnection wrapper with auto-close semantics and explicit transaction support (commit, rollback, setAutoCommit).

Automatic schema discovery

  • Discovers tables, views, columns, primary keys, unique constraints, auto-increment flags, nullability and foreign keys via standard JDBC metadata.

  • Includes/excludes tables and views by regular expression patterns, and toggles scanning of tables vs. views separately.

  • System schemas (information_schema, sys, system, xdb) are skipped automatically.

Schema mapping & type mapping

  • Translates discovered (or declared) SQL tables into ConnId object classes and columns into ConnId attributes.

  • Built-in mapping rules adjust the discovered schema: nullable columns are not required, primary keys and auto-increment columns are not updatable/creatable, large types (BLOB/CLOB) are not returned by default, views are read-only.

  • UID detection strategies: single primary key, composite primary key (first column + joined extras), unique column fallback.

  • SQL-to-ConnId value/type mapping for each column type, including datetime and binary handling.

Multitable support

  • Automatic detection of child-table relationships from foreign key constraints and naming conventions (<table>_id columns).

  • Detects single-valued embedded, multi-valued embedded and multi-valued simple-attribute child tables, plus many-to-many junction tables.

  • Detected relationships are exposed as (multi-valued) attributes on the parent object class and resolved at search time.

  • Related rows are writable through the parent object class: create, update and delete manage the child-table and junction-table rows in the same transaction.

ConnId support & operations

  • Built-in handlers for search, create, update, delete per object class, selected based on the object class properties (writable table vs. view).

  • Search: ConnId filters translated to SQL predicates, paginated execution, built-in where {} refinement and fully custom SQL query support; read-only joined object classes (flat LEFT JOIN projections, see joined attributes (read-only)) are filterable and paginated the same way.

  • Write: transactional create/update/delete with ConnId exception mapping (duplicate key → AlreadyExistsException, constraint/data errors → InvalidAttributeValueException, connection problems → ConnectionFailedException).

  • Incremental sync operation based on a sync column with soft-delete (tombstone) support.

Scripting support

  • Custom Groovy DSLs for schema customization (object classes, tables, attributes) and operation handlers (search, write enablement/disablement).

  • Scripting is intended to configure connectors with minimal boilerplate; all built-in behavior remains available without any script.

Declarative YAML support

  • Connector manifest and schema definitions can be expressed in declarative YAML (connector.manifest.yaml, *.native.schema.yaml) in addition to Groovy.

  • YAML-declared schema binds directly into the same live schema builder as Groovy declarations.

Dialect abstraction

  • SQL generation is based on QueryDSL SQL templates auto-detected from the connected database; dialect-specific constructs (pagination, identity keys) are handled by QueryDSL’s dialect-specific SQL template layer (see the QueryDSL documentation).

Development support

  • Development mode exposes the discovered SQL object classes as an additional object class for schema introspection in management tools.

  • Script validation support — a candidate script is compiled and evaluated against all sibling scripts before deployment.

SQL framework is still under development. Incremental sync registration and the declarative relationship script API are not fully wired yet — see the status notes in the individual pages.

SQL connector design

  • Providing out-of-the-box support for common relational database layouts.

  • Flexible enough to handle most of the quirks of real-world database schemas.

  • Not strongly tied to Groovy with potential to replace with a more declarative approach (YAML front-end).

  • Strong leaning on declarative (auto-discovery + small customization) approach instead of scripting.

The base connector framework is based heavily on the Strategy and Delegation patterns:

  • Schema mapping rules — a chain of rules (attribute-level and table-level) transforms raw JDBC metadata into a ConnId schema. Rules are added in a fixed order and each may contribute detected actions that also affect operation handlers (for example: views become read-only, child tables become attributes on the parent).

  • Operation handlers — each object class gets a handler per ConnId operation. Built-in QueryDSL-based handlers are registered by default; handlers configured by scripts take precedence; per-attribute value resolvers are attached for detected relationships.

Groovy builder API

Most of the Groovy scripts are not full-blown scripts, but small declarative blocks evaluated against builder objects:

  • Schema scripts delegate to a per-object-class schema builder (objectClass { …​ }), which in turn delegates to attribute builders for attribute { …​ } blocks.

  • Operation handler scripts delegate to a per-object-class operation support builder with search { …​ }, create { …​ }, update { …​ }, delete { …​ } blocks.

Because the scripts run in a shared Groovy shell, helper functions defined in one script can be reused by later scripts of the same kind (schema or handler).

See the How to develop connectors using the SQL framework section for guidance on building connectors with the Polygon SQL framework.

Was this page helpful?
YES NO
Thanks for your feedback