JDBC data sources

SOURCE_JDBC() connects FeatureQL to any JDBC-compatible database — PostgreSQL, MySQL, Oracle, SQL Server, and others. This gives your real-time features direct access to relational data without building custom data-fetching services.

Creating a JDBC source

Establish a connection using a standard JDBC connection string:

CREATE OR REPLACE FEATURE FM.DEMO1.POSTGRES_CONN AS
    SOURCE_JDBC(
        'postgresql://featuremesh:featuremesh@host.docker.internal:5433/featuremesh?sslmode=disable'  -- DSN
        WITH (
            tables = ARRAY['customers_view'],  -- Array of tables that can be exposed
            timeout='500ms'
        )
    );

The tables array acts as an allowlist — only the listed tables and views are accessible through this connection. This is both a security measure and a documentation aid: anyone reading the feature definition can see exactly which tables it touches.

Querying with EXTERNAL_COLUMNS()

The most straightforward way to access JDBC data is EXTERNAL_COLUMNS() with a VIEW() reference. This maps database columns directly to feature fields, just like batch data source mappings:

WITH
    CUSTOMER_ID := INPUT(BIGINT),
    POSTGRES_SRC := EXTERNAL_COLUMNS(
      customer_id BIGINT BIND TO CUSTOMER_ID,
      last_order_id BIGINT,
      orders_json VARCHAR
      FROM VIEW(FM.DEMO1.POSTGRES_CONN[customers_view])
    ),
    ORDERS_JSON := POSTGRES_SRC[orders_json],
SELECT
    CUSTOMER_ID := BIND_VALUES(ARRAY[1,2,4]),
    LAST_ORDER_ID := POSTGRES_SRC[last_order_id],
    ORDERS := json_parse_as(ORDERS_JSON, TYPE 'ARRAY(ROW(order_id BIGINT, order_date VARCHAR, price FLOAT)'),
;

The BIND TO CUSTOMER_ID clause links the customer_id column to the input feature, so FeatureMesh knows how to parameterize the query. Fields stored as JSON strings (like orders_json) need explicit parsing with JSON_PARSE_AS() — a limitation of the DataFusion execution engine.

Querying with EXTERNAL_VIEW()

For more control over the SQL sent to the database, use EXTERNAL_VIEW() with an explicit query:

WITH
    CUSTOMER_ID := INPUT(BIGINT),
    POSTGRES_SRC := EXTERNAL_VIEW(
        `SELECT customer_id, last_order_id, orders_json FROM %FM.DEMO1.POSTGRES_CONN[customers_view]`
        ON `SELF.customer_id=%CUSTOMER_ID`
        AS ROW(customer_id BIGINT, last_order_id BIGINT, orders_json VARCHAR)
    ),
    ORDERS_JSON := POSTGRES_SRC[orders_json],
SELECT
    CUSTOMER_ID := BIND_VALUES(ARRAY[1,2,4]),
    LAST_ORDER_ID := POSTGRES_SRC[last_order_id],
    ORDERS := json_parse_as(ORDERS_JSON, TYPE 'ARRAY(ROW(order_id BIGINT, order_date VARCHAR, price FLOAT)'),
;

The %FM.DEMO1.POSTGRES_CONN[customers_view] placeholder resolves to the actual table reference, and %CUSTOMER_ID binds the input parameter. This approach is useful when you need to select specific columns or apply database-side transformations.

Best practices

Reuse connections: Create one SOURCE_JDBC() per database and share it across features. Connection pooling happens automatically.

Be precise with types: Specify exact types in ROW() specifications. Type mismatches between the database and FeatureQL cause runtime errors, not compile-time warnings.

Watch for load: FeatureMesh can generate bursts of concurrent queries when serving many requests. Monitor your database load and consider connection limits, read replicas, or caching layers if needed.

Index your join columns: The columns used in BIND TO or ON clauses become WHERE conditions in the generated SQL. Make sure they're indexed in the source database.