Foreign Connection Functions¶

The sql_tvf_connect/sql_tvf_disconnect and esql_tvf_connect/esql_tvf_disconnect builtins manage the per-session cache of foreign connections used by sql_tvf, esql_tvf, and the ENGINE = SQL|ESQL external tables. One session caches at most 16 distinct connections.

Description¶

Foreign connections are cached per session and live until an explicit disconnect or the session closes. Handles are derived from the connection config, so reconnecting with the same config reuses the cached entry. All cached connections are closed when the session ends.

Syntax¶

sql_tvf_connect(config)
sql_tvf_disconnect(handle)
esql_tvf_connect(config)
esql_tvf_disconnect(handle)

Arguments¶

Argument	Description
`config`	A JSON string describing the foreign source. SQL connections carry `driver` (`mysql` or `postgres`) and `dsn`; Elasticsearch connections carry `addresses` and optional credentials.
`handle`	The connection handle returned by the matching `_connect` call. It is deterministic per config; passing it to the matching `_disconnect` detaches and closes that connection.

Functions¶

Function	Description
`sql_tvf_connect(config)`	Opens or reuses a SQL connection and returns a handle.
`sql_tvf_disconnect(handle)`	Detaches and closes the SQL connection; returns `true` on success and `false` on a second disconnect of the same handle.
`esql_tvf_connect(config)`	Opens or reuses an Elasticsearch connection and returns a handle.
`esql_tvf_disconnect(handle)`	Detaches and closes the Elasticsearch connection.

The connection limit¶

A session caches at most 16 distinct foreign connections. The 17th distinct config is rejected with an error, and disconnecting a handle frees a slot. Whitespace differences in an otherwise identical config canonicalize to the same handle and do not consume another slot.

Usage Notes¶

Handles are deterministic per config; two concurrent scans with the same config reuse the first entry.
Connections are bound to the session’s CN and are not transferred by connection migration.
Disconnecting a handle that an external table is implicitly using removes it from the cache; the next scan reconnects.

Examples¶

SET @h1 = sql_tvf_connect('{"driver":"mysql","dsn":"user:pass@tcp(host:port)/db"}');
SET @h2 = esql_tvf_connect('{"addresses":["https://es:9200"]}');

SELECT sql_tvf_disconnect(@h1);  -- true
SELECT sql_tvf_disconnect(@h1);  -- false

These examples require reachable foreign sources and are not paste-and-run scripts.

See Also¶