> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need. # Querying The `ArrowFlight` service sends SQL to Amp's query server and decodes the results into JavaScript objects. Use it to read data from datasets: a few rows for a page, or millions for a report. It runs the same SQL as [`ampsql`](/clis/ampsql): [SQL](/querying/sql) describes what Amp's SQL supports. ## Prerequisites * The SDK, [installed](/sdks/typescript#install-the-sdk). * A query server to connect to (see [Connecting](/sdks/typescript/connecting)). The examples use the [Quickstart](/get-started/quickstart)'s Amp and its `acme/eth_mainnet` dataset. ## Run a query `flight.query(sql)` runs a query, and succeeds with its results once they've all arrived: ```ts [typed.ts] import { ArrowFlight } from "@edgeandnode/amp" import { layerTransportGrpc } from "@edgeandnode/amp/arrow-flight/node" import { Effect, Layer, Schema } from "effect" const Block = Schema.Struct({ block_num: Schema.Number, timestamp: Schema.Date, gas_used: Schema.Number, }) const program = Effect.gen(function* () { const flight = yield* ArrowFlight.ArrowFlight const batches = yield* flight.query( `SELECT block_num, timestamp, gas_used FROM "acme/eth_mainnet".blocks ORDER BY block_num LIMIT 3`, { schema: Block, bigIntHandling: "number", dateHandling: "date" }, ) for (const block of batches.flatMap((batch) => batch.data)) { console.log(block.block_num, block.timestamp.toISOString(), block.gas_used) } }) const AmpLayer = ArrowFlight.layer.pipe(Layer.provide(layerTransportGrpc({ baseUrl: "http://localhost:1602" }))) await Effect.runPromise(program.pipe(Effect.provide(AmpLayer))) ``` :::terminal ```bash node typed.ts ``` ``` 24000000 2025-12-13T00:06:11.000Z 57860235 24000001 2025-12-13T00:06:23.000Z 27660983 24000002 2025-12-13T00:06:35.000Z 17602635 ``` ::: The options ask for numbers and dates instead of strings, and `schema` gives the rows a type: `block.timestamp` is a `Date`. The next sections explain both. ## Results Amp sends results in batches of rows, as [Apache Arrow](https://arrow.apache.org) record batches, and the SDK decodes each into a `QueryResult`: * `data`: the batch's rows, each an object with a property per column. * `metadata`: the [block ranges](/sdks/typescript/domain#blockrange) the rows come from, for each chain, and `rangesComplete`, which is `true` once all the rows for those ranges have arrived. `query` returns all the batches, in order. The last one has no rows, and `rangesComplete: true`: Amp sends it to mark the end. Here's the full result of `SELECT block_num, timestamp, hash, base_fee_per_gas FROM "acme/eth_mainnet".blocks WHERE block_num = 24000000`, with `JSON.stringify`: ```json [ { "data": [ { "block_num": "24000000", "timestamp": "2025-12-13T00:06:11.000Z", "hash": "c4zBcW6h8IrawtTiIwrtzuLVzT9l1m1dNZfgXXEKPVA=", "base_fee_per_gas": "20101622" } ], "metadata": { "ranges": [ { "network": "mainnet", "numbers": { "start": 24000000, "end": 24000099 }, "hash": "0x6ed4b3511195052eea47925fe5035f5e3fc528440eed62649ba82058a1b70e40", "prevHash": "0xe8f20171e085c248ac4d60b65ddeb9c1bd679149cc1be9925ad58799dc380b25" } ], "rangesComplete": false } }, { "data": [], "metadata": { "ranges": [ { "network": "mainnet", "numbers": { "start": 24000000, "end": 24000099 }, "hash": "0x6ed4b3511195052eea47925fe5035f5e3fc528440eed62649ba82058a1b70e40", "prevHash": "0xe8f20171e085c248ac4d60b65ddeb9c1bd679149cc1be9925ad58799dc380b25" } ], "rangesComplete": true } } ] ``` The ranges cover the blocks the query read, not just the rows it returned: this query selected one block, of the dataset's 100. To get all the rows, flatten the batches: `batches.flatMap((batch) => batch.data)`. ### How values are decoded Each column's Arrow type decides what its values become. Four options change the defaults: | Arrow type | Becomes | Option | | -------------------------------------------------------------- | ----------------------------------------------- | ---------------- | | 8-, 16-, and 32-bit integers, and floating-point numbers | `number` | | | 64-bit integers (`Int64`, `UInt64`), like `block_num` | `string`, like `"24000000"` | `bigIntHandling` | | Decimals (`Decimal128`, `Decimal256`), like `base_fee_per_gas` | `string`, like `"20101622"`, always | | | Booleans | `boolean` | | | Strings (`Utf8`, `LargeUtf8`) | `string` | | | Binary data (`Binary`, `FixedSizeBinary`), like `hash` | `string`, in base64 | `binaryHandling` | | Dates and timestamps | `string`, in ISO 8601 | `dateHandling` | | Lists | Arrays, with their values decoded the same way | | | Structs | Objects, with their values decoded the same way | | | `NULL` | `null`, or no property | `includeNulls` | 64-bit integers and decimals can be larger than a JavaScript `number` holds exactly (2^53), which is why they're strings by default. The SDK can't read the `Utf8View` string type, which `CAST(… AS VARCHAR)` produces: the query fails with a [`ParseSchemaError`](/sdks/typescript/errors#parseschemaerror). Cast with `arrow_cast(…, 'Utf8')` instead. ### Options `query`'s second argument takes these options, all optional: | Option | Type | Default | Description | | ----------------- | ------------------------------------- | ---------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `bigIntHandling` | `"string"`, `"number"`, or `"bigint"` | `"string"` | What 64-bit integers become. `"number"` loses precision above 2^53. Like `{ bigIntHandling: "bigint" }`, which gives `24000000n`. | | `binaryHandling` | `"base64"`, `"hex"`, or `"array"` | `"base64"` | What binary data becomes: a base64 string, a hexadecimal string without `0x` (`"738cc171…"`), or an array of bytes (`[115, 140, …]`). | | `dateHandling` | `"iso"`, `"timestamp"`, or `"date"` | `"iso"` | What dates and timestamps become: an ISO 8601 string, milliseconds since 1970 (`1765584371000`), or a `Date`. | | `includeNulls` | `boolean` | `true` | Whether `NULL` values are in the rows, as `null`. With `false`, their properties are left out. | | `schema` | A `Schema` | None | Checks each row, and gives the results its type. See [Give rows a type](/sdks/typescript/querying#give-rows-a-type). | | `stream` | `boolean` | None | Runs the query as a [streaming query](/querying/streaming-queries), which never ends. Use [`ProtocolStream`](/sdks/typescript/streaming) instead, which also detects reorgs. Any value turns streaming on, even `false`. | | `resumeWatermark` | `ReadonlyArray` | None | With `stream`, resumes a stream after these block ranges. See [Resume a stream](/sdks/typescript/streaming#resume-a-stream). | ## Give rows a type Without a schema, each row is a `Record`. A schema, from Effect's `Schema` module, describes a row's columns: the SDK checks each row against it, and the results take its type. Describe the values as the options decode them: with `bigIntHandling: "number"` and `dateHandling: "date"`, `block_num` is a `Schema.Number` and `timestamp` a `Schema.Date`, as in the [example above](/sdks/typescript/querying#run-a-query). A schema can also convert values. With the default options, `Schema.NumberFromString` reads `block_num`'s string as a number, and `Schema.DateFromString` reads `timestamp`'s as a `Date`. A row that doesn't match fails the query with a [`ParseRecordBatchError`](/sdks/typescript/errors#parserecordbatcherror). Its `cause` is the schema's error, which says which row and column, and what it expected. Without `bigIntHandling: "number"`, the example fails with: ``` Expected number at [0]["block_num"] ``` ## Stream large results `query` keeps every batch in memory until the last one arrives. For large results, `flight.streamQuery` gives you each batch as it arrives, as a [Stream](/sdks/typescript/effect#streams), so you can process it and let it go: ```ts [stream-results.ts] import { ArrowFlight } from "@edgeandnode/amp" import { layerTransportGrpc } from "@edgeandnode/amp/arrow-flight/node" import { Effect, Layer, Stream } from "effect" const program = Effect.gen(function* () { const flight = yield* ArrowFlight.ArrowFlight let rows = 0 yield* flight.streamQuery(`SELECT tx_hash, gas_used FROM "acme/eth_mainnet".transactions`).pipe( Stream.runForEach((batch) => Effect.sync(() => { rows += batch.data.length const [range] = batch.metadata.ranges console.log(`${batch.data.length} rows, blocks ${range?.numbers.start} to ${range?.numbers.end}`) }), ), ) console.log(`${rows} rows in total`) }) const AmpLayer = ArrowFlight.layer.pipe(Layer.provide(layerTransportGrpc({ baseUrl: "http://localhost:1602" }))) await Effect.runPromise(program.pipe(Effect.provide(AmpLayer))) ``` :::terminal ```bash node stream-results.ts ``` ``` 8192 rows, blocks 24000000 to 24000099 8192 rows, blocks 24000000 to 24000099 769 rows, blocks 24000000 to 24000099 0 rows, blocks 24000000 to 24000099 17153 rows in total ``` ::: This is still a batch query: the stream ends after the last batch. To keep receiving rows as new blocks arrive, use a [streaming query](/sdks/typescript/streaming). ## Explain a query `flight.explain(sql)` shows how Amp would run a query, as a table of plan steps: one row per step, with its `node` (the operation) and `depth` (how deep it sits in the plan tree). With `{ analyze: true }`, Amp runs the query, and each row also has what the step measured, like how many rows it produced and how long it took. Use it to find out why a query is slow. ```ts [explain.ts] import { ArrowFlight } from "@edgeandnode/amp" import { layerTransportGrpc } from "@edgeandnode/amp/arrow-flight/node" import { Effect, Layer } from "effect" const program = Effect.gen(function* () { const flight = yield* ArrowFlight.ArrowFlight const sql = `SELECT block_num, gas_used FROM "acme/eth_mainnet".blocks WHERE gas_used > 30000000` const plan = yield* flight.explain(sql) console.log(plan) const analyzed = yield* flight.explain(sql, { analyze: true }) console.table(analyzed.rows, ["node", "depth", "output_rows", "elapsed_compute_secs"]) }) const AmpLayer = ArrowFlight.layer.pipe(Layer.provide(layerTransportGrpc({ baseUrl: "http://localhost:1602" }))) await Effect.runPromise(program.pipe(Effect.provide(AmpLayer))) ``` :::terminal ```bash node explain.ts ``` ``` { columns: [ 'node', 'depth' ], rows: [ { node: 'Filter', depth: 0 }, { node: 'TableScan', depth: 1 } ] } ┌─────────┬─────────────────────┬───────┬─────────────┬──────────────────────┐ │ (index) │ node │ depth │ output_rows │ elapsed_compute_secs │ ├─────────┼─────────────────────┼───────┼─────────────┼──────────────────────┤ │ 0 │ 'SnapshotTableExec' │ 0 │ 51 │ 0.00000596 │ │ 1 │ 'RepartitionExec' │ 1 │ 51 │ 0.00000596 │ │ 2 │ 'DataSourceExec' │ 2 │ 51 │ 0.00000208 │ └─────────┴─────────────────────┴───────┴─────────────┴──────────────────────┘ ``` ::: The analyzed plan has about 50 columns: `columns` lists them, in the order they first appear. Durations are in seconds, in columns ending with `_secs`. ## Errors A query fails with one of these errors, all in `ArrowFlight.ArrowFlightError`: * [`RpcError`](/sdks/typescript/errors#rpcerror): the query server rejected the query, or couldn't be reached. Its `cause` says why: `[invalid_argument] SQL parse error` for a typo, `table 'amp.acme/missing.blocks' not found` for a table that doesn't exist, `[unauthenticated] Jwt is missing` from a hosted Amp without a token. * [`ParseRecordBatchError`](/sdks/typescript/errors#parserecordbatcherror): a row didn't match your `schema`. * [`ParseSchemaError`](/sdks/typescript/errors#parseschemaerror): the results have a type the SDK can't read. [Errors](/sdks/typescript/errors#arrow-flight-errors) lists them all, with what causes each. ## Reference Import these from `@edgeandnode/amp`'s `ArrowFlight` namespace, or from `@edgeandnode/amp/arrow-flight`. [Connecting](/sdks/typescript/connecting#reference) documents the transport ones: `Transport`, `Interceptors`, `layerInterceptorToken`, and `layerInterceptorBearerAuth`. ### `ArrowFlight` ```ts class ArrowFlight extends Context.Service readonly query: (sql: string, options?: Options) => Effect>, ArrowFlightError> readonly streamQuery: (sql: string, options?: Options) => Stream, ArrowFlightError> readonly explain: (sql: string, options?: { readonly analyze?: boolean | undefined }) => Effect }> ``` The service that runs queries. Get it with `yield* ArrowFlight.ArrowFlight`. #### `query` Runs `sql`, and succeeds with every result batch, in order, once the last has arrived. * **Parameters**: `sql`, the query; `options`, the [options](/sdks/typescript/querying#options). * **Returns**: `Effect>, ArrowFlightError>`, where `Row` is your schema's type, or `Record` without one. * **Fails with**: [`ArrowFlightError`](/sdks/typescript/querying#arrowflighterror). ```ts const batches = yield* flight.query(`SELECT count(*) AS blocks FROM "acme/eth_mainnet".blocks`) // [{ data: [{ blocks: "100" }], metadata: … }, { data: [], metadata: … }] ``` #### `streamQuery` Runs `sql`, and emits each result batch as it arrives. The stream ends after the last batch, unless `options.stream` is set: then it never ends (see [Streaming](/sdks/typescript/streaming)). * **Parameters**: `sql`, the query; `options`, the [options](/sdks/typescript/querying#options). * **Returns**: `Stream, ArrowFlightError>`. * **Fails with**: [`ArrowFlightError`](/sdks/typescript/querying#arrowflighterror). See [Stream large results](/sdks/typescript/querying#stream-large-results) for an example. #### `explain` Runs `EXPLAIN sql`, or `EXPLAIN ANALYZE sql` with `{ analyze: true }`, and returns its plan as a table. * **Parameters**: `sql`, the query to explain; `options.analyze`, `true` to run the query and measure each step (default `false`). * **Returns**: `Effect`. * **Fails with**: [`ArrowFlightError`](/sdks/typescript/querying#arrowflighterror). See [Explain a query](/sdks/typescript/querying#explain-a-query) for an example. #### `client` The Connect client the service sends its requests with, for Arrow Flight's other methods. You don't need it to run queries. ### `layer` ```ts const layer: Layer ``` Builds the `ArrowFlight` service. Needs a [`Transport`](/sdks/typescript/connecting#transport). When the [`Auth`](/sdks/typescript/authentication#auth) service is also provided, each request carries your saved sign-in, for `layerInterceptorBearerAuth` to send. ### `QueryOptions` ```ts interface QueryOptions { readonly schema?: Schema.Codec | undefined readonly bigIntHandling?: "string" | "number" | "bigint" | undefined readonly binaryHandling?: "base64" | "hex" | "array" | undefined readonly dateHandling?: "iso" | "timestamp" | "date" | undefined readonly includeNulls?: boolean | undefined readonly stream?: boolean | undefined readonly resumeWatermark?: ReadonlyArray | undefined } ``` The options of `query` and `streamQuery`, described in [Options](/sdks/typescript/querying#options). ### `QueryResult` ```ts interface QueryResult { readonly data: ReadonlyArray readonly metadata: RecordBatchMetadata } ``` A batch of results: its rows, and the [`RecordBatchMetadata`](/sdks/typescript/domain#recordbatchmetadata) that says which block ranges they come from. ### `ExtractQueryResult` ```ts type ExtractQueryResult = Options extends { readonly schema: Schema.Top } ? QueryResult> : QueryResult> ``` The type of a query's batches, given its options: `QueryResult` of the schema's type, or of `Record`. ### `ExplainResult` ```ts const ExplainResult: Schema.Struct<{ columns: ReadonlyArray; rows: ReadonlyArray }> ``` What `explain` returns. `rows` has one [`ExplainRow`](/sdks/typescript/querying#explainrow) per step of the plan, in the order they appear in the plan tree. `columns` is every column any row has, in the order they first appear: `node`, `depth`, then the steps' properties and measurements. ### `ExplainRow` ```ts const ExplainRow: Schema.Record ``` One step of a plan. It always has `node`, the operation's name, and `depth`, its level in the plan tree (`0` at the top). Its other columns are the step's numeric properties and, with `analyze`, its measurements: durations in seconds, in columns ending with `_secs`, and pairs like `row_groups_pruned_statistics` split into `…_total` and `…_matched`. ### `ExplainCell` ```ts const ExplainCell: Schema.NullOr> ``` A value in an `ExplainRow`: a number when Amp's plan text has one, the text itself otherwise (like `"816.0 B"`), or `null` for a measurement Amp reports as `N/A`. ### `PlanNode` ```ts const PlanNode: Schema.Struct<{ name: string depth: number properties: Record metrics: Record }> ``` A step of a plan, as text, before it becomes an `ExplainRow`. `explain` doesn't return it: it's for code that parses plans itself. ### `ArrowFlightError` ```ts type ArrowFlightError = | RpcError | NoEndpointsError | MultipleEndpointsError | TicketNotFoundError | ParseRecordBatchError | ParseDictionaryBatchError | ParseSchemaError ``` Every error `ArrowFlight` fails with. [Errors](/sdks/typescript/errors#arrow-flight-errors) describes each.