Database sources

SOURCE_DB() connects FeatureQL to the databases your services already read: PostgreSQL, MySQL and DynamoDB. Real-time features read their rows by key, without a data-fetching service in between.

Creating a database source

Establish a connection with a connection string; its scheme picks the database:

FeatureQL
CREATE OR REPLACE FEATURE FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONN AS
    SOURCE_DB(
        'postgresql://featuremesh@host.docker.internal:25432/featuremesh?sslmode=disable'
        WITH (
            password = SECRET('env', 'FEATUREMESH_DEMOS_PG_PASSWORD'),
            tables=ARRAY['serving_slt_customers', 'serving_slt_customer_orders'],
            timeout='500ms'
        )
    );
Result
feature_name VARCHARstatus VARCHARmessage VARCHAR
FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONNCREATEDFeature created as not exists

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.

The password is not written in the definition: SECRET('env', 'FEATUREMESH_DEMOS_PG_PASSWORD') is read from the serving engine's environment when it connects. See Credentials .

In a local development process, synchronously install the persisted connection before querying it:

FeatureQL
REFRESH SERVING;
Result
FEATURE VARCHARKIND VARCHARSTATUS VARCHARMESSAGE VARCHARSINCE VARCHAR
FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONNSOURCE_DBLOADED

The refresh returns only after the DataFusion catalog can resolve the configured table.

Supported databases

The DSN's scheme picks the database:

DatabaseDSNReads
PostgreSQLpostgresql://user@host:5432/db?sslmode=requireBy key, children by key, and aggregations computed in the database
MySQL, MariaDBmysql://user@host:3306/db?sslmode=requiredBy key, children by key, and aggregations computed in the database (integer keys, no time windows yet)
DynamoDBdynamodb://aws?region=eu-west-1By partition key; aggregations in the serving engine

A MySQL source is declared like a PostgreSQL one:

MYSQL_CONN := SOURCE_DB(
    'mysql://featuremesh@db.internal:3306/shop?sslmode=required'
    WITH (
        password = SECRET('env', 'MYSQL_PASSWORD'),
        tables = ARRAY('orders', 'customers')
    )
)

DynamoDB

A DynamoDB table is read by its partition key: a table without a sort key with BatchGetItem (100 keys a request), a table with one with a Query per key, which returns all the items of the key. A read without keys fails rather than scan the table.

DYNAMO := SOURCE_DB(
    'dynamodb://aws?region=eu-west-1'
    WITH (tables = ARRAY('account_events', 'users'), timeout = '2s')
),
USR := EXTERNAL_COLUMNS(
    user_id BIGINT#USERS BIND TO USER_ID,
    segment VARCHAR
    FROM TABLE(DYNAMO[users])
)
  • The host aws stands for dynamodb.<region>.amazonaws.com. Any other host is used as is, such as DynamoDB Local on localhost:8000 (with tls=false outside localhost).
  • Credentials: the DSN's user and password are the access key and secret key (set them with username = SECRET(...) and password = SECRET(...)). Without them, the serving engine reads the standard AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and AWS_SESSION_TOKEN. The region is region=, else AWS_REGION, else us-east-1.
  • Types: DynamoDB has no schema, so the serving engine samples the table. S is VARCHAR; N is BIGINT when the sampled values are integers, DECIMAL otherwise; BOOL is BOOLEAN; maps, lists, sets and binary values are their DynamoDB JSON as VARCHAR. A missing attribute is NULL. Declare the types you expect in EXTERNAL_COLUMNS().

Querying with EXTERNAL_COLUMNS()

Read a table with EXTERNAL_COLUMNS() and FROM TABLE(source[table]): the source feature, then the table in brackets. This maps database columns directly to feature fields, just like batch data source mappings:

FeatureQL
DEFINE
    CUSTOMER_ID := INPUT(BIGINT),
    POSTGRES_SRC := EXTERNAL_COLUMNS(
        customer_id BIGINT BIND TO CUSTOMER_ID,
        last_order_id BIGINT,
        status VARCHAR
        FROM TABLE(FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONN[serving_slt_customers])
    ),
RETURN
    CUSTOMER_ID := BIND_VALUES(ARRAY[1, 2]),
    LAST_ORDER_ID := POSTGRES_SRC[last_order_id],
    STATUS := POSTGRES_SRC[status]
;
Result
CUSTOMER_ID BIGINTLAST_ORDER_ID BIGINTSTATUS VARCHAR
1101active
2202inactive

The BIND TO CUSTOMER_ID clause links the customer_id column to the input feature, so FeatureMesh knows how to parameterize the query.

The connection reads current database state. Updating customer 2 in PostgreSQL changes the next serving result without recreating the source:

FeatureQL
DEFINE
    CUSTOMER_ID := INPUT(BIGINT),
    POSTGRES_SRC := EXTERNAL_COLUMNS(
        customer_id BIGINT BIND TO CUSTOMER_ID,
        last_order_id BIGINT,
        status VARCHAR
        FROM TABLE(FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONN[serving_slt_customers])
    ),
RETURN
    CUSTOMER_ID := BIND_VALUE(2),
    LAST_ORDER_ID := POSTGRES_SRC[last_order_id],
    STATUS := POSTGRES_SRC[status]
;
Result
CUSTOMER_ID BIGINTLAST_ORDER_ID BIGINTSTATUS VARCHAR
2303active

Reading the children of a key

A customer has many orders. The orders table is keyed by order_id, but a feature of a customer wants the orders whose customer_id is the customer's: a read by a column that is not the table's key, returning several rows per customer. RELATED() aggregates them:

FeatureQL
DEFINE
    CUSTOMERS := ENTITY(),
    ORDERS := ENTITY(),
    CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),
    ORDER_ID := INPUT(BIGINT#ORDERS),
    ORD := EXTERNAL_COLUMNS(
        order_id BIGINT#ORDERS BIND TO ORDER_ID,
        customer_id BIGINT#CUSTOMERS,
        amount DOUBLE
        FROM TABLE(FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONN[serving_slt_customer_orders])
    ),
    OF_CUSTOMER := ORD[customer_id] = CUSTOMER_ID,
RETURN
    CUSTOMER_ID,
    N_ORDERS := RELATED(COUNT() ON OF_CUSTOMER),
    TOTAL := RELATED(SUM(ORD[amount]) ON OF_CUSTOMER),
    LARGEST := RELATED(MAX(ORD[amount]) ON OF_CUSTOMER)
FOR CROSS
    CUSTOMER_ID := BIND_VALUES(ARRAY[1, 2, 3]),
    NESTED ORDER_ID := @BIND_KEYSET(ORD)
;
Result
CUSTOMER_ID BIGINTN_ORDERS BIGINTTOTAL DOUBLELARGEST DOUBLE
1255.540.0
2360.030.0
30NULLNULL

  • ORD maps the orders table, keyed by order_id. Its customer_id column is annotated #CUSTOMERS.
  • OF_CUSTOMER is the condition that ties an order to the customer. Its equality on customer_id is what the source is read by: one query for all the customers of the request, WHERE customer_id IN (...). Index the column.
  • Each RELATED(aggregate ON OF_CUSTOMER) aggregates the orders of each customer. The three share their condition, so the orders are read once.
  • NESTED ORDER_ID := @BIND_KEYSET(ORD) makes the orders a nested domain of each customer: the answer has one row per customer. A prepared statement makes this binding itself (see Aggregations over a key's history ).

Customer 3 has no orders: the count is 0, the sum and the maximum are NULL.

Where aggregations run

In a prepared statement over PostgreSQL, the aggregations and everything they read (the filter, the expressions inside the aggregate) are computed by PostgreSQL: one GROUP BY query per call, one row per key back. Logic over the aggregates, joins with other sources, Redis and HTTP reads run in the serving engine. MySQL and MariaDB do the same for COUNT, COUNT_IF, SUM, MIN, MAX and AVG of DOUBLE values over a table read by an integer key; the call's keys reach the database as one JSON array, so MariaDB needs 10.6 or later. Declare the engine with the source property target (target = 'mysql/mariadb'). AVG of integers, text keys and time windows stay in the serving engine. The source property aggregate_pushdown = FALSE keeps the aggregations in the serving engine, which then reads the rows by key. DynamoDB is always read by key, its aggregations computed in the serving engine.

Aggregations over a key's history shows both on a time window.

Credentials

A source reads its password, or any other property, with SECRET(provider, name [, key]). The registry stores a reference, never the value; the serving engine resolves it each time it connects, so a rotated secret is picked up at the next connection.

CallReads
SECRET('env', 'PG_PASSWORD')The serving engine's environment variable PG_PASSWORD
SECRET('file', '/run/secrets/pg_password')A file's content, without its trailing newline: Docker and Kubernetes secrets
SECRET('file', '/run/secrets/db.json', 'password')The field password of a file holding a JSON object, the shape secret managers often mount

The username and password properties replace the DSN's own user and password, encoded for the DSN, so a password may hold any character:

POSTGRES_CONN := SOURCE_DB(
    'postgresql://db.internal:5432/shop?sslmode=require'
    WITH (
        username = SECRET('file', '/run/secrets/db.json', 'username'),
        password = SECRET('file', '/run/secrets/db.json', 'password'),
        tables = ARRAY('orders')
    )
)

A secret the serving engine cannot read fails its source, not the refresh. Here the environment variable is not set:

FeatureQL
CREATE OR REPLACE FEATURE FM.TUTORIALS.EXTERNAL_JDBC.UNSET_SECRET_CONN AS
    SOURCE_DB(
        'postgresql://featuremesh@host.docker.internal:25432/featuremesh?sslmode=disable'
        WITH (
            password = SECRET('env', 'FEATUREMESH_DOCS_UNSET_PASSWORD'),
            tables=ARRAY['serving_slt_customers']
        )
    );
Result
feature_name VARCHARstatus VARCHARmessage VARCHAR
FM.TUTORIALS.EXTERNAL_JDBC.UNSET_SECRET_CONNCREATEDFeature created as not exists

FeatureQL
REFRESH SERVING;
Result
FEATURE VARCHARKIND VARCHARSTATUS VARCHARMESSAGE VARCHARSINCE VARCHAR
FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONNSOURCE_DBLOADED
FM.TUTORIALS.EXTERNAL_JDBC.UNSET_SECRET_CONNSOURCE_DBERRORdatasource: config parse failed: SECRET('env', 'FEATUREMESH_DOCS_UNSET_PASSWORD'): the environment variable is not set

The source is in ERROR and nothing is served from it; the message names the secret, never a value. The other sources are not affected. Set the variable, restart the serving engine, and refresh.

SECRET() is read only in the properties of a source, where the serving engine resolves it. A query that reads it elsewhere is refused, so a secret never ends up in a result:

FeatureQL
RETURN PG_PASSWORD := SECRET('env', 'FEATUREMESH_DEMOS_PG_PASSWORD');
Result
ERROR UE/SECRET-OUTSIDE-SOURCE

A password written in clear in the DSN or the password property is stored by the registry, so the hosted platform refuses it with SOURCE-INLINE-PASSWORD: keep it in your secrets and reference it with SECRET(). Obvious placeholders still work, with a warning, so prototypes and tests stay easy: an empty value, only x, * or ., a value starting with fake_, test_, dummy_ or example_, a <...> placeholder, an ${VAR} reference, or one of password, secret, changeme, test, example, dummy, fake, demo, postgres, admin, featuremesh, local. The embedded pip engine only warns.

Best practices

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

Be precise with types: Declare exact column types in EXTERNAL_COLUMNS(). 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 clauses and in the equalities of RELATED ... ON conditions become WHERE conditions in the generated SQL. Make sure they're indexed in the source database, with the time column second when a window filters on it.