> **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. # JavaScript Functions Status: Experimental. When SQL and the [built-in functions](/querying/functions) can't express a calculation, a [derived dataset](/datasets/derived-datasets) can define its own functions, in JavaScript. Its tables call them like any other function, and so can your queries. Amp runs them in V8, the JavaScript engine of Chrome and Node.js, with no access to the network, to files, or to the machine running them. ## Prerequisites * [`ampctl`](/clis/ampctl), to register and deploy datasets, and a [connection](/querying/connecting) to a query server. The examples use `ampsql`. * To run the examples, the [example datasets](/querying/connecting#example-datasets). ## Define a function Ethereum sets each block's base fee, the price per unit of gas every transaction pays, from the block before it: the fee goes up by as much as 12.5% when that block used more than half its gas limit, and down when it used less. This example puts that rule in a function, and uses it in a dataset that predicts each block's next base fee. ::::steps ### Write the function The function takes the block's base fee, gas used, and gas limit. They arrive as `BigInt`s, JavaScript's type for integers of any size, so the arithmetic stays exact: ```js [next_base_fee.js] function next_base_fee(base_fee, gas_used, gas_limit) { const target = gas_limit / 2n if (gas_used === target) return base_fee const change = (base_fee * (gas_used > target ? gas_used - target : target - gas_used)) / target / 8n return gas_used > target ? base_fee + (change > 0n ? change : 1n) : base_fee - change } ``` ### Declare it in the manifest Add the function to the dataset's manifest under `functions`, with the types of its arguments and of what it returns, and its code as a string. The table calls it as `self.next_base_fee`: ```json [block_fees.json] { "kind": "manifest", "dependencies": { "eth": "acme/eth_mainnet@1.0.0" }, "tables": { "base_fees": { "input": { "sql": "SELECT block_num, base_fee_per_gas AS base_fee, self.next_base_fee(base_fee_per_gas, gas_used, gas_limit) AS next_base_fee FROM eth.blocks" }, "schema": { "arrow": { "fields": [ { "name": "_block_num", "type": "UInt64", "nullable": false }, { "name": "_ts", "type": { "Timestamp": ["Nanosecond", "+00:00"] }, "nullable": false }, { "name": "block_num", "type": "UInt64", "nullable": false }, { "name": "base_fee", "type": { "Decimal128": [38, 0] }, "nullable": true }, { "name": "next_base_fee", "type": { "Decimal256": [76, 0] }, "nullable": true } ] } } } }, "functions": { "next_base_fee": { "inputTypes": [{ "Decimal128": [38, 0] }, "UInt64", "UInt64"], "outputType": { "Decimal256": [76, 0] }, "source": { "source": "function next_base_fee(base_fee, gas_used, gas_limit) {\n const target = gas_limit / 2n\n if (gas_used === target) return base_fee\n const change = (base_fee * (gas_used > target ? gas_used - target : target - gas_used)) / target / 8n\n return gas_used > target ? base_fee + (change > 0n ? change : 1n) : base_fee - change\n}\n", "filename": "next_base_fee.js" } } } } ``` The function returns a `BigInt`, which Amp stores as a `Decimal256(76, 0)`, so that's its `outputType`, and the type of the `next_base_fee` column (see [Types](/querying/functions/javascript#types)). ### Register and deploy the dataset ```bash ampctl dataset register acme/block_fees ./block_fees.json --tag 1.0.0 ampctl dataset deploy acme/block_fees@1.0.0 --end-block 24000099 ``` Registering checks the function's types, and plans the table's SQL with them, so a wrong type or call fails there. ### Query the table :::terminal ```bash ampsql 'SELECT block_num, base_fee, next_base_fee FROM "acme/block_fees".base_fees ORDER BY block_num LIMIT 4' ``` ```text +-----------+----------+---------------+ | block_num | base_fee | next_base_fee | +-----------+----------+---------------+ | 24000000 | 20101622 | 22435114 | | 24000001 | 22435114 | 22216464 | | 24000002 | 22216464 | 21068858 | | 24000003 | 21068858 | 20001551 | +-----------+----------+---------------+ ``` ::: Each prediction is the next block's base fee: ```sql SELECT f.block_num, f.next_base_fee AS predicted, b.base_fee_per_gas AS actual FROM "acme/block_fees".base_fees f JOIN "acme/eth_mainnet".blocks b ON b.block_num = f.block_num + 1 ORDER BY f.block_num LIMIT 4 ``` ```text [Output] +-----------+-----------+----------+ | block_num | predicted | actual | +-----------+-----------+----------+ | 24000000 | 22435114 | 22435114 | | 24000001 | 22216464 | 22216464 | | 24000002 | 21068858 | 21068858 | | 24000003 | 20001551 | 20001551 | +-----------+-----------+----------+ ``` :::: ## Declare a function A manifest's `functions` maps each function's name to its declaration: | Field | Type | Description | | ----------------- | -------------------- | ----------------------------------------------------------------------------------------------------------------- | | `inputTypes` | array of Arrow types | The type of each argument, in order, or `[]` for a function without arguments. | | `outputType` | Arrow type | The type of what the function returns. It must match what the code returns (see [Return values](/querying/functions/javascript#return-values)). | | `source.source` | string | JavaScript code that defines a function with the same name. It can define other functions and constants too. | | `source.filename` | string | The file name error messages give for the code, like `next_base_fee.js`. | * **Names** start with a letter or `_`, followed by letters, digits, `_`, or `$`. SQL lowercases the names you don't quote, so use lowercase names, like `next_base_fee`. * **Types** are written like the types of a table's schema: a simple type as a string, like `"UInt64"`, `"Utf8"`, or `"Boolean"`, and a type with parameters as an object, like `{ "Decimal128": [38, 0] }`, `{ "FixedSizeBinary": 20 }`, or `{ "Timestamp": ["Nanosecond", "+00:00"] }`. To find a column's type, see the [table schemas](/datasets/tables/evm-rpc), or call `arrow_typeof(column)` in a query. Registering a manifest fails on a type functions can't use: | Message contains | Cause | | --------------------------------------------------------------------- | --------------------------------------------------------------- | | `output uses an unsupported type: type 'Binary' cannot be converted…` | `outputType` is bytes, a list, a map, a dictionary, or a union. | | `Decimal type with scale 2 is not supported` | A decimal type has digits after the decimal point. | | ``unknown variant `uint64` `` | The type's name is misspelled. Names are case-sensitive. | ## Call a function A function's name is qualified by the dataset that declares it, like a table's: | Where | Call it as | | ------------------------------------------------------- | ------------------------------------------------------------------ | | In the tables of the dataset that declares it | `self.next_base_fee(…)` | | In a dataset that has the declaring one as a dependency | Its alias, like `fees.next_base_fee(…)` | | In any query | Its dataset's reference, like `"acme/block_fees".next_base_fee(…)` | Another dataset reaches it through its dependencies, as it does its tables: ```json { "kind": "manifest", "dependencies": { "eth": "acme/eth_mainnet@1.0.0", "fees": "acme/block_fees@1.0.0" }, "tables": { "forecast": { "input": { "sql": "SELECT block_num, fees.next_base_fee(base_fee_per_gas, gas_used, gas_limit) AS next_base_fee FROM eth.blocks" }, "schema": { "arrow": { "fields": [ { "name": "_block_num", "type": "UInt64", "nullable": false }, { "name": "_ts", "type": { "Timestamp": ["Nanosecond", "+00:00"] }, "nullable": false }, { "name": "block_num", "type": "UInt64", "nullable": false }, { "name": "next_base_fee", "type": { "Decimal256": [76, 0] }, "nullable": true } ] } } } } } ``` A query names the dataset as it would to read its tables (see [naming tables](/querying/sql#naming-tables)), or uses an alias from the [`amp-alias` header](/querying/dataset-aliases): ```sql SELECT block_num, "acme/block_fees".next_base_fee(base_fee_per_gas, gas_used, gas_limit) AS next_base_fee FROM "acme/eth_mainnet".blocks ORDER BY block_num DESC LIMIT 1 ``` ```text [Output] +-----------+---------------+ | block_num | next_base_fee | +-----------+---------------+ | 24000099 | 68570764 | +-----------+---------------+ ``` A name without its dataset, like `next_base_fee(…)`, fails with `Invalid function 'next_base_fee'`, even in the tables of the dataset that declares it. Arguments must have exactly the declared types: Amp doesn't convert them. A literal `7` is an `Int64`, so passing it to a `UInt64` argument fails with `coercion from Int64 to the signature Exact(UInt64) failed`, which lists every argument's type when the function takes several. Convert it: `arrow_cast(7, 'UInt64')`. ## Types ### Arguments Amp converts each argument from its Arrow type to a JavaScript value. A `NULL` argument is `null`. | Arrow type | JavaScript value | | --------------------------------------------------------------- | ----------------------------------------------------------------------- | | `Boolean` | A boolean | | `Int8`, `Int16`, `Int32`, `UInt8`, `UInt16`, `UInt32` | A number | | `Float16`, `Float32`, `Float64` | A number | | `Int64`, `UInt64` | A `BigInt` | | `Decimal32`, `Decimal64`, `Decimal128`, `Decimal256`, scale `0` | A `BigInt` | | `Utf8`, `LargeUtf8`, `Utf8View` | A string | | `Binary`, `LargeBinary`, `BinaryView`, `FixedSizeBinary` | A `Uint8Array` of the bytes | | `Timestamp` | A `BigInt` of the time since 1970, in the type's unit, like nanoseconds | | `Date32` | The days since 1970, as a number | | `Date64` | The milliseconds since 1970, as a `BigInt` | | `Struct` | An object, with a property for each field | | `List`, `LargeList`, `FixedSizeList` | An array | | `Map` | An object, with its keys as property names | Because `Int64`, `UInt64`, and integer decimals arrive as `BigInt`s, their arithmetic needs `BigInt` operands, like `gas_limit / 2n`. Mixing them with numbers throws `TypeError: Cannot mix BigInt and other types`. Convert with `Number(value)` when the value fits in a number, below 2⁵³. ### Return values Amp reads the value a function returns as an Arrow type, from what the value is, then checks it against the `outputType`. When they differ, the query fails with `column types must match schema types, expected Float64 but found Int32`. | The function returns | Amp reads it as | | --------------------------------------------------- | ------------------------------------------------------------------------------ | | `true` or `false` | `Boolean` | | A whole number from −2,147,483,648 to 2,147,483,647 | `Int32` | | A whole number from 2,147,483,648 to 4,294,967,295 | `UInt32` | | Any other number | `Float64` | | A string | `Utf8` | | A `BigInt` | `Decimal256(76, 0)`, when it fits in 256 bits | | An object | `Struct`, with a field for each property, in alphabetical order, each nullable | | An array or a typed array | Nothing: the query fails with `Unsupported JS type: Array` | So the type to declare depends on what the code returns: * **Integers**: return a number in the `Int32` range and declare `"Int32"`, or return a `BigInt` and declare `{ "Decimal256": [76, 0] }`. To get another integer type, convert the result in SQL: `arrow_cast(self.f(x), 'UInt64')`. A function can't return an `Int64` or a `UInt64` directly. * **Fractions**: declare `"Float64"` only if the function never returns a whole number. `3 / 2` is `1.5`, a `Float64`, but `4 / 2` is `2`, an `Int32`, which fails. Otherwise, return a string, like `String(a / b)`, declare `"Utf8"`, and convert it in SQL: `CAST(self.f(x) AS DOUBLE)`. * **Objects**: declare a `Struct` with every property, in alphabetical order, each with `"nullable": true`. Each field needs all the keys of an Arrow field: ```json { "Struct": [ { "name": "doubled", "data_type": "Int32", "nullable": true, "dict_id": 0, "dict_is_ordered": false, "metadata": {} }, { "name": "label", "data_type": "Utf8", "nullable": true, "dict_id": 0, "dict_is_ordered": false, "metadata": {} } ] } ``` * **Times and dates**: a function can't return them. Return a number, and convert it in SQL. :::warning A function can't return `null` or `undefined`. A call that does fails the query, with `Inconsistent types in ScalarValue::iter_to_array` or `Expected ScalarValue::Null element`. Return a value of the declared type for every row instead, like `''`, and turn it into `NULL` in SQL if you need to: `NULLIF(self.f(x), '')`. ::: ## How functions run * Amp calls a function once per row. Rows come in batches, and each batch runs in a new JavaScript environment: global variables last from one row to the next within a batch, but not between batches. Don't rely on them. * Functions get JavaScript's standard objects, like `Math`, `JSON`, `BigInt`, `Date`, and `Intl`, but nothing from a browser or Node.js: no `fetch`, `require`, `setTimeout`, `TextDecoder`, or `process`. `console` exists, but what it logs goes nowhere. * Amp runs up to as many batches at once as the machine has CPU cores. * Amp doesn't stop a function that runs too long: one that never returns holds up its query or materialization job. * Keep functions deterministic. `Math.random()` and `Date.now()` work, but a table built with them gets different rows each time it's materialized. ## Errors When a function throws, the query fails with `exception in script`, followed by the error, where in the code it happened, and the stack trace. This function divides `UInt64` arguments, which are `BigInt`s, then multiplies them by a number: ```js [gas_pct.js] function gas_pct(gas_used, gas_limit) { return Math.round((gas_used / gas_limit) * 100) } ``` ```text [Output] exception in script -> Uncaught: TypeError: Cannot mix BigInt and other types, use explicit conversions --> gas_pct.js:2:43-44 return Math.round((gas_used / gas_limit) * 100) ^ Stack trace: TypeError: Cannot mix BigInt and other types, use explicit conversions at gas_pct (gas_pct.js:2:44) ``` In a derived dataset, the error fails the materialization job, which Amp [retries](/managing-amp/jobs/retries), waiting longer each time, until you stop it. To fix the function, register a new version of the dataset, and deploy it. | Message contains | Cause | Fix | | -------------------------------------------------- | --------------------------------------------------------------------- | ----------------------------------------------------------------------------------- | | `Invalid function 'next_base_fee'` | The call doesn't name the function's dataset, or the name is wrong. | Qualify it, like `self.next_base_fee(…)` (see [Call a function](/querying/functions/javascript#call-a-function)). | | `coercion from … to the signature Exact(…) failed` | An argument doesn't have the declared type. | Convert it with `arrow_cast`. | | `function next_base_fee not found` | The code doesn't define a function with the declared name. | Name the JavaScript function after its key in `functions`. | | `exception in script -> Uncaught: SyntaxError` | The code doesn't parse. | Fix the syntax at the line and column given. | | `column types must match schema types` | The function returned another type than its `outputType`. | Change what it returns, or its `outputType` (see [Return values](/querying/functions/javascript#return-values)). | | `Inconsistent types in ScalarValue::iter_to_array` | Rows returned different types, like `Int32` and `Float64`, or `null`. | Return the same type for every row. | | `Expected ScalarValue::Null element` | A row returned `null` or `undefined`. | Return a value of the declared type. | | `Unsupported JS type: Array` | The function returned an array. | Return a string or an object instead. |