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

> Export database schema and table data using DBX JSON-RPC methods. Discover schema, generate DDL, and stream large results efficiently.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: how-to-guide
- Published: 2026-07-05

---

**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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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:**

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

```

**Retrieving table DDL:**

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

```

**Streaming table data:**

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

```

**Automated schema export script:**

```bash
#!/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`](https://github.com/t8y2/dbx/blob/main/agents/drivers/oracle-go/main.go) and [`agents/drivers/xugu/main.go`](https://github.com/t8y2/dbx/blob/main/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.