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:
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'
)
);| feature_name VARCHAR | status VARCHAR | message VARCHAR |
|---|---|---|
| FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONN | CREATED | Feature 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:
REFRESH SERVING;| FEATURE VARCHAR | KIND VARCHAR | STATUS VARCHAR | MESSAGE VARCHAR | SINCE VARCHAR |
|---|---|---|---|---|
| FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONN | SOURCE_DB | LOADED |
The refresh returns only after the DataFusion catalog can resolve the configured table.
Supported databases
The DSN's scheme picks the database:
| Database | DSN | Reads |
|---|---|---|
| PostgreSQL | postgresql://user@host:5432/db?sslmode=require | By key, children by key, and aggregations computed in the database |
| MySQL, MariaDB | mysql://user@host:3306/db?sslmode=required | By key, children by key, and aggregations computed in the database (integer keys, no time windows yet) |
| DynamoDB | dynamodb://aws?region=eu-west-1 | By 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
awsstands fordynamodb.<region>.amazonaws.com. Any other host is used as is, such as DynamoDB Local onlocalhost:8000(withtls=falseoutside localhost). - Credentials: the DSN's user and password are the access key and secret key (set them with
username = SECRET(...)andpassword = SECRET(...)). Without them, the serving engine reads the standardAWS_ACCESS_KEY_ID,AWS_SECRET_ACCESS_KEYandAWS_SESSION_TOKEN. The region isregion=, elseAWS_REGION, elseus-east-1. - Types: DynamoDB has no schema, so the serving engine samples the table.
SisVARCHAR;NisBIGINTwhen the sampled values are integers,DECIMALotherwise;BOOLisBOOLEAN; maps, lists, sets and binary values are their DynamoDB JSON asVARCHAR. A missing attribute isNULL. Declare the types you expect inEXTERNAL_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:
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]
;| CUSTOMER_ID BIGINT | LAST_ORDER_ID BIGINT | STATUS VARCHAR |
|---|---|---|
| 1 | 101 | active |
| 2 | 202 | inactive |
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:
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]
;| CUSTOMER_ID BIGINT | LAST_ORDER_ID BIGINT | STATUS VARCHAR |
|---|---|---|
| 2 | 303 | active |
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:
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)
;| CUSTOMER_ID BIGINT | N_ORDERS BIGINT | TOTAL DOUBLE | LARGEST DOUBLE |
|---|---|---|---|
| 1 | 2 | 55.5 | 40.0 |
| 2 | 3 | 60.0 | 30.0 |
| 3 | 0 | NULL | NULL |
ORDmaps the orders table, keyed byorder_id. Itscustomer_idcolumn is annotated#CUSTOMERS.OF_CUSTOMERis the condition that ties an order to the customer. Its equality oncustomer_idis 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.
| Call | Reads |
|---|---|
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:
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']
)
);| feature_name VARCHAR | status VARCHAR | message VARCHAR |
|---|---|---|
| FM.TUTORIALS.EXTERNAL_JDBC.UNSET_SECRET_CONN | CREATED | Feature created as not exists |
REFRESH SERVING;| FEATURE VARCHAR | KIND VARCHAR | STATUS VARCHAR | MESSAGE VARCHAR | SINCE VARCHAR |
|---|---|---|---|---|
| FM.TUTORIALS.EXTERNAL_JDBC.POSTGRES_CONN | SOURCE_DB | LOADED | ||
| FM.TUTORIALS.EXTERNAL_JDBC.UNSET_SECRET_CONN | SOURCE_DB | ERROR | datasource: 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:
RETURN PG_PASSWORD := SECRET('env', 'FEATUREMESH_DEMOS_PG_PASSWORD');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.