Exporting Database Schema and Table Data with DBX: JSON-RPC Methods and Driver Implementation

DBX enables language-agnostic exporting of database schema and table data through JSON-RPC methods that discover schema objects, generate DDL, and stream large result sets via configurable pagination.

DBX is a database-as-a-service agent that abstracts database operations behind a uniform JSON-RPC interface. Located in the t8y2/dbx repository, this tool implements specific RPC methods in driver files such as agents/drivers/oracle-go/main.go to enable exporting database schema and table data without requiring database-specific client libraries or dialect knowledge.

Schema Discovery and Metadata Export

DBX exposes several RPC methods for discovering database structures. These methods filter system schemas and return normalized metadata suitable for automated export pipelines.

Enumerating Schemas and Tables

The list_schemas RPC method returns visible database schemas while filtering out system schemas. Implemented in agents/drivers/oracle-go/main.go at lines 645-648 within the listSchemas function, this method returns an array of schema names the user can access.

To retrieve tables within a specific schema, the list_tables RPC method (implemented in listTables at lines 649-655) returns an array of objects containing name, table_type, and comment fields for both tables and views. For other database objects like procedures and packages, the list_objects RPC method provides similar functionality via the listObjects implementation.

Extracting Column Definitions

Complete column metadata is available through the get_columns RPC method. The underlying getColumns function (lines 721-748) returns a slice of columnInfo structures describing each column's type, nullability, default values, primary key status, comments, and precision. This enables accurate reconstruction of table schemas even without native DDL generation.

DDL Generation for Schema Export

DBX provides two mechanisms for generating DDL: native database metadata extraction and hand-crafted DDL builders for when native metadata is unavailable.

Native DDL Retrieval

The get_table_ddl RPC method generates CREATE statements for tables and views. Implemented in getTableDDL (lines 1168-1176), this method resolves the object type and calls DBMS_METADATA.GET_DDL for Oracle databases, returning a complete CREATE statement string.

When the object type parameter is empty, DBX auto-detects whether the target is a TABLE or VIEW before generating the appropriate DDL.

Fallback DDL Construction

When native metadata functions are unavailable, DBX falls back to the buildTableDDL internal function (lines 1268-1295). This method walks the column list retrieved from getColumns and constructs a CREATE TABLE statement manually, including primary key constraints and default values.

This fallback ensures that schema export works consistently across different database versions and configurations, even when proprietary metadata packages are restricted.

Streaming Table Data Export

For exporting table contents, DBX implements session-based pagination to handle large datasets without loading entire tables into memory.

Paged Result Sets

The start_table_read RPC method initiates a SELECT query and returns the first page of results along with a session identifier. The startTableRead implementation (lines 1400-1414) accepts parameters for schema, SQL query, maxRows, and pageSize.

Subsequent pages are retrieved using the fetch_table_read_page RPC method, implemented in fetchTableReadPage (lines 1439-1449). Clients pass the sessionId received from the initial call to continue fetching until the has_more flag returns false. After completion, clients should call close_table_read_session to release server resources, though DBX will clean up automatically if omitted.

This streaming approach allows exporting very large tables by processing rows in configurable chunks rather than loading entire datasets into application memory.

Direct Query Execution

For custom extraction logic, the execute_query RPC method (implemented in executeQuery at lines 1575-1602) executes arbitrary SQL statements. For SELECT statements, it returns result rows; for DML operations, it returns affected row counts. The execute_query_page variant supports paginated results for complex queries.

Transactional exports are supported via execute_transaction, which runs multiple statements within a single database transaction, optionally switching schemas first via the executeTransaction function (lines 1415-1436).

Driver Implementation Architecture

All export capabilities follow a common implementation pattern across drivers. The connect RPC establishes a *sql.DB connection using DSN parameters built in buildDSN (lines 78-115). Schema names are normalized via the normalizeSchema helper, which maps empty schema references to the current user's schema (currentSchema).

Query execution flows through queryRows, a thin wrapper around db.QueryContext, followed by scanRow which normalizes driver-specific value types to generic Go any values before JSON encoding. This architecture ensures that adding support for new database types only requires implementing the same RPC interface, as demonstrated by the sibling xugu driver.

Practical Export Examples

Listing all schemas:

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "list_schemas",
  "params": { "visible_schemas": [] }
}

Retrieving table DDL:

{
  "jsonrpc": "2.0",
  "id": 2,
  "method": "get_table_ddl",
  "params": {
    "schema": "HR",
    "table": "EMPLOYEES",
    "object_type": ""
  }
}

Streaming table data:

{
  "jsonrpc": "2.0",
  "id": 3,
  "method": "start_table_read",
  "params": {
    "schema": "HR",
    "sql": "SELECT * FROM EMPLOYEES",
    "maxRows": 0,
    "pageSize": 500
  }
}

Automated schema export script:

#!/usr/bin/env bash
SCHEMAS=$(dbx-cli call list_schemas | jq -r '.result[]')
for SCH in $SCHEMAS; do
  TABLES=$(dbx-cli call list_tables --schema "$SCH" | jq -r '.result[].name')
  for TBL in $TABLES; do
    DDL=$(dbx-cli call get_table_ddl --schema "$SCH" --table "$TBL")
    echo "$DDL" > "${SCH}_${TBL}.sql"
  done
done

Summary

• Schema Discovery: Use list_schemas, list_tables, and get_columns to enumerate database structures and metadata.
• DDL Generation: get_table_ddl provides native DDL with buildTableDDL as a fallback for incomplete metadata.
• Data Streaming: start_table_read and fetch_table_read_page enable memory-efficient export of large tables through session-based pagination.
• Cross-Database Support: Implementation in agents/drivers/oracle-go/main.go and agents/drivers/xugu/main.go demonstrates the uniform interface across different database engines.

Frequently Asked Questions

What is the maximum page size for exporting table data?

DBX does not enforce a hard maximum page size, but the start_table_read method accepts a pageSize parameter to control row counts per request. When maxRows is set to 0, DBX uses a default of 1000 rows. The client can specify larger values, though optimal sizes depend on network latency and memory constraints on the DBX server.

Does DBX support exporting stored procedures and triggers?

Yes, the list_objects RPC method can enumerate stored procedures, packages, triggers, and other schema objects. While get_table_ddl focuses on tables and views, the object listing capabilities include these additional types in the returned metadata, allowing clients to identify objects for subsequent specialized export queries.

How does DBX handle schema name normalization?

The normalizeSchema helper function maps empty schema parameters to the current user's schema (currentSchema). This ensures that RPC calls can use relative references without specifying the full schema path, while absolute references remain unchanged. This normalization occurs consistently across all schema-dependent RPC methods.

Can I export data without using the streaming methods?

Yes, the execute_query RPC method supports direct SQL execution for single-shot exports. For large datasets, however, the streaming methods (start_table_read and fetch_table_read_page) are recommended because they maintain server-side state and prevent memory exhaustion on both the client and DBX server.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}