# SQLite

Source: https://zshot-cli.com/reference/formats/sqlite/
Markdown: https://zshot-cli.com/reference/formats/sqlite/index.md
Section: [Reference](https://zshot-cli.com/reference/) › [Formats](https://zshot-cli.com/reference/formats/)
Output type: [`sqlite`](https://zshot-cli.com/reference/flags/common/output-type/) · Extension: [`.sqlite`](https://zshot-cli.com/reference/flags/common/file-name/)
License: [Pro](https://zshot-cli.com/support/licenses/pro)

A page or a crawl as one SQLite database you can search and query. It holds what a WARC holds — every exchange with its body, which page it belongs to, and which responses revisit a stored payload — beside each page's rendered HTML, text and screenshot, and the links a crawl followed.

| Table | Holds |
| --- | --- |
| `crawl` | The capture: the zshot version, target URL and command line |
| `pages` | Each page's URL, title, HTML, `innerText`, screenshot and the page whose link led to it |
| `resources` | Each HTTP exchange, its headers as JSON |
| `payloads` | Each response body once, by digest |
| `page_resources` | Which page loaded each exchange, and whether it was served in full or as a revisit |
| `links` | Each link a crawled page offered, and what the crawl made of it |
| `pages_fts`, `payloads_fts` | Full-text search over page text and text bodies |
| `headers` | Each exchange's headers, one per row, names lowercased |

`--sqlite PATH` writes one beside any other output. [`latest.sql`](/reference/formats/sqlite/schema) documents every column. `/schemas/sqlite/<version>.sql` holds each release's.

## Example

**CLI**

```sh
zshot -f winnie-the-pooh.sqlite \
  --crawl-links \
  --crawl-link-depth 1 \
  https://zshot-cli.com/example_assets/winnie-the-pooh/
```

**JSON** — `POST /` body for the [API](/reference/server/api), Pro license

```json
{
  "url": "https://zshot-cli.com/example_assets/winnie-the-pooh/",
  "output_type": "sqlite",
  "crawl_links": true,
  "crawl_link_depth": 1
}
```

**curl**

```sh
curl -X POST http://127.0.0.1:3000/ \
  -H "Content-Type: application/json" \
  -d '{"url":"https://zshot-cli.com/example_assets/winnie-the-pooh/","output_type":"sqlite","crawl_links":true,"crawl_link_depth":1}' \
  -o winnie-the-pooh.sqlite
```

## Queries

Pages that mention a word, with the matching text:

```sql
SELECT p.url, snippet(pages_fts, 1, '[', ']', '…', 10)
FROM pages_fts JOIN pages p ON p.id = pages_fts.rowid
WHERE pages_fts MATCH 'honey';
```

What one page loaded, and how:

```sql
SELECT resource_url, status, served, revisit_reason, size
FROM page_resources_v WHERE page_url LIKE '%/chapter-1%';
```

Responses a server told caches not to store:

```sql
SELECT r.url, h.value
FROM headers h JOIN resources r ON r.id = h.resource_id
WHERE h.direction = 'response' AND h.name = 'cache-control' AND h.value LIKE '%no-store%';
```

Each revisit, and the page that stored its payload:

```sql
SELECT page_url, resource_url, revisit_reason, original_page_id FROM revisits_v;
```

## Related

Flags:

- [`--sqlite`](https://zshot-cli.com/reference/flags/archive/sqlite/)
- [`--navigator-session-archived`](https://zshot-cli.com/reference/flags/navigator/navigator-session-archived/)
- [`--crawl-links`](https://zshot-cli.com/reference/flags/archive/crawl-links/)
- [`--crawl-url-is-sitemap`](https://zshot-cli.com/reference/flags/archive/crawl-url-is-sitemap/)
- [`--crawl-allow-url`](https://zshot-cli.com/reference/flags/archive/crawl-allow-url/)
- [`--crawl-deny-url`](https://zshot-cli.com/reference/flags/archive/crawl-deny-url/)
- [`--crawl-delay`](https://zshot-cli.com/reference/flags/archive/crawl-delay/)
- [`--crawl-concurrency`](https://zshot-cli.com/reference/flags/archive/crawl-concurrency/)
- [`--crawl-media-sources`](https://zshot-cli.com/reference/flags/archive/crawl-media-sources/)
- [`--crawl-link-depth`](https://zshot-cli.com/reference/flags/archive/crawl-link-depth/)
- [`--crawl-link-selector`](https://zshot-cli.com/reference/flags/archive/crawl-link-selector/)
- [`--crawl-max-links`](https://zshot-cli.com/reference/flags/archive/crawl-max-links/)
- [`--crawl-sitemap-max-depth`](https://zshot-cli.com/reference/flags/archive/crawl-sitemap-max-depth/)
- [`--archive-body-size-limit`](https://zshot-cli.com/reference/flags/archive/archive-body-size-limit/)
- [`--archive-spill-max-bytes`](https://zshot-cli.com/reference/flags/archive/archive-spill-max-bytes/)

Topics:

- [Archive](https://zshot-cli.com/reference/flags/archive/)
- [Crawl a site into a WACZ](https://zshot-cli.com/examples/crawl-links/)
- [latest.sql](https://zshot-cli.com/reference/formats/sqlite/schema/) — SQLite archive schema

## Navigation

- [Examples](https://zshot-cli.com/examples/)
- [Download](https://zshot-cli.com/download/)
- [Purchase](https://zshot-cli.com/purchase/)
- [Reference](https://zshot-cli.com/reference/)
  - [Formats](https://zshot-cli.com/reference/formats/)
    - [PNG](https://zshot-cli.com/reference/formats/png/)
    - [HTML](https://zshot-cli.com/reference/formats/html/)
    - [MHTML](https://zshot-cli.com/reference/formats/mhtml/)
    - [Plain Text](https://zshot-cli.com/reference/formats/txt/)
    - [Download](https://zshot-cli.com/reference/formats/download/)
    - [JPEG](https://zshot-cli.com/reference/formats/jpeg/)
    - [JPEG XL](https://zshot-cli.com/reference/formats/jpegxl/)
    - [WebP](https://zshot-cli.com/reference/formats/webp/)
    - [TIFF](https://zshot-cli.com/reference/formats/tiff/)
    - [BMP](https://zshot-cli.com/reference/formats/bmp/)
    - [PDF](https://zshot-cli.com/reference/formats/pdf/)
    - [Markdown](https://zshot-cli.com/reference/formats/markdown/)
    - [AVIF](https://zshot-cli.com/reference/formats/avif/)
    - [GIF](https://zshot-cli.com/reference/formats/gif/)
    - [APNG](https://zshot-cli.com/reference/formats/apng/)
    - [WebM](https://zshot-cli.com/reference/formats/webm/)
    - [MP4](https://zshot-cli.com/reference/formats/mp4/)
    - [MKV](https://zshot-cli.com/reference/formats/mkv/)
    - [HAR](https://zshot-cli.com/reference/formats/har/)
    - [WARC](https://zshot-cli.com/reference/formats/warc/)
    - [AXTree](https://zshot-cli.com/reference/formats/axtree/)
    - **SQLite**
      - [latest.sql](https://zshot-cli.com/reference/formats/sqlite/schema/)
    - [WACZ](https://zshot-cli.com/reference/formats/wacz/)
    - [LLM JSON](https://zshot-cli.com/reference/formats/llm_json/)
    - [LLM Text](https://zshot-cli.com/reference/formats/llm_text/)
    - [Goal](https://zshot-cli.com/reference/formats/goal/)
    - [Trace](https://zshot-cli.com/reference/formats/trace/)
    - [CPU profile](https://zshot-cli.com/reference/formats/pprof_cpu/)
    - [Memory profile](https://zshot-cli.com/reference/formats/pprof_memory/)
  - [Flags](https://zshot-cli.com/reference/flags/)
  - [Agents](https://zshot-cli.com/reference/agents/)
  - [Configuration](https://zshot-cli.com/reference/configuration/)
  - [Crawling](https://zshot-cli.com/reference/crawling/)
  - [Devices](https://zshot-cli.com/reference/devices/)
  - [Docker](https://zshot-cli.com/reference/docker/)
  - [Exit Codes](https://zshot-cli.com/reference/exit_codes/)
  - [Linux](https://zshot-cli.com/reference/linux/)
  - [LLM Connections](https://zshot-cli.com/reference/llm_connections/)
  - [LLM Formatting](https://zshot-cli.com/reference/llm_formatting/)
  - [Local Inputs](https://zshot-cli.com/reference/inputs/)
  - [Navigator](https://zshot-cli.com/reference/navigator/)
  - [Security](https://zshot-cli.com/reference/security/)
  - [Server](https://zshot-cli.com/reference/server/)
  - [Shell completions](https://zshot-cli.com/reference/shell_completions/)
  - [Storage](https://zshot-cli.com/reference/storage/)
  - [Timeouts](https://zshot-cli.com/reference/timeouts/)
  - [Web Bot Auth](https://zshot-cli.com/reference/web_bot_auth/)
- [Support](https://zshot-cli.com/support/)
- [Changelog](https://zshot-cli.com/changelog/)

Site index: https://zshot-cli.com/llms.txt