# Datasette JSON Extractor (`datamule/datasette-json-extractor`) Actor Point at ANY Datasette instance and pull structured data via its uniform JSON API. Discover every database + table, page a table's rows into a clean dataset, or run a read-only SQL query. Works on any datasette publish site with no per-site scraper. Pay per record. - **URL**: https://apify.com/datamule/datasette-json-extractor.md - **Developed by:** [Datamule](https://apify.com/datamule) (community) - **Categories:** Developer tools - **Stats:** 2 total users, 1 monthly users, 100.0% runs succeeded, 0 bookmarks - **User rating**: No ratings yet ## Pricing from $0.35 / 1,000 record extracteds This Actor is paid per event. You are not charged for the Apify platform usage, but only a fixed price for specific events. Since this Actor supports Apify Store discounts, the price gets lower the higher subscription plan you have. Learn more: https://docs.apify.com/platform/actors/running/actors-in-store#pay-per-event ## What's an Apify Actor? Actors are a software tools running on the Apify platform, for all kinds of web data extraction and automation use cases. In Batch mode, an Actor accepts a well-defined JSON input, performs an action which can take anything from a few seconds to a few hours, and optionally produces a well-defined JSON output, datasets with results, or files in key-value store. In Standby mode, an Actor provides a web server which can be used as a website, API, or an MCP server. Actors are written with capital "A". ## How to integrate an Actor? If asked about integration, you help developers integrate Actors into their projects. You adapt to their stack and deliver integrations that are safe, well-documented, and production-ready. The best way to integrate Actors is as follows. In JavaScript/TypeScript projects, use official [JavaScript/TypeScript client](https://docs.apify.com/api/client/js.md): ```bash npm install apify-client ``` In Python projects, use official [Python client library](https://docs.apify.com/api/client/python.md): ```bash pip install apify-client ``` In shell scripts, use [Apify CLI](https://docs.apify.com/cli/docs.md): ````bash # MacOS / Linux curl -fsSL https://apify.com/install-cli.sh | bash # Windows irm https://apify.com/install-cli.ps1 | iex ```bash In AI frameworks, you might use the [Apify MCP server](https://docs.apify.com/platform/integrations/mcp.md). If your project is in a different language, use the [REST API](https://docs.apify.com/api/v2.md). For usage examples, see the [API](#api) section below. For more details, see Apify documentation as [Markdown index](https://docs.apify.com/llms.txt) and [Markdown full-text](https://docs.apify.com/llms-full.txt). # README ## Datasette JSON Extractor Point this Actor at **any [Datasette](https://datasette.io) instance** and pull its data out through Datasette's uniform JSON API — no per-site scraper, no HTML parsing. Datasette publishes SQLite databases as browsable, queryable websites, and every instance exposes the same JSON endpoints, so one Actor works across all of them: the project's own `datasette.io`, `covid-19.datasettes.com`, the UK Register of Members' Interests, `timezones.datasette.io`, and the thousands of sites published with `datasette publish`. ### Three modes, chosen automatically from your input #### 1. Discovery — map an instance Give it just a **base URL** (and optionally turn on *List databases & tables only*) and it returns **one row per (database, table)** with the column list, primary keys, row count, and the direct table/database URLs — a cheap map of everything the instance serves. ```json { "baseUrl": "https://latest.datasette.io", "listDatabasesOnly": true } ```` #### 2. Rows — page a whole table Add a **database** and **table** and it pages that table into a clean dataset, following Datasette's cursor until the table is exhausted or *Max records* is hit. Each row's own columns are spread to top-level fields (so the dataset mirrors the source table) plus a lossless `raw` object. ```json { "baseUrl": "https://latest.datasette.io", "database": "fixtures", "table": "facetable", "maxRecords": 5000 } ``` #### 3. SQL — run a read-only query Provide a **SQL** string and it runs it against Datasette's read-only SQL API and returns one record per result row. Datasette blocks writes by default; this Actor never attempts them. ```json { "baseUrl": "https://latest.datasette.io", "database": "fixtures", "sql": "select state, count(*) as n from facetable group by state order by n desc" } ``` ### Input | Field | Description | | --- | --- | | `baseUrl` **(required)** | The Datasette instance root, e.g. `https://latest.datasette.io`. Don't include `/-/databases.json` — just the root. A path-mounted instance (`https://host/data`) works too, and if you paste a database or table URL the Actor recovers the root automatically. | | `database` | The database to read. Leave empty in discovery mode to list all; in rows/SQL mode the Actor defaults to the sole database when an instance has just one. | | `table` | The table (or view) to page. Setting this switches to rows mode. | | `sql` | An optional read-only SQL query. Takes precedence over `table`. | | `listDatabasesOnly` | Discovery-only switch — map databases + tables without reading rows. | | `maxRecords` | Max total records to emit (default 1000). Each record is one billable event. | | `pageSize` | Rows requested per page in rows mode (default 1000, server-capped). | | `bearerToken` | Optional bearer token for a private/gated instance. Never logged. | | `extraHeaders` | Optional extra request headers (e.g. an API-gateway key). | ### Output Every record shares a stable set of normalized fields — `_source`, `_database`, `_table`, `_mode`, `_rowIndex`, `primaryKey` — plus mode-specific data: - **Discovery** rows carry `tableName`, `rowCount`, `columns`, `primaryKeys`, `isView`, `tableUrl`, `databaseUrl`. - **Rows / SQL** records spread the source columns to top-level fields, plus a lossless `raw` object of the original row. ### Pricing Pay-per-event: **one `record` charge per emitted row** (a table in discovery mode, a row in rows/SQL mode). You pay only for what you extract. ### Notes - A table with 0 rows returns 0 records and exits cleanly. - A non-Datasette host fails fast with a clear message. - Public instances need no credentials. The `bearerToken` / `extraHeaders` inputs cover gated deployments and are never logged. # Actor input Schema ## `baseUrl` (type: `string`): The root URL of ANY Datasette instance. Examples: https://latest.datasette.io (reference server), https://register-of-members-interests.datasettes.com (UK Register of Members' Interests), https://covid-19.datasettes.com, https://timezones.datasette.io. Do NOT include /-/databases.json — just the instance root. A Datasette mounted under a path prefix (https://host/data) also works; if you paste a database or table URL the actor recovers the instance root automatically. ## `database` (type: `string`): The database to read (the name shown in /-/databases.json, e.g. fixtures on latest.datasette.io or regmem on the Register of Members' Interests). Leave EMPTY in discovery mode to list every database; leave empty in rows/SQL mode when the instance exposes exactly one database (the common single-database deployment) and the actor will default to it. ## `table` (type: `string`): The table (or SQL view) whose rows you want. Set this to switch from discovery to rows mode: the actor pages {baseUrl}/{database}/{table}.json following Datasette's cursor until the table is exhausted or Max records is hit, emitting one record per row (the row's own columns spread to top-level fields plus a lossless raw object). Leave empty to run discovery, or use SQL below for a custom query. ## `sql` (type: `string`): An optional read-only SQL query run against the chosen database via Datasette's SQL API ({database}.json?sql=...). Datasette blocks writes by default; this actor never attempts them. When set, this takes precedence over Table. Example: select country\_name, count(\*) as n from facetable group by country\_name order by n desc. Each returned row becomes one record. ## `listDatabasesOnly` (type: `boolean`): When on, the actor only maps the instance — one row per (database, table) with columns, primary keys, row count and direct URLs — and does not read any table rows. This is the default behaviour when no Table and no SQL are set; the switch is here for clarity. Use it first to discover what an instance exposes, then set a Table or SQL query to extract data. ## `maxRecords` (type: `integer`): Maximum total number of records to emit (rows in rows/SQL mode, (database, table) pairs in discovery mode). Each returned record is one billable event. Default 1000. ## `pageSize` (type: `integer`): How many rows to request per page in rows mode (Datasette's \_size parameter; capped by the server, typically at 1000). Larger pages mean fewer requests. Default 1000. Ignored in discovery and SQL modes. ## `bearerToken` (type: `string`): Optional bearer token for a key-gated / private Datasette instance. Sent as Authorization: Bearer \*\*\* — NOT required for public instances (latest.datasette.io, the datasettes.com family) — leave empty. Never logged. ## `extraHeaders` (type: `object`): Optional extra HTTP headers to send with every request, as a JSON object, e.g. {"x-api-key": "..."} for an instance behind an API gateway. Leave empty for public instances. ## Actor input object example ```json { "baseUrl": "https://latest.datasette.io", "listDatabasesOnly": false, "maxRecords": 1000, "pageSize": 1000 } ``` # Actor output Schema ## `results` (type: `string`): No description # API You can run this Actor programmatically using our API. Below are code examples in JavaScript, Python, and CLI, as well as the OpenAPI specification and MCP server setup. ## JavaScript example ```javascript import { ApifyClient } from 'apify-client'; // Initialize the ApifyClient with your Apify API token // Replace the '' with your token const client = new ApifyClient({ token: '', }); // Prepare Actor input const input = { "baseUrl": "https://latest.datasette.io", "maxRecords": 1000, "pageSize": 1000 }; // Run the Actor and wait for it to finish const run = await client.actor("datamule/datasette-json-extractor").call(input); // Fetch and print Actor results from the run's dataset (if any) console.log('Results from dataset'); console.log(`💾 Check your data here: https://console.apify.com/storage/datasets/${run.defaultDatasetId}`); const { items } = await client.dataset(run.defaultDatasetId).listItems(); items.forEach((item) => { console.dir(item); }); // 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/js/docs ``` ## Python example ```python from apify_client import ApifyClient # Initialize the ApifyClient with your Apify API token # Replace '' with your token. client = ApifyClient("") # Prepare the Actor input run_input = { "baseUrl": "https://latest.datasette.io", "maxRecords": 1000, "pageSize": 1000, } # Run the Actor and wait for it to finish run = client.actor("datamule/datasette-json-extractor").call(run_input=run_input) # Fetch and print Actor results from the run's dataset (if there are any) print("💾 Check your data here: https://console.apify.com/storage/datasets/" + run["defaultDatasetId"]) for item in client.dataset(run["defaultDatasetId"]).iterate_items(): print(item) # 📚 Want to learn more 📖? Go to → https://docs.apify.com/api/client/python/docs/quick-start ``` ## CLI example ```bash echo '{ "baseUrl": "https://latest.datasette.io", "maxRecords": 1000, "pageSize": 1000 }' | apify call datamule/datasette-json-extractor --silent --output-dataset ``` ## MCP server setup ```json { "mcpServers": { "apify": { "command": "npx", "args": [ "mcp-remote", "https://mcp.apify.com/?tools=datamule/datasette-json-extractor", "--header", "Authorization: Bearer " ] } } } ``` ## OpenAPI specification ```json { "openapi": "3.0.1", "info": { "title": "Datasette JSON Extractor", "description": "Point at ANY Datasette instance and pull structured data via its uniform JSON API. Discover every database + table, page a table's rows into a clean dataset, or run a read-only SQL query. Works on any datasette publish site with no per-site scraper. Pay per record.", "version": "0.1", "x-build-id": "QqZDx46bSylNrQ0WV" }, "servers": [ { "url": "https://api.apify.com/v2" } ], "paths": { "/acts/datamule~datasette-json-extractor/run-sync-get-dataset-items": { "post": { "operationId": "run-sync-get-dataset-items-datamule-datasette-json-extractor", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for its completion, and returns Actor's dataset items in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } }, "/acts/datamule~datasette-json-extractor/runs": { "post": { "operationId": "runs-sync-datamule-datasette-json-extractor", "x-openai-isConsequential": false, "summary": "Executes an Actor and returns information about the initiated run in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK", "content": { "application/json": { "schema": { "$ref": "#/components/schemas/runsResponseSchema" } } } } } } }, "/acts/datamule~datasette-json-extractor/run-sync": { "post": { "operationId": "run-sync-datamule-datasette-json-extractor", "x-openai-isConsequential": false, "summary": "Executes an Actor, waits for completion, and returns the OUTPUT from Key-value store in response.", "tags": [ "Run Actor" ], "requestBody": { "required": true, "content": { "application/json": { "schema": { "$ref": "#/components/schemas/inputSchema" } } } }, "parameters": [ { "name": "token", "in": "query", "required": true, "schema": { "type": "string" }, "description": "Enter your Apify token here" } ], "responses": { "200": { "description": "OK" } } } } }, "components": { "schemas": { "inputSchema": { "type": "object", "required": [ "baseUrl" ], "properties": { "baseUrl": { "title": "Datasette instance root URL", "type": "string", "description": "The root URL of ANY Datasette instance. Examples: https://latest.datasette.io (reference server), https://register-of-members-interests.datasettes.com (UK Register of Members' Interests), https://covid-19.datasettes.com, https://timezones.datasette.io. Do NOT include /-/databases.json — just the instance root. A Datasette mounted under a path prefix (https://host/data) also works; if you paste a database or table URL the actor recovers the instance root automatically." }, "database": { "title": "Database", "type": "string", "description": "The database to read (the name shown in /-/databases.json, e.g. fixtures on latest.datasette.io or regmem on the Register of Members' Interests). Leave EMPTY in discovery mode to list every database; leave empty in rows/SQL mode when the instance exposes exactly one database (the common single-database deployment) and the actor will default to it." }, "table": { "title": "Table (rows mode)", "type": "string", "description": "The table (or SQL view) whose rows you want. Set this to switch from discovery to rows mode: the actor pages {baseUrl}/{database}/{table}.json following Datasette's cursor until the table is exhausted or Max records is hit, emitting one record per row (the row's own columns spread to top-level fields plus a lossless raw object). Leave empty to run discovery, or use SQL below for a custom query." }, "sql": { "title": "SQL query (advanced, read-only)", "type": "string", "description": "An optional read-only SQL query run against the chosen database via Datasette's SQL API ({database}.json?sql=...). Datasette blocks writes by default; this actor never attempts them. When set, this takes precedence over Table. Example: select country_name, count(*) as n from facetable group by country_name order by n desc. Each returned row becomes one record." }, "listDatabasesOnly": { "title": "List databases & tables only (discovery)", "type": "boolean", "description": "When on, the actor only maps the instance — one row per (database, table) with columns, primary keys, row count and direct URLs — and does not read any table rows. This is the default behaviour when no Table and no SQL are set; the switch is here for clarity. Use it first to discover what an instance exposes, then set a Table or SQL query to extract data.", "default": false }, "maxRecords": { "title": "Max records (total)", "minimum": 1, "type": "integer", "description": "Maximum total number of records to emit (rows in rows/SQL mode, (database, table) pairs in discovery mode). Each returned record is one billable event. Default 1000.", "default": 1000 }, "pageSize": { "title": "Page size (rows mode)", "minimum": 1, "maximum": 1000, "type": "integer", "description": "How many rows to request per page in rows mode (Datasette's _size parameter; capped by the server, typically at 1000). Larger pages mean fewer requests. Default 1000. Ignored in discovery and SQL modes.", "default": 1000 }, "bearerToken": { "title": "Bearer token (optional)", "type": "string", "description": "Optional bearer token for a key-gated / private Datasette instance. Sent as Authorization: Bearer *** — NOT required for public instances (latest.datasette.io, the datasettes.com family) — leave empty. Never logged." }, "extraHeaders": { "title": "Extra request headers", "type": "object", "description": "Optional extra HTTP headers to send with every request, as a JSON object, e.g. {\"x-api-key\": \"...\"} for an instance behind an API gateway. Leave empty for public instances." } } }, "runsResponseSchema": { "type": "object", "properties": { "data": { "type": "object", "properties": { "id": { "type": "string" }, "actId": { "type": "string" }, "userId": { "type": "string" }, "startedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "finishedAt": { "type": "string", "format": "date-time", "example": "2025-01-08T00:00:00.000Z" }, "status": { "type": "string", "example": "READY" }, "meta": { "type": "object", "properties": { "origin": { "type": "string", "example": "API" }, "userAgent": { "type": "string" } } }, "stats": { "type": "object", "properties": { "inputBodyLen": { "type": "integer", "example": 2000 }, "rebootCount": { "type": "integer", "example": 0 }, "restartCount": { "type": "integer", "example": 0 }, "resurrectCount": { "type": "integer", "example": 0 }, "computeUnits": { "type": "integer", "example": 0 } } }, "options": { "type": "object", "properties": { "build": { "type": "string", "example": "latest" }, "timeoutSecs": { "type": "integer", "example": 300 }, "memoryMbytes": { "type": "integer", "example": 1024 }, "diskMbytes": { "type": "integer", "example": 2048 } } }, "buildId": { "type": "string" }, "defaultKeyValueStoreId": { "type": "string" }, "defaultDatasetId": { "type": "string" }, "defaultRequestQueueId": { "type": "string" }, "buildNumber": { "type": "string", "example": "1.0.0" }, "containerUrl": { "type": "string" }, "usage": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "integer", "example": 1 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } }, "usageTotalUsd": { "type": "number", "example": 0.00005 }, "usageUsd": { "type": "object", "properties": { "ACTOR_COMPUTE_UNITS": { "type": "integer", "example": 0 }, "DATASET_READS": { "type": "integer", "example": 0 }, "DATASET_WRITES": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_READS": { "type": "integer", "example": 0 }, "KEY_VALUE_STORE_WRITES": { "type": "number", "example": 0.00005 }, "KEY_VALUE_STORE_LISTS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_READS": { "type": "integer", "example": 0 }, "REQUEST_QUEUE_WRITES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_INTERNAL_GBYTES": { "type": "integer", "example": 0 }, "DATA_TRANSFER_EXTERNAL_GBYTES": { "type": "integer", "example": 0 }, "PROXY_RESIDENTIAL_TRANSFER_GBYTES": { "type": "integer", "example": 0 }, "PROXY_SERPS": { "type": "integer", "example": 0 } } } } } } } } } } ```