In-process

Sometimes the thing you need to protect is not across a network. You have one process, two DuckDB connections, and one of them is running SQL you would rather not fully trust — a query a user typed, a notebook cell, a plugin.

virtual_catalog is a separate extension for exactly that. It exposes selected tables from a source database to a target database in the same process, with the same per-table read / readwrite model as the network server, and no protocol in between.

The handshake

Three plain SQL statements. The target attaches a catalog, the source registers its tables and receives a one-time token, and the target redeems it:

-- 1. TARGET: the catalog the bridge is injected into (must exist first)
ATTACH ':memory:' AS my_bridge (TYPE virtual_catalog);
CREATE SCHEMA IF NOT EXISTS my_bridge.main;

-- 2. SOURCE: declare what is shared, get a token
SELECT vcat_register_source(
    'my_bridge', 'source_catalog', 'main',
    MAP {'users': 'readwrite', 'logs': 'read'},
    MAP {'users': 'id'}                            -- pk overrides, optional
);
-- → 'NONCE:UUID'

-- 3. TARGET: redeem it
SELECT vcat_setup_bridge('my_bridge', '<token>', 'my_bridge', 'main');
-- → 'ok'

The token carries a nonce unique to the loaded extension binary, so mismatched versions are rejected, and it expires after 30 seconds if unused. Permissions are declared by the source and cannot be changed by the target — the trusted side sets the rules, and the untrusted side cannot widen them.

From Python the three statements collapse into one call:

from n6k_server.bridge import bridge

bridge(source, target, "my_bridge",
       source_catalog="memory", source_schema="main",
       permissions={"users": "readwrite", "logs": "read"})

target.sql("SELECT * FROM my_bridge.main.users")

Any binding that can execute SQL can drive it directly — Go, Node, Rust, Java, C++. There are no out-of-band calls.

What the target sees

Tables it was granted, and nothing else. An unlisted table raises a CatalogException — it is not hidden-but-reachable, it is absent.

Reads get the same pushdown as the network path: only requested columns are fetched, and =, !=, <, <=, >, >=, IN, IS NULL and IS NOT NULL are pushed into the query run against the source.

Writes on readwrite tables go through INSERT, UPDATE, and DELETE. Inserts use DuckDB's Appender on the source; updates and deletes collect rowids during the scan and apply them at finalize. Write permission is validated at plan time and re-checked at finalize.

Writable tables need a primary key, discovered from duckdb_constraints() or information_schema, or supplied explicitly. A readwrite table with no usable key errors at setup — the same rule, and the same timing, as the server's boot check.

Introspection

A client rendering a UI usually needs to know what it is allowed to do before it offers a button. One call answers it, and works on a bridge catalog, a plain DuckDB catalog, and a remote TYPE n6k catalog alike:

SELECT * FROM n6k_table_permissions('app');

For a bridge catalog this is an in-memory metadata lookup — no query against the source. n6k_table_describe returns the same capability bits plus columns, defaults, and enum domains for a single table, so a client can render a table editor from one call instead of stitching together DESCRIBE, duckdb_constraints(), and enum_range().

Teardown

SELECT vcat_unregister_bridge('my_bridge', 'main');

Not optional in a long-lived process. The registry holds a reference to the source database, so until this runs, DETACH on either side frees nothing and the bridge id stays claimed.

Transaction semantics

Worth reading before you rely on it:

• Stateless — commit and rollback on the target are no-ops
• No snapshot isolation — the target always sees the source's latest committed data
• Inserts commit on the source independently of the target's transaction

CREATE TABLE and RETURNING are not supported through a bridge entry.

Which one to reach for