This is the full developer documentation for DB9
# DB9 Documentation
> PostgreSQL-compatible distributed SQL database built on TiKV. Instant provisioning, multi-tenant isolation, and built-in extensions for AI agents.
DB9 gives every agent, user, or CI run its own Postgres-compatible database in under a second. Use the sections below to find your starting point — whether you’re evaluating DB9, building agent workflows, setting up production infrastructure, or integrating with your existing stack.
## Start Here
[Section titled “Start Here”](#start-here)
What is DB9?
What DB9 is, who it’s for, and how it compares to other serverless Postgres options. [Read the overview →](/docs/overview/)
Quick Start
Install the CLI or SDK and create your first database in under a minute. [Get started →](/docs/quickstart/)
Why DB9 for AI Agents
Built-in embeddings, file system, HTTP from SQL, branching, and instant provisioning — why agents choose DB9. [Read more →](/docs/why-db9-for-ai-agents/)
Connect
Connection strings, psql, ORMs, drivers, TLS, and authentication — everything you need to connect. [Connect →](/docs/connect/)
Architecture
TiKV storage, pgwire protocol, SQL execution pipeline, multi-tenant isolation, and the extension system. [Architecture →](/docs/architecture/)
Production Checklist
Authentication, secrets, connection management, branching strategy, observability, and recovery. [Production checklist →](/docs/production-checklist/)
## Build
[Section titled “Build”](#build)
Guides for agent workflows, embeddings, file ingestion, HTTP calls, branching, and scheduling.
Agent Workflows
The full agent lifecycle — provisioning, embeddings, file ingestion, HTTP calls, branching, and scheduling through SQL. [Agent workflows →](/docs/agent-workflows/overview/)
Vector Search
Built-in embeddings with `embedding()`, HNSW indexes, and semantic search — no external embedding API needed. [Vector search →](/docs/extensions/vector/)
File System (fs9)
Store and query files, logs, and artifacts through SQL using the fs9 extension. [fs9 reference →](/docs/extensions/fs9/)
HTTP from SQL
Call external APIs directly from SQL — webhooks, LLM calls, and data enrichment without leaving the database. [HTTP extension →](/docs/extensions/http/)
Scheduled Jobs
Run periodic tasks with pg\_cron — maintenance, data pipelines, and automated workflows. [pg\_cron reference →](/docs/extensions/pg-cron/)
## Platform
[Section titled “Platform”](#platform)
How DB9 provisions, isolates, and manages databases at scale.
Provisioning
Create, manage, and delete databases programmatically — CLI, SDK, or REST API. Fleet patterns and lifecycle states. [Provisioning →](/docs/platform/provisioning/)
Multi-Tenant Patterns
Database-per-user, database-per-app, ephemeral-per-task, and branch-per-preview — choose the right isolation model. [Multi-tenant patterns →](/docs/platform/multi-tenant-patterns/)
## Reference
[Section titled “Reference”](#reference)
CLI Reference
Full reference for the `db9` command-line tool — auth, databases, SQL, filesystem, branching, and cron. [CLI docs →](/docs/cli/)
TypeScript SDK
`get-db9` package — `instantDatabase()`, full client API, credential management, and TypeScript types. [SDK docs →](/docs/sdk/)
SQL Reference
Data types, DDL/DML, built-in functions, transactions, system catalog, and advanced SQL features. [SQL reference →](/docs/sql/)
Extensions
fs9 file system, HTTP client, pg\_cron, vector search, full-text search, hstore, Parquet import, and more. [Extensions →](/docs/extensions/)
# DB9 with Claude Code
> Give Claude Code full database capabilities — create, query, branch, and manage DB9 databases from natural language prompts.
The DB9 skill for Claude Code teaches the agent how to use the `db9` CLI and SQL to create databases, run queries, manage files, create branches, and schedule jobs — all from natural language prompts.
## What the Integration Provides
[Section titled “What the Integration Provides”](#what-the-integration-provides)
Once the DB9 skill is installed, Claude Code can:
* Create and manage databases (`db9 create`, `db9 list`, `db9 delete`)
* Run SQL queries (`db9 db sql`)
* Upload and query files via the filesystem (`db9 fs`)
* Create database branches for safe experimentation (`db9 branch`)
* Schedule recurring SQL jobs (`db9 db cron`)
* Use built-in extensions: vector search, embeddings, HTTP from SQL, full-text search
* Manage authentication and tokens
The skill file is a Markdown document installed to Claude Code’s `skills/` directory. Claude Code automatically reads files from `~/.claude/skills/` (user scope) and `./.claude/skills/` (project scope) as context. The DB9 skill includes CLI syntax, SQL patterns, extension usage, and security guidelines. It also instructs Claude Code to periodically re-read the latest version from `https://db9.ai/skill.md`.
## Prerequisites
[Section titled “Prerequisites”](#prerequisites)
* [Claude Code](https://docs.anthropic.com/en/docs/claude-code) installed and working
* The `db9` CLI installed:
Terminal
```bash
curl -fsSL https://db9.ai/install | sh
```
You do **not** need to be logged in to DB9 before onboarding. The skill installation does not send any tokens or credentials.
## Install the DB9 Skill
[Section titled “Install the DB9 Skill”](#install-the-db9-skill)
The recommended way to install the skill is via `db9 onboard`:
Terminal
```bash
# Interactive — auto-detects Claude Code if installed
db9 onboard
# Non-interactive, Claude Code only
db9 onboard --yes --agent claude
# Install for both user and project scope
db9 onboard --yes --agent claude --scope both
```
### Scope options
[Section titled “Scope options”](#scope-options)
| Scope | Install location | When to use |
| ------------------------ | ------------------------------- | ----------------------------------- |
| `--scope user` (default) | `~/.claude/skills/db9/SKILL.md` | Skill available in all projects |
| `--scope project` | `./.claude/skills/db9/SKILL.md` | Skill scoped to this project only |
| `--scope both` | Both locations | Recommended for active DB9 projects |
### Safety checks
[Section titled “Safety checks”](#safety-checks)
Before making any changes, you can preview what `db9 onboard` will do:
Terminal
```bash
# Show where files would be written (zero changes)
db9 onboard --dry-run --agent claude
# Show resolved target paths
db9 onboard --print-locations --agent claude
```
The onboard command:
* Never sends tokens or credentials anywhere
* Backs up existing skill files before overwriting
* Skips installation if the local version is already newer (use `--force` to override)
* Uses atomic file writes to avoid partial installs
## Verify the Installation
[Section titled “Verify the Installation”](#verify-the-installation)
After installing, verify Claude Code can see the skill:
Terminal
```bash
# Check the skill file exists
cat ~/.claude/skills/db9/SKILL.md | head -5
```
Expected output:
Output
```text
---
name: db9
version: 1.0.0
description: Serverless Postgres for AI agents...
---
```
Then start Claude Code and try a prompt:
```plaintext
Create a DB9 database called "test-app" and show me the connection string.
```
Claude Code should run `db9 create --name test-app --show-secrets` and return the connection details.
## Example Prompts
[Section titled “Example Prompts”](#example-prompts)
Once the skill is active, try these prompts with Claude Code:
### Database management
[Section titled “Database management”](#database-management)
```plaintext
Create a DB9 database for my project and set it as the default.
```
```plaintext
List all my DB9 databases and show which one is the default.
```
### Schema and queries
[Section titled “Schema and queries”](#schema-and-queries)
```plaintext
Create a users table with id, email, name, and created_at columns in my DB9 database.
Then insert three sample users and query them back.
```
```plaintext
Show me the schema of all tables in my DB9 database.
```
### Vector search and embeddings
[Section titled “Vector search and embeddings”](#vector-search-and-embeddings)
```plaintext
Create a documents table with a text column and vector embeddings.
Insert some sample documents and run a semantic similarity search.
```
### File operations
[Section titled “File operations”](#file-operations)
```plaintext
Upload the README.md file to my DB9 database filesystem,
then query its contents using SQL.
```
### Branching
[Section titled “Branching”](#branching)
```plaintext
Create a branch of my database called "experiment",
add a new column to the users table on the branch,
then delete the branch.
```
### Scheduled jobs
[Section titled “Scheduled jobs”](#scheduled-jobs)
```plaintext
Set up a pg_cron job that runs VACUUM on my database every night at 3am.
```
## Update the Skill
[Section titled “Update the Skill”](#update-the-skill)
The skill file is versioned. To update to the latest version:
Terminal
```bash
db9 onboard --yes --agent claude
```
If your local version is newer than the remote (e.g., you customized it), the update is skipped. Use `--force` to overwrite:
Terminal
```bash
db9 onboard --yes --agent claude --force
```
You can also point to a custom skill source:
Terminal
```bash
# From a URL
db9 onboard --agent claude --skill-url https://example.com/custom-skill.md
# From a local file
db9 onboard --agent claude --skill-path ./my-custom-skill.md
```
## Current Limitations
[Section titled “Current Limitations”](#current-limitations)
* **No direct database connection from Claude Code** — Claude Code uses the `db9` CLI to interact with databases, not a direct pgwire connection. All operations go through CLI commands or the REST API.
* **CLI must be authenticated** — Before Claude Code can create or manage databases, you need to have run `db9 login` or `db9 create` (which auto-registers an anonymous account) at least once.
* **Anonymous account limits** — Anonymous accounts are limited to 5 active databases. Run `db9 claim` to upgrade to a verified account and remove this limit.
* **Skill is read-only context** — The skill file provides instructions to Claude Code but does not execute anything. All actual operations run through the `db9` CLI with your normal permissions.
## Next Pages
[Section titled “Next Pages”](#next-pages)
* [Agent Workflows Overview](/docs/agent-workflows/overview/) — how DB9 fits into agent pipelines
* [CLI Reference](/docs/cli/) — complete `db9` command reference
* [Quick Start](/docs/quickstart/) — get started with DB9 in two minutes
* [Extensions](/docs/extensions/) — vector search, HTTP, fs9, pg\_cron, and more
* [Connect](/docs/connect/) — connection strings for ORMs and drivers
# Install DB9 Skills
> Use db9 onboard to install the DB9 skill into Claude Code, OpenAI Codex, OpenCode, and other coding agents.
`db9 onboard` installs or updates the DB9 skill file into supported coding agents. The skill teaches agents how to use the `db9` CLI and SQL for database management, queries, file operations, branching, and scheduling.
## How It Works
[Section titled “How It Works”](#how-it-works)
1. The CLI detects which agents are installed on your system
2. It downloads the latest skill file from `https://db9.ai/skill.md` (or uses an embedded fallback if the download fails)
3. It compares versions and writes `SKILL.md` to each agent’s skills directory
4. The agent reads the skill file as context on future sessions
The onboard command **never sends tokens or credentials**. It only writes a Markdown file to your local filesystem.
## Quick Start
[Section titled “Quick Start”](#quick-start)
Terminal
```bash
# Interactive — auto-detects installed agents and prompts
db9 onboard
# Non-interactive, install for all detected agents
db9 onboard --yes --all
# Install for specific agents
db9 onboard --yes --agent claude --agent codex
```
## Supported Agents
[Section titled “Supported Agents”](#supported-agents)
| Agent | `--agent` value | User scope path | Project scope path | Supports `--scope project`? |
| -------------- | --------------- | ---------------------------------------- | --------------------------------- | --------------------------- |
| Claude Code | `claude` | `~/.claude/skills/db9/SKILL.md` | `./.claude/skills/db9/SKILL.md` | Yes |
| OpenAI Codex | `codex` | `~/.codex/skills/db9/SKILL.md` | — | No (user only) |
| OpenCode | `opencode` | `~/.config/opencode/skills/db9/SKILL.md` | `./.opencode/skills/db9/SKILL.md` | Yes |
| Generic agents | `agents` | `~/.agents/skills/db9/SKILL.md` | `./.agents/skills/db9/SKILL.md` | Yes |
* **Codex** respects the `CODEX_HOME` environment variable. If set, the skill installs to `$CODEX_HOME/skills/db9/SKILL.md` instead of `~/.codex/...`.
* **Generic agents** (`--agent agents`) targets a shared `~/.agents/` directory for agent frameworks that follow this convention.
## Scope
[Section titled “Scope”](#scope)
The `--scope` flag controls where the skill file is written:
| Scope | Behavior |
| ------------------------ | ------------------------------------------------------------------------------------ |
| `--scope user` (default) | Installs to the user’s home directory. Skill is available in all projects. |
| `--scope project` | Installs relative to the current working directory. Skill is scoped to this project. |
| `--scope both` | Installs to both locations (when supported by the agent). |
Terminal
```bash
# User scope (default)
db9 onboard --yes --agent claude
# Project scope
db9 onboard --yes --agent claude --scope project
# Both scopes
db9 onboard --yes --agent claude --scope both
```
> **Codex limitation:** OpenAI Codex currently supports only `--scope user`. Requesting `--scope project --agent codex` explicitly will produce an error. When using `--all --scope project`, Codex is automatically skipped with a warning.
## Auto-Detection
[Section titled “Auto-Detection”](#auto-detection)
When you run `db9 onboard` without `--agent` or `--all`, the CLI detects which agents are installed:
| Agent | Detection method |
| -------------- | ------------------------------------------------------------------- |
| Claude Code | `claude` binary on PATH or `~/.claude/` directory exists |
| OpenAI Codex | `codex` binary on PATH, `$CODEX_HOME` exists, or `~/.codex/` exists |
| OpenCode | `opencode` binary on PATH or `~/.config/opencode/` exists |
| Generic agents | `~/.agents/` or `./.agents/` directory exists |
If no agents are detected, the CLI prints a tip suggesting explicit `--agent` flags.
## Safety and Introspection
[Section titled “Safety and Introspection”](#safety-and-introspection)
### Dry run
[Section titled “Dry run”](#dry-run)
Preview all actions without writing anything:
Terminal
```bash
db9 onboard --dry-run
```
Output shows each target with its planned action (`install`, `update`, or `skip`) and the reason.
### Print locations
[Section titled “Print locations”](#print-locations)
Show resolved file paths without downloading or writing:
Terminal
```bash
db9 onboard --print-locations
db9 onboard --print-locations --agent claude --scope both
```
### Version comparison
[Section titled “Version comparison”](#version-comparison)
The skill file uses semantic versioning in its YAML frontmatter (`version: 1.0.0`). On update:
* If the local version **matches** the remote, the file is skipped (up-to-date)
* If the local version is **older**, the file is updated
* If the local version is **newer** (e.g., you customized it), the update is skipped — use `--force` to override
### Backup
[Section titled “Backup”](#backup)
When updating an existing skill file, the CLI creates a timestamped backup (e.g., `SKILL.md.bak.1710000000000`) before overwriting.
### Atomic writes
[Section titled “Atomic writes”](#atomic-writes)
File writes use a temporary file and atomic rename to prevent partial installs.
## Update and Force
[Section titled “Update and Force”](#update-and-force)
Terminal
```bash
# Update to the latest skill version
db9 onboard --yes --all
# Force overwrite even if local version is newer
db9 onboard --yes --agent claude --force
```
## Custom Skill Source
[Section titled “Custom Skill Source”](#custom-skill-source)
Point to a custom skill file instead of the default `https://db9.ai/skill.md`:
Terminal
```bash
# From a URL
db9 onboard --agent claude --skill-url https://example.com/custom-skill.md
# From a local file
db9 onboard --agent claude --skill-path ./my-custom-skill.md
```
If the remote URL download fails and the default URL was used, the CLI falls back to an embedded copy of the skill bundled into the binary.
## All Flags Reference
[Section titled “All Flags Reference”](#all-flags-reference)
| Flag | Description |
| --------------------- | ----------------------------------------------------------------------------------- |
| `--agent ` | Agent to install for (repeatable). Values: `codex`, `claude`, `opencode`, `agents`. |
| `--all` | Install for all supported agents. |
| `--scope ` | Install scope: `user` (default), `project`, or `both`. |
| `--yes` / `-y` | Skip prompts and choose safe defaults. |
| `--dry-run` | Print intended actions and make zero filesystem changes. |
| `--force` | Overwrite existing skill even when local version is newer. |
| `--print-locations` | Print resolved target locations without writing. |
| `--skill-url ` | Skill source URL (default: `https://db9.ai/skill.md`). |
| `--skill-path ` | Install from a local skill file instead of downloading. |
## What the Skill Teaches
[Section titled “What the Skill Teaches”](#what-the-skill-teaches)
The skill file instructs agents on:
* Database operations (create, list, connect, delete, branch)
* SQL execution and schema management
* Filesystem operations (fs cp, fs sh, fs mount, fs ls)
* User and token management
* Migration and cron job management
* Authentication flows (login, claim, anonymous, adopt)
* Browser SDK integration and publishable key management
The skill content is updated with each CLI release. Run `db9 onboard --yes --all` to get the latest version.
## Current Limitations
[Section titled “Current Limitations”](#current-limitations)
* **Codex is user-scope only** — project-scope support may be added in a future Codex release.
* **No automatic updates** — the skill file includes an instruction for agents to re-read from the URL periodically, but `db9 onboard` itself must be re-run to update the on-disk file.
* **Agent-specific skill directories are a convention** — if an agent framework changes its skills directory path, `db9 onboard` needs a CLI update to match.
## Next Pages
[Section titled “Next Pages”](#next-pages)
* [DB9 with Claude Code](/docs/agent-workflows/claude-code/) — Claude Code-specific setup and example prompts
* [DB9 with OpenAI Codex](/docs/agent-workflows/openai-codex/) — Codex-specific setup and scope constraints
* [Agent Workflows Overview](/docs/agent-workflows/overview/) — how DB9 fits into agent pipelines
* [CLI Reference](/docs/cli/) — complete command reference including `db9 onboard`
# DB9 with OpenAI Codex
> Give OpenAI Codex CLI full database capabilities — create, query, branch, and manage DB9 databases from natural language prompts.
The DB9 skill for OpenAI Codex teaches the agent how to use the `db9` CLI and SQL to create databases, run queries, manage files, create branches, and schedule jobs — all from natural language prompts.
## What the Integration Provides
[Section titled “What the Integration Provides”](#what-the-integration-provides)
Once the DB9 skill is installed, Codex can:
* Create and manage databases (`db9 create`, `db9 list`, `db9 delete`)
* Run SQL queries (`db9 db sql`)
* Upload and query files via the filesystem (`db9 fs`)
* Create database branches for safe experimentation (`db9 branch`)
* Schedule recurring SQL jobs (`db9 db cron`)
* Use built-in extensions: vector search, embeddings, HTTP from SQL, full-text search
* Manage authentication and tokens
The skill file is a Markdown document installed to Codex’s `skills/` directory. It includes CLI syntax, SQL patterns, extension usage, and security guidelines.
## Prerequisites
[Section titled “Prerequisites”](#prerequisites)
* [OpenAI Codex CLI](https://github.com/openai/codex) installed and working
* The `db9` CLI installed:
Terminal
```bash
curl -fsSL https://db9.ai/install | sh
```
You do **not** need to be logged in to DB9 before onboarding. The skill installation does not send any tokens or credentials.
## Install the DB9 Skill
[Section titled “Install the DB9 Skill”](#install-the-db9-skill)
The recommended way to install the skill is via `db9 onboard`:
Terminal
```bash
# Interactive — auto-detects Codex if installed
db9 onboard
# Non-interactive, Codex only
db9 onboard --yes --agent codex
# Install for all detected agents at once
db9 onboard --yes --all
```
### Scope
[Section titled “Scope”](#scope)
Codex currently supports **user scope only**. The skill is installed to:
```plaintext
~/.codex/skills/db9/SKILL.md
```
If the `CODEX_HOME` environment variable is set, the skill installs to `$CODEX_HOME/skills/db9/SKILL.md` instead.
> **Note:** `--scope project` is not supported for Codex. If you pass `--scope project --agent codex`, the CLI will return an error. When using `--all --scope project`, Codex is automatically skipped with a warning.
### Safety checks
[Section titled “Safety checks”](#safety-checks)
Before making any changes, you can preview what `db9 onboard` will do:
Terminal
```bash
# Show where files would be written (zero changes)
db9 onboard --dry-run --agent codex
# Show resolved target paths
db9 onboard --print-locations --agent codex
```
The onboard command:
* Never sends tokens or credentials anywhere
* Backs up existing skill files before overwriting
* Skips installation if the local version is already newer (use `--force` to override)
* Uses atomic file writes to avoid partial installs
## Verify the Installation
[Section titled “Verify the Installation”](#verify-the-installation)
After installing, verify the skill file exists:
Terminal
```bash
cat ~/.codex/skills/db9/SKILL.md | head -5
```
Expected output:
Output
```text
---
name: db9
version: 1.0.0
description: Serverless Postgres for AI agents...
---
```
Then start Codex and try a prompt:
```plaintext
Create a DB9 database called "test-app" and show me the connection string.
```
Codex should run `db9 create --name test-app --show-secrets` and return the connection details.
## Example Prompts
[Section titled “Example Prompts”](#example-prompts)
Once the skill is active, try these prompts with Codex:
### Database management
[Section titled “Database management”](#database-management)
```plaintext
Create a DB9 database for my project and set it as the default.
```
```plaintext
List all my DB9 databases and delete any that start with "test-".
```
### Schema and queries
[Section titled “Schema and queries”](#schema-and-queries)
```plaintext
Create a users table with id, email, name, and created_at columns in my DB9 database.
Then insert three sample users and query them back.
```
### Vector search and embeddings
[Section titled “Vector search and embeddings”](#vector-search-and-embeddings)
```plaintext
Create a documents table with a text column and vector embeddings.
Insert some sample documents and run a semantic similarity search.
```
### File operations
[Section titled “File operations”](#file-operations)
```plaintext
Upload the README.md file to my DB9 database filesystem,
then query its contents using SQL.
```
### Branching
[Section titled “Branching”](#branching)
```plaintext
Create a branch of my database called "experiment",
add a new column to the users table on the branch,
then delete the branch.
```
## Update the Skill
[Section titled “Update the Skill”](#update-the-skill)
The skill file is versioned. To update to the latest version:
Terminal
```bash
db9 onboard --yes --agent codex
```
If your local version is newer than the remote (e.g., you customized it), the update is skipped. Use `--force` to overwrite:
Terminal
```bash
db9 onboard --yes --agent codex --force
```
## Current Limitations
[Section titled “Current Limitations”](#current-limitations)
* **User scope only** — Codex does not currently support project-scoped skills. The skill is always installed to `~/.codex/skills/db9/SKILL.md` (or `$CODEX_HOME`).
* **No direct database connection** — Codex uses the `db9` CLI, not a direct pgwire connection. All operations go through CLI commands or the REST API.
* **CLI must be authenticated** — Before Codex can create or manage databases, you need to have run `db9 login` or `db9 create` (which auto-registers an anonymous account) at least once.
* **Anonymous account limits** — Anonymous accounts are limited to 5 active databases. Run `db9 claim` to upgrade to a verified account and remove this limit.
* **Skill is read-only context** — The skill file provides instructions to Codex but does not execute anything. All actual operations run through the `db9` CLI with your normal permissions.
## Next Pages
[Section titled “Next Pages”](#next-pages)
* [Agent Workflows Overview](/docs/agent-workflows/overview/) — how DB9 fits into agent pipelines
* [DB9 with Claude Code](/docs/agent-workflows/claude-code/) — same integration for Claude Code
* [CLI Reference](/docs/cli/) — complete `db9` command reference
* [Quick Start](/docs/quickstart/) — get started with DB9 in two minutes
* [Extensions](/docs/extensions/) — vector search, HTTP, fs9, pg\_cron, and more
# Agent Workflows
> How AI agents use DB9 as a programmable backend — provisioning, storing, searching, calling APIs, branching, and scheduling through standard SQL.
DB9 gives AI agents a complete backend through the PostgreSQL wire protocol. Instead of stitching together separate services for storage, embeddings, files, HTTP, and scheduling, agents do all of it in SQL.
This page is the starting point for building agent workflows on DB9. It maps common agent tasks to the DB9 capabilities that solve them, shows how those capabilities compose, and links to deeper guides.
## Who should read this
[Section titled “Who should read this”](#who-should-read-this)
* **Agent developers** building assistants, copilots, or autonomous agents that need persistent state.
* **Platform engineers** creating agent infrastructure where each agent, user, or task gets its own database.
* **Teams already using DB9** who want to move beyond basic storage into full agent automation.
If you’re still deciding whether DB9 is the right fit, start with [Why DB9 for AI Agents](/docs/why-db9-for-ai-agents/).
## The agent lifecycle in DB9
[Section titled “The agent lifecycle in DB9”](#the-agent-lifecycle-in-db9)
Most agent workflows follow a predictable lifecycle. DB9 has a built-in primitive for each stage:
| Agent lifecycle stage | DB9 primitive | How to access |
| ------------------------------- | ---------------------------------------- | ------------------- |
| **Provision** a workspace | `instantDatabase()` or `db9 create` | SDK, CLI |
| **Store** structured state | Standard SQL tables | Any Postgres client |
| **Search** semantically | `embedding()` + pgvector operators | SQL |
| **Ingest** files and artifacts | fs9 functions and table source | SQL, CLI, SDK |
| **Call** external APIs | `http_get()`, `http_post()`, and friends | SQL |
| **Branch** for safe experiments | `db9 branch create` | CLI, SDK |
| **Schedule** recurring work | pg\_cron | SQL, CLI |
| **Onboard** agent tooling | `db9 onboard` | CLI |
Each primitive is accessible through standard SQL or the PostgreSQL wire protocol. Agents don’t need a proprietary SDK to use them — any Postgres driver works.
## Provision: one database per agent, user, or task
[Section titled “Provision: one database per agent, user, or task”](#provision-one-database-per-agent-user-or-task)
Agent systems typically need to create databases on demand.
No signup required for agents
DB9 provisions a database in under a second with no signup required for anonymous use. Agents can create databases autonomously without any pre-configured credentials.
**From the TypeScript SDK:**
TypeScript
```typescript
import { instantDatabase } from 'get-db9';
const db = await instantDatabase({
name: 'agent-session-42',
seed: `
CREATE TABLE memory (id SERIAL, key TEXT, value JSONB);
CREATE TABLE artifacts (id SERIAL, path TEXT, content TEXT);
`,
});
// db.connectionString → ready for any Postgres client
```
▶ Run
`instantDatabase()` is idempotent — if a database with that name already exists, it returns the existing one. This makes agent restarts safe.
**From the CLI:**
Terminal
```bash
db9 create --name agent-session-42
```
▶ Run
For fleet operations, the SDK client also exposes `databases.create()`, `databases.list()`, `databases.delete()`, and `databases.credentials()` for full programmatic lifecycle management.
**Common patterns:**
* **Database-per-agent** — each agent instance gets its own database for complete isolation.
* **Database-per-user** — multi-tenant applications give each end user a dedicated database.
* **Database-per-task** — disposable databases for one-shot jobs, deleted when the task completes.
→ *Deeper guide: [Provisioning](/docs/platform/provisioning/)*
## Store: standard SQL for structured state
[Section titled “Store: standard SQL for structured state”](#store-standard-sql-for-structured-state)
Agents store context, conversation history, tool outputs, and intermediate results in regular Postgres tables. DB9 supports the full range of SQL data types, DDL, DML, transactions, and indexes.
SQL
```sql
-- Agent stores a tool result
INSERT INTO memory (key, value)
VALUES ('search_result', '{"query": "quarterly revenue", "hits": 14}');
-- Agent retrieves its context
SELECT key, value FROM memory ORDER BY id DESC LIMIT 10;
```
Because DB9 speaks the PostgreSQL wire protocol, agents can use any Postgres-compatible ORM or driver: Prisma, Drizzle, SQLAlchemy, TypeORM, or raw `pg` connections.
→ *Reference: [SQL](/docs/sql/), [Connect](/docs/connect/)*
## Search: built-in embeddings and vector queries
[Section titled “Search: built-in embeddings and vector queries”](#search-built-in-embeddings-and-vector-queries)
DB9 includes a server-side `embedding()` function that generates vectors without a separate embedding service. Combined with pgvector-compatible operators and HNSW indexes, agents can build semantic search in pure SQL.
SQL
```sql
-- Enable the extension (once per database)
CREATE EXTENSION IF NOT EXISTS embedding;
-- Store content with its embedding
INSERT INTO knowledge (content, vec)
VALUES (
'DB9 supports branching for safe experiments',
embedding('DB9 supports branching for safe experiments')::vector(1024)
);
-- Semantic search
SELECT content, vec <-> embedding('how do I test safely?')::vector(1024) AS distance
FROM knowledge
ORDER BY distance
LIMIT 5;
```
The `embedding()` function accepts an optional model name and dimensions parameter:
SQL
```sql
-- Use a specific model
SELECT embedding('hello world', 'bedrock/amazon-titan-v2', 1024);
```
Embeddings are generated server-side and cached within each statement execution. The per-statement limit is 100 embedding calls (configurable via the `embedding.max_calls` session parameter).
→ *Guide: [RAG with Built-in Embeddings](/docs/guides/rag-with-built-in-embeddings/) (coming soon) · Reference: [Vector Search](/docs/extensions/vector/)*
## Ingest: query files from SQL with fs9
[Section titled “Ingest: query files from SQL with fs9”](#ingest-query-files-from-sql-with-fs9)
Agents produce and consume files — logs, CSVs, JSON exports, Parquet snapshots. fs9 makes these queryable without loading them into tables first.
**Read files as tables:**
SQL
```sql
-- Query a CSV directly
SELECT * FROM extensions.fs9('/data/results.csv');
-- Query a Parquet file (read one file at a time; globs do not decode Parquet)
SELECT * FROM extensions.fs9('/exports/january.parquet');
```
**Manage files from SQL:**
SQL
```sql
-- Write a result file
SELECT fs9_write('/output/summary.json', '{"status": "complete", "rows": 1024}');
-- Read a file
SELECT fs9_read('/output/summary.json');
-- List directory contents
SELECT * FROM extensions.fs9('/output/', recursive := true);
```
**Manage files from the CLI or SDK:**
Terminal
```bash
# Copy a local file into the database
db9 fs cp ./data.csv mydb:/data/data.csv
# Interactive file shell
db9 fs sh mydb
# FUSE mount (access db files like a local directory)
db9 fs mount mydb ./mnt
```
The SDK provides a full file API (`fs.read`, `fs.write`, `fs.list`, `fs.stat`, etc.) over WebSocket for programmatic access.
File limits: 100 MB per file, 128 MB concurrent read budget, 10,000 files per glob query.
→ *Guide: [Analyze Agent Logs with fs9](/docs/guides/analyze-agent-logs-with-fs9/) (coming soon) · Reference: [fs9](/docs/extensions/fs9/)*
## Call: HTTP requests from SQL
[Section titled “Call: HTTP requests from SQL”](#call-http-requests-from-sql)
Agents often need to call external services — webhooks, LLM APIs, enrichment endpoints. DB9’s HTTP extension makes this possible from SQL without leaving the database:
SQL
```sql
-- GET request
SELECT status, content FROM http_get('https://api.example.com/status');
-- POST with JSON body
SELECT status, content
FROM http_post(
'https://hooks.slack.com/services/T.../B.../xxx',
'{"text": "Agent task complete"}',
'application/json'
);
```
Safety boundaries are enforced:
* HTTPS only (port 443) — no plaintext HTTP by default
* Private and loopback IPs are blocked (SSRF protection)
* 100 requests per SQL statement, 20 concurrent per tenant
* 1 MB max response body, 256 KB max request body
* 5-second request timeout, 1-second connect timeout
→ *Guide: [HTTP from SQL](/docs/guides/http-from-sql/) (coming soon) · Reference: [HTTP Extension](/docs/extensions/http/)*
## Branch: safe experiments and rollback
[Section titled “Branch: safe experiments and rollback”](#branch-safe-experiments-and-rollback)
Agents can fork a database to try a risky operation, validate the result, and discard the branch if it fails:
Terminal
```bash
# Create a branch
db9 branch create mydb --name experiment-v2
# Agent works on the branch using its own connection string...
# If the experiment failed, delete the branch
db9 branch delete
# If it succeeded, promote the result (or just keep using the branch)
```
Branches are independent database copies created from the parent’s schema at the time of branching. Each branch gets its own connection string, credentials, and isolated storage — writes to the branch do not affect the parent.
The SDK also supports branching programmatically:
TypeScript
```typescript
const branch = await client.databases.branch(parentId, { name: 'experiment-v2' });
// branch.connectionString → isolated workspace
```
**Common branch patterns:**
* **Preview environments** — branch per pull request for isolated testing.
* **Schema experiments** — try a migration on a branch before applying to production.
* **Task isolation** — each agent task gets a branch, merged or discarded on completion.
→ *Guide: [Branching Workflows](/docs/guides/branching-workflows/) (coming soon)*
## Schedule: recurring jobs with pg\_cron
[Section titled “Schedule: recurring jobs with pg\_cron”](#schedule-recurring-jobs-with-pg_cron)
Agents can schedule periodic work — cache refreshes, log cleanup, periodic API polling — directly in the database:
SQL
```sql
-- Schedule a cleanup job every 6 hours
SELECT cron.schedule(
'cleanup-old-context',
'0 */6 * * *',
$$DELETE FROM memory WHERE created_at < now() - interval '7 days'$$
);
-- Check execution history
SELECT * FROM cron.job_run_details ORDER BY runid DESC LIMIT 5;
```
The CLI provides full cron management:
Terminal
```bash
db9 db cron mydb create '*/30 * * * *' "SELECT * FROM http_get('https://api.example.com/poll')"
db9 db cron mydb list
db9 db cron mydb history --limit 10
db9 db cron mydb status
```
Jobs run inside the database engine with no external scheduler. You can enable, disable, and delete jobs through SQL or the CLI.
→ *Guide: [Scheduled Jobs with pg\_cron](/docs/guides/scheduled-jobs-with-pg-cron/) (coming soon) · Reference: [pg\_cron](/docs/extensions/pg-cron/)*
## Onboard: install DB9 as an agent skill
[Section titled “Onboard: install DB9 as an agent skill”](#onboard-install-db9-as-an-agent-skill)
The `db9 onboard` command installs a DB9 skill file into your AI coding agent’s skills directory. Once installed, the agent can use DB9 commands as part of its normal workflow.
Terminal
```bash
# Auto-detect installed agents and install
db9 onboard
# Target a specific agent
db9 onboard --agent claude
db9 onboard --agent codex
db9 onboard --agent opencode
# Install for all detected agents
db9 onboard --all
# Preview what would be installed
db9 onboard --dry-run
```
**Supported agents:**
| Agent | User scope | Project scope |
| -------------- | ---------------------------------------- | --------------------------------- |
| Claude Code | `~/.claude/skills/db9/SKILL.md` | `./.claude/skills/db9/SKILL.md` |
| OpenAI Codex | `~/.codex/skills/db9/SKILL.md` | — (user scope only) |
| OpenCode | `~/.config/opencode/skills/db9/SKILL.md` | `./.opencode/skills/db9/SKILL.md` |
| Generic agents | `~/.agents/skills/db9/SKILL.md` | `./.agents/skills/db9/SKILL.md` |
Use `--scope user`, `--scope project`, or `--scope both` to control where skills are installed. Skill files include a semver version in their frontmatter — `db9 onboard` only updates when a newer version is available (use `--force` to override).
→ *Guide: [Install DB9 Skills](/docs/agent-workflows/install-db9-skills/) (coming soon) · [Claude Code](/docs/agent-workflows/claude-code/) (coming soon) · [OpenAI Codex](/docs/agent-workflows/openai-codex/) (coming soon)*
## Deploy: serverless functions for data processing
[Section titled “Deploy: serverless functions for data processing”](#deploy-serverless-functions-for-data-processing)
When a workflow requires logic beyond what a single SQL query can express, agents can deploy [Serverless Functions](/docs/functions/) — JavaScript/TypeScript code that runs with native SQL and `ctx.fs9` filesystem access. Functions are useful for data transformation pipelines, webhook handlers, and any task where application code is cleaner than SQL.
## Composing capabilities
[Section titled “Composing capabilities”](#composing-capabilities)
The real power of DB9 for agents is that these primitives compose through SQL. A single agent workflow can:
1. **Create** a database with `instantDatabase()`
2. **Ingest** a CSV with `SELECT * FROM extensions.fs9('/data/input.csv')`
3. **Embed** and index the content with `embedding()`
4. **Search** semantically with pgvector operators
5. **Call** an external API with `http_post()` to deliver results
6. **Schedule** a follow-up job with `cron.schedule()`
7. **Branch** to try an alternative approach
All of this happens inside the database, in SQL, through a standard Postgres connection. No orchestrator, no sidecar, no message queue.
## Next steps
[Section titled “Next steps”](#next-steps)
* [DB9 with Claude Code](/docs/agent-workflows/claude-code/) — install the DB9 skill and start using databases from Claude Code
* [DB9 with OpenAI Codex](/docs/agent-workflows/openai-codex/) — install the DB9 skill for OpenAI Codex CLI
* [Quick Start](/docs/quickstart/) — create your first database in under a minute
* [Connect](/docs/connect/) — connection strings, drivers, and authentication
* [Why DB9 for AI Agents](/docs/why-db9-for-ai-agents/) — the positioning case for using DB9 in agent systems
* [CLI Reference](/docs/cli/) — full command reference for `db9 create`, `db9 onboard`, `db9 branch`, and more
* [TypeScript SDK](/docs/sdk/) — `instantDatabase()`, client API, and programmatic database management
* [RAG with Built-in Embeddings](/docs/guides/rag-with-built-in-embeddings/) — build a retrieval pipeline using DB9-native embeddings
* [Branching Workflows](/docs/guides/branching-workflows/) — preview environments, task isolation, and safe migrations using branches
* [Extensions](/docs/extensions/) — deep dives into fs9, HTTP, embeddings, vector search, and pg\_cron
# REST API Reference
> Complete reference for the DB9 Customer REST API — authentication, databases, SQL execution, filesystem, branching, users, tokens, and more.
The DB9 REST API lets you manage databases, execute SQL, and interact with the platform programmatically. All customer endpoints live under `https://api.db9.ai/customer`.
For most use cases, the [CLI](/docs/cli/) or [TypeScript SDK](/docs/sdk/) provide a more ergonomic interface. The REST API is ideal for custom integrations, CI/CD pipelines, or languages without an SDK.
## Authentication
[Section titled “Authentication”](#authentication)
Most requests require a `Bearer` token in the `Authorization` header:
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" https://api.db9.ai/customer/databases
```
▶ Run
**Exceptions:** `POST /customer/anonymous-register` requires no credentials. `POST /customer/anonymous-refresh` authenticates using `anonymous_id` + `anonymous_secret` in the request body (no Bearer token).
Obtain tokens via:
* `db9 token create` (CLI)
* `db9 login --api-key ` (verifies and saves an existing API key locally)
* Anonymous bootstrap (`/customer/anonymous-register`)
## Error Responses
[Section titled “Error Responses”](#error-responses)
All errors return a consistent JSON shape:
Error Response Shape
```json
{
"message": "human-readable message describing what went wrong"
}
```
| Status | Meaning |
| ------ | --------------------------------------------- |
| `400` | Bad request — missing or invalid fields |
| `401` | Unauthorized — missing or invalid token |
| `403` | Forbidden — token lacks required scope |
| `404` | Not found — resource does not exist |
| `409` | Conflict — resource already exists |
| `422` | Unprocessable — valid JSON but semantic error |
| `429` | Rate limited — slow down and retry |
| `500` | Internal server error |
## Account & Sessions
[Section titled “Account & Sessions”](#account--sessions)
GET `/customer/me`
Return the current customer's identity and profile.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/me
```
▶ Run
Response
```json
{
"id": "cus_01h9xxxxxxxxxxxxxxxx",
"email": "user@example.com",
"created_at": "2026-01-15T10:00:00Z",
"status": "active"
}
```
POST `/customer/anonymous-register`
Create an anonymous account and return a session token. No credentials required.
Terminal
```bash
curl -X POST https://api.db9.ai/customer/anonymous-register
```
▶ Run
Response
```json
{
"anonymous_id": "anon_01h9xxxxxxxxxxxxxxxx",
"anonymous_secret": "sk_anon_xxxxxxxxxxxxxxxxxxxx",
"token": "eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...",
"expires_at": "2027-01-15T10:00:00Z",
"is_anonymous": true
}
```
POST `/customer/anonymous-refresh`
Refresh an anonymous session token using the customer ID and secret.
| Field | Type | Required | Description |
| ------------------ | ------ | -------- | ------------------------ |
| `anonymous_id` | string | yes | Anonymous customer ID |
| `anonymous_secret` | string | yes | Anonymous account secret |
Terminal
```bash
curl -X POST \
-H "Content-Type: application/json" \
-d '{"anonymous_id": "anon_01h9xx", "anonymous_secret": "sk_anon_xx"}' \
https://api.db9.ai/customer/anonymous-refresh
```
Request
```json
{ "anonymous_id": "anon_01h9xxxxxxxxxxxxxxxx", "anonymous_secret": "sk_anon_xxxxxxxxxxxxxxxxxxxx" }
```
Response
```json
{
"token": "eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...",
"expires_at": "2027-02-15T10:00:00Z"
}
```
POST `/customer/claim`
Upgrade an anonymous account to a verified identity using an Auth0 id\_token. Requires a Bearer token.
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"id_token": ""}' \
https://api.db9.ai/customer/claim
```
Request
```json
{ "id_token": "" }
```
Response
```json
{
"id": "cus_01h9xxxxxxxxxxxxxxxx",
"email": "user@example.com",
"claimed": true
}
```
POST `/customer/adopt-anonymous-databases/preflight`
Pre-check eligibility and quotas before adopting databases from an anonymous account.
| Field | Type | Required | Description |
| ----------------------- | ------ | -------- | ------------------------------------ |
| `anonymous_customer_id` | string | yes | Customer ID of the anonymous account |
| `anonymous_secret` | string | yes | Secret for the anonymous account |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"anonymous_customer_id": "anon_01h9xx", "anonymous_secret": "sk_anon_xx"}' \
https://api.db9.ai/customer/adopt-anonymous-databases/preflight
```
Request
```json
{
"anonymous_customer_id": "anon_01h9xxxxxxxxxxxxxxxx",
"anonymous_secret": "sk_anon_xxxxxxxxxxxxxxxxxxxx"
}
```
Response
```json
{
"databases": [
{ "id": "db_01h9xxxxxxxxxxxxxxxx", "name": "myapp", "state": "ACTIVE" }
],
"quota_ok": true,
"current_count": 1,
"database_limit": 10
}
```
POST `/customer/adopt-anonymous-databases`
Transfer databases from an anonymous account to your authenticated account.
| Field | Type | Required | Description |
| ----------------------- | --------- | -------- | --------------------------------------- |
| `anonymous_customer_id` | string | yes | Customer ID of the anonymous account |
| `anonymous_secret` | string | yes | Secret for the anonymous account |
| `database_ids` | string\[] | yes | List of database IDs to adopt (max 100) |
| `idempotency_key` | string | yes | Unique key for idempotent retries |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"anonymous_customer_id": "anon_01h9xx", "anonymous_secret": "sk_anon_xx", "database_ids": ["db_01h9xx"], "idempotency_key": "adopt-2026-01-15"}' \
https://api.db9.ai/customer/adopt-anonymous-databases
```
Request
```json
{
"anonymous_customer_id": "anon_01h9xxxxxxxxxxxxxxxx",
"anonymous_secret": "sk_anon_xxxxxxxxxxxxxxxxxxxx",
"database_ids": ["db_01h9xxxxxxxxxxxxxxxx"],
"idempotency_key": "adopt-2026-01-15"
}
```
Response
```json
{
"transferred": ["db_01h9xxxxxxxxxxxxxxxx"],
"skipped": [],
"total_transferred": 1
}
```
## Databases
[Section titled “Databases”](#databases)
POST `/customer/databases`
Create a new database. Returns credentials and connection string immediately.
| Field | Type | Required | Description |
| ---------------- | ------ | -------- | ------------------------------------------ |
| `name` | string | yes | Database name |
| `region` | string | no | Deployment region |
| `admin_password` | string | no | Admin password (auto-generated if omitted) |
| `project_id` | string | no | Project ID to group databases under |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"name": "myapp", "region": "us-east-1"}' \
https://api.db9.ai/customer/databases
```
▶ Run
Request
```json
{
"name": "myapp",
"region": "us-east-1"
}
```
Response
```json
{
"id": "db_01h9xxxxxxxxxxxxxxxx",
"name": "myapp",
"region": "us-east-1",
"state": "ready",
"connection_string": "postgresql://admin:password@db-01h9xx.db9.ai:5433/myapp",
"admin_user": "admin",
"admin_password": "generated-password",
"created_at": "2026-01-15T10:00:00Z",
"last_active_at": null,
"last_modified_at": null
}
```
GET `/customer/databases`
List all databases in your account.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases
```
▶ Run
Response
```json
[
{
"id": "db_01h9xxxxxxxxxxxxxxxx",
"name": "myapp",
"region": "us-east-1",
"state": "ready",
"created_at": "2026-01-15T10:00:00Z",
"last_active_at": null,
"last_modified_at": null
}
]
```
GET `/customer/databases/{database_id}`
Get database details including connection string and current state.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx
```
▶ Run
Response
```json
{
"id": "db_01h9xxxxxxxxxxxxxxxx",
"name": "myapp",
"region": "us-east-1",
"state": "ready",
"connection_string": "postgresql://admin:password@db-01h9xx.db9.ai:5433/myapp",
"created_at": "2026-01-15T10:00:00Z",
"last_active_at": null,
"last_modified_at": null
}
```
DELETE `/customer/databases/{database_id}`
Permanently delete a database. This action is irreversible.
Terminal
```bash
curl -X DELETE -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx
```
Response
```json
{ "message": "Database disabled" }
```
POST `/customer/databases/{database_id}/reset-password`
Reset the admin password for a database and return the new credentials.
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/reset-password
```
▶ Run
Response
```json
{
"admin_user": "admin",
"admin_password": "new-generated-password",
"connection_string": "postgresql://admin:new-generated-password@db-01h9xx.db9.ai:5433/postgres"
}
```
GET `/customer/databases/{database_id}/credentials`
Retrieve stored admin credentials for a database.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/credentials
```
▶ Run
Response
```json
{
"admin_user": "admin",
"admin_password": "stored-password",
"connection_string": "postgresql://admin:stored-password@db-01h9xx.db9.ai:5432/myapp"
}
```
GET `/customer/databases/{database_id}/schema`
Get the database schema — tables, columns, and types.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/schema
```
▶ Run
Response
```json
{
"tables": [
{
"schema": "public",
"name": "users",
"columns": [
{ "name": "id", "type": "integer", "nullable": false },
{ "name": "email", "type": "text", "nullable": false },
{ "name": "created_at", "type": "timestamp", "nullable": true }
]
}
],
"views": [
{ "name": "active_users", "schema": "public" }
]
}
```
GET `/customer/databases/{database_id}/observability`
Get database metrics and slow query samples.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/observability
```
▶ Run
Response
```json
{
"summary": {
"tps": 42.5,
"latency_avg_ms": 3.2,
"latency_p99_ms": 18.7,
"active_connections": 3,
"database_storage_bytes": 10485760,
"fs_logical_bytes": 2097152
},
"samples": [
{
"query": "SELECT * FROM events WHERE user_id = $1",
"sample_count": 120,
"error_count": 0,
"latency_avg_ms": 245.0,
"latency_p99_ms": 480.0,
"latency_max_ms": 612.0,
"last_seen_ms_ago": 5000
}
]
}
```
## Batch Create
[Section titled “Batch Create”](#batch-create)
POST `/customer/databases/batch`
Create multiple databases in a single request. Each item in the array is created independently — partial success is possible.
| Field | Type | Required | Description |
| ------------ | ------ | -------- | --------------------------------------------------- |
| `databases` | array | yes | Array of `{ name, region?, admin_password? }` items |
| `project_id` | string | no | Project ID to assign all databases to |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"databases": [{"name": "shard-1"}, {"name": "shard-2", "region": "us-east-1"}], "project_id": "proj-abc"}' \
https://api.db9.ai/customer/databases/batch
```
Request
```json
{
"databases": [
{ "name": "shard-1" },
{ "name": "shard-2", "region": "us-east-1" }
],
"project_id": "proj-abc"
}
```
Response
```json
{
"created": [
{ "id": "db_01...", "name": "shard-1", "state": "ready", ... },
{ "id": "db_02...", "name": "shard-2", "state": "ready", ... }
],
"failed": [],
"total_requested": 2,
"total_created": 2
}
```
## SQL Execution
[Section titled “SQL Execution”](#sql-execution)
POST `/customer/databases/{database_id}/sql`
Execute a SQL query and return results as column names + row arrays.
| Field | Type | Required | Description |
| -------------- | ------ | -------- | ------------------------------------ |
| `query` | string | no | Inline SQL query |
| `file_content` | string | no | SQL content (alternative to `query`) |
Provide either `query` or `file_content`, not both.
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"query": "SELECT id, email FROM users LIMIT 2"}' \
https://api.db9.ai/customer/databases/db_01h9xx/sql
```
▶ Run
Request
```json
{ "query": "SELECT id, email FROM users LIMIT 2" }
```
Response
```json
{
"columns": [
{ "name": "id", "data_type": "integer" },
{ "name": "email", "data_type": "text" }
],
"rows": [
[1, "alice@example.com"],
[2, "bob@example.com"]
],
"row_count": 2,
"command": "SELECT"
}
```
POST `/customer/databases/{database_id}/dump`
Export schema and data as SQL. Pass {"ddl\_only": true} for schema-only export.
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"ddl_only": false}' \
https://api.db9.ai/customer/databases/db_01h9xx/dump
```
▶ Run
Request
```json
{ "ddl_only": false }
```
Response
```json
{
"sql": "-- DB9 dump\nCREATE TABLE users (...);\nINSERT INTO users VALUES (...);",
"object_count": 3
}
```
## Connect Tokens & Keys
[Section titled “Connect Tokens & Keys”](#connect-tokens--keys)
POST `/customer/databases/{database_id}/connect-token`
Mint a short-lived JWT connect token. Pass {"role": "admin"} in the body.
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"role": "admin"}' \
https://api.db9.ai/customer/databases/db_01h9xx/connect-token
```
▶ Run
Request
```json
{ "role": "admin" }
```
Response
```json
{
"host": "db-01h9xx.db9.ai",
"port": 5433,
"database": "postgres",
"user": "tenant_id.admin",
"token": "eyJhbGciOiJSUzI1NiIsInR5cCI6IkpXVCJ9...",
"fs_websocket_url": "wss://fs.db9.ai/ws",
"expires_in_seconds": 900,
"expires_at": "2026-01-15T11:00:00Z",
"tls": "not_enforced"
}
```
POST `/customer/databases/{database_id}/connect-keys`
Create a long-lived DB Connect Key. The key value is only returned once.
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"role": "admin", "scopes": []}' \
https://api.db9.ai/customer/databases/db_01h9xx/connect-keys
```
▶ Run
Request
```json
{ "role": "admin", "scopes": [] }
```
Response
```json
{
"id": "ck_01h9xxxxxxxxxxxxxxxx",
"name": "ci",
"connect_key": "db9ck_xxxxxxxxxxxxxxxxxxxx",
"role": "admin",
"scopes": [],
"created_at": "2026-01-15T10:00:00Z"
}
```
GET `/customer/databases/{database_id}/connect-keys`
List all DB Connect Keys (metadata only — key values are never returned).
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/connect-keys
```
▶ Run
Response
```json
[
{
"id": "ck_01h9xxxxxxxxxxxxxxxx",
"name": "ci",
"role": "admin",
"scopes": [],
"created_at": "2026-01-15T10:00:00Z",
"expires_at": "2027-01-15T10:00:00Z"
}
]
```
DELETE `/customer/databases/{database_id}/connect-keys/{key_id}`
Revoke a DB Connect Key immediately.
Terminal
```bash
curl -X DELETE -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/connect-keys/ck_01h9xx
```
Response
```json
{ "message": "Connect key revoked" }
```
## Auth Configuration
[Section titled “Auth Configuration”](#auth-configuration)
GET `/customer/databases/{database_id}/auth-config`
Get the browser data-plane authentication configuration. Default auth\_mode is byo\_jwt.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/auth-config
```
▶ Run
Response
```json
{
"auth_mode": "byo_jwt",
"byo_jwt": {
"jwks_url": "https://example.com/.well-known/jwks.json",
"audience": "my-app",
"issuer": "https://example.com",
"subject_claim": "sub",
"claims_allowlist": [],
"claims_max_bytes": 4096
},
"created_at": "2026-01-15T10:00:00Z",
"updated_at": "2026-01-15T10:00:00Z"
}
```
PUT `/customer/databases/{database_id}/auth-config`
Create or update the browser data-plane authentication configuration.
| Field | Type | Required | Description |
| ----------- | ------ | -------- | ------------------------------------ |
| `auth_mode` | string | no | Authentication mode (e.g. `byo_jwt`) |
| `byo_jwt` | object | no | Bring-your-own JWT configuration |
Terminal
```bash
curl -X PUT -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"auth_mode": "byo_jwt", "byo_jwt": {"jwks_url": "https://example.com/.well-known/jwks.json", "audience": "my-app", "issuer": "https://example.com"}}' \
https://api.db9.ai/customer/databases/db_01h9xx/auth-config
```
Request
```json
{
"auth_mode": "byo_jwt",
"byo_jwt": {
"jwks_url": "https://example.com/.well-known/jwks.json",
"audience": "my-app",
"issuer": "https://example.com",
"subject_claim": "sub",
"claims_allowlist": [],
"claims_max_bytes": 4096
}
}
```
Response
```json
{
"auth_mode": "byo_jwt",
"byo_jwt": {
"jwks_url": "https://example.com/.well-known/jwks.json",
"audience": "my-app",
"issuer": "https://example.com",
"subject_claim": "sub",
"claims_allowlist": [],
"claims_max_bytes": 4096
},
"created_at": "2026-01-15T10:00:00Z",
"updated_at": "2026-01-15T10:00:00Z"
}
```
## Publishable Keys
[Section titled “Publishable Keys”](#publishable-keys)
POST `/customer/databases/{database_id}/publishable-keys`
Create a publishable key for browser/client-side data access. Key value returned once only.
| Field | Type | Required | Description |
| ----------------- | --------- | -------- | ------------------------------- |
| `name` | string | no | Key name |
| `allowed_origins` | string\[] | no | CORS allowed origins |
| `exposed_schemas` | string\[] | no | Schemas accessible via this key |
| `exposed_tables` | string\[] | no | Tables accessible via this key |
| `rate_limit` | object | no | Rate limiting (`rps`, `burst`) |
| `expires_in_days` | number | no | Key expiration in days |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"name": "web-app", "allowed_origins": ["https://example.com"], "exposed_schemas": ["public"]}' \
https://api.db9.ai/customer/databases/db_01h9xx/publishable-keys
```
▶ Run
Request
```json
{
"name": "web-app",
"allowed_origins": ["https://example.com"],
"exposed_schemas": ["public"],
"exposed_tables": ["posts", "comments"],
"rate_limit": { "rps": 100, "burst": 200 },
"expires_in_days": 90
}
```
Response
```json
{
"id": "pk_01h9xxxxxxxxxxxxxxxx",
"publishable_key": "db9pk_xxxxxxxxxxxxxxxxxxxx",
"name": "web-app",
"allowed_origins": ["https://example.com"],
"exposed_schemas": ["public"],
"exposed_tables": ["posts", "comments"],
"expires_at": "2026-04-15T10:00:00Z",
"created_at": "2026-01-15T10:00:00Z"
}
```
GET `/customer/databases/{database_id}/publishable-keys`
List all publishable keys (metadata only — key values are never returned).
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/publishable-keys
```
▶ Run
Response
```json
[
{
"id": "pk_01h9xxxxxxxxxxxxxxxx",
"name": "web-app",
"allowed_origins": ["https://example.com"],
"exposed_schemas": ["public"],
"exposed_tables": ["posts", "comments"],
"expires_at": "2026-04-15T10:00:00Z",
"created_at": "2026-01-15T10:00:00Z"
}
]
```
DELETE `/customer/databases/{database_id}/publishable-keys/{key_id}`
Revoke a publishable key immediately.
Terminal
```bash
curl -X DELETE -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/publishable-keys/ck_01h9xx
```
Response
```json
{ "message": "Publishable key revoked" }
```
## Service Keys
[Section titled “Service Keys”](#service-keys)
POST `/customer/databases/{database_id}/service-keys`
Create a service key for server-side operations (CRUD only — consumption API not yet implemented). Key value returned once only.
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/service-keys
```
▶ Run
Response
```json
{
"id": "sk_01h9xxxxxxxxxxxxxxxx",
"name": "service-key",
"service_key": "db9sk_xxxxxxxxxxxxxxxxxxxx",
"created_at": "2026-01-15T10:00:00Z"
}
```
GET `/customer/databases/{database_id}/service-keys`
List all service keys (metadata only).
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/service-keys
```
▶ Run
Response
```json
[
{
"id": "sk_01h9xxxxxxxxxxxxxxxx",
"name": "service-key",
"created_at": "2026-01-15T10:00:00Z",
"expires_at": "2027-01-15T10:00:00Z"
}
]
```
DELETE `/customer/databases/{database_id}/service-keys/{key_id}`
Revoke a service key immediately.
Terminal
```bash
curl -X DELETE -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/service-keys/ck_01h9xx
```
Response
```json
{ "message": "Service key revoked" }
```
## Database Users
[Section titled “Database Users”](#database-users)
GET `/customer/databases/{database_id}/users`
List all PostgreSQL users in the database.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/users
```
▶ Run
Response
```json
[
{ "name": "admin", "is_superuser": true, "can_login": true, "can_create_db": true, "can_create_role": true },
{ "name": "app_user", "is_superuser": false, "can_login": true, "can_create_db": false, "can_create_role": false }
]
```
POST `/customer/databases/{database_id}/users`
Create a new PostgreSQL user. Returns the generated password (returned once only).
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"username": "app_user", "password": "playground123"}' \
https://api.db9.ai/customer/databases/db_01h9xx/users
```
▶ Run
Request
```json
{ "username": "app_user" }
```
Response
```json
{
"message": "User 'app_user' created",
"username": "app_user",
"password": "generated-password",
"connection_string": "postgresql://tenant_id.app_user@db-01h9xx.db9.ai:5433/postgres",
"connection_string_with_password": "postgresql://tenant_id.app_user:generated-password@db-01h9xx.db9.ai:5433/postgres"
}
```
DELETE `/customer/databases/{database_id}/users/{username}`
Delete a PostgreSQL user from the database.
Terminal
```bash
curl -X DELETE -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/users/app_user
```
Response
```json
{ "message": "User 'app_user' deleted" }
```
## Migrations
[Section titled “Migrations”](#migrations)
GET `/customer/databases/{database_id}/migrations`
List all applied migrations with names, checksums, and timestamps.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/migrations
```
▶ Run
Response
```json
[
{
"name": "add_users_table",
"checksum": "sha256:abc123",
"applied_at": "2026-01-15T10:00:00Z",
"sql_preview": "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT NOT NULL);"
}
]
```
POST `/customer/databases/{database_id}/migrations`
Apply a migration. Migrations are idempotent by name — re-applying the same name returns 409.
| Field | Type | Required | Description |
| ---------- | ------ | -------- | ----------------------------------------- |
| `name` | string | yes | Migration name (must be unique) |
| `sql` | string | yes | SQL to execute |
| `checksum` | string | no | Content digest for integrity verification |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"name": "add_users_table", "sql": "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT NOT NULL);"}' \
https://api.db9.ai/customer/databases/db_01h9xx/migrations
```
▶ Run
Request
```json
{
"name": "add_users_table",
"sql": "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT NOT NULL);"
}
```
Response
```json
{
"status": "applied",
"name": "add_users_table"
}
```
## Branching
[Section titled “Branching”](#branching)
POST `/customer/databases/{database_id}/branch`
Create a branch — a full copy of the database schema (and optionally data) at a point in time.
| Field | Type | Required | Description |
| ------------- | ------ | -------- | -------------------------------------------------------------------------------------- |
| `name` | string | yes | Branch name |
| `snapshot_at` | string | no | RFC 3339 UTC timestamp for point-in-time branching (omit to branch from current state) |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"name": "feature-branch"}' \
https://api.db9.ai/customer/databases/db_01h9xx/branch
```
▶ Run
Request
```json
{
"name": "feature-branch",
"snapshot_at": "2026-03-22T06:00:00Z"
}
```
Response
```json
{
"id": "db_01h9yyyyyyyyyyyyyyyy",
"name": "feature-branch",
"parent_database_id": "db_01h9xxxxxxxxxxxxxxxx",
"region": "us-east-1",
"state": "ready",
"connection_string": "postgresql://admin:password@db-01h9yy.db9.ai:5433/feature-branch",
"created_at": "2026-01-15T10:00:00Z",
"last_active_at": null,
"last_modified_at": null
}
```
## Backups & Restores
[Section titled “Backups & Restores”](#backups--restores)
Not Yet Generally Available
The backup and restore API requires backend infrastructure configuration (`backup_s3_bucket`). On the shared production environment, these endpoints currently return **503 — Backup service is not configured**. Contact the DB9 team if you need access to backups.
POST `/customer/databases/{database_id}/backups`
Create a backup of a database.
| Field | Type | Required | Description |
| ------- | ------ | -------- | ----------------------------------- |
| `label` | string | no | Human-readable label for the backup |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"label": "pre-migration-snap"}' \
https://api.db9.ai/customer/databases/db_01h9xx/backups
```
Request
```json
{
"label": "pre-migration-snap"
}
```
Response
```json
{
"id": "bkp_01...",
"database_id": "db_01h9xxxxxxxxxxxxxxxx",
"state": "creating",
"label": "pre-migration-snap",
"created_at": "2026-04-09T10:00:00Z"
}
```
GET `/customer/databases/{database_id}/backups`
List all backups for a database.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/backups
```
GET `/customer/databases/{database_id}/backups/{backup_id}`
Get details of a specific backup.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/backups/bkp_01h9xx
```
DELETE `/customer/databases/{database_id}/backups/{backup_id}`
Delete a backup.
Terminal
```bash
curl -X DELETE -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/backups/bkp_01h9xx
```
POST `/customer/restores`
Restore a database from a backup. Creates a new database with the restored data.
| Field | Type | Required | Description |
| ----------------- | ------ | -------- | -------------------------------- |
| `backup_id` | string | yes | ID of the backup to restore from |
| `target_name` | string | yes | Name for the restored database |
| `idempotency_key` | string | no | Idempotency key for safe retries |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"backup_id": "bkp_01h9xx", "target_name": "myapp-restored"}' \
https://api.db9.ai/customer/restores
```
Request
```json
{
"backup_id": "bkp_01...",
"target_name": "myapp-restored"
}
```
GET `/customer/restores/{restore_id}`
Check the status of a restore operation.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/restores/rst_01h9xx
```
## Filesystem
[Section titled “Filesystem”](#filesystem)
POST `/customer/databases/{database_id}/fs-connect`
Obtain database credentials and filesystem WebSocket URL for fs9 access.
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/fs-connect
```
▶ Run
Response
```json
{
"database_id": "db_01h9xxxxxxxxxxxxxxxx",
"database_name": "myapp",
"admin_user": "admin",
"admin_password": "stored-password",
"connection_string": "postgresql://tenant_id.admin@db-01h9xx.db9.ai:5433/postgres",
"fs_websocket_url": "wss://fs.db9.ai/ws",
"connection_string_with_password": "postgresql://tenant_id.admin:stored-password@db-01h9xx.db9.ai:5433/postgres"
}
```
## Functions
[Section titled “Functions”](#functions)
POST `/customer/databases/{database_id}/functions`
Deploy a serverless function. Functions run in an isolated environment with optional database access, secrets, and filesystem scope.
| Field | Type | Required | Description |
| ------------------- | --------- | -------- | ------------------------------------------------------------------ |
| `name` | string | yes | Function name |
| `entrypoint` | string | yes | Function entrypoint (e.g. `index.handler`) |
| `bundle_ref` | string | yes | Bundle storage reference |
| `bundle_digest` | string | no | Bundle content digest for integrity |
| `run_as` | string | no | Database role to execute as |
| `bypass_rls` | boolean | no | Bypass Row-Level Security |
| `limits` | object | no | Execution limits |
| `network_allowlist` | string\[] | no | Allowed outbound network destinations |
| `fs9_scope` | object | no | Filesystem access scope |
| `secret_bindings` | string\[] | no | Secrets to bind to the function |
| `cron_schedule` | string | no | Cron expression for scheduled execution |
| `visibility` | string | no | `"public"` (callable via publishable key) or `"private"` (default) |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"name": "my-function", "entrypoint": "index.handler", "bundle_ref": "s3://bucket/bundle.zip"}' \
https://api.db9.ai/customer/databases/db_01h9xx/functions
```
Request
```json
{
"name": "my-function",
"entrypoint": "index.handler",
"bundle_ref": "s3://bucket/bundle.zip",
"bundle_digest": "sha256:abc123",
"run_as": "app_user",
"bypass_rls": false,
"limits": { "timeout_ms": 30000 },
"network_allowlist": ["https://api.example.com"],
"fs9_scope": { "read": ["/data"], "write": [] },
"secret_bindings": ["API_KEY"],
"cron_schedule": "*/5 * * * *"
}
```
Response
```json
{
"function": {
"id": "fn_01h9xxxxxxxxxxxxxxxx",
"name": "my-function",
"enabled": true,
"visibility": "private",
"created_at": "2026-01-15T10:00:00Z",
"updated_at": "2026-01-15T10:00:00Z"
},
"version": {
"id": "ver_01h9xxxxxxxxxxxxxxxx",
"version": 1,
"entrypoint": "index.handler",
"bundle_ref": "s3://bucket/bundle.zip",
"bundle_digest": "sha256:abc123",
"run_as": "app_user",
"bypass_rls": false,
"created_at": "2026-01-15T10:00:00Z"
}
}
```
GET `/customer/databases/{database_id}/functions`
List all deployed functions.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/functions
```
▶ Run
Response
```json
[
{
"id": "fn_01h9xxxxxxxxxxxxxxxx",
"name": "my-function",
"enabled": true,
"visibility": "private",
"created_at": "2026-01-15T10:00:00Z",
"updated_at": "2026-01-15T10:00:00Z"
}
]
```
POST `/customer/databases/{database_id}/functions/{function_id}/invoke`
Invoke a function synchronously and return the output.
| Field | Type | Required | Description |
| ----------------- | ------ | -------- | ------------------------------------- |
| `input` | object | no | Function input payload |
| `idempotency_key` | string | no | Unique key for idempotent invocations |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"input": {"user_id": "u_123"}}' \
https://api.db9.ai/customer/databases/db_01h9xx/functions/fn_01h9xx/invoke
```
▶ Run
Request
```json
{ "input": { "user_id": "u_123" } }
```
Response
```json
{
"run_id": "run_01h9xxxxxxxxxxxxxxxx",
"function_id": "fn_01h9xxxxxxxxxxxxxxxx",
"version_id": "ver_01h9xxxxxxxxxxxxxxxx",
"status": "completed",
"result_json": "{\"result\":\"ok\"}"
}
```
GET `/customer/databases/{database_id}/functions/{function_id}/runs`
List all runs for a function, ordered by most recent first.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/functions/fn_01h9xx/runs
```
▶ Run
Response
```json
[
{
"id": "run_01h9xxxxxxxxxxxxxxxx",
"function_id": "fn_01h9xxxxxxxxxxxxxxxx",
"version_id": "ver_01h9xxxxxxxxxxxxxxxx",
"trigger_type": "invoke",
"status": "completed",
"run_as": "app_user",
"attempt": 1,
"created_at": "2026-01-15T10:00:00Z"
}
]
```
GET `/customer/databases/{database_id}/functions/{function_id}/runs/{run_id}`
Get details for a specific function run including output and timing.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/functions/fn_01h9xx/runs/run_01h9xx
```
▶ Run
Response
```json
{
"id": "run_01h9xxxxxxxxxxxxxxxx",
"function_id": "fn_01h9xxxxxxxxxxxxxxxx",
"version_id": "ver_01h9xxxxxxxxxxxxxxxx",
"trigger_type": "invoke",
"status": "completed",
"run_as": "app_user",
"attempt": 1,
"created_at": "2026-01-15T10:00:00Z"
}
```
GET `/customer/databases/{database_id}/functions/{function_id}/runs/{run_id}/logs`
Get structured log output for a specific function run.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/functions/fn_01h9xx/runs/run_01h9xx/logs
```
▶ Run
Response
```json
{
"run_id": "run_01h9xxxxxxxxxxxxxxxx",
"logs": "2026-01-15T10:00:00.010Z [info] Function started\n2026-01-15T10:00:00.140Z [info] Function completed"
}
```
## Secrets
[Section titled “Secrets”](#secrets)
GET `/customer/databases/{database_id}/secrets`
List all secret names and timestamps. Secret values are never returned.
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/secrets
```
▶ Run
Response
```json
[
{
"name": "API_KEY",
"created_at": "2026-01-15T10:00:00Z",
"updated_at": "2026-01-20T08:00:00Z"
}
]
```
POST `/customer/databases/{database_id}/secrets`
Create a secret. The value is encrypted at rest and never returned after creation.
| Field | Type | Required | Description |
| ------- | ------ | -------- | ------------------------------------------ |
| `name` | string | yes | Secret name (alphanumeric and underscores) |
| `value` | string | yes | Secret value (encrypted at rest) |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"name": "API_KEY", "value": "sk-live-xxxxxxxxxxxx"}' \
https://api.db9.ai/customer/databases/db_01h9xx/secrets
```
▶ Run
Request
```json
{ "name": "API_KEY", "value": "sk-live-xxxxxxxxxxxx" }
```
Response
```json
{
"name": "API_KEY",
"created_at": "2026-01-15T10:00:00Z"
}
```
PUT `/customer/databases/{database_id}/secrets/{secret_name}`
Update an existing secret's value.
| Field | Type | Required | Description |
| ------- | ------ | -------- | ---------------- |
| `value` | string | yes | New secret value |
Terminal
```bash
curl -X PUT -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"value": "sk-live-new-xxxxxxxxxxxx"}' \
https://api.db9.ai/customer/databases/db_01h9xx/secrets/API_KEY
```
▶ Run
Request
```json
{ "value": "sk-live-new-xxxxxxxxxxxx" }
```
Response
```json
{
"name": "API_KEY",
"updated_at": "2026-02-01T09:00:00Z"
}
```
DELETE `/customer/databases/{database_id}/secrets/{secret_name}`
Delete a secret permanently.
Terminal
```bash
curl -X DELETE -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/databases/db_01h9xx/secrets/API_KEY
```
**Response:** `204 No Content` — empty body.
## API Tokens
[Section titled “API Tokens”](#api-tokens)
POST `/customer/tokens`
Create an API token. The token value is returned once only — store it securely.
| Field | Type | Required | Description |
| ----------------- | ------- | -------- | --------------------------------------------------------------------------------------------------- |
| `name` | string | no | Token name |
| `expires_in_days` | integer | no | Expiry in days (default: 365) |
| `scope_json` | string | no | JSON string scoping token to specific databases, e.g. `{"databases":[{"id":"","access":"ro"}]}` |
Terminal
```bash
curl -X POST -H "Authorization: Bearer $DB9_API_KEY" \
-H "Content-Type: application/json" \
-d '{"name": "ci-token", "expires_in_days": 365}' \
https://api.db9.ai/customer/tokens
```
▶ Run
Request
```json
{
"name": "ci-token",
"expires_in_days": 365
}
```
Response
```json
{
"id": "tok_01h9xxxxxxxxxxxxxxxx",
"name": "ci-token",
"token": "a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4e5f6a1b2c3d4",
"expires_at": "2027-01-15T10:00:00Z",
"created_at": "2026-01-15T10:00:00Z"
}
```
GET `/customer/tokens`
List all API tokens (metadata only — token values are never returned).
Terminal
```bash
curl -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/tokens
```
▶ Run
Response
```json
[
{
"id": "tok_01h9xxxxxxxxxxxxxxxx",
"name": "ci-token",
"expires_at": "2027-01-15T10:00:00Z",
"created_at": "2026-01-15T10:00:00Z"
}
]
```
DELETE `/customer/tokens/{token_id}`
Revoke an API token immediately. All requests using this token will be rejected.
Terminal
```bash
curl -X DELETE -H "Authorization: Bearer $DB9_API_KEY" \
https://api.db9.ai/customer/tokens/tok_01h9xx
```
Response
```json
{ "message": "Token revoked" }
```
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [Error Codes](/docs/error-codes/) — Full HTTP status code reference with causes and resolution
* [CLI Reference](/docs/cli/) — Command-line interface
* [TypeScript SDK](/docs/sdk/) — Server-side SDK wrapping this API
* [Browser SDK](/docs/sdk-browser/) — Client-side data access
* [Connect](/docs/connect/) — Connection strings and drivers
# Architecture
> How DB9 works — the control plane, data plane, TiKV storage, pgwire protocol, SQL execution pipeline, multi-tenant isolation, and extension system.
DB9 is a PostgreSQL-compatible distributed SQL database built on [TiKV](https://tikv.org/). It separates a **control plane** (API server, CLI, SDK) from a **data plane** (the SQL engine that speaks the PostgreSQL wire protocol and stores data in TiKV).
This page explains how the pieces fit together, how queries flow from client to storage, and where the architectural boundaries are.
## Mental model
[Section titled “Mental model”](#mental-model)
Output
```text
+-------------------------------------------------+
| Clients |
| psql ORMs drivers SDK CLI Browser |
+------------------------+------------------------+
|
+-----------+-----------+
| |
v v
+-------------------+ +-------------------+
| Control Plane | | Data Plane |
| (db9-backend) | | (db9-server) |
| | | |
| REST API | | pgwire protocol |
| Auth & tokens | | SQL engine |
| DB lifecycle | | Extensions |
| Branching | | Worker engine |
| Observability | | |
+---------+---------+ +---------+---------+
| |
+-----------+-----------+
v
+-----------------+
| TiKV |
| (distributed |
| KV storage) |
+-----------------+
```
**Control plane** — manages database lifecycle, authentication, tokens, branching, and observability. You interact with it through the `db9` CLI, the TypeScript SDK, or the REST API.
**Data plane** — runs SQL. The `db9-server` process accepts PostgreSQL wire protocol connections, parses and optimizes SQL, executes queries, and reads/writes data in TiKV. Extensions like fs9, HTTP, embedding, vector search, and pg\_cron run here as compiled-in capabilities.
**TiKV** — the storage layer. A distributed key-value store with Raft consensus and ACID transactions. Each DB9 database gets its own isolated keyspace in TiKV, so tenants share infrastructure but never data.
## Core components
[Section titled “Core components”](#core-components)
### db9-server (data plane)
[Section titled “db9-server (data plane)”](#db9-server-data-plane)
The SQL engine. A single async Rust process running on the Tokio runtime.
| Component | Role |
| --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **pgwire listener** | Accepts PostgreSQL wire protocol connections on port `5433`. Handles startup, TLS, authentication, and the Simple Query / Extended Query protocols. |
| **SQL parser** | Parses SQL text into an AST using [sqlparser-rs](https://github.com/sqlparser-rs/sqlparser-rs). |
| **Analyzer** | Single-pass name resolution and type checking. Resolves column references, infers types, applies coercions, and tracks subquery scopes. |
| **Optimizer** | Cost-based query optimization. Converts analyzed queries into logical plans, applies rewrite rules (predicate pushdown, join reordering via DPccp), then selects physical operators. Uses table statistics from `ANALYZE` when available. |
| **Executor** | Volcano-model pull-based iterator pipeline. Operators include table scan, index range scan, HNSW k-NN scan, hash join, nested-loop join, sort, aggregate, and window functions. |
| **TiKV store** | Facade over the TiKV client. Manages transactions (pessimistic locking, snapshot isolation), key encoding, and per-keyspace connection pooling. |
| **RLS engine** | Row-Level Security enforcement. Injects policy predicates into query plans, validates DML operations against WITH CHECK clauses, and manages bypass logic for superusers and BYPASSRLS roles. |
| **Extension runtime** | Compiled-in extensions registered at startup. Each extension can add SQL functions, types, operators, and background workers. |
| **Worker engine** | Unified async task queue stored in TiKV. Drives pg\_cron schedules, async triggers, auto-analyze, HNSW index background merges, and [Serverless Functions](/docs/functions/) execution. |
### db9-backend (control plane)
[Section titled “db9-backend (control plane)”](#db9-backend-control-plane)
The API server. An [Axum](https://github.com/tokio-rs/axum) web application backed by a metadata database (SQLite or PostgreSQL).
| Component | Role |
| ------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **REST API** | Database CRUD, user management, token lifecycle, branching, migrations, observability, and SQL execution via HTTP. |
| **Public Data API** | REST endpoint (`/public/v1/databases/:id/query`) for browser SDK queries. Validates publishable keys, optional BYO JWT tokens, and enforces schema/table access. Issues connect tokens under the resolved role for RLS enforcement. |
| **Auth service** | Anonymous trial accounts, Auth0 SSO, API tokens, publishable keys, BYO JWT validation, and connect tokens (short-lived pgwire credentials). |
| **PD client** | Manages TiKV keyspaces — creates and deletes the isolated storage namespaces that back each database. |
| **PG client** | Connects to db9-server to execute tenant setup SQL (roles, default extensions, seed data). |
| **Reconciler** | Background process that detects and recovers failed provisioning or deletion operations. |
### db9 CLI and TypeScript SDK
[Section titled “db9 CLI and TypeScript SDK”](#db9-cli-and-typescript-sdk)
Client-side tools that talk to both planes:
* **Control plane** — `db9 create`, `db9 list`, `db9 token create`, `db9 branch create`, and other management commands go through the REST API.
* **Data plane** — `db9 sql`, `db9 db sql --direct`, and `db9 db connect` (which mints a short-lived JWT) connect directly to db9-server over pgwire. The SDK does not yet publish a native `connectToken()` equivalent.
## Query execution flow
[Section titled “Query execution flow”](#query-execution-flow)
When a client sends a SQL query, it follows this path through db9-server:
Output
```text
SQL text
│
├─ Parse (sqlparser-rs → AST)
│
├─ Dispatch
│ ├─ Data statements (SELECT, INSERT, UPDATE, DELETE) → Analyzer
│ ├─ DDL / utility statements → direct handler
│ └─ Transaction control (BEGIN, COMMIT, SAVEPOINT) → session state
│
├─ Analyze (name resolution, type checking, scope tracking)
│
├─ Optimize
│ ├─ Logical plan (AnalyzedQuery → LogicalPlan)
│ ├─ Rewrite rules (predicate pushdown, join reordering, subquery decorrelation)
│ └─ Physical plan (scan selection, join selection, aggregate strategy)
│
├─ Execute (Volcano iterator pipeline)
│ ├─ BEGIN transaction (pessimistic)
│ ├─ Pull rows through operator tree
│ ├─ Read/write TiKV (get, scan, put, delete)
│ └─ COMMIT or ROLLBACK
│
└─ Respond (pgwire RowDescription + DataRow messages)
```
### Scan strategies
[Section titled “Scan strategies”](#scan-strategies)
The physical planner selects from several scan methods:
| Scan type | When used |
| -------------------- | ------------------------------------------------------------------------------------------------------------------------ |
| **Table scan** | No matching index, or large result sets where a sequential scan is cheaper. |
| **Index range scan** | BTree index matches the predicate (equality, range, IN-list). Supports expression indexes and partial indexes. |
| **HNSW k-NN scan** | `ORDER BY vec <-> query LIMIT k` with a vector index. Uses the [usearch](https://github.com/unum-cloud/usearch) library. |
### Transaction model
[Section titled “Transaction model”](#transaction-model)
* **Default isolation**: `READ COMMITTED`, with PostgreSQL’s statement-level snapshot semantics — each statement in a transaction sees rows committed by other sessions before it started.
* **REPEATABLE READ**: Supported, backed by a single TiKV MVCC snapshot held for the transaction’s lifetime.
* **Serializable**: Not implemented. TiKV provides snapshot isolation, not PostgreSQL-style serializable snapshot isolation (SSI).
DB9 Difference: SERIALIZABLE is downgraded, not rejected
On the PostgreSQL wire protocol, `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE` succeeds with `WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ`. The transaction then runs at REPEATABLE READ, so anomalies only SSI prevents — write skew in particular — are not detected. `SHOW transaction_isolation` reports the level actually applied (`repeatable read`), which is how you can detect the downgrade at runtime. Over the HTTP SQL API the same request returns an error instead.
* **Savepoints**: Full PostgreSQL-style `SAVEPOINT` / `ROLLBACK TO` with undo records tracked in TiKV.
* **Autocommit**: Statements outside an explicit transaction run in autocommit mode.
## Multi-tenant isolation
[Section titled “Multi-tenant isolation”](#multi-tenant-isolation)
DB9 isolates tenants at the storage layer using TiKV keyspaces.
Output
```text
TiKV Cluster
├─ Keyspace A (tenant_a)
│ ├─ System metadata (tables, schemas, types, extensions, roles)
│ ├─ Table data (rows keyed by table ID + primary key)
│ ├─ Index data (secondary index entries)
│ └─ Worker state (cron jobs, task queue)
│
├─ Keyspace B (tenant_b)
│ └─ ... (completely independent copy)
│
└─ Keyspace C (tenant_c)
└─ ...
```
**How it works:**
1. The client connects with username `tenant_id.role` (e.g., `a1b2c3d4e5f6.admin`).
2. db9-server parses the username to extract the tenant ID and look up the keyspace.
3. A `TenantHandle` is acquired from the connection pool, binding all storage operations to that keyspace.
4. All key encoding includes the keyspace prefix, so reads and writes are physically separated in TiKV.
Each tenant gets independent:
* Table and schema namespaces
* Roles and privileges
* Extension installation state
* Worker queue and cron schedules
* Memory accounting (`DB9_TENANT_MEMORY_QUOTA_BYTES`)
There is no cross-tenant query path. A connection can only access its own keyspace.
## Extension system
[Section titled “Extension system”](#extension-system)
Extensions are compiled into the db9-server binary and registered at startup. They are not dynamically loaded plugins.
Each extension adds some combination of:
* **SQL functions** (e.g., `http_get()`, `fs9_read()`, `embedding()`)
* **Types** (e.g., `vector(384)`)
* **Operators** (e.g., `<->` for vector distance)
* **Index methods** (e.g., HNSW for vector search)
* **Background workers** (e.g., pg\_cron scheduler)
Extensions are installed per-tenant with `CREATE EXTENSION`:
SQL
```sql
CREATE EXTENSION http;
CREATE EXTENSION fs9;
CREATE EXTENSION vector;
CREATE EXTENSION embedding;
CREATE EXTENSION pg_cron;
```
Installation state is stored in TiKV under the tenant’s keyspace, so each database independently controls which extensions are active.
Some extensions are enabled by default when a database is created (http, pg\_cron). Others require explicit installation.
For the full list, see the [Extensions overview](/docs/extensions/).
## Connection handling
[Section titled “Connection handling”](#connection-handling)
db9-server uses a semaphore-based admission control model:
* **Max connections**: 1000 by default (configurable).
* **Per-connection**: Each client connection gets its own async Tokio task with session-local state (transaction, settings, search path).
* **Connection pool**: `TikvClientPool` caches per-keyspace `TikvStore` instances. Idle tenants are evicted after 300 seconds by default.
* **TLS**: Supported via cert/key configuration. SCRAM-SHA-256 is the default authentication method.
For connection string format, authentication options, and driver compatibility, see [Connect to DB9](/docs/connect/).
## Constraints and boundaries
[Section titled “Constraints and boundaries”](#constraints-and-boundaries)
| Area | Current state |
| ------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Execution model** | Single-process, single-node. No distributed query execution across multiple db9-server instances. |
| **Plan cache** | Session-local only. No shared prepared statement cache across connections. |
| **GIN indexes** | Functional — the planner generates GIN scan plans and the executor reads the index (`EXPLAIN ANALYZE` shows `KV Table Scan Pairs: 0`). `jsonb_ops` containment on highly distinct values still reads much of the index, so it gains less than array or full-text predicates. |
| **Parallel query** | Not supported. Queries execute on a single Tokio task. |
| **Foreign data wrappers** | Not supported. Use the HTTP extension for external data access. |
| **Logical replication** | Not supported. Use branching or the REST API for data movement. |
These are architectural boundaries, not bugs. They reflect the current stage of the system. Check the [SQL limits](/docs/sql/limits/) page for detailed compatibility notes.
## When this architecture fits well
[Section titled “When this architecture fits well”](#when-this-architecture-fits-well)
* **Agent and automation workloads** — instant provisioning, per-task isolation, and built-in capabilities (embeddings, files, HTTP, cron) reduce the number of external services.
* **Multi-tenant SaaS** — keyspace isolation provides strong data separation without managing separate database clusters.
* **Development and CI** — branching and disposable databases are first-class operations, not workarounds.
* **Workloads that fit in a single node** — the SQL engine is mature and the optimizer is cost-based, but there is no distributed execution layer today.
## When to consider alternatives
[Section titled “When to consider alternatives”](#when-to-consider-alternatives)
* You need distributed query execution across multiple nodes for analytical workloads.
* You depend on PostgreSQL extensions that DB9 does not support — check [Extensions](/docs/extensions/) and [SQL limits](/docs/sql/limits/).
* You need logical replication, foreign data wrappers, or custom C extensions.
## Next steps
[Section titled “Next steps”](#next-steps)
* [Production Checklist](/docs/production-checklist/) — authentication, observability, recovery, and operational limits
* [Connect to DB9](/docs/connect/) — connection strings, drivers, and authentication
* [Why DB9 for AI Agents](/docs/why-db9-for-ai-agents/) — how the architecture enables agent workflows
* [Extensions](/docs/extensions/) — fs9, HTTP, vector search, pg\_cron, and more
* [SQL Reference](/docs/sql/) — supported SQL syntax and compatibility notes
* [Compatibility Matrix](/docs/platform/compatibility-matrix/) — full PostgreSQL compatibility surface
* [CLI Reference](/docs/cli/) — full command reference for the control plane
# CLI Reference
> Complete reference for the db9 command-line tool — installation, authentication, database management, SQL, filesystem, filesystem watch, branching, serverless functions, and agent onboarding.
The `db9` CLI is the primary interface for creating databases, running SQL, managing files, branching, and onboarding AI agents. This page is a complete command reference.
## Common Journeys
[Section titled “Common Journeys”](#common-journeys)
| I want to… | Start here |
| ------------------------------------------ | ----------------------------------------------------------- |
| Try DB9 in under two minutes | [Quick Start](/docs/quickstart/) |
| Connect an ORM or driver | [Connect](/docs/connect/) |
| Understand the architecture | [Architecture](/docs/architecture/) |
| Set up DB9 for Claude Code or Codex | [Agent Workflows](/docs/agent-workflows/overview/) |
| Create databases programmatically | [Provisioning](/docs/platform/provisioning/) |
| Inspect query performance | [Inspect & Observability](#inspect-and-observability) below |
| Run SQL from the terminal | [SQL Execution](#sql-execution) below |
| Upload files to a database | [Filesystem](#filesystem) below |
| Create a branch for testing | [Branching](#branching) below |
| Schedule recurring SQL jobs | [Cron Jobs](#cron-jobs) below |
| Watch filesystem changes in real time | [Filesystem Watch](#filesystem-watch) below |
| Deploy serverless functions (experimental) | [Functions](#functions) below |
| Prepare for production | [Production Checklist](/docs/production-checklist/) |
## Installation
[Section titled “Installation”](#installation)
Install with the official script (Linux/macOS, amd64/arm64):
Terminal
```bash
curl -fsSL https://db9.ai/install | sh
```
To control where binaries land, set `DB9_INSTALL_DIR`:
Terminal
```bash
DB9_INSTALL_DIR="$HOME/.local/bin" curl -fsSL https://db9.ai/install | sh
```
Verify:
Terminal
```bash
db9 --version
db9 status
```
▶ Run
The installer places `db9` (and optionally `db9-fuse` for FUSE mounts) into your chosen directory.
### macOS prerequisite for `db9 fs mount`
[Section titled “macOS prerequisite for db9 fs mount”](#macos-prerequisite-for-db9-fs-mount)
`db9 fs mount` on macOS currently depends on the macFUSE kernel backend.
Terminal
```bash
brew install --cask macfuse
```
After installation, macOS may require manual system extension approval in **System Settings > Privacy & Security** before FUSE mounts are allowed.
## Global Flags
[Section titled “Global Flags”](#global-flags)
These flags apply to every `db9` command:
| Flag | Env Variable | Default | Description |
| --------------------- | ------------------ | -------------------- | --------------------------------------------------------------------------------------------------------- |
| `--api-url ` | `DB9_API_URL` | `https://api.db9.ai` | API base URL |
| `--output ` | — | `table` | Output format: `table`, `json`, `csv`, `raw` (CSV without headers), or `quiet` (minimal machine-readable) |
| `--json` | — | — | Shorthand for `--output json` |
| `--quiet` | — | — | Suppress non-essential output (only print machine-readable values) |
| `--insecure` | `DB9_INSECURE` | — | Skip TLS certificate verification |
| `--experimental` | `DB9_EXPERIMENTAL` | — | Enable experimental features (e.g. `functions`) |
| `-d, --database ` | `DB9_DATABASE` | — | Override the default database for this command |
## Authentication
[Section titled “Authentication”](#authentication)
DB9 supports three auth paths: **anonymous trial**, **SSO login**, and **API key** (CI/agents). Tokens are stored in `~/.db9/credentials`.
> **Security:** Treat `DB9_API_KEY` as a secret. Never send your token to any domain other than `api.db9.ai`.
Terminal
```bash
# Zero-setup trial (auto creates anonymous account + token)
db9 create --name quickstart
# Upgrade anonymous account to verified SSO identity
db9 claim
db9 claim --id-token
# Human operator login (browser-based)
db9 login
# API key login (CI/CD, agents)
db9 login --api-key
# Agent runtime
export DB9_API_KEY=
```
See [Token Management](#token-management) for creating automation tokens.
### Adopt Anonymous Databases
[Section titled “Adopt Anonymous Databases”](#adopt-anonymous-databases)
Transfer databases created under an anonymous trial account into your verified account:
Terminal
```bash
db9 adopt
```
This launches an interactive flow that verifies ownership of the anonymous account (via `anonymous_secret`) and migrates selected databases into your current account. For details, see [Anonymous & Claimed Databases](/docs/platform/anonymous-and-claimed-databases/).
## Setup & Utilities
[Section titled “Setup & Utilities”](#setup--utilities)
Terminal
```bash
# Guided setup: login and create your first database
db9 init
# Set or show default database (omit DB to show current)
db9 use
db9 use --clear
# Remove stored credentials
db9 logout
# Update db9 (and db9-fuse) to the latest version
db9 update
# Generate shell completion scripts
db9 completion bash
db9 completion zsh
# Show / set CLI configuration
db9 config show
db9 config set api_url https://api.db9.ai
```
## Database Lifecycle
[Section titled “Database Lifecycle”](#database-lifecycle)
Database commands accept `` as a database name (preferred) or ID. When omitted, the default database set by `db9 use` is used.
These are **top-level commands** — use `db9 create`, not `db9 db create`.
Terminal
```bash
# Create (name is optional; will auto-generate if omitted)
db9 create --name myapp
db9 create --name myapp --region us-west
db9 create --name myapp --project # assign to a project
db9 create --name myapp --password # set the admin password (default: random)
db9 create --name myapp --show-secrets # print password & connection string
# List
db9 list
# List with database storage sizes
db9 list --size
# Filter by project
db9 list --project
# Check login status
db9 status
# Show database status
db9 db status
# Print passwordless connection info (psql-compatible)
db9 db connect
# Output connection as DATABASE_URL=... format (for .env files)
db9 db connect --env
# Create a short-lived connect token (10-min TTL, for psql/ORM usage) — same as `db9 db connect`, use --user for a specific role
db9 db connect --user app_user
# Reset admin password
db9 db reset-password
# Delete (prompts unless you pass --yes)
db9 delete --yes
```
> **Deprecated forms:** `db9 db create`, `db9 db list`, `db9 db delete`, `db9 db branch`, and `db9 db connect-token` still work but print a deprecation warning. Use the top-level forms instead (`db9 db connect-token` → `db9 db connect`). Note the deprecated `db9 db connect-token` takes `--role`, while `db9 db connect` takes `--user`; neither accepts the other’s flag.
#### `db9 list` output
[Section titled “db9 list output”](#db9-list-output)
The table form prints a leading unlabeled column that marks the default database with `*`, followed by:
| Column | Shown by default | Notes |
| --------------- | ------------------ | --------------------------------------- |
| `ID` | yes | 12-character database ID |
| `NAME` | yes | |
| `STATE` | yes | Uppercase, e.g. `ACTIVE` |
| `REGION` | yes | |
| `PROJECT` | yes | Project UUID |
| `CREATED` | yes | |
| `LAST ACTIVE` | yes | `-` until activity is observed |
| `SIZE` | only with `--size` | Requires an extra API call per database |
| `LAST MODIFIED` | only with `--size` | |
`db9 list --json` returns an array of objects with nine fields: `id`, `name`, `state`, `region`, `project_id`, `created_at`, `last_active_at`, `last_modified_at`, and `default`. The two activity fields are RFC 3339 timestamps, or `null` when no activity has been observed yet; `db9 db status --json` carries them too.
For programmatic database creation patterns, see [Provisioning](/docs/platform/provisioning/).
## Shorthand Aliases
[Section titled “Shorthand Aliases”](#shorthand-aliases)
Most `db9 db ` subcommands have top-level aliases so you can type less:
| Shorthand | Equivalent |
| ---------------------------- | ------------------------- |
| `db9 sql ` | `db9 db sql ` |
| `db9 connect ` | `db9 db connect ` |
| `db9 inspect ` | `db9 db inspect ` |
| `db9 users ` | `db9 db users ` |
| `db9 seed ` | `db9 db seed ` |
| `db9 dump ` | `db9 db dump ` |
| `db9 cron ` | `db9 db cron ` |
| `db9 cat :/path` | Read a remote file |
| `db9 rm :/path` | Remove a remote file |
| `db9 mv :/src :/dst` | Move/rename a remote file |
| `db9 sh ` | `db9 fs sh ` |
| `db9 cp ` | `db9 fs cp ` |
| `db9 ls :/path` | `db9 fs ls :/path` |
| `db9 mount ` | `db9 fs mount ` |
The `cat`, `rm`, and `mv` shorthands operate on the database filesystem directly — no need for `db9 fs sh`.
## Inspect and Observability
[Section titled “Inspect and Observability”](#inspect-and-observability)
| Subcommand | Description |
| ----------------------------- | ---------------------------------- |
| `db9 db inspect` | Overview metrics dashboard |
| `db9 db inspect queries` | Query samples and performance |
| `db9 db inspect report` | Combined summary + queries report |
| `db9 db inspect schemas` | List database schemas |
| `db9 db inspect tables` | List database tables |
| `db9 db inspect indexes` | List database indexes |
| `db9 db inspect slow-queries` | Slow queries sorted by p99 latency |
Terminal
```bash
# Overview metrics
db9 db inspect
# Subcommands
db9 db inspect queries # query samples and performance
db9 db inspect report # combined summary + queries report
db9 db inspect schemas # list database schemas
db9 db inspect tables # list database tables
db9 db inspect indexes # list database indexes
db9 db inspect slow-queries # slow queries sorted by p99 latency
```
For production monitoring guidance, see [Production Checklist](/docs/production-checklist/).
## Database Users
[Section titled “Database Users”](#database-users)
Terminal
```bash
# List database users
db9 db users list
# Create a new database user (password auto-generated if omitted)
db9 db users create --username app_user --password 'SecurePass1'
# Delete a database user
db9 db users delete --username app_user
```
## SQL Execution
[Section titled “SQL Execution”](#sql-execution)
Run SQL via inline query (`-q` / `--query`), file (`-f` / `--file`), stdin, or an interactive REPL. The `` argument is optional — if omitted, the CLI auto-selects the default database or prompts interactively.
The interactive REPL defaults to **direct pgwire mode** (faster, real PostgreSQL error messages) with automatic API fallback. Use `--api` to force HTTP API mode, or `--direct` / `-D` to explicitly request pgwire.
Terminal
```bash
# Inline
db9 db sql --query "SELECT now()"
# From file
db9 db sql --file ./query.sql
# From stdin
cat ./query.sql | db9 db sql
# Interactive REPL (TTY only — defaults to direct pgwire)
db9 db sql
# Force HTTP API mode (disables auto-direct in REPL)
db9 db sql --api
# Explicit direct pgwire mode
db9 db sql --direct
db9 db sql --direct --dsn "postgresql://..."
# Execute a seed SQL file
db9 db seed ./seed.sql
# Export database schema (and optionally data) as SQL
db9 db dump
db9 db dump --ddl-only
db9 db dump --output-file ./backup.sql
```
## Filesystem
[Section titled “Filesystem”](#filesystem)
Each database has a remote filesystem. Use `db9 fs` for shell access, file copy, and (optionally) FUSE mounts.
Terminal
```bash
# List remote files and directories
db9 fs ls :/path
db9 fs ls --long :/path # long format (permissions, size, mtime)
db9 fs ls --recursive :/path # recursive listing
# Interactive filesystem shell
db9 fs sh
# Run a single command (bash -c style)
db9 fs sh --command "ls -la"
# Quick file operations (top-level shortcuts)
db9 cat :/path/to/file # Print file contents
db9 rm :/path/to/file # Remove a file
db9 mv :/old/path :/new/path # Move or rename
db9 tailf :/logs/app.log # Follow remote file appends
# Upload / download (scp-like)
db9 fs cp ./local.txt :/remote/path/local.txt
db9 fs cp :/remote/path/remote.txt ./remote.txt
# Recursive upload / download (directories)
db9 fs cp --recursive ./imports :/data/imports
# Glob upload / multi-file upload
db9 fs cp ./*.go :/remote/
db9 fs cp a.go b.go :/remote/
# Mount via FUSE (requires db9-fuse and system FUSE support)
db9 fs mount "$HOME/mnt/mydb"
db9 fs mount "$HOME/mnt/mydb" --read-only
db9 fs mount "$HOME/mnt/mydb" --cache-ttl 30
db9 fs mount "$HOME/mnt/mydb" --multipart-threshold 16777216 # 16 MB
db9 fs mount "$HOME/mnt/mydb" -f # foreground
db9 fs mount "$HOME/mnt/mydb" --write-async # client async writeback (alias: --writeback); close() seals to local journal, drains opportunistically — not a remote-commit promise
db9 fs mount "$HOME/mnt/mydb" --include '*.md' # sync only .md files
db9 fs mount "$HOME/mnt/mydb" --include '*.rs' --exclude 'target/**'
db9 fs mount "$HOME/mnt/mydb" --no-ignore # disable .fuseignore
```
### Filesystem Watch
[Section titled “Filesystem Watch”](#filesystem-watch)
Monitor filesystem events in real time. The `watch` command uses WebSocket push when the server advertises the `watch` capability, and falls back to polling the `fs9_events()` table function when it does not.
Terminal
```bash
# Watch all events on a database
db9 fs watch :/
# Watch events under a specific path
db9 fs watch :/data/
# Poll every 5 seconds (default: 1) — applies only to the polling fallback
db9 fs watch :/ --interval 5
# Output JSON lines (for piping to other tools)
db9 fs watch :/ --json
```
Supported event types: `CREATE`, `WRITE`, `DELETE`, `RENAME`, `MKDIR`. Events include the path, timestamp, file size, and (for renames) the old path.
Plain-text output example:
Output
```text
10:23:45.123 CREATE /data/file.txt
10:23:46.456 RENAME /data/old.txt -> /data/new.txt
10:23:47.789 DELETE /data/file.txt
```
If the internal event ring overflows (events arrive faster than the polling interval), the CLI prints a warning so you can increase the polling frequency or narrow the path prefix.
### File Tail (follow)
[Section titled “File Tail (follow)”](#file-tail-follow)
Follow a remote file and print appended content, similar to `tail -f`:
Terminal
```bash
# Follow a log file (prints last 10 lines, then streams new content)
db9 fs tailf :/logs/app.log
# Show last 50 lines before following
db9 fs tailf :/logs/app.log --lines 50
# Custom polling interval (default: 1 second)
db9 fs tailf :/logs/app.log --interval 2
```
### Tee (stdin to remote file)
[Section titled “Tee (stdin to remote file)”](#tee-stdin-to-remote-file)
Read stdin, echo it to stdout, and write it to a remote file — like Unix `tee` but targeting the database filesystem:
Terminal
```bash
# Pipe command output to a remote file
echo "hello" | db9 fs tee :/logs/out.txt
# Append instead of overwriting
cat local.txt | db9 fs tee -a :/logs/out.txt
```
| Flag | Default | Description |
| -------------- | ------- | ----------------------------------------- |
| `-a, --append` | off | Append to the file instead of overwriting |
Advanced filesystem flags (all `fs` subcommands): `--ws-url ` (direct WebSocket URL), `--ws-port ` (default: 5480).
### Filesystem Shell Builtins
[Section titled “Filesystem Shell Builtins”](#filesystem-shell-builtins)
The interactive shell (`db9 fs sh`) supports a POSIX-like command set:
| Category | Commands |
| --------------- | --------------------------------------------------------------------------------------------------- |
| File ops | `ls`, `cat`, `cp`, `mv`, `rm`, `touch`, `mkdir`, `stat`, `find`, `tree`, `diff`, `patch` |
| Text processing | `grep`, `head`, `tail`, `sort`, `uniq`, `cut`, `wc`, `tr`, `rev`, `tee`, `jq` |
| Navigation | `cd`, `pwd`, `basename`, `dirname` |
| Shell | `echo`, `printf`, `read`, `export`, `set`, `test`, `alias`, `source`, `env`, `date`, `sleep`, `seq` |
| Control flow | `if`/`then`/`fi`, `for`/`do`/`done`, `while`/`do`/`done`, `case`/`esac`, pipes, redirections |
Use `help ` inside the shell for per-command usage.
For querying files with SQL, see [fs9 extension](/docs/extensions/fs9/).
## Migrations
[Section titled “Migrations”](#migrations)
Manage local migration files and apply them to a database. Default directory is `./migrations`.
Terminal
```bash
# Create a new migration file
db9 migration new create_users
# List local migration files
db9 migration list
# See applied vs pending
db9 migration status
# Apply pending migrations
db9 migration up
```
All migration subcommands accept `--dir ` to override the default `./migrations` directory.
## Branching
[Section titled “Branching”](#branching)
Branches are schema copies used for safe experimentation. Branch commands are **top-level** under `db9 branch`.
Terminal
```bash
# Create a branch from an existing database
db9 branch create --name feature1
db9 branch create --name feature1 --show-secrets # print password + connection string
db9 branch create --name feature1 --show-password # print password only
db9 branch create --name feature1 --show-connection-string # print connection string only
# Point-in-time branch (PITR) — branch from a specific past timestamp
db9 branch create --name rollback --snapshot-at 2026-03-22T06:00:00Z
# List branches of a database
db9 branch list
# Delete a branch database
db9 branch delete
```
The `--snapshot-at` flag accepts an RFC 3339 UTC timestamp (e.g. `2026-03-22T06:00:00Z`). When TiKV snapshot restore is available, the branch will contain the parent’s data as of that timestamp rather than the current state.
For branch-based workflows (preview environments, rollback, task isolation), see [Multi-Tenant Patterns](/docs/platform/multi-tenant-patterns/).
## Cron Jobs
[Section titled “Cron Jobs”](#cron-jobs)
Schedule SQL with pg\_cron (extension required). Cron commands live under `db9 db cron`.
| Subcommand | Description |
| --------------------- | -------------------------- |
| `db9 db cron list` | List all cron jobs |
| `db9 db cron create` | Create a cron job |
| `db9 db cron delete` | Delete a job by ID or name |
| `db9 db cron history` | Show job execution history |
| `db9 db cron enable` | Enable a disabled job |
| `db9 db cron disable` | Disable a job |
| `db9 db cron status` | Show job status |
Terminal
```bash
# List jobs
db9 db cron list
# Create (schedule + SQL command)
db9 db cron create "*/5 * * * *" "SELECT 1"
# Create from file (optional name enables upsert semantics)
db9 db cron create "0 * * * *" --name hourly_job -f ./job.sql
# History / status
db9 db cron history --job --limit 20
db9 db cron status
# Enable / disable / delete by job id or job name
db9 db cron disable
db9 db cron enable
db9 db cron delete
```
For scheduling patterns and operational guidance, see [pg\_cron extension](/docs/extensions/pg-cron/).
## Functions
[Section titled “Functions”](#functions)
Serverless functions let you deploy and run JavaScript/TypeScript code that executes against your database.
| Subcommand | Description |
| ------------------------------ | --------------------------------------------------- |
| `db9 functions list` | List functions in a database |
| `db9 functions create` | Create a new function |
| `db9 functions update` | Update an existing function by name or ID |
| `db9 functions invoke` | Invoke a function |
| `db9 functions history` | List recent runs, or show details of a specific run |
| `db9 functions logs` | Show logs for a specific run |
| `db9 functions secrets list` | List all secrets (names only) |
| `db9 functions secrets set` | Create or update a secret |
| `db9 functions secrets delete` | Delete a secret |
Terminal
```bash
# List functions (this subcommand takes --database or a positional DB, not --db)
db9 functions list --database myapp
# Create a function (reads index.js or main.ts from current directory)
db9 functions create my-func --db myapp
# Create with TypeScript source
db9 functions create my-func --db myapp --ts
# Create with execution limits
db9 functions create my-func --db myapp \
--limits-json '{"timeout_ms":60000,"memory_mb":128}'
db9 functions create my-func --db myapp --limits-file ./limits.json
db9 functions create my-func --db myapp --timeout 30000
# Bind secrets to a function
db9 functions create my-func --db myapp --secret API_KEY=my_api_key
# Grant filesystem access to a function
db9 functions create my-func --db myapp --fs9-scope /data:ro --fs9-scope /output:rw
# Create a public function (callable via publishable key)
db9 functions create my-func --db myapp --visibility public
# Update an existing function
db9 functions update my-func --db myapp -f ./updated.js
db9 functions update my-func --db myapp --timeout 60000 --secret NEW_KEY=my_new_key
db9 functions update my-func --db myapp --visibility private
# Invoke a function
db9 functions invoke my-func --db myapp
db9 functions invoke my-func --db myapp --payload '{"key":"value"}'
# View run history and logs
db9 functions history my-func --db myapp -n 50
db9 functions history my-func --db myapp
db9 functions logs my-func --db myapp
# Manage secrets
db9 functions secrets list --db myapp
db9 functions secrets set MY_SECRET --db myapp --value "secret-value"
db9 functions secrets set MY_SECRET --db myapp --value-stdin
db9 functions secrets delete MY_SECRET --db myapp
```
The `--db` flag is optional when a default database is set via `db9 use`. The `--timeout` convenience flag overrides any `timeout_ms` value in the limits JSON.
## Sharing
[Section titled “Sharing”](#sharing)
`db9 share` is a shorthand for creating a database-scoped token. It creates the token and prints a ready-to-use connection string.
Terminal
```bash
# Read-only share (default), 7-day expiry
db9 share my-app
# Read-write share
db9 share my-app --rw
# Custom expiry (30 days)
db9 share my-app --expires 30
# Custom token name
db9 share my-app --name team-token
```
| Flag | Default | Description |
| ------------------ | --------------------- | -------------------------------------------- |
| `--rw` | off | Grant read-write access (default: read-only) |
| `--expires ` | `7` | Token expiry in days |
| `--name ` | `share--YYYYMMDD` | Custom token name |
Under the hood this calls `POST /customer/tokens` with the appropriate `scope_json`. For more control (multi-database scopes, custom names), use `db9 token create --scope`.
## Explorer
[Section titled “Explorer”](#explorer)
`db9 explore` launches a browser-based file and SQL explorer. It downloads a pre-built SPA to `~/.db9/explorer/` and serves it locally with API proxying.
Terminal
```bash
# Open explorer for a database
db9 explore mydb
# Custom port
db9 explore mydb --port 8080
# Don't auto-open the browser
db9 explore mydb --no-open
# Use cached assets without checking for updates
db9 explore --no-download
# Force re-download even if cached
db9 explore mydb --force-download
```
| Flag | Default | Description |
| ------------------ | ------- | ------------------------------------- |
| `--port ` | `7979` | Local port (env: `DB9_EXPLORER_PORT`) |
| `--no-open` | off | Skip automatic browser open |
| `--no-download` | off | Use cached assets, skip update check |
| `--force-download` | off | Re-download assets even if cached |
## Token Management
[Section titled “Token Management”](#token-management)
Create and manage API tokens for CI/CD pipelines, automation, and agent workflows.
Terminal
```bash
# Show the current raw token (for use with DB9_API_KEY)
db9 token show
# Create a new API token (default: 365-day expiry)
db9 token create --name my-agent
db9 token create --name ci-token --expires-in-days 90
# Create a database-scoped token (read-only or read-write)
db9 token create --name readonly-token --scope mydb:ro
db9 token create --name multi-db --scope app:rw --scope analytics:ro
# List your API tokens
db9 token list
# Revoke a token
db9 token revoke
```
Store created tokens in a secret manager. See [Production Checklist](/docs/production-checklist/) for token security guidance.
## Type Generation
[Section titled “Type Generation”](#type-generation)
Generate types from the live schema (prints to stdout). Available languages: TypeScript and Python.
Terminal
```bash
# TypeScript
db9 gen types --lang typescript --schema public > db9.types.ts
# Python
db9 gen types --lang python --schema public > db9_types.py
```
## Agent Onboarding
[Section titled “Agent Onboarding”](#agent-onboarding)
`db9 onboard` installs or updates the DB9 skill into supported local coding agents. It does not require login and does not send tokens anywhere.
Terminal
```bash
# Interactive wizard
db9 onboard
# Non-interactive
db9 onboard --yes --all
db9 onboard --yes --agent codex --agent claude --scope user
# Safety / introspection (zero filesystem changes)
db9 onboard --dry-run
db9 onboard --print-locations
# Advanced: custom skill source
db9 onboard --skill-url https://db9.ai/skill.md
db9 onboard --skill-path ./SKILL.md
db9 onboard --force
```
Supported agents and default install locations:
| Agent | `--scope user` | `--scope project` | Notes |
| ---------- | --------------------------------------------------- | --------------------------------- | ---------------------------------------- |
| `codex` | `~/.codex/skills/db9/SKILL.md` (or `$CODEX_HOME/…`) | — | Codex currently supports user scope only |
| `claude` | `~/.claude/skills/db9/SKILL.md` | `./.claude/skills/db9/SKILL.md` | `--scope both` installs both |
| `opencode` | `~/.config/opencode/skills/db9/SKILL.md` | `./.opencode/skills/db9/SKILL.md` | `--scope both` installs both |
| `agents` | `~/.agents/skills/db9/SKILL.md` | `./.agents/skills/db9/SKILL.md` | Generic agent-compatible directory |
For agent workflow patterns, see [Agent Workflows](/docs/agent-workflows/overview/).
## Error Messages
[Section titled “Error Messages”](#error-messages)
Common errors and how to resolve them.
| Error | Cause | Resolution |
| ----------------------------- | --------------------------------------------------------- | ----------------------------------------------------------------------------------- |
| `not authenticated` | No token found in `~/.db9/credentials` | Run `db9 login` or set `DB9_API_KEY` env var |
| `database not found` | Wrong name/ID, or database was deleted | Run `db9 list` to see available databases |
| `permission denied` | Token lacks the required scope | Create a new token: `db9 token create` |
| `connection refused` | API endpoint unreachable | Check network; verify `--api-url` flag or `DB9_API_URL` env var |
| `rate limit exceeded` | Too many requests in a short window | Wait and retry; implement exponential backoff in scripts |
| `name already exists` | Duplicate database name | Choose a different name, or delete the existing database first |
| `invalid token` | Token has expired or been revoked | Run `db9 login` or create a new token |
| `database is not active` | Database is in `CREATING`, `CLONING`, or `DISABLED` state | Check state with `db9 db status `; wait for `ACTIVE` |
| `no databases found` | Account has no databases yet | Run `db9 create --name ` to create one |
| `WebSocket connection failed` | Filesystem (`fs9`) WebSocket unavailable | Verify the database is active; check `fs_websocket_url` with `db9 db status ` |
## Exit Codes
[Section titled “Exit Codes”](#exit-codes)
`db9` follows standard Unix exit code conventions for use in shell scripts and CI/CD pipelines.
| Code | Meaning | Example trigger |
| ---- | ------------- | ----------------------------------------------------------------------------------------------------- |
| `0` | Success | Command completed without errors (including `--help` and `--version`) |
| `1` | Runtime error | The command parsed, but failed — database not found, SQL error, API error. See stderr |
| `2` | Usage error | The command did not parse — unknown subcommand or flag, missing required argument, invalid flag value |
Check for **any** non-zero code rather than testing for `1` specifically: a typo in a flag name exits `2`, not `1`.
**Usage in shell scripts:**
Terminal
```bash
db9 create --name mydb
if [ $? -ne 0 ]; then
echo "Database creation failed" >&2
exit 1
fi
# Or inline
db9 create --name mydb || { echo "Failed"; exit 1; }
```
**Capturing error output:**
Terminal
```bash
output=$(db9 db sql mydb -q "SELECT 1" 2>&1)
if [ $? -ne 0 ]; then
echo "Error: $output" >&2
exit 1
fi
```
## Troubleshooting
[Section titled “Troubleshooting”](#troubleshooting)
### Authentication failures
[Section titled “Authentication failures”](#authentication-failures)
**Symptom:** `not authenticated` or `invalid token` errors.
Terminal
```bash
# Check current auth status
db9 status
# Re-authenticate interactively
db9 login
# Use an API key directly (CI/CD)
export DB9_API_KEY=your-token
db9 list
# View the credentials file
cat ~/.db9/credentials
# List all tokens
db9 token list
```
### Network errors and connection refused
[Section titled “Network errors and connection refused”](#network-errors-and-connection-refused)
**Symptom:** `connection refused` or timeouts when running any command.
Terminal
```bash
# Check which API endpoint is configured
db9 status
# Override the API URL for a single command
db9 --api-url https://api.db9.ai list
# Set permanently via environment
export DB9_API_URL=https://api.db9.ai
```
If behind a corporate proxy, ensure `HTTPS_PROXY` or `https_proxy` is set in your environment before running `db9` commands.
### Permission denied
[Section titled “Permission denied”](#permission-denied)
**Symptom:** Commands fail with `permission denied` after authentication succeeds.
API tokens can be created with limited scopes. Create a new full-access token and switch to it:
Terminal
```bash
db9 token create --name full-access
export DB9_API_KEY=
```
### Database stuck in non-ACTIVE state
[Section titled “Database stuck in non-ACTIVE state”](#database-stuck-in-non-active-state)
**Symptom:** `database is not active` when running SQL or filesystem commands.
Terminal
```bash
# Check current state
db9 db status mydb
```
States and meanings:
| State | Description |
| --------------- | ------------------------------------------------ |
| `CREATING` | Initial provisioning in progress (usually < 30s) |
| `CLONING` | Branch copy in progress |
| `ACTIVE` | Ready for connections |
| `DISABLING` | Deletion in progress |
| `DISABLED` | Deleted or suspended |
| `CREATE_FAILED` | Provisioning failed — delete and recreate |
For branch databases, poll until active:
Terminal
```bash
while [ "$(db9 db status mydb --json 2>/dev/null | jq -r .state)" != "ACTIVE" ]; do
echo "Waiting for ACTIVE state…"
sleep 2
done
```
### Filesystem (fs9) WebSocket errors
[Section titled “Filesystem (fs9) WebSocket errors”](#filesystem-fs9-websocket-errors)
**Symptom:** `WebSocket connection failed` when using `db9 fs` commands.
Terminal
```bash
# Verify the database is ACTIVE
db9 db status mydb
# Check the WebSocket URL is present
db9 db status mydb --json | jq .fs_websocket_url
```
If using the TypeScript SDK on Node.js 18–20, install the `ws` package:
Terminal
```bash
npm install ws
```
Then pass it as the `WebSocket` option when creating the client — see [Filesystem (client.fs)](/docs/sdk/#filesystem-clientfs).
### Verbose debug output
[Section titled “Verbose debug output”](#verbose-debug-output)
Set `DB9_DEBUG=1` to enable verbose logging for any command:
Terminal
```bash
DB9_DEBUG=1 db9 list
DB9_DEBUG=1 db9 db sql mydb -q "SELECT 1"
```
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [TypeScript SDK](/docs/sdk/) — Server-side database management with get-db9
* [Browser SDK](/docs/sdk-browser/) — Client-side data access with @db9/browser
* [SQL Reference](/docs/sql/) — SQL engine compatibility and features
* [Extensions](/docs/extensions/) — fs9, HTTP, vector, pg\_cron, and more
* [Connect](/docs/connect/) — Connection strings, TLS, and driver configuration
# DB9 vs Neon
> A fair comparison of DB9 and Neon — when each is the better choice for your PostgreSQL workload, and where they differ on architecture, branching, extensions, and developer experience.
DB9 and Neon are both serverless PostgreSQL-compatible databases, but they solve different problems. Neon optimizes for infrastructure flexibility — autoscaling, scale-to-zero, and efficient branching. DB9 optimizes for instant provisioning, AI agent integration, and built-in application-layer extensions.
This page is for developers evaluating both platforms. It covers the key differences honestly so you can choose the right one.
## At a Glance
[Section titled “At a Glance”](#at-a-glance)
| Capability | DB9 | Neon |
| ------------------------- | ----------------------------------------------------------------------------- | --------------------------------------- |
| **Provisioning** | Synchronous, under 1 second | Seconds (project + endpoint startup) |
| **Autoscaling** | No | Yes (0.25–56 CU) |
| **Scale-to-zero** | No (always on) | Yes (5-minute idle default) |
| **Branching** | Full data copy, async | Copy-on-write, instant |
| **Read replicas** | No | Yes (zero storage overhead) |
| **Connection pooling** | No built-in pooler | Built-in PgBouncer |
| **Serverless driver** | Yes (`@db9/browser` over HTTP) | Yes (`@neondatabase/serverless`) |
| **Extensions** | 9 built-in | 40+ community |
| **HTTP from SQL** | Yes (built-in `http` extension — GET, POST, PUT, DELETE, PATCH, HEAD) | No |
| **File system in SQL** | Yes (built-in `fs9` extension) | No |
| **FUSE filesystem mount** | Yes (`db9 fs mount`) | No |
| **Native embeddings** | Yes (built-in `embedding()` function) | No (pgvector only, external embeddings) |
| **Document chunking** | Yes (built-in `CHUNK_TEXT()` for RAG) | No |
| **Full-text search** | Yes (English, Chinese/jieba, ngram tokenizers) | Yes (standard PostgreSQL FTS) |
| **Parquet import** | Yes (built-in `read_parquet()` + COPY FORMAT parquet) | No |
| **Agent onboarding** | Yes (`db9 onboard` for Claude, Codex, etc.) | No |
| **Anonymous access** | Yes (no signup required for up to 5 databases) | No (account required) |
| **CLI observability** | Yes (`db9 db inspect` — queries, slow-queries, schemas, indexes) | No |
| **Logical replication** | No | Yes |
| **Row-level security** | Yes | Yes |
| **LISTEN/NOTIFY** | Yes (pgwire sessions; HTTP API can `NOTIFY` only) | Yes |
| **Transaction isolation** | READ COMMITTED / REPEATABLE READ (SERIALIZABLE downgraded to REPEATABLE READ) | Full SERIALIZABLE |
| **Storage engine** | TiKV (distributed KV) | Custom (Pageserver + Safekeeper) |
| **Wire protocol** | pgwire v3 | pgwire v3 |
## When should I use DB9 instead of Neon?
**Instant database provisioning at scale.** DB9 creates databases synchronously in under a second. This makes it practical to provision a database per user, per agent task, or per CI run without waiting. The SDK’s `instantDatabase()` creates and returns a ready-to-use connection in a single call.
**AI agent workflows.** DB9 is designed for AI agents. The `db9 onboard` CLI installs skills for Claude Code, OpenAI Codex, Opencode, and other agents. Anonymous accounts let agents create databases without signup flows. Built-in `embedding()` generates vector embeddings from text directly in SQL — no external embedding API needed.
**Application-layer extensions.** DB9 includes extensions that run application logic inside the database:
* `http` — make HTTP requests from SQL (GET, POST, PUT, DELETE, PATCH, HEAD)
* `fs9` — read and write files from SQL, query file contents as tables; FUSE mount for local filesystem access
* `embedding()` — generate text embeddings natively (default model: text-embedding-v4, 1024 dimensions)
* `CHUNK_TEXT()` — split documents into overlapping chunks for RAG pipelines
* `read_parquet()` — import Parquet files directly in SQL
* `pg_cron` — schedule recurring SQL jobs
* Full-text search with Chinese tokenizer support (jieba/zhparser)
These are compiled into DB9 and available without installation. Neon does not offer equivalent built-in HTTP, file system, native embedding, document chunking, or Parquet import capabilities.
**No-signup developer experience.** Developers can install the CLI and create a database without creating an account. Anonymous accounts support up to 5 databases and can be upgraded later with `db9 claim`. This reduces friction for prototyping, tutorials, and agent-driven workflows.
## When should I use Neon instead of DB9?
**Variable or unpredictable workloads.** Neon autoscales compute between a minimum and maximum (up to 56 CU) based on load. When idle, compute scales to zero — you pay nothing for unused databases. DB9 databases are always on with fixed compute.
**Cost-sensitive development environments.** Neon’s free tier includes 100 projects with scale-to-zero. Development and staging databases that sit idle most of the time cost nothing. DB9 does not scale to zero, so idle databases still consume resources.
**Development branching.** Neon branches are copy-on-write and instant regardless of database size. A 100 GB production database branches in milliseconds with no additional storage until writes diverge. DB9 branches are full data copies — creation is asynchronous and takes seconds to minutes depending on size, with each branch consuming its own storage.
**Read-heavy workloads.** Neon supports read replicas that share the same storage backend. You can scale read capacity independently without data duplication. DB9 does not support read replicas.
**Broad extension ecosystem.** Neon supports 40+ PostgreSQL extensions including PostGIS, pg\_trgm, pgcrypto, ltree, and pg\_partman. DB9 has 9 built-in extensions and cannot install community extensions. If your application depends on PostGIS or other specialized extensions, Neon is the better fit.
**Serverless and edge deployments.** Neon’s `@neondatabase/serverless` and DB9’s `@db9/browser` both enable SQL queries over HTTP from edge runtimes (Cloudflare Workers, Vercel Edge Functions) where TCP connections are not available. DB9’s standard pgwire path remains TCP-based for traditional PostgreSQL drivers.
**Full PostgreSQL feature parity.** Neon supports table partitioning, logical replication, and SERIALIZABLE isolation. DB9 does not support table partitioning or logical replication, advisory locks are node-local rather than cross-process/global, and `LISTEN` requires a direct pgwire connection (the HTTP SQL API can `NOTIFY` but not subscribe).
Choose Neon if you need these PostgreSQL features
If your application depends on table partitioning, logical replication, community extensions (PostGIS, pg\_trgm, pgcrypto), or true SERIALIZABLE isolation, Neon is the better choice for your current requirements.
## Architecture Differences
[Section titled “Architecture Differences”](#architecture-differences)
### Storage
[Section titled “Storage”](#storage)
**DB9** uses TiKV, a distributed key-value store, as its storage engine. Each database gets an isolated TiKV keyspace. This architecture enables instant provisioning (creating a keyspace is fast) but means branching requires a full data copy.
**Neon** uses a custom storage layer (Pageserver + Safekeeper) that separates compute from storage. This enables copy-on-write branching, point-in-time recovery, and read replicas that share storage. Compute endpoints connect to storage over the network and can scale independently.
### Compute
[Section titled “Compute”](#compute)
**DB9** runs a custom SQL engine that parses, optimizes, and executes queries against TiKV. Each database has dedicated compute that is always running. There is no autoscaling or scale-to-zero.
**Neon** runs standard PostgreSQL (with custom storage hooks). Compute endpoints autoscale between configured CU ranges and suspend after idle timeout. Resumed endpoints reconnect to persistent storage.
### Branching
[Section titled “Branching”](#branching)
**DB9** branches by copying all data from the parent database into a new TiKV keyspace. This is asynchronous — state progresses from CLONING to ACTIVE. Maximum 2 concurrent branch creations. Each branch is a fully independent database.
**Neon** branches by creating a new compute endpoint pointing to the same storage at a specific point in time. No data is copied at branch time. Writes to the branch are stored as deltas. This is instant and storage-efficient.
## Extension Comparison
[Section titled “Extension Comparison”](#extension-comparison)
| Extension / Capability | DB9 | Neon |
| -------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ----------------------- |
| pgvector (vector type + HNSW) | Built-in | Available |
| Native embedding generation | Built-in (`embedding()`) | Not available |
| Document chunking for RAG | Built-in (`CHUNK_TEXT()`) | Not available |
| HTTP requests from SQL | Built-in (`http` — 6 methods) | Not available |
| File system access from SQL | Built-in (`fs9` extension) | Not available |
| FUSE filesystem mount | Built-in (`db9 fs mount`) | Not available |
| Parquet import | Built-in (`read_parquet()` + COPY) | Not available |
| Full-text search (Chinese/jieba) | Built-in (zhparser extension) | Standard PostgreSQL FTS |
| pg\_cron (job scheduling) | Built-in | Available |
| uuid-ossp | Built-in | Available |
| hstore | Built-in | Available |
| PostGIS | Not available | Available |
| pg\_trgm (trigram search) | Not available | Available |
| pgcrypto | Metadata shim only — `CREATE EXTENSION` succeeds but registers no functions; `gen_random_uuid()` and `digest()` are built-ins, while `crypt()`/`gen_salt()`/`hmac()` are unavailable | Available |
| ltree | Not available | Available |
| pg\_partman | Not available | Available |
| Custom / community extensions | Not supported | 40+ supported |
## ORM and Driver Compatibility
[Section titled “ORM and Driver Compatibility”](#orm-and-driver-compatibility)
Both platforms support standard PostgreSQL ORMs and drivers. DB9 has tested compatibility with:
| ORM / Driver | DB9 Test Results | Neon |
| ------------ | -------------------- | --------- |
| Prisma | 89/89 (100%) | Supported |
| Drizzle | 75/75 (100%) | Supported |
| SQLAlchemy | Tested (smoke + e2e) | Supported |
| TypeORM | 147/150 (98%) | Supported |
| Sequelize | 87/87 (100%) | Supported |
| Knex | 97/97 (100%) | Supported |
| GORM | Tested (smoke + e2e) | Supported |
Both platforms use pgwire v3 and work with any PostgreSQL-compatible driver (node-postgres, psycopg, pgx, JDBC).
## Migration Between Platforms
[Section titled “Migration Between Platforms”](#migration-between-platforms)
Moving from Neon to DB9 or vice versa uses standard `pg_dump` / `psql` workflows. See the [Migrate from Neon](/docs/migrations/from-neon/) guide for step-by-step instructions.
Key migration considerations:
* DB9 does not use Neon’s serverless driver — switch to `@db9/browser` (HTTP) or a standard PostgreSQL driver
* DB9 does not support logical replication
* DB9 connection strings use a different format (`tenant_id.role@host:5433/postgres`)
* Extensions not in DB9’s built-in set will not be available
## Summary
[Section titled “Summary”](#summary)
**DB9 is the better choice when** you need instant database provisioning, AI agent integration, or application-layer SQL extensions (HTTP, files, native embeddings). It excels at multi-tenant patterns where each user or agent gets their own database.
**Neon is the better choice when** you need autoscaling, scale-to-zero, efficient branching, read replicas, or broad extension support. It excels at variable workloads where cost optimization and PostgreSQL feature completeness matter.
Both platforms are PostgreSQL-compatible and work with the same ORMs, drivers, and SQL. The right choice depends on which capabilities matter most for your workload.
## Next Pages
[Section titled “Next Pages”](#next-pages)
* [Migrate from Neon](/docs/migrations/from-neon/) — step-by-step migration guide
* [DB9 vs Supabase](/docs/comparisons/db9-vs-supabase/) — comparison with Supabase
* [Compatibility Matrix](/docs/platform/compatibility-matrix/) — detailed feature support
* [Overview](/docs/overview/) — what DB9 is and who it is for
* [Why DB9 for AI Agents](/docs/why-db9-for-ai-agents/) — the agent-native positioning
# DB9 vs PlanetScale
> A fair comparison of DB9 and PlanetScale Postgres — when each is the better choice for your PostgreSQL workload, and where they differ on architecture, extensions, and developer experience.
DB9 and PlanetScale are both managed PostgreSQL-compatible databases, but they solve different problems. PlanetScale optimizes for operational reliability — high availability, read replicas, and connection pooling. db9 database optimizes for instant provisioning, AI agent integration, and built-in application-layer extensions.
This page is for developers evaluating both platforms. It covers the key differences honestly so you can choose the right one.
## At a Glance
[Section titled “At a Glance”](#at-a-glance)
| Capability | DB9 | PlanetScale Postgres |
| ------------------------- | ----------------------------------------------------------------------------- | ---------------------------------------------- |
| **PostgreSQL version** | Custom engine (pgwire v3) | Standard PostgreSQL v17 |
| **Storage engine** | TiKV (distributed KV) | EBS or NVMe Metal |
| **Provisioning** | Synchronous, under 1 second | Not documented |
| **Scale-to-zero** | No (always on) | No (always on) |
| **High availability** | No | Yes (1 primary + 2 replicas, 3 AZs) |
| **Connection pooling** | No built-in pooler | Yes (PgBouncer add-on) |
| **Read replicas** | No | Yes (add-on) |
| **Branching** | Full data copy, async | Schema-only copy from backup |
| **Free tier** | Yes (no signup, up to 5 databases) | No (14-day trial, $5/mo minimum) |
| **Native embeddings** | Yes (built-in `embedding()` function) | No |
| **Document chunking** | Yes (built-in `CHUNK_TEXT()` for RAG) | No |
| **HTTP from SQL** | Yes (built-in `http` extension — GET, POST, PUT, DELETE, PATCH, HEAD) | No |
| **File system in SQL** | Yes (built-in `fs9` extension) | No |
| **Parquet import** | Yes (built-in `read_parquet()` + COPY FORMAT parquet) | No |
| **Agent onboarding** | Yes (`db9 onboard` for Claude, Codex, etc.) | No (MCP server available) |
| **MCP integration** | No | Yes (16 tools, hosted) |
| **Extensions** | 9 built-in | Curated (vectorscale, TimescaleDB, pg\_strict) |
| **LISTEN/NOTIFY** | Yes (pgwire sessions; HTTP API can `NOTIFY` only) | Yes (standard PostgreSQL) |
| **Transaction isolation** | READ COMMITTED / REPEATABLE READ (SERIALIZABLE downgraded to REPEATABLE READ) | Full SERIALIZABLE |
## When should I use DB9 instead of PlanetScale?
**Instant database provisioning at scale.** DB9 creates databases synchronously in under a second. This makes it practical to provision a database per user, per agent task, or per CI run without waiting. The SDK’s `instantDatabase()` creates and returns a ready-to-use connection in a single call.
**AI agent workflows.** DB9 is designed for AI agents. The `db9 onboard` CLI installs skills for Claude Code, OpenAI Codex, Opencode, and other agents. Anonymous accounts let agents create databases without signup flows. Built-in `embedding()` generates vector embeddings from text directly in SQL — no external embedding API needed.
**Application-layer extensions.** DB9 includes extensions that run application logic inside the database:
* `http` — make HTTP requests from SQL (GET, POST, PUT, DELETE, PATCH, HEAD)
* `fs9` — read and write files from SQL, query file contents as tables; FUSE mount for local filesystem access
* `embedding()` — generate text embeddings natively (default model: text-embedding-v4, 1024 dimensions)
* `CHUNK_TEXT()` — split documents into overlapping chunks for RAG pipelines
* `read_parquet()` — import Parquet files directly in SQL
* `pg_cron` — schedule recurring SQL jobs
* Full-text search with Chinese tokenizer support (jieba/zhparser)
These are compiled into DB9 and available without installation. PlanetScale does not offer equivalent built-in HTTP, file system, native embedding, document chunking, or Parquet import capabilities.
**No-signup developer experience.** Developers can install the CLI and create a database without creating an account. Anonymous accounts support up to 5 databases and can be upgraded later with `db9 claim`. PlanetScale requires a paid account ($5/mo minimum) after a 14-day trial — there is no permanent free tier.
## When should I use PlanetScale instead of DB9?
**Production workloads requiring high availability.** PlanetScale deploys every database with one primary and two replicas across three availability zones by default. Automatic failover handles node failures without application changes. DB9 does not offer built-in multi-AZ replication or automatic failover.
**Read-heavy workloads.** PlanetScale supports read replicas as an add-on, letting you scale read capacity independently. DB9 does not support read replicas.
**Connection-heavy applications.** PlanetScale offers a PgBouncer-based connection pooling add-on for applications that open many short-lived connections (serverless functions, high-concurrency web apps). DB9 does not include a built-in connection pooler.
**MCP-first agent integration.** PlanetScale provides a hosted MCP server with 16 tools for database management, schema exploration, and query execution. If your agent workflow is built around MCP rather than CLI onboarding, PlanetScale has a more mature MCP integration. DB9 focuses on CLI-based agent onboarding via `db9 onboard`.
**Standard PostgreSQL compatibility.** PlanetScale runs standard PostgreSQL v17 with full support for SERIALIZABLE isolation and a curated set of extensions including vectorscale, TimescaleDB, and pg\_strict. DB9 does not support SERIALIZABLE isolation, its extension set is limited to 9 built-in options, and `LISTEN` requires a direct pgwire connection.
Choose PlanetScale if you need these PostgreSQL features
If your application depends on SERIALIZABLE isolation, high availability with automatic failover, connection pooling, or read replicas, PlanetScale is the better choice for your current requirements.
## Architecture Differences
[Section titled “Architecture Differences”](#architecture-differences)
### Storage
[Section titled “Storage”](#storage)
**DB9** uses TiKV, a distributed key-value store, as its storage engine. Each database gets an isolated TiKV keyspace. This architecture enables instant provisioning (creating a keyspace is fast) but means branching requires a full data copy.
**PlanetScale** uses either EBS or NVMe Metal storage depending on the plan. Storage is tightly coupled to compute nodes. The three-node default deployment (one primary, two replicas) provides durability through synchronous replication across availability zones.
### Compute
[Section titled “Compute”](#compute)
**DB9** runs a custom SQL engine that parses, optimizes, and executes queries against TiKV. Each database has dedicated compute that is always running. There is no autoscaling or scale-to-zero.
**PlanetScale** runs standard PostgreSQL v17. Each database has fixed compute with always-on nodes. There is no autoscaling or scale-to-zero. The focus is on operational reliability through multi-AZ replication rather than elastic scaling.
### Branching
[Section titled “Branching”](#branching)
**DB9** branches by copying all data from the parent database into a new TiKV keyspace. This is asynchronous — state progresses from CLONING to ACTIVE. Maximum 2 concurrent branch creations. Each branch is a fully independent database with a complete data copy.
**PlanetScale** branches by creating a schema-only copy from a backup of the source database. Data is not included — the branch starts with an empty dataset matching the source schema. This is lightweight but means branches cannot be used for data-inclusive testing without additional data loading.
## Extension Comparison
[Section titled “Extension Comparison”](#extension-comparison)
| Extension / Capability | DB9 | PlanetScale Postgres |
| -------------------------------- | ---------------------------------- | --------------------------- |
| pgvector (vector type + HNSW) | Built-in | Available (via vectorscale) |
| Native embedding generation | Built-in (`embedding()`) | Not available |
| Document chunking for RAG | Built-in (`CHUNK_TEXT()`) | Not available |
| HTTP requests from SQL | Built-in (`http` — 6 methods) | Not available |
| File system access from SQL | Built-in (`fs9` extension) | Not available |
| FUSE filesystem mount | Built-in (`db9 fs mount`) | Not available |
| Parquet import | Built-in (`read_parquet()` + COPY) | Not available |
| Full-text search (Chinese/jieba) | Built-in (zhparser extension) | Standard PostgreSQL FTS |
| pg\_cron (job scheduling) | Built-in | Not documented |
| TimescaleDB | Not available | Available |
| vectorscale | Not available | Available |
| pg\_strict | Not available | Available |
| Custom / community extensions | Not supported | Curated set supported |
## ORM and Driver Compatibility
[Section titled “ORM and Driver Compatibility”](#orm-and-driver-compatibility)
Both platforms support standard PostgreSQL ORMs and drivers. DB9 has tested compatibility with:
| ORM / Driver | DB9 Test Results | PlanetScale Postgres |
| ------------ | -------------------- | -------------------- |
| Prisma | 89/89 (100%) | Supported |
| Drizzle | 75/75 (100%) | Supported |
| SQLAlchemy | Tested (smoke + e2e) | Supported |
| TypeORM | 147/150 (98%) | Supported |
| Sequelize | 87/87 (100%) | Supported |
| Knex | 97/97 (100%) | Supported |
| GORM | Tested (smoke + e2e) | Supported |
Both platforms use pgwire v3 and work with any PostgreSQL-compatible driver (node-postgres, psycopg, pgx, JDBC).
## Migration Between Platforms
[Section titled “Migration Between Platforms”](#migration-between-platforms)
Moving from PlanetScale to DB9 or vice versa uses standard `pg_dump` / `psql` workflows. See the [Migrate from PlanetScale](/docs/migrations/from-planetscale/) guide for step-by-step instructions.
Key migration considerations:
* PlanetScale runs standard PostgreSQL v17; DB9 runs a custom engine — test query compatibility before migrating
* DB9 does not support SERIALIZABLE isolation
* DB9 connection strings use a different format (`tenant_id.role@host:5433/postgres`)
* Extensions not in DB9’s built-in set will not be available
* PlanetScale does not include DB9’s application-layer extensions (HTTP, file system, native embeddings)
## Summary
[Section titled “Summary”](#summary)
**DB9 is the better choice when** you need instant database provisioning, AI agent integration, or application-layer SQL extensions (HTTP, files, native embeddings). It excels at multi-tenant patterns where each user or agent gets their own database, and its free anonymous tier removes friction for prototyping.
**PlanetScale is the better choice when** you need production-grade high availability, read replicas, connection pooling, or full PostgreSQL feature compatibility. It excels at operational reliability with its three-AZ default deployment and standard PostgreSQL v17 engine.
Both platforms are PostgreSQL-compatible and work with standard ORMs, drivers, and SQL. Neither supports scale-to-zero. The right choice depends on whether your priority is developer experience and AI-native features (DB9) or operational reliability and PostgreSQL completeness (PlanetScale).
## Next Pages
[Section titled “Next Pages”](#next-pages)
* [DB9 vs Neon](/docs/comparisons/db9-vs-neon/) — comparison with Neon
* [DB9 vs Supabase](/docs/comparisons/db9-vs-supabase/) — comparison with Supabase
* [Compatibility Matrix](/docs/platform/compatibility-matrix/) — detailed feature support
* [Overview](/docs/overview/) — what DB9 is and who it is for
* [Why DB9 for AI Agents](/docs/why-db9-for-ai-agents/) — the agent-native positioning
# DB9 vs Supabase
> A fair comparison of DB9 and Supabase — database-only vs full application platform, and when each is the right choice.
DB9 is a serverless PostgreSQL-compatible database. Supabase is a full application platform — PostgreSQL database plus authentication, file storage, realtime subscriptions, edge functions, and an auto-generated REST API.
They are not direct substitutes. DB9 competes with Supabase’s database layer. Everything else in Supabase (Auth, Storage, Realtime, Edge Functions, PostgREST) has no equivalent in DB9. This page helps you understand which approach fits your project.
## At a Glance
[Section titled “At a Glance”](#at-a-glance)
| Capability | DB9 | Supabase |
| --------------------------- | ----------------------------------------------------------------------------- | --------------------------------------- |
| **Product scope** | Database only | Full application platform |
| **PostgreSQL database** | Yes | Yes (standard PostgreSQL) |
| **Authentication** | No | Yes (GoTrue) |
| **File storage** | fs9 extension (SQL-based) | Yes (S3-backed, CDN) |
| **Realtime subscriptions** | No | Yes (CDC + LISTEN/NOTIFY) |
| **Auto-generated REST API** | No | Yes (PostgREST) |
| **Edge Functions** | No | Yes (Deno) |
| **Row-level security** | Yes | Yes |
| **Provisioning speed** | Synchronous, under 1 second | Seconds to minutes (full project setup) |
| **Agent onboarding** | Yes (`db9 onboard`) | No |
| **Anonymous access** | Yes (no signup, up to 5 databases) | No (account required) |
| **Native embeddings** | Yes (`embedding()` function) | No (pgvector only, external embeddings) |
| **Document chunking** | Yes (built-in `CHUNK_TEXT()` for RAG) | No |
| **Full-text search** | Yes (English, Chinese/jieba, ngram tokenizers) | Yes (standard PostgreSQL FTS) |
| **Parquet import** | Yes (built-in `read_parquet()` + COPY) | No |
| **FUSE filesystem mount** | Yes (`db9 fs mount`) | No |
| **HTTP from SQL** | Yes (built-in `http` — 6 methods) | Yes (pg\_net extension) |
| **CLI observability** | Yes (`db9 db inspect`) | No (dashboard-based) |
| **Connection pooling** | No built-in pooler | Yes (Supavisor) |
| **Extensions** | 9 built-in | 40+ (PostGIS, pg\_trgm, pgcrypto, etc.) |
| **LISTEN/NOTIFY** | Yes (pgwire sessions; HTTP API can `NOTIFY` only) | Yes |
| **Logical replication** | No | Yes |
| **Transaction isolation** | READ COMMITTED / REPEATABLE READ (SERIALIZABLE downgraded to REPEATABLE READ) | Full SERIALIZABLE |
| **Wire protocol** | pgwire v3 | pgwire v3 |
## When should I use DB9 instead of Supabase?
**You want a database, not a platform.** If your application already has its own auth, storage, and API layer — or you prefer to compose these from separate services — DB9 gives you a PostgreSQL-compatible database without bundled platform services. There is no auth system to configure, no storage policies to manage, and no PostgREST to learn.
**Instant database provisioning at scale.** DB9 creates databases synchronously in under a second. The SDK’s `instantDatabase()` returns a ready-to-use connection in a single call. This makes database-per-user, database-per-agent, and database-per-CI-run patterns practical without orchestration.
**AI agent workflows.** DB9 is built for AI agents. The `db9 onboard` CLI installs skills for Claude Code, OpenAI Codex, Opencode, and other agents. Anonymous accounts let agents create databases without signup flows. Built-in `embedding()` generates vector embeddings from text directly in SQL — no external embedding API or infrastructure needed.
**Application-layer SQL extensions.** DB9 includes extensions that bring application logic into SQL:
* `http` — make HTTP requests from SQL (GET, POST, PUT, DELETE, PATCH, HEAD)
* `fs9` — read, write, and query files from SQL; parse JSONL/CSV/TSV as tables; FUSE mount for local access
* `embedding()` — generate text embeddings natively (default model: text-embedding-v4, 1024 dimensions)
* `CHUNK_TEXT()` — split documents into overlapping chunks for RAG pipelines
* `read_parquet()` — import Parquet files directly in SQL
* `pg_cron` — schedule recurring SQL jobs
* Full-text search with Chinese tokenizer support (jieba/zhparser)
These are compiled into DB9 and always available. Supabase has pg\_net for outbound HTTP webhooks and pg\_cron, but does not offer built-in file system access, native embedding generation, document chunking, or Parquet import.
**No-signup developer experience.** Install the CLI and create a database immediately — no account creation, no email verification. Anonymous accounts support up to 5 databases and can be upgraded later with `db9 claim`.
## When should I use Supabase instead of DB9?
**You want a full backend, not just a database.** Supabase bundles authentication (GoTrue), file storage (S3-backed with CDN), realtime subscriptions (CDC), edge functions (Deno), and an auto-generated REST/GraphQL API. If your project needs most of these, Supabase provides them as a single integrated platform. Building the same stack from separate services takes more effort.
**Client-side database access.** Supabase’s auto-generated PostgREST API and client libraries (`@supabase/supabase-js`) let frontend applications query the database directly with row-level security enforcing access control. DB9 provides the [Browser SDK](/docs/sdk-browser/) (`@db9/browser`) with a chainable query builder for client-side access, backed by publishable keys and RLS.
**Row-level security.** Supabase projects heavily use RLS to enforce per-user data access at the database level. This is core to Supabase’s security model for client-side access. DB9 supports RLS with CREATE POLICY, ENABLE/FORCE ROW LEVEL SECURITY, and per-operation policies (SELECT, INSERT, UPDATE, DELETE). The [Browser SDK](/docs/sdk-browser/) uses RLS as its primary access control mechanism.
**Realtime features.** Supabase Realtime combines PostgreSQL’s LISTEN/NOTIFY with change data capture to push database changes to connected clients. DB9 supports LISTEN/NOTIFY over pgwire, but has no change data capture — you emit events yourself with triggers and fan them out from your own service. If your application needs live updates delivered to browser clients out of the box, Supabase handles this natively.
Choose Supabase if you need these features
If your app needs LISTEN/NOTIFY realtime subscriptions, Auth (GoTrue), S3-backed file storage, Edge Functions, or community extensions like PostGIS or pgcrypto, Supabase is a better fit for those requirements today.
**Broad extension ecosystem.** Supabase supports 40+ PostgreSQL extensions including PostGIS, pg\_trgm, pgcrypto, ltree, and pg\_graphql. DB9 has 9 built-in extensions and cannot install community extensions.
**Standard PostgreSQL.** Supabase runs standard PostgreSQL with full feature parity — SERIALIZABLE isolation, table partitioning, table inheritance, advisory locks, logical replication, and all PL/pgSQL capabilities. DB9’s custom SQL engine has compatibility gaps in these areas.
**Dashboard and visual tools.** Supabase includes a web dashboard with a SQL editor, table viewer, auth management, storage browser, and logs. DB9 is CLI-first with no web dashboard.
## Architecture Differences
[Section titled “Architecture Differences”](#architecture-differences)
### Scope
[Section titled “Scope”](#scope)
**DB9** is a database service. You get a PostgreSQL-compatible database with built-in extensions. Everything else — auth, file storage, APIs, realtime — is your responsibility.
**Supabase** is an application platform. Each project includes a PostgreSQL database, GoTrue auth server, S3-compatible storage, Realtime server, PostgREST API, and Deno edge function runtime. These services are pre-integrated and share the same project.
### Storage engine
[Section titled “Storage engine”](#storage-engine)
**DB9** uses TiKV, a distributed key-value store. Each database gets an isolated keyspace. This enables instant provisioning but means branching requires full data copies.
**Supabase** uses standard PostgreSQL with its default storage engine. Compute and storage are not separated — scaling requires upgrading the instance.
### Multi-tenancy
[Section titled “Multi-tenancy”](#multi-tenancy)
**DB9** supports database-per-tenant natively. Creating thousands of databases is practical because provisioning is synchronous and sub-second. The SDK’s `instantDatabase()` is idempotent and handles the full lifecycle.
**Supabase** is designed around one project per application (or a small number of projects). Multi-tenancy is typically handled within a single database using schemas or RLS policies, not by creating separate projects per tenant.
## Extension Comparison
[Section titled “Extension Comparison”](#extension-comparison)
| Extension / Capability | DB9 | Supabase |
| -------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------ |
| pgvector (vector type + HNSW) | Built-in | Available |
| Native embedding generation | Built-in (`embedding()`) | Not available |
| Document chunking for RAG | Built-in (`CHUNK_TEXT()`) | Not available |
| HTTP requests from SQL | Built-in (`http` — 6 methods) | pg\_net (async webhooks) |
| File system access from SQL | Built-in (`fs9` extension) | Not available |
| FUSE filesystem mount | Built-in (`db9 fs mount`) | Not available |
| Parquet import | Built-in (`read_parquet()` + COPY) | Not available |
| Full-text search (Chinese/jieba) | Built-in (zhparser extension) | Standard PostgreSQL FTS |
| pg\_cron (job scheduling) | Built-in | Available |
| uuid-ossp | Built-in | Available |
| hstore | Built-in | Available |
| PostGIS | Not available | Available |
| pg\_trgm (trigram search) | Not available | Available |
| pgcrypto | Metadata shim only — `CREATE EXTENSION` succeeds but registers no functions; `gen_random_uuid()` and `digest()` are built-ins, while `crypt()`/`gen_salt()`/`hmac()` are unavailable | Available |
| pg\_graphql | Not available | Available |
| pgsodium (encryption) | Not available | Available |
| Custom / community extensions | Not supported | 40+ supported |
## ORM and Driver Compatibility
[Section titled “ORM and Driver Compatibility”](#orm-and-driver-compatibility)
Both platforms work with standard PostgreSQL ORMs and drivers. DB9 has tested compatibility with:
| ORM / Driver | DB9 Test Results | Supabase |
| ------------ | -------------------- | --------- |
| Prisma | 89/89 (100%) | Supported |
| Drizzle | 75/75 (100%) | Supported |
| SQLAlchemy | Tested (smoke + e2e) | Supported |
| TypeORM | 147/150 (98%) | Supported |
| Sequelize | 87/87 (100%) | Supported |
| Knex | 97/97 (100%) | Supported |
| GORM | Tested (smoke + e2e) | Supported |
Supabase additionally provides its own client library (`@supabase/supabase-js`) that wraps PostgREST. DB9 does not have an equivalent — use standard PostgreSQL drivers or ORMs.
## Migration Between Platforms
[Section titled “Migration Between Platforms”](#migration-between-platforms)
Moving from Supabase to DB9 uses standard `pg_dump` with schema exclusions for Supabase’s internal schemas. See the [Migrate from Supabase](/docs/migrations/from-supabase/) guide for step-by-step instructions.
Key considerations:
* DB9 replaces only the database — plan replacements for Auth, Storage, Realtime, and Edge Functions
* RLS policies transfer directly — DB9 supports CREATE POLICY with the same syntax
* The Supabase client library must be replaced with a standard PostgreSQL driver or ORM
* DB9 connection strings use a different format (`tenant_id.role@host:5433/postgres`)
## Summary
[Section titled “Summary”](#summary)
**DB9 is the better choice when** you want a standalone PostgreSQL-compatible database with instant provisioning, AI agent integration, and built-in SQL extensions for HTTP, files, and embeddings. It fits teams that compose their own backend from separate services.
**Supabase is the better choice when** you want a full application backend — database, auth, storage, realtime, and API — in one integrated platform. It fits teams that want to ship quickly with batteries included, especially for client-side-heavy applications that rely on RLS and PostgREST.
The decision is architectural: do you want a database or a platform? If you already have (or prefer to build) your own auth, storage, and API layers, DB9 gives you a fast, agent-friendly database. If you want those services bundled and integrated, Supabase provides them.
## Next Pages
[Section titled “Next Pages”](#next-pages)
* [Migrate from Supabase](/docs/migrations/from-supabase/) — step-by-step migration guide
* [DB9 vs Neon](/docs/comparisons/db9-vs-neon/) — comparison with Neon (database vs database)
* [Compatibility Matrix](/docs/platform/compatibility-matrix/) — detailed feature support
* [Overview](/docs/overview/) — what DB9 is and who it is for
* [Why DB9 for AI Agents](/docs/why-db9-for-ai-agents/) — the agent-native positioning
# DB9 vs Turso
> A fair comparison of DB9 and Turso — when each is the better choice, and where they differ on SQL dialect, architecture, edge replication, and developer experience.
DB9 and Turso are both serverless databases designed for modern applications, but they are built on fundamentally different foundations. db9 database is PostgreSQL-compatible, backed by TiKV. Turso is built on libSQL, a fork of SQLite, with per-database file storage and edge replication.
This is not just a feature comparison — it is a SQL dialect decision. Choosing between PostgreSQL and SQLite/libSQL affects your schema design, query patterns, ORM compatibility, and migration path. This page helps you evaluate both platforms honestly.
## At a Glance
[Section titled “At a Glance”](#at-a-glance)
| Capability | DB9 | Turso |
| ------------------------- | ---------------------------------------------------------------- | --------------------------------------- |
| **SQL dialect** | PostgreSQL-compatible | SQLite/libSQL |
| **Wire protocol** | pgwire v3 | HTTP/WebSocket (libSQL protocol) |
| **Storage engine** | TiKV (distributed KV) | SQLite B-tree (per-database file) |
| **Provisioning** | Synchronous, under 1 second | Instant (no specific SLA) |
| **Edge replication** | No | Yes (multi-region groups) |
| **Embedded replicas** | No | Yes (local SQLite file sync) |
| **Vector search** | Yes (pgvector built-in, HNSW) | Yes (native, no extension) |
| **Native embeddings** | Yes (built-in `embedding()` function) | No |
| **Full-text search** | Yes (English, Chinese/jieba, ngram tokenizers) | Yes (Tantivy-powered) |
| **HTTP from SQL** | Yes (built-in `http` — 6 methods) | No |
| **File system in SQL** | Yes (built-in `fs9` extension) | No |
| **FUSE filesystem mount** | Yes (`db9 fs mount`) | No |
| **Document chunking** | Yes (built-in `CHUNK_TEXT()` for RAG) | No |
| **Parquet import** | Yes (built-in `read_parquet()` + COPY) | No |
| **Agent onboarding** | Yes (`db9 onboard` for Claude, Codex, etc.) | AgentFS (sandbox + COW overlays) |
| **Anonymous access** | Yes (no signup, up to 5 databases) | No (account required) |
| **Free tier** | Yes (no signup, 5 databases) | Yes (100 databases, 5 GB) |
| **SDKs** | TypeScript, Browser | 10 languages |
| **Concurrent writes** | Yes | Yes (libSQL multi-writer) |
| **Extension ecosystem** | 9 built-in PostgreSQL extensions | SQLite extensions (different ecosystem) |
| **ORM compatibility** | PostgreSQL ORMs (Prisma, Drizzle, SQLAlchemy, etc.) | SQLite ORMs and Drizzle (libSQL driver) |
| **CLI observability** | Yes (`db9 db inspect` — queries, slow-queries, schemas, indexes) | No |
| **Row-level security** | Yes | No (application-level auth) |
## When should I use DB9 instead of Turso?
**You need PostgreSQL compatibility.** DB9 speaks pgwire v3 and works with the entire PostgreSQL ecosystem — Prisma, Drizzle, SQLAlchemy, TypeORM, Sequelize, Knex, GORM, and any PostgreSQL driver. If your application already uses PostgreSQL, or you need PostgreSQL-specific features like row-level security, `jsonb` operators, CTEs with writeable expressions, or window functions, DB9 is a direct fit. Turso uses SQLite SQL, which has a different type system, different ALTER TABLE behavior, and different ORM driver requirements.
**AI agent workflows.** db9 database is designed for AI agents. The `db9 onboard` CLI installs skills for Claude Code, OpenAI Codex, Opencode, and other agents. Anonymous accounts let agents create databases without signup flows. Built-in `embedding()` generates vector embeddings from text directly in SQL — no external embedding API needed. Turso offers AgentFS with sandbox environments and copy-on-write overlays, but does not include native embedding generation.
**Application-layer extensions.** DB9 includes extensions that run application logic inside the database:
* `http` — make HTTP requests from SQL (GET, POST, PUT, DELETE, PATCH, HEAD)
* `fs9` — read and write files from SQL, query file contents as tables; FUSE mount for local filesystem access
* `embedding()` — generate text embeddings natively (default model: text-embedding-v4, 1024 dimensions)
* `CHUNK_TEXT()` — split documents into overlapping chunks for RAG pipelines
* `read_parquet()` — import Parquet files directly in SQL
* `pg_cron` — schedule recurring SQL jobs
* Full-text search with Chinese tokenizer support (jieba/zhparser)
These are compiled into DB9 and available without installation. Turso does not offer equivalent HTTP-from-SQL, file system access, native embedding generation, document chunking, or Parquet import capabilities.
**No-signup developer experience.** Developers can install the CLI and create a database without creating an account. Anonymous accounts support up to 5 databases and can be upgraded later with `db9 claim`. This reduces friction for prototyping, tutorials, and agent-driven workflows.
**Instant provisioning with guarantees.** DB9 creates databases synchronously in under a second with a deterministic SLA. The SDK’s `instantDatabase()` creates and returns a ready-to-use connection in a single call. This makes database-per-user, database-per-agent, and database-per-CI-run patterns practical without orchestration.
## When should I use Turso instead of DB9?
**Edge-first, latency-sensitive workloads.** Turso replicates databases to edge locations using multi-region groups. Reads are served from the nearest replica. If your application runs globally and read latency is critical, Turso’s edge replication reduces round-trips to the database. DB9 does not replicate to edge locations.
**Embedded replicas for offline or local-first apps.** Turso syncs a full SQLite replica to the application process as a local file. Reads hit the local file with zero network latency. Writes sync back to the primary. This is powerful for mobile apps, desktop apps, and CLI tools that need to work offline or with intermittent connectivity. DB9 does not support embedded replicas.
**SQLite simplicity.** If your application already uses SQLite, or you want the simplicity of SQLite’s type system and file-based model, Turso extends that foundation with server-side features. SQLite’s single-file mental model, zero-configuration local development, and broad language support make it a natural fit for lightweight applications.
**Broad SDK coverage.** Turso provides official SDKs in 10 languages including TypeScript, Python, Go, Rust, Java, PHP, Ruby, Swift, Kotlin, and .NET. DB9 provides TypeScript and Browser SDKs. For non-TypeScript backends, DB9 works through standard PostgreSQL drivers, but Turso offers purpose-built libSQL clients with features like embedded replicas built in.
**High database count on free tier.** Turso’s free tier includes 100 databases and 5 GB of storage. DB9’s free tier provides 5 databases without signup. If you need many lightweight databases for free, Turso offers a higher ceiling.
Choose Turso if you need these capabilities
If your application depends on edge replication, embedded local replicas, offline-first sync, or native SDKs beyond TypeScript, Turso is the better choice for those requirements today.
## Architecture Differences
[Section titled “Architecture Differences”](#architecture-differences)
### Storage
[Section titled “Storage”](#storage)
**DB9** uses TiKV, a distributed key-value store, as its storage engine. Each database gets an isolated TiKV keyspace. This architecture enables instant provisioning (creating a keyspace is fast) and supports PostgreSQL-compatible transaction semantics at READ COMMITTED and REPEATABLE READ.
**Turso** stores each database as a SQLite file using SQLite’s B-tree storage format. The primary database lives in one region, and replicas are full copies of the SQLite file synced to edge locations. libSQL extends SQLite with server-side write-ahead log (WAL) replication for multi-region consistency.
### Compute
[Section titled “Compute”](#compute)
**DB9** runs a custom SQL engine that parses, optimizes, and executes PostgreSQL-compatible queries against TiKV. Each database has dedicated compute that is always running.
**Turso** runs libSQL (a fork of SQLite) with extensions for server-side operation. Compute is co-located with the SQLite file at each replica location. The libSQL engine handles both local reads and forwarded writes to the primary.
### Replication
[Section titled “Replication”](#replication)
**DB9** does not replicate databases across regions. All reads and writes go to the same location.
**Turso** replicates databases across configurable region groups. Reads are served locally from the nearest replica. Writes are forwarded to the primary region and then propagated to replicas. Embedded replicas extend this model to the application process itself, syncing a local SQLite file.
## SQL Dialect Differences
[Section titled “SQL Dialect Differences”](#sql-dialect-differences)
This is the most significant technical difference between the two platforms. DB9 and Turso use different SQL dialects, which affects schema design, queries, and tooling.
### Type System
[Section titled “Type System”](#type-system)
**DB9 (PostgreSQL):** Rich type system with `integer`, `bigint`, `numeric`, `text`, `varchar(n)`, `boolean`, `timestamp`, `timestamptz`, `jsonb`, `uuid`, `bytea`, arrays, composite types, and more. Strict type checking at insert time.
**Turso (SQLite):** Dynamic type system with type affinities — `INTEGER`, `REAL`, `TEXT`, `BLOB`, `NUMERIC`. Any column can store any type. Type enforcement is application-side or via `STRICT` tables (libSQL extension).
### Schema Changes
[Section titled “Schema Changes”](#schema-changes)
**DB9 (PostgreSQL):** Full `ALTER TABLE` support — add/drop/rename columns, change types, add/drop constraints, rename tables, all within transactions.
**Turso (SQLite):** Limited `ALTER TABLE` — add columns and rename columns/tables. Dropping columns requires SQLite 3.35.0+. Changing column types or adding constraints to existing columns requires creating a new table, copying data, and renaming.
### Queries
[Section titled “Queries”](#queries)
**DB9 (PostgreSQL):** Supports CTEs (`WITH`), window functions (`ROW_NUMBER()`, `RANK()`, `LAG()`), `LATERAL` joins, `jsonb` operators (`->`, `->>`, `@>`, `?`), `RETURNING` clauses, upsert with `ON CONFLICT`, and `GENERATE_SERIES()`.
**Turso (SQLite):** Supports CTEs and window functions. Does not support `LATERAL` joins. JSON functions use `json_extract()` instead of operator syntax. Supports upsert with `ON CONFLICT`. `RETURNING` is available in newer SQLite/libSQL versions.
### ORM Compatibility
[Section titled “ORM Compatibility”](#orm-compatibility)
**DB9** works with PostgreSQL-native ORMs: Prisma (postgresql provider), Drizzle (pg driver), SQLAlchemy (postgresql dialect), TypeORM, Sequelize, Knex, and GORM.
**Turso** works with SQLite-compatible ORMs: Prisma (sqlite provider + libSQL adapter), Drizzle (libSQL driver), and others that support SQLite. Switching from PostgreSQL ORMs to SQLite ORMs requires schema and query changes.
## ORM and Driver Compatibility
[Section titled “ORM and Driver Compatibility”](#orm-and-driver-compatibility)
Because DB9 and Turso use different SQL dialects, they work with different ORM configurations:
| ORM / Driver | DB9 (PostgreSQL) | Turso (SQLite/libSQL) |
| ------------ | ------------------------------------ | -------------------------------------------- |
| Prisma | `postgresql` provider — 89/89 (100%) | `sqlite` provider + `@prisma/adapter-libsql` |
| Drizzle | `pg` driver — 75/75 (100%) | `libsql` driver |
| SQLAlchemy | `postgresql` dialect — Tested | Not supported (SQLite dialect differs) |
| TypeORM | PostgreSQL driver — 147/150 (98%) | SQLite driver (different feature set) |
| Sequelize | PostgreSQL dialect — 87/87 (100%) | SQLite dialect |
| Knex | PostgreSQL client — 97/97 (100%) | SQLite3 client |
| GORM | PostgreSQL driver — Tested | SQLite driver |
Switching between platforms requires changing ORM provider/dialect settings, not just connection strings.
## Migration
[Section titled “Migration”](#migration)
Migration between DB9 and Turso is not a simple dump-and-restore because they use different SQL dialects. Moving between PostgreSQL and SQLite requires schema translation. See the [Migrate from Turso](/docs/migrations/from-turso/) guide for PostgreSQL-specific migration steps.
Key migration considerations:
* **Type mapping:** PostgreSQL types like `jsonb`, `uuid`, `timestamptz`, and arrays have no direct SQLite equivalents. These must be mapped to `TEXT` or `BLOB` with application-level parsing.
* **Schema changes:** PostgreSQL `ALTER TABLE` operations may need to be rewritten as create-copy-rename sequences for SQLite.
* **Query rewriting:** PostgreSQL-specific syntax (`jsonb` operators, `LATERAL` joins, `GENERATE_SERIES()`) must be rewritten for SQLite.
* **Driver changes:** PostgreSQL drivers (node-postgres, psycopg, pgx) must be replaced with libSQL clients, or vice versa.
* **ORM reconfiguration:** ORM provider/dialect settings and type mappings need to change.
Tools like `pgloader` or custom ETL scripts can help, but expect a non-trivial migration effort in either direction.
## Summary
[Section titled “Summary”](#summary)
**DB9 is the better choice when** you need PostgreSQL compatibility, AI agent integration, or application-layer SQL extensions (HTTP, files, native embeddings). It excels at multi-tenant patterns where each user or agent gets their own database, and when your stack already uses PostgreSQL ORMs and drivers.
**Turso is the better choice when** you need edge replication, embedded local replicas, offline-first sync, or SQLite simplicity. It excels at globally distributed read-heavy workloads and applications that benefit from a local SQLite file synced to the server.
The fundamental choice is between PostgreSQL and SQLite. If your team, tooling, and application are built around PostgreSQL, DB9 keeps you in that ecosystem with added agent and extension capabilities. If you prefer SQLite’s lightweight model and need edge replication, Turso extends SQLite into a distributed database.
## Next Pages
[Section titled “Next Pages”](#next-pages)
* [DB9 vs Neon](/docs/comparisons/db9-vs-neon/) — comparison with Neon (PostgreSQL vs PostgreSQL)
* [DB9 vs Supabase](/docs/comparisons/db9-vs-supabase/) — comparison with Supabase (database vs platform)
* [Compatibility Matrix](/docs/platform/compatibility-matrix/) — detailed feature support
* [Overview](/docs/overview/) — what DB9 is and who it is for
* [Why DB9 for AI Agents](/docs/why-db9-for-ai-agents/) — the agent-native positioning
# Connect to DB9
> How to connect to a DB9 database — connection strings, psql, ORMs, drivers, the TypeScript SDK, authentication options, and TLS.
DB9 speaks the PostgreSQL wire protocol on port `5433`. Any client that connects to Postgres — `psql`, a Node.js driver, Prisma, SQLAlchemy, or a raw `libpq` binding — connects to DB9 with no adapter or proxy needed.
## Connection string format
[Section titled “Connection string format”](#connection-string-format)
Every DB9 connection string follows this pattern:
Output
```text
postgresql://.@:/postgres
```
| Component | Value | Notes |
| ------------ | -------------------------------- | ---------------------------------------------------------------------------- |
| **Scheme** | `postgresql://` or `postgres://` | Both work. |
| **Username** | `.` | The tenant ID is assigned at database creation. The default role is `admin`. |
| **Host** | `pg.db9.io` | The public endpoint for hosted DB9. |
| **Port** | `5433` | Default PostgreSQL wire protocol port for DB9. |
| **Database** | `postgres` | All DB9 databases use the `postgres` database name. |
Example:
Output
```text
postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres
```
## Get your connection string
[Section titled “Get your connection string”](#get-your-connection-string)
### Using the CLI
[Section titled “Using the CLI”](#using-the-cli)
After creating a database, use `db9 db connect` to retrieve connection details:
Terminal
```bash
# Create a database
db9 create --name myapp
# Show connection info
db9 db connect myapp
```
▶ Run
`db9 db connect` mints a short-lived token by default (recommended for production and automation). It prints a ready-to-use connection string with the token already embedded as the password, plus the matching `psql` command:
Output
```text
Temporary connection string (10min):
postgresql://a1b2c3d4e5f6.admin:ey...@pg.db9.io:5433/postgres
User:
a1b2c3d4e5f6.admin
Expires:
10min (at 2026-03-12T10:30:00Z)
psql Command:
psql "postgresql://a1b2c3d4e5f6.admin:ey...@pg.db9.io:5433/postgres"
Long-lived DSN:
db9 db users myapp create --username --password
db9 db reset-password myapp
```
The token is embedded in the connection string rather than printed as a separate field. Use `--json` for machine-readable output (`connection_string`, `host`, `port`, `user`, `database`, `expires_at`, `expires_in`, `expires_in_seconds`).
To reset a static password instead:
Terminal
```bash
db9 db reset-password myapp
```
### Using the TypeScript SDK
[Section titled “Using the TypeScript SDK”](#using-the-typescript-sdk)
The SDK returns a connection string automatically when you create or connect to a database:
TypeScript
```typescript
import { instantDatabase } from 'get-db9';
const db = await instantDatabase({ name: 'myapp' });
console.log(db.connectionString);
// postgresql://a1b2c3d4e5f6.admin:password@pg.db9.io:5433/postgres
```
▶ Run
Or call the connect-token REST endpoint directly to get a short-lived token — the SDK does not yet publish a native `connectToken()` method:
TypeScript
```typescript
const res = await fetch(`https://api.db9.ai/customer/databases/${databaseId}/connect-token`, {
method: 'POST',
headers: {
Authorization: `Bearer ${process.env.DB9_API_KEY}`,
'Content-Type': 'application/json',
},
body: JSON.stringify({ role: 'admin' }),
});
const { host, port, database, user, token, expires_at } = await res.json();
```
## Connect with psql
[Section titled “Connect with psql”](#connect-with-psql)
Use the connection string directly or let `db9 db connect` generate the command for you:
Terminal
```bash
# Using a connect token (recommended)
PGPASSWORD='' psql "postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres"
# Using a static password
PGPASSWORD='' psql "postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres"
```
You can also use a `.pgpass` file:
Terminal
```bash
echo "pg.db9.io:5433:postgres:a1b2c3d4e5f6.admin:" >> ~/.pgpass
chmod 600 ~/.pgpass
psql "postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres"
```
Or use the built-in DB9 SQL REPL, which handles authentication automatically:
Terminal
```bash
db9 db sql myapp -q "SELECT version()"
```
▶ Run
## Connect with ORMs and drivers
[Section titled “Connect with ORMs and drivers”](#connect-with-orms-and-drivers)
DB9 works with any PostgreSQL-compatible driver or ORM. Set the connection string as your database URL.
* Node.js (pg)
TypeScript
```typescript
import pg from 'pg';
const client = new pg.Client({
connectionString: 'postgresql://a1b2c3d4e5f6.admin:password@pg.db9.io:5433/postgres',
});
await client.connect();
const res = await client.query('SELECT NOW()');
console.log(res.rows[0]);
await client.end();
```
* Prisma
Output
```text
# .env
DATABASE_URL="postgresql://a1b2c3d4e5f6.admin:password@pg.db9.io:5433/postgres"
```
prisma
```prisma
// schema.prisma
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
```
* Drizzle
TypeScript
```typescript
import { drizzle } from 'drizzle-orm/node-postgres';
const db = drizzle('postgresql://a1b2c3d4e5f6.admin:password@pg.db9.io:5433/postgres');
```
* Python (SQLAlchemy)
Python
```python
from sqlalchemy import create_engine
# With psycopg2 (default driver)
engine = create_engine(
"postgresql+psycopg2://a1b2c3d4e5f6.admin:password@pg.db9.io:5433/postgres"
)
```
* Go (pgx)
Go
```go
conn, err := pgx.Connect(context.Background(),
"postgresql://a1b2c3d4e5f6.admin:password@pg.db9.io:5433/postgres")
```
For detailed setup guides with migrations, schema patterns, and troubleshooting, see the integration guides for [Prisma](/docs/guides/prisma/), [Drizzle](/docs/guides/drizzle/), [SQLAlchemy](/docs/guides/python-sqlalchemy/), [TypeORM](/docs/guides/typeorm/), [Sequelize](/docs/guides/sequelize/), [Knex](/docs/guides/knex/), and [GORM](/docs/guides/gorm/).
## Authentication
[Section titled “Authentication”](#authentication)
DB9 supports three ways to authenticate connections:
### Static password
[Section titled “Static password”](#static-password)
Set at database creation or reset with `db9 db reset-password`. The password is embedded in the connection string or passed via `PGPASSWORD`. Simple, but the credential does not expire.
### Short-lived connect token
[Section titled “Short-lived connect token”](#short-lived-connect-token)
Generated with `db9 db connect`. The token is used as the password and expires automatically. Recommended for production and CI/CD. The TypeScript SDK does not yet publish a native `connectToken()` method — see [API Reference — Connect Tokens & Keys](/docs/api/#connect-tokens--keys) for the REST equivalent.
Terminal
```bash
# Generate a token for a specific role
db9 db connect myapp --user admin
```
### API token (for SDK and REST API)
[Section titled “API token (for SDK and REST API)”](#api-token-for-sdk-and-rest-api)
Named tokens created with `db9 token create` are used for the DB9 REST API and SDK, not for direct pgwire connections. Use them when your application manages databases programmatically rather than connecting with SQL.
Terminal
```bash
db9 token create --name ci-agent --expires-in-days 90
export DB9_API_KEY=
```
See the [TypeScript SDK](/docs/sdk/) docs for the full auth lifecycle, including anonymous trial accounts and Auth0 SSO.
### Publishable key (for browser access)
[Section titled “Publishable key (for browser access)”](#publishable-key-for-browser-access)
For client-side applications, use a publishable key (`db9pk_...`) with the [Browser SDK](/docs/sdk-browser/). Publishable keys are scoped to specific schemas and tables and enforce [Row-Level Security](/docs/sql/rls/) policies.
TypeScript
```typescript
import { createDb9BrowserClient } from '@db9/browser';
const db9 = createDb9BrowserClient({
apiUrl: 'https://api.db9.ai',
databaseId: 'your-database-id',
anonKey: 'db9pk_your_publishable_key',
});
const { data } = await db9.from('todos').select('*');
```
Publishable keys are created via the REST API. See [Security & Auth — Publishable Keys](/docs/platform/security-and-auth/#publishable-keys) for setup.
## TLS
[Section titled “TLS”](#tls)
DB9’s hosted service supports TLS connections. The server uses SCRAM-SHA-256 authentication by default.
Most PostgreSQL clients default to `sslmode=prefer`, which will use TLS when available. For stricter security:
Terminal
```bash
psql "postgresql://a1b2c3d4e5f6.admin@pg.db9.io:5433/postgres?sslmode=require"
```
Or in a connection string for an ORM:
Output
```text
DATABASE_URL="postgresql://a1b2c3d4e5f6.admin:password@pg.db9.io:5433/postgres?sslmode=require"
```
## Multi-tenant username format
[Section titled “Multi-tenant username format”](#multi-tenant-username-format)
DB9 uses the username field to route connections to the correct tenant. The format is:
Output
```text
.
```
* The **tenant ID** is a 12-character alphanumeric string assigned when the database is created.
* The **role** defaults to `admin`. You can create additional roles with `db9 db users create`.
This encoding means your username in connection strings will look different from a typical Postgres setup, but it works transparently with all standard PostgreSQL drivers.
## Connection checklist
[Section titled “Connection checklist”](#connection-checklist)
| Check | Details |
| ----------------- | ----------------------------------------------------------------------------------- |
| **Port** | `5433`, not the default PostgreSQL `5432` |
| **Database name** | Always `postgres` |
| **Username** | Must include the tenant ID prefix: `.` |
| **Password** | Static password or connect token — both go in `PGPASSWORD` or the connection string |
| **TLS** | Use `sslmode=require` or `sslmode=prefer` |
| **Protocol** | Standard PostgreSQL wire protocol — no special drivers needed |
## Troubleshooting
[Section titled “Troubleshooting”](#troubleshooting)
**“password authentication failed”**
* Verify your tenant ID and role are correct in the username field.
* If using a connect token, check that it has not expired.
* Run `db9 db connect myapp` to generate a fresh token.
**“connection refused” on port 5432**
* DB9 uses port `5433`, not the PostgreSQL default `5432`.
**“SSL SYSCALL error” or TLS handshake failures**
* Ensure your client supports TLS. Try adding `?sslmode=require` to the connection string.
* Some older clients may need updated CA certificates.
**ORM connection timeouts**
* Check that your ORM’s connection string uses the correct port (`5433`) and database name (`postgres`).
* Verify your network allows outbound connections on port `5433`.
## Next steps
[Section titled “Next steps”](#next-steps)
* [Quick Start](/docs/quickstart/) — create your first database and run SQL
* [CLI Reference](/docs/cli/) — full command reference including `db connect` and `db reset-password`
* [TypeScript SDK](/docs/sdk/) — `instantDatabase()`, credential management, and programmatic access
* [Browser SDK](/docs/sdk-browser/) — client-side data access with publishable keys and RLS
* [Architecture](/docs/architecture/) — how DB9 routes connections through the pgwire protocol to TiKV
* [Extensions](/docs/extensions/) — enable vector search, fs9, HTTP from SQL, and pg\_cron after connecting
# Error Code Reference
> Complete reference for DB9 REST API HTTP status codes — error shapes, common causes, and resolution steps for every status code.
All DB9 REST API errors return a consistent JSON shape with a human-readable message:
Error Response Shape
```json
{
"message": "human-readable message describing what went wrong"
}
```
Use the HTTP status code for programmatic error handling. Use `message` for displaying messages to users or for debugging.
## Status Codes
[Section titled “Status Codes”](#status-codes)
### 200 OK
[Section titled “200 OK”](#200-ok)
The request succeeded. The response body contains the requested resource or operation result.
Example
```json
{
"id": "db_01h9xxxxxxxxxxxxxxxx",
"name": "myapp",
"state": "ready"
}
```
### 400 Bad Request
[Section titled “400 Bad Request”](#400-bad-request)
The request body is missing required fields, contains invalid values, or is malformed JSON.
Example
```json
{
"message": "field 'name' is required"
}
```
| Common cause | Resolution |
| ---------------------------------------------------------- | ---------------------------------------------------------------- |
| Missing required field | Check the endpoint’s field table and include all required fields |
| Invalid field type (e.g. string where array expected) | Verify your request body matches the documented types |
| Providing both `query` and `file_content` in SQL execution | Provide only one |
| Invalid `scope_json` format | Ensure the value is a valid JSON string, not an object |
### 401 Unauthorized
[Section titled “401 Unauthorized”](#401-unauthorized)
The request has no `Authorization` header, the token is expired, or the token is invalid.
Example
```json
{
"message": "invalid or expired token"
}
```
| Common cause | Resolution |
| ---------------------------------------------------------- | --------------------------------------------------------------------- |
| Missing `Authorization: Bearer ` header | Add the header to your request |
| Token has expired | Create a new token with `db9 token create` or `POST /customer/tokens` |
| Wrong token (e.g. publishable key used for management API) | Use your API token (`DB9_API_KEY`), not a publishable or service key |
| Anonymous secret mismatch | Verify `anonymous_secret` matches the value returned at registration |
### 403 Forbidden
[Section titled “403 Forbidden”](#403-forbidden)
The token is valid but lacks the scope required to perform the operation.
Example
```json
{
"message": "token does not have access to this database"
}
```
| Common cause | Resolution |
| ----------------------------------------------------- | --------------------------------------------------------------------------------- |
| Token scoped to specific databases, accessing another | Check `scope_json` on the token — create a new token with broader scope if needed |
| Read-only token attempting a write operation | Create a token without the `ro` access restriction |
### 404 Not Found
[Section titled “404 Not Found”](#404-not-found)
The requested resource does not exist, or the authenticated customer does not own it.
Example
```json
{
"message": "database not found"
}
```
| Common cause | Resolution |
| --------------------------------------- | ------------------------------------------------------- |
| Database ID typo or copy-paste error | Double-check the `database_id` in your request path |
| Database was deleted | Check your database list with `GET /customer/databases` |
| Resource belongs to a different account | Ensure you are using the correct API token |
| Key or run ID from a different database | Verify all IDs are from the same database |
### 409 Conflict
[Section titled “409 Conflict”](#409-conflict)
The operation conflicts with existing state — typically a duplicate name or a state precondition.
Example
```json
{
"message": "database with this name already exists"
}
```
| Common cause | Resolution |
| ---------------------------------------------------------- | ----------------------------------------------------------------------- |
| Creating a database/branch with a name that already exists | Choose a different name or delete the existing resource first |
| Applying a migration that was already applied | Check applied migrations with `GET /customer/databases/{id}/migrations` |
| Creating a secret that already exists | Use `PUT` to update an existing secret, not `POST` |
| Adopting a database already owned by your account | The database is already in your account — no action needed |
### 422 Unprocessable Entity
[Section titled “422 Unprocessable Entity”](#422-unprocessable-entity)
The request is syntactically valid but semantically incorrect — the server understands the request but cannot process it.
Example
```json
{
"message": "SQL syntax error at position 14: unexpected token 'FORM'"
}
```
| Common cause | Resolution |
| -------------------------------------------------------- | ------------------------------------------------------------------------ |
| SQL syntax error in `POST /sql` or `POST /migrations` | Fix the SQL — the `error` field contains PostgreSQL’s error message |
| Invalid cron expression in function deploy | Validate the cron string (e.g. use [crontab.guru](https://crontab.guru)) |
| Quota exceeded (databases, branches, keys) | Check your plan limits or delete unused resources |
| Invalid JWKS URL in auth config | Ensure the URL returns a valid JWKS JSON document |
| `snapshot_at` timestamp outside available history window | Use a more recent timestamp for point-in-time branching |
### 429 Too Many Requests
[Section titled “429 Too Many Requests”](#429-too-many-requests)
The request was rate limited. Slow down and retry after the indicated delay.
Example
```json
{
"message": "rate limit exceeded, retry after 30 seconds"
}
```
| Common cause | Resolution |
| ---------------------------------------------------- | -------------------------------------------------------- |
| Too many anonymous account registrations from one IP | Wait before registering more anonymous accounts |
| Function invocation rate limit | Add a delay between invocations or reduce call frequency |
| Burst of management API requests | Implement exponential backoff with jitter |
The `Retry-After` response header (when present) indicates the number of seconds to wait before retrying.
### 500 Internal Server Error
[Section titled “500 Internal Server Error”](#500-internal-server-error)
An unexpected error occurred on the DB9 servers.
Example
```json
{
"message": "internal server error"
}
```
| Common cause | Resolution |
| -------------------------------------- | ------------------------------------------------------------------------------------- |
| Transient server error | Retry the request with exponential backoff |
| Persistent 500 on a specific operation | Contact support via [Discord](https://discord.gg/5P3RuyZCgs) with the request details |
## Handling Errors in Code
[Section titled “Handling Errors in Code”](#handling-errors-in-code)
### TypeScript
[Section titled “TypeScript”](#typescript)
TypeScript
```typescript
import { createDb9Client } from 'get-db9';
const client = createDb9Client();
try {
const db = await client.databases.create({ name: 'myapp' });
} catch (err) {
if (err.status === 409) {
// Database already exists — retrieve it instead
const list = await client.databases.list();
const existing = list.databases.find(d => d.name === 'myapp');
} else if (err.status === 429) {
// Rate limited — implement retry logic
console.error('Rate limited:', err.message);
} else {
throw err;
}
}
```
### curl
[Section titled “curl”](#curl)
Terminal
```bash
response=$(curl -s -w "\n%{http_code}" \
-H "Authorization: Bearer $DB9_API_KEY" \
-X POST https://api.db9.ai/customer/databases \
-H "Content-Type: application/json" \
-d '{"name": "myapp"}')
body=$(echo "$response" | head -1)
status=$(echo "$response" | tail -1)
if [ "$status" = "409" ]; then
echo "Database already exists"
elif [ "$status" = "201" ] || [ "$status" = "200" ]; then
echo "Created: $body"
else
echo "Error $status: $body"
fi
```
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [REST API Reference](/docs/api/) — all endpoints with request/response examples
* [TypeScript SDK](/docs/sdk/) — typed client with built-in error handling
* [CLI Reference](/docs/cli/) — command-line interface
# Extensions
> DB9 extensions grouped by task — files, search, integration, automation, and data import.
DB9 ships all extensions compiled into the server binary. There is no runtime dynamic loading — every extension is available immediately, and `CREATE EXTENSION` records install metadata per database.
## Extensions by Task
[Section titled “Extensions by Task”](#extensions-by-task)
### Files and Data
[Section titled “Files and Data”](#files-and-data)
| Extension | What it does | Docs |
| ----------- | ------------------------------------------------------------------- | ------------------------------------------- |
| **fs9** | Query, read, and write the database filesystem from SQL | [fs9 reference](/docs/extensions/fs9/) |
| **parquet** | Read Parquet files from the filesystem or HTTP URLs into SQL tables | [Parquet import](/docs/extensions/parquet/) |
### Search
[Section titled “Search”](#search)
| Extension | What it does | Docs |
| -------------------- | ---------------------------------------------------------------------------------------- | ----------------------------------------- |
| **vector** | pgvector-compatible similarity search (HNSW index building disabled this release) | [Vector Search](/docs/extensions/vector/) |
| **embedding** | Server-side text embedding via `EMBEDDING()` and `EMBED_TEXT()` — no external API needed | [Vector Search](/docs/extensions/vector/) |
| **Full-Text Search** | PostgreSQL-compatible full-text search with language tokenizers | [Full-Text Search](/docs/extensions/fts/) |
| **zhparser** | Chinese full-text search tokenizer (jieba-rs, always available) | [Full-Text Search](/docs/extensions/fts/) |
### Integration and Automation
[Section titled “Integration and Automation”](#integration-and-automation)
| Extension | What it does | Docs |
| ------------ | ----------------------------------------------------------------------- | ------------------------------------- |
| **http** | Make HTTP requests from SQL (GET, POST, PUT, DELETE as table functions) | [HTTP client](/docs/extensions/http/) |
| **pg\_cron** | Schedule SQL statements to run on a cron expression | [pg\_cron](/docs/extensions/pg-cron/) |
### Compatibility
[Section titled “Compatibility”](#compatibility)
| Extension | What it does | Docs |
| ------------- | ------------------------------------------------------ | ----------------------------------- |
| **uuid-ossp** | UUID generation functions (`uuid_generate_v4()`, etc.) | [uuid-ossp](/docs/extensions/uuid/) |
| **hstore** | PostgreSQL-compatible key-value store type | [hstore](/docs/extensions/hstore/) |
## Enabling Extensions
[Section titled “Enabling Extensions”](#enabling-extensions)
Most extensions require `CREATE EXTENSION` before use. A few are pre-enabled or always available.
### Pre-enabled (available on every new database)
[Section titled “Pre-enabled (available on every new database)”](#pre-enabled-available-on-every-new-database)
These extensions are installed automatically when a database is created:
SQL
```sql
-- Already enabled — no CREATE EXTENSION needed
SELECT http_get('https://httpbin.org/get');
SELECT count(*) FROM cron.job;
```
Note
`http_get` and the other HTTP functions return `JSONB` — call them as scalar functions (`SELECT http_get(...)`), not as table-valued functions. See [HTTP client](/docs/extensions/http/) for the full reference.
pg\_cron job execution is unavailable in the current release
The `cron` schema functions are installed and callable immediately, but `cron.schedule(...)` currently returns an error — job scheduling/execution requires the worker subsystem, which is disabled for this release. See [pg\_cron](/docs/extensions/pg-cron/) for details.
* **http** — HTTP client functions available immediately
* **pg\_cron** — Extension pre-installed; catalog and management functions (`cron.job`, `cron.unschedule`, etc.) are available immediately, but job scheduling/execution is unavailable in the current release
### Require CREATE EXTENSION
[Section titled “Require CREATE EXTENSION”](#require-create-extension)
These extensions must be explicitly enabled before their functions are available:
SQL
```sql
CREATE EXTENSION IF NOT EXISTS fs9;
CREATE EXTENSION IF NOT EXISTS embedding;
CREATE EXTENSION IF NOT EXISTS parquet;
```
▶ Run
### Metadata shims (built-in, CREATE EXTENSION optional)
[Section titled “Metadata shims (built-in, CREATE EXTENSION optional)”](#metadata-shims-built-in-create-extension-optional)
These capabilities work without `CREATE EXTENSION`, but running it records metadata in `pg_extension` for ORM and tool compatibility:
Run CREATE EXTENSION for ORM compatibility
Even though vector, uuid-ossp, and hstore work without `CREATE EXTENSION`, running it registers the extension in `pg_extension`. ORMs like Prisma and Drizzle check this table to detect capabilities — running `CREATE EXTENSION` prevents false-negative compatibility errors. The same applies to `pgcrypto` and `plpgsql`, which exist only as `pg_extension` entries.
* **vector** — The `vector` type and operators (`<->`, `<#>`, `<=>`) work as built-ins. HNSW index building is disabled in the current release, so similarity search runs as an exact scan — see [Vector Search](/docs/extensions/vector/#hnsw-indexes). `CREATE EXTENSION vector` is recommended so ORMs that check `pg_extension` see it as installed.
* **uuid-ossp** — UUID functions (`uuid_generate_v4()`, etc.) are built-in. `CREATE EXTENSION "uuid-ossp"` records metadata for `pg_dump`/`pg_restore` compatibility.
* **hstore** — Extension metadata is recorded for client compatibility. DB9 does not currently implement full hstore semantics.
* **pgcrypto** — Metadata only, so Supabase and ORM bootstrap scripts that run `CREATE EXTENSION pgcrypto` succeed unchanged. It registers no functions of its own: `gen_random_uuid()` and `digest()` are DB9 built-ins that work with or without it. The rest of pgcrypto is **not** implemented — see the caution below.
* **plpgsql** — PL/pgSQL is compiled in. `CREATE EXTENSION plpgsql` records metadata so restored dumps (which almost always contain it) do not fail.
`CREATE EXTENSION pgcrypto` succeeds, but the crypto functions do not exist
Running `CREATE EXTENSION pgcrypto` returns `CREATE EXTENSION` and adds a row to `pg_extension`, so a schema restore will not fail on it. It does **not** make pgcrypto’s functions available. `crypt()`, `gen_salt()`, `hmac()`, `encrypt()`/`decrypt()`, `pgp_sym_encrypt()` and `gen_random_bytes()` all raise `42883 function ... does not exist` when called.
Only `gen_random_uuid()` and `digest()` work, and both are DB9 built-ins available on a database with no extensions installed. Hash a value with `digest(data, 'sha256')`, `sha256()` or `md5()`; move password hashing and symmetric encryption into your application layer.
### Always available (no-op)
[Section titled “Always available (no-op)”](#always-available-no-op)
* **zhparser** — The Chinese full-text tokenizer is compiled in and always active. `CREATE EXTENSION zhparser` is accepted but has no effect.
## Extension Schemas
[Section titled “Extension Schemas”](#extension-schemas)
Extensions install their functions into specific schemas:
| Extension | Default schema | Access pattern |
| --------- | -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| http | `extensions` | `http_get(...)` for the scalar JSONB form; `SELECT * FROM extensions.http_get(...)` for the table form. The qualifier is **not** interchangeable — see [http](/docs/extensions/http/) |
| fs9 | `extensions` | `fs9_read(...)` or `extensions.fs9_read(...)` — both resolve on the default `search_path` |
| embedding | `extensions` | `embedding(...)` — the bare name; `extensions.embedding(...)` does not resolve |
| parquet | `extensions` | `SELECT * FROM extensions.read_parquet(...)` — table-valued, `FROM` position only |
| pg\_cron | `cron` | `cron.schedule(...)`, `cron.unschedule(...)` |
| vector | `public` | Operators and type available directly |
| uuid-ossp | `public` | `uuid_generate_v4()` available directly |
| hstore | `public` | Type available directly |
| pgcrypto | `extensions` | Metadata only — registers no functions (`gen_random_uuid()`, `digest()` are built-ins) |
| plpgsql | `public` | Metadata only — PL/pgSQL is compiled in |
## Choosing the Right Extension
[Section titled “Choosing the Right Extension”](#choosing-the-right-extension)
| I want to… | Use |
| ------------------------------------------ | -------------------------------------------------------------------------------------------------- |
| Store and search vector embeddings | [vector](/docs/extensions/vector/) + [embedding](/docs/extensions/vector/) |
| Build a RAG pipeline without external APIs | [embedding](/docs/extensions/vector/) + [fs9](/docs/extensions/fs9/) |
| Call an external API from SQL | [http](/docs/extensions/http/) |
| Query files on the database filesystem | [fs9](/docs/extensions/fs9/) |
| Watch filesystem changes in real time | [fs9 events](/docs/extensions/fs9/#event-notifications) + [CLI watch](/docs/cli/#filesystem-watch) |
| Import Parquet data | [parquet](/docs/extensions/parquet/) |
| Schedule a recurring cleanup or report | [pg\_cron](/docs/extensions/pg-cron/) |
| Search text content | [Full-Text Search](/docs/extensions/fts/) |
| Generate UUIDs | [uuid-ossp](/docs/extensions/uuid/) |
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [Architecture](/docs/architecture/) — How extensions fit into the DB9 server
* [SQL Reference](/docs/sql/) — SQL engine compatibility and features
* [RAG with Built-in Embeddings](/docs/guides/rag-with-built-in-embeddings/) — End-to-end tutorial using embedding() and vector search
* [Analyze Agent Logs with fs9](/docs/guides/analyze-agent-logs-with-fs9/) — Tutorial: write and query agent logs from SQL
* [HTTP from SQL](/docs/guides/http-from-sql/) — Tutorial: call APIs, webhooks, and enrich data from SQL
* [Scheduled Jobs with pg\_cron](/docs/guides/scheduled-jobs-with-pg-cron/) — Tutorial: periodic tasks, monitoring, and job management
* [Agent Workflows](/docs/agent-workflows/overview/) — Extensions in agent pipelines
* [CLI Reference](/docs/cli/) — db9 db sql for running extension commands
# CHUNK_TEXT — Document Chunking
> Split documents into overlapping chunks for RAG pipelines using the CHUNK_TEXT table-valued function — with smart markdown-aware splitting.
`CHUNK_TEXT` is a built-in table-valued function that splits text into overlapping chunks suitable for embedding and retrieval-augmented generation (RAG) pipelines. It is markdown-aware — it prefers to break at paragraph boundaries, headings, and list items rather than mid-sentence.
## Syntax
[Section titled “Syntax”](#syntax)
SQL
```sql
SELECT chunk_index, chunk_text, chunk_pos
FROM CHUNK_TEXT(
content,
[max_chars],
[overlap_chars],
[title]
);
```
All parameters except `content` are optional and can be passed positionally or as named arguments.
| Parameter | Type | Default | Description |
| --------------- | ------ | ------- | -------------------------------------------------------------------------------------- |
| `content` | `TEXT` | — | The document text to chunk (max 1 MB). |
| `max_chars` | `INT` | `3600` | Target maximum chunk size in characters (\~900 tokens). |
| `overlap_chars` | `INT` | `540` | Number of characters to overlap between adjacent chunks (default: 15% of `max_chars`). |
| `title` | `TEXT` | `NULL` | Document title prepended to each chunk. |
## Output Columns
[Section titled “Output Columns”](#output-columns)
| Column | Type | Description |
| ------------- | ------ | -------------------------------------------------------- |
| `chunk_index` | `INT` | Zero-based chunk number. |
| `chunk_text` | `TEXT` | The chunk content (with title prefix if provided). |
| `chunk_pos` | `INT` | Character offset of this chunk in the original document. |
## Basic Usage
[Section titled “Basic Usage”](#basic-usage)
SQL
```sql
SELECT chunk_index, length(chunk_text) AS len, chunk_pos
FROM CHUNK_TEXT(
content => 'PostgreSQL is a powerful database system...
(long document text)',
max_chars => 200,
overlap_chars => 30
);
```
▶ Run
Output
```text
chunk_index | len | chunk_pos
-------------+-----+-----------
0 | 127 | 0
1 | 181 | 97
2 | 153 | 248
3 | 151 | 371
```
## With Title
[Section titled “With Title”](#with-title)
When `title` is provided, it is prepended to each chunk so that embedding models have document context:
SQL
```sql
SELECT chunk_index, chunk_text
FROM CHUNK_TEXT(
'Hello world. This is the document body.',
title => 'My Document'
);
```
▶ Run
Output
```text
chunk_index | chunk_text
-------------+---------------------------------------------------
0 | title: My Document | text: Hello world. This is...
```
## Smart Splitting
[Section titled “Smart Splitting”](#smart-splitting)
`CHUNK_TEXT` identifies natural break points in the text and assigns them a score based on proximity to the target chunk boundary:
* **Paragraph breaks** (double newlines) — strongest break point
* **Markdown headings** (`## ...`) — strong break point
* **List items** (`- ...`, `1. ...`) — moderate break point
* **Sentence endings** — fallback break point
Code fences (triple backticks) are treated as no-break zones — the function will not split inside a fenced code block.
## RAG Pipeline Example
[Section titled “RAG Pipeline Example”](#rag-pipeline-example)
Combine `CHUNK_TEXT` with `embedding()` and HNSW indexes for a complete RAG pipeline:
Step 4 is a no-op in the current release
HNSW index building is disabled server-side — over the wire protocol the `CREATE INDEX` in step 4 is rejected with `55000` (`feature "hnsw_index" is unavailable`), and over the HTTP SQL API it reports `CREATE INDEX` and appears in `pg_indexes` but is never used by the planner. The pipeline still works end to end: semantic search falls back to exact scan and returns correct results. See [HNSW Indexes](/docs/extensions/vector/#hnsw-indexes).
SQL
```sql
-- 1. Create the documents and chunks tables
CREATE TABLE rag_documents (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT
);
CREATE TABLE rag_chunks (
id SERIAL PRIMARY KEY,
doc_id INT REFERENCES rag_documents(id),
chunk_index INT,
chunk_text TEXT,
-- Must match the embedding model's output size: the default
-- text-embedding-v4 model returns 1024 dimensions.
embedding VECTOR(1024)
);
-- 2. Add a document to chunk
INSERT INTO rag_documents (title, content)
VALUES ('Branching', repeat('DB9 branching copies the full dataset asynchronously. ', 80));
-- 3. Chunk and embed that document
INSERT INTO rag_chunks (doc_id, chunk_index, chunk_text, embedding)
SELECT
1,
c.chunk_index,
c.chunk_text,
embedding(c.chunk_text)
FROM CHUNK_TEXT(
content => (SELECT content FROM rag_documents WHERE id = 1),
max_chars => 1500,
overlap_chars => 200,
title => (SELECT title FROM rag_documents WHERE id = 1)
) AS c;
-- 4. Create an HNSW index for fast search
CREATE INDEX ON rag_chunks USING hnsw (embedding vector_cosine_ops);
-- 5. Semantic search
SELECT chunk_text, embedding <=> embedding('How does branching work?') AS distance
FROM rag_chunks
ORDER BY distance
LIMIT 5;
```
▶ Run
## Use in CTEs
[Section titled “Use in CTEs”](#use-in-ctes)
`CHUNK_TEXT` works in `WITH` clauses for multi-step processing:
SQL
```sql
WITH raw_chunks AS (
SELECT chunk_index, chunk_text, chunk_pos
FROM CHUNK_TEXT(
content => 'Long document...',
max_chars => 2000
)
)
SELECT chunk_index, length(chunk_text) AS size
FROM raw_chunks
WHERE length(chunk_text) > 100;
```
▶ Run
## Edge Cases
[Section titled “Edge Cases”](#edge-cases)
| Input | Result |
| -------------------------------- | ----------------------------------- |
| `NULL` content | Zero rows returned. |
| Empty string `''` | Zero rows returned. |
| Content shorter than `max_chars` | Single chunk with the full content. |
| Content exceeding 1 MB | Error. |
## Limits
[Section titled “Limits”](#limits)
| Limit | Value |
| ----------------------- | ------------------------------ |
| Max content size | 1 MB |
| Default `max_chars` | 3,600 chars (\~900 tokens) |
| Default `overlap_chars` | 540 chars (15% of `max_chars`) |
| Min `max_chars` | 1 |
| Min `overlap_chars` | 0 |
See [Limits and Quotas](/docs/platform/limits-and-quotas/) for the complete list.
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [Vector Search](/docs/extensions/vector/) — Embedding and HNSW indexes
* [Built-in Functions](/docs/sql/functions/) — All SQL functions
* [fs9 — File System](/docs/extensions/fs9/) — Ingest files to chunk from the filesystem
# fs9 — File System
> Read, write, query, and watch files from SQL using the fs9 extension — with directory listing, glob matching, format auto-detection, event notifications, and a WebSocket API for programmatic access.
fs9 is a TiKV-backed file system accessible from SQL. It provides scalar functions for reading and writing files, a table function for querying file contents as rows, and a WebSocket API for programmatic access from the TypeScript SDK.
Files are stored in TiKV using page-based storage (16 KB pages) with inode metadata — not on a local disk. Each database has its own isolated file system.
## Installation
[Section titled “Installation”](#installation)
SQL
```sql
CREATE EXTENSION IF NOT EXISTS fs9;
```
▶ Run
Superuser required
All fs9 functions require superuser privileges. Only the default `admin` role can use fs9 from SQL. Regular database users created via `db9 db users create` will get a permission denied error.
## FUSE Mount on macOS
[Section titled “FUSE Mount on macOS”](#fuse-mount-on-macos)
If you use `db9 fs mount` on macOS, install macFUSE first:
Terminal
```bash
brew install --cask macfuse
```
Then approve the macFUSE system extension in **System Settings > Privacy & Security** if prompted. After approval, retry the mount command.
Notes:
* This prerequisite applies to `db9 fs mount` only.
* Other `db9 fs` commands such as `fs cp`, `fs ls`, and `fs sh` do not require macFUSE.
* See the [CLI Reference](/docs/cli/#macos-prerequisite-for-db9-fs-mount) for the command-level mount docs.
## Scalar Functions
[Section titled “Scalar Functions”](#scalar-functions)
### fs9\_read
[Section titled “fs9\_read”](#fs9_read)
Read an entire file as text.
SQL
```sql
SELECT extensions.fs9_read('/logs/app.log');
```
Returns `TEXT` (UTF-8, lossy conversion for non-UTF-8 bytes). Returns `NULL` if the path argument is `NULL`.
### fs9\_read\_bytea
[Section titled “fs9\_read\_bytea”](#fs9_read_bytea)
Read an entire file as binary.
SQL
```sql
SELECT extensions.fs9_read_bytea('/data/image.png');
```
Returns `BYTEA`. Use this instead of `fs9_read` when the file contains non-UTF-8 binary data.
### fs9\_write
[Section titled “fs9\_write”](#fs9_write)
Write content to a file, creating it if it does not exist and overwriting if it does. Returns the number of bytes written.
SQL
```sql
SELECT extensions.fs9_write('/data/output.txt', 'hello world');
-- Returns: 11
```
Accepts both `TEXT` and `BYTEA` content.
Parent directory must already exist
`fs9_write` (and `fs9_append`) do not create parent directories automatically. Writing to a path whose parent directory does not exist yet fails with `58P01` — `fs9 resource was not found`, which names no path. Call `extensions.fs9_mkdir('/data', true)` first if the directory may not exist.
### fs9\_append
[Section titled “fs9\_append”](#fs9_append)
Append content to the end of a file. Creates the file if it does not exist. Returns the number of bytes appended.
SQL
```sql
SELECT extensions.fs9_append('/logs/events.jsonl', '{"event":"click","ts":"2026-03-12T10:00:00Z"}' || E'\n');
```
Append does not insert a line separator
`fs9_append` writes the bytes exactly as given. If the existing file does not already end with a newline, the appended record is joined onto the last line. For JSONL that produces one malformed line, which is [silently skipped](#file-reading) when the file is read as JSON — so **both** the previous record and the new one disappear from query results, with no error. The bytes are still on disk and remain visible with `format => 'text'`.
Always terminate appended records with `E'\n'` (as above), and make sure the file’s existing final line ends with one.
### fs9\_read\_at
[Section titled “fs9\_read\_at”](#fs9_read_at)
Read a range of bytes from a file. Returns `TEXT`.
SQL
```sql
SELECT extensions.fs9_read_at('/data/large.bin', 0, 1024);
-- Reads first 1024 bytes
```
Both offset and length must be non-negative. Reading beyond the end of the file returns whatever bytes are available.
### fs9\_read\_at\_bytea
[Section titled “fs9\_read\_at\_bytea”](#fs9_read_at_bytea)
Read a range of bytes from a file. Returns `BYTEA`.
SQL
```sql
SELECT extensions.fs9_read_at_bytea('/data/large.bin', 0, 1024);
-- Reads first 1024 bytes as binary
```
Same semantics as `fs9_read_at`, but returns raw bytes instead of text.
### fs9\_write\_at
[Section titled “fs9\_write\_at”](#fs9_write_at)
Write data at a specific offset in a file. Creates the file if it does not exist. Fills any gap between the current end and the offset with null bytes. Returns the number of bytes written.
SQL
```sql
SELECT extensions.fs9_write_at('/data/file.bin', 100, 'data at offset 100');
```
### fs9\_truncate
[Section titled “fs9\_truncate”](#fs9_truncate)
Truncate a file to a specific size. If the new size is larger than the current size, the file is padded with null bytes. Returns `TRUE` on success.
SQL
```sql
SELECT extensions.fs9_truncate('/logs/rolling.log', 0);
-- Empties the file
```
### fs9\_exists
[Section titled “fs9\_exists”](#fs9_exists)
Check whether a file or directory exists.
SQL
```sql
SELECT extensions.fs9_exists('/data/config.json');
-- Returns: true or false
```
### fs9\_size
[Section titled “fs9\_size”](#fs9_size)
Return the size of a file in bytes.
SQL
```sql
SELECT extensions.fs9_size('/data/export.csv');
-- Returns: 48271
```
### fs9\_mtime
[Section titled “fs9\_mtime”](#fs9_mtime)
Return the last modification time of a file as an RFC 3339 timestamp (UTC).
SQL
```sql
SELECT extensions.fs9_mtime('/data/export.csv');
-- Returns: '2026-03-12T15:30:45Z'
```
### fs9\_remove
[Section titled “fs9\_remove”](#fs9_remove)
Remove a file or directory. Returns the number of items removed. Supports glob patterns.
SQL
```sql
-- Remove a single file
SELECT extensions.fs9_remove('/tmp/scratch.txt');
-- Remove a directory and its contents
SELECT extensions.fs9_remove('/tmp/work/', true);
-- Remove files matching a glob
SELECT extensions.fs9_remove('/logs/2026-01-*.jsonl');
```
The second argument (`recursive`) defaults to `false`. A non-empty directory requires `recursive = true`.
### fs9\_mkdir
[Section titled “fs9\_mkdir”](#fs9_mkdir)
Create a directory. Returns `TRUE` on success.
SQL
```sql
-- Create a single directory
SELECT extensions.fs9_mkdir('/data/exports');
-- Create nested directories
SELECT extensions.fs9_mkdir('/data/exports/2026/03', true);
```
The second argument (`recursive`) defaults to `false`. With `recursive = true`, parent directories are created as needed.
### fs9\_storage\_stats
[Section titled “fs9\_storage\_stats”](#fs9_storage_stats)
Table-valued function that returns aggregated filesystem storage statistics.
SQL
```sql
SELECT total_files, total_directories, total_logical_bytes
FROM extensions.fs9_storage_stats();
```
| Column | Type | Description |
| --------------------- | ----- | ---------------------------------------- |
| `total_files` | INT64 | Total number of files |
| `total_directories` | INT64 | Total number of directories |
| `total_logical_bytes` | INT64 | Total logical size of all files in bytes |
Always returns a single row. Useful for monitoring filesystem usage from SQL or building storage dashboards.
Storage stats do not currently track writes accurately
`fs9_storage_stats()` does not reliably reflect the current filesystem contents — it can return `0` for all columns on a database with existing files, or a stale count that does not update after new writes. Do not rely on it for accurate monitoring yet; enumerate files directly with `extensions.fs9('/path/')` per directory if you need an accurate count.
## Table Function
[Section titled “Table Function”](#table-function)
The `extensions.fs9()` table function reads files and directories as SQL rows. It operates in three modes depending on the path.
Use named parameters, not positional arguments
Optional arguments — `recursive`, `exclude`, `format`, `delimiter`, `header` — must be passed by name (`format => 'csv'`). A positional second argument is rejected:
```plaintext
ERROR: fs9: unexpected positional argument (use named parameters like format => 'csv')
```
### Directory listing
[Section titled “Directory listing”](#directory-listing)
When the path ends with `/`, fs9 returns directory entries:
SQL
```sql
SELECT path, type, size, mtime
FROM extensions.fs9('/logs/')
ORDER BY path;
```
▶ Run
| Column | Type | Description |
| ------- | ----- | --------------------------------- |
| `path` | TEXT | Full path |
| `type` | TEXT | `"file"` or `"dir"` |
| `size` | INT64 | Size in bytes (0 for directories) |
| `mode` | INT64 | Unix permission mode |
| `mtime` | TEXT | Last modified (RFC 3339 UTC) |
Use `recursive => true` to walk subdirectories:
SQL
```sql
SELECT path, size FROM extensions.fs9('/data/', recursive => true)
WHERE type = 'file'
ORDER BY size DESC;
```
Use `exclude` to skip files matching a pattern:
SQL
```sql
SELECT * FROM extensions.fs9('/logs/', exclude => '*.tmp');
```
### File reading
[Section titled “File reading”](#file-reading)
When the path points to a file, fs9 reads its contents as rows. The format is auto-detected from the file extension:
| Extension | Format | Row schema |
| ------------------- | ---------- | ----------------------------------------------------- |
| `.csv` | CSV | `_line_number INT`, columns from header, `_path TEXT` |
| `.tsv` | TSV | `_line_number INT`, columns from header, `_path TEXT` |
| `.jsonl`, `.ndjson` | JSON Lines | `_line_number INT`, `line JSONB`, `_path TEXT` |
| `.parquet` | Parquet | Schema from file metadata |
| Other | Plain text | `_line_number INT`, `line TEXT`, `_path TEXT` |
SQL
```sql
-- CSV with auto-detected columns
SELECT * FROM extensions.fs9('/data/users.csv');
-- JSONL as queryable JSONB
SELECT line->>'level' AS level, count(*)
FROM extensions.fs9('/logs/app.jsonl')
GROUP BY 1;
```
▶ Run
Override the format or delimiter with named parameters:
SQL
```sql
-- Force CSV format on a .dat file
SELECT * FROM extensions.fs9('/data/raw.dat', format => 'csv');
-- Pipe-delimited file without a header row
SELECT col_0, col_1
FROM extensions.fs9('/data/raw.dat', format => 'csv', delimiter => '|', header => false);
```
For CSV/TSV without a header, columns are named `col_0`, `col_1`, etc.
Invalid JSON lines in JSONL files are silently skipped.
### Glob matching
[Section titled “Glob matching”](#glob-matching)
When the path contains `*`, `?`, or `[`, fs9 expands the glob and reads all matching files:
SQL
```sql
-- All CSV files in a directory
SELECT * FROM extensions.fs9('/data/sales/*.csv');
-- Recursive match across subdirectories
SELECT _path, line->>'event' AS event
FROM extensions.fs9('/logs/**/*.jsonl')
ORDER BY _path, _line_number;
```
▶ Run
The schema is determined by the first matching file. All files use the same format. The `_path` column identifies which file each row came from.
Glob parameters:
* `format`, `delimiter`, `header` — same as file reading
* `exclude` — glob pattern to skip (e.g., `*.tmp`)
Hidden files (dotfiles) are excluded unless the pattern explicitly starts with `.` or contains `/.`.
Globs do not decode Parquet
Glob expansion decodes CSV/TSV and JSONL only. A `.parquet` file matched by a glob falls back to the plain-text reader, so a pattern like `extensions.fs9('/data/*.parquet')` returns **zero rows** with the raw-text schema (`_line_number`, `line`, `_path`) instead of the file’s real columns — it does not raise an error.
Read Parquet a file at a time, where the schema is taken from the file metadata:
SQL
```sql
SELECT * FROM extensions.fs9('/data/export.parquet');
```
To load several Parquet files, `COPY` each one — see [Parquet Import](/docs/extensions/parquet/).
Glob read budget
If total bytes read across all matching files exceeds 100 MB, fs9 stops reading and returns partial results with a warning. Use `exclude` patterns or more targeted paths for large directories.
## Event Notifications
[Section titled “Event Notifications”](#event-notifications)
`db9 fs watch` is the recommended way to consume events
`fs9_events()` can be queried directly from SQL (see below), but `db9 fs watch` from the CLI handles cursor-based polling for you and is the recommended interface for most applications.
`fs9_events()` must be called **schema-qualified** — `SELECT * FROM extensions.fs9_events()`. The unqualified name is not registered as a table-valued function and fails with `ERROR: function fs9_events() does not exist` (`42883`).
The `fs9_events()` table function returns a stream of filesystem mutation events. It powers `db9 fs watch` and can be queried directly from SQL for custom change-tracking.
Start your cursor at `'0'`, not at an arbitrary timestamp
Events are delivered — a `fs9_write` shows up as a `CREATE`/`WRITE` row immediately. But a `since_id` older than the stream’s retained range returns **zero rows instead of the full backlog**:
SQL
```sql
SELECT count(*) FROM extensions.fs9_events(); -- 17
SELECT count(*) FROM extensions.fs9_events('0'); -- 17
SELECT count(*) FROM extensions.fs9_events('1750000000000-0'); -- 0 (cursor too old)
```
Seed a new consumer with `'0'`, then advance it using `stream_id` values you actually read back. Do not synthesize a cursor from a wall-clock timestamp.
SQL
```sql
-- All events since the beginning
SELECT * FROM extensions.fs9_events();
-- Events after a stream ID you previously read back (cursor-based polling)
SELECT * FROM extensions.fs9_events('1786979518971-0');
-- Events under a specific path prefix
SELECT * FROM extensions.fs9_events('0', '/data/');
-- Events with a custom limit (default: 10,000)
SELECT * FROM extensions.fs9_events('0', '/logs/', 5000);
```
### Parameters
[Section titled “Parameters”](#parameters)
| Parameter | Type | Default | Description |
| ------------- | ----- | ------- | ------------------------------------------------------------------------------------------------------- |
| `since_id` | TEXT | `'0'` | Return events with stream ID greater than this value (Redis Stream ID format, e.g. `'1711324800000-0'`) |
| `path_prefix` | TEXT | `NULL` | Filter to events where path starts with this prefix |
| `limit` | INT64 | `10000` | Maximum number of event rows to return |
### Return columns
[Section titled “Return columns”](#return-columns)
| Column | Type | Description |
| ------------ | ----------- | ------------------------------------------------- |
| `stream_id` | TEXT | Redis Stream ID (e.g. `'1711324800000-0'`) |
| `event_type` | TEXT | `CREATE`, `WRITE`, `DELETE`, `RENAME`, or `MKDIR` |
| `path` | TEXT | Affected path (post-mutation) |
| `old_path` | TEXT | Previous path (only set for `RENAME` events) |
| `inode` | INT64 | Inode ID |
| `generation` | INT64 | Post-mutation generation |
| `is_dir` | BOOLEAN | Whether the target is a directory |
| `size` | INT64 | Post-mutation file size (0 for directories) |
| `timestamp` | TIMESTAMPTZ | Event timestamp |
> **Note:** `since_id` also accepts integer values for backwards compatibility — they are converted to string internally.
### Cursor-based polling pattern
[Section titled “Cursor-based polling pattern”](#cursor-based-polling-pattern)
SQL
```sql
-- Initial read: get the latest stream_id
SELECT stream_id FROM extensions.fs9_events('0') ORDER BY stream_id DESC LIMIT 1;
-- Subsequent polls: pass the last seen stream_id as since_id
SELECT stream_id, event_type, path, old_path, size, timestamp
FROM extensions.fs9_events(:last_stream_id)
ORDER BY stream_id;
```
For CLI-based watching, use `db9 fs watch` which handles the polling loop, overflow detection, and formatting automatically. See the [CLI Reference](/docs/cli/#filesystem-watch).
## Symbolic Links
[Section titled “Symbolic Links”](#symbolic-links)
fs9 supports symbolic links through the CLI FUSE mount and WebSocket API.
### CLI (FUSE Mount)
[Section titled “CLI (FUSE Mount)”](#cli-fuse-mount)
Terminal
```bash
# Create a symlink
ln -s /data/report.csv /data/latest.csv
# Read the symlink target
readlink /data/latest.csv
```
### WebSocket API
[Section titled “WebSocket API”](#websocket-api)
The `symlink` and `readlink` operations are available over the WebSocket protocol:
* `symlink(path, target)` — create a symbolic link at `path` pointing to `target`
* `readlink(path)` — return the target of a symbolic link
Symlink properties:
* Mode: `0o777` (all permissions)
* Type: reported as `"symlink"` in `stat` responses
* Maximum target length: 4096 bytes
* Writing to a symlink directly is not allowed — write to the target path instead
## File Permissions (Mode Bits)
[Section titled “File Permissions (Mode Bits)”](#file-permissions-mode-bits)
When creating files and directories, fs9 preserves Unix permission bits:
| Operation | Default Mode |
| ---------------- | -------------------- |
| File create | `0644` (`rw-r--r--`) |
| Directory create | `0755` (`rwxr-xr-x`) |
| Symlink | `0777` (`rwxrwxrwx`) |
Executables uploaded with `db9 fs cp` or written via FUSE retain their original mode bits (e.g., `0755` for shell scripts). The `mode` field is visible in `stat` responses and `db9 fs ls -l` output.
## JSONL Support in fssh
[Section titled “JSONL Support in fssh”](#jsonl-support-in-fssh)
The `jq` builtin in `db9 fs sh` supports JSON Lines (JSONL) format — when input contains multiple JSON objects (one per line), the filter is applied to each object independently:
Terminal
```bash
db9 fs sh mydb -c "cat /logs/events.jsonl | jq '.event'"
```
Output
```text
"click"
"pageview"
"submit"
```
## Practical Patterns
[Section titled “Practical Patterns”](#practical-patterns)
### Append-only log ingestion
[Section titled “Append-only log ingestion”](#append-only-log-ingestion)
SQL
```sql
-- Write a log entry
SELECT extensions.fs9_append(
'/logs/agent.jsonl',
'{"ts":"2026-03-12T10:00:00Z","action":"search","query":"revenue"}' || E'\n'
);
-- Query log entries
SELECT line->>'action' AS action, count(*)
FROM extensions.fs9('/logs/agent.jsonl')
GROUP BY 1 ORDER BY 2 DESC;
```
▶ Run
### Periodic report generation with pg\_cron
[Section titled “Periodic report generation with pg\_cron”](#periodic-report-generation-with-pg_cron)
SQL
```sql
SELECT cron.schedule('daily-report', '0 6 * * *', $$
SELECT extensions.fs9_write(
'/reports/daily-' || to_char(now(), 'YYYY-MM-DD') || '.csv',
(SELECT string_agg(id || ',' || name || ',' || total, E'\n')
FROM (SELECT id::text, name, sum(amount)::text AS total
FROM orders WHERE created_at > now() - interval '1 day'
GROUP BY id, name) t)
)
$$);
```
### Upload via HTTP and store in fs9
[Section titled “Upload via HTTP and store in fs9”](#upload-via-http-and-store-in-fs9)
SQL
```sql
SELECT extensions.fs9_write(
'/imports/feed.json',
http_get('https://api.example.com/feed')->>'content'
);
```
### Clean up old files
[Section titled “Clean up old files”](#clean-up-old-files)
SQL
```sql
-- List files older than 7 days
SELECT path, mtime FROM extensions.fs9('/logs/')
WHERE type = 'file'
AND mtime < to_char(now() - interval '7 days', 'YYYY-MM-DD"T"HH24:MI:SS"Z"');
-- Remove them
SELECT extensions.fs9_remove('/logs/2026-02-*.jsonl');
```
▶ Run
## Permissions
[Section titled “Permissions”](#permissions)
All fs9 scalar functions and the table function require **superuser** privileges. The default `admin` role is a superuser. Regular database users created via `db9 db users create` cannot use fs9.
### Connect key scopes
[Section titled “Connect key scopes”](#connect-key-scopes)
When accessing fs9 via the WebSocket API (used by the TypeScript SDK), connect keys control access:
| Scope | Permissions |
| -------- | --------------------------------------------------- |
| `fs9:ro` | Read-only: stat, read, readdir, size, mtime, exists |
| `fs9:rw` | Read and write: all operations |
Connect keys are created via the REST API:
Terminal
```bash
curl -X POST https://api.db9.ai/customer/databases//connect-keys \
-H "Authorization: Bearer $DB9_TOKEN" \
-H "Content-Type: application/json" \
-d '{"name": "agent-fs", "scopes": ["fs9:rw"]}'
```
## WebSocket API
[Section titled “WebSocket API”](#websocket-api-1)
The TypeScript SDK uses a WebSocket connection for fs9 file operations. This provides streaming support for large files and avoids SQL overhead for file I/O.
The WebSocket server listens on port 5480 by default. The protocol uses JSON frames with an `op` field and a request `id` for correlation.
Available operations: `auth`, `stat`, `readdir`, `mkdir`, `read`, `write`, `pwrite`, `append`, `truncate`, `unlink`, `rm`, `rename`, `symlink`, `readlink`.
Files larger than 1 MB are automatically streamed in 64 KB chunks.
Connection limits:
* Auth timeout: 10 seconds
* Idle timeout: 5 minutes
* Max connections per tenant: 50
* Max JSON frame: 2 MB
See the [TypeScript SDK](/docs/sdk/) for the client-side API.
## Limits
[Section titled “Limits”](#limits)
| Limit | Value |
| ----------------------------------------- | --------- |
| Max file size | 100 MB |
| Max total bytes per glob query | 100 MB |
| Max files per glob expansion | 10,000 |
| Concurrent read budget (scalar functions) | 128 MB |
| Page size (internal storage) | 16 KB |
| Write stream flush threshold | 256 KB |
| WebSocket max connections per tenant | 50 |
| WebSocket idle timeout | 5 minutes |
| WebSocket max JSON frame | 2 MB |
| WebSocket streaming chunk size | 64 KB |
## Error Messages
[Section titled “Error Messages”](#error-messages)
| Error | SQLSTATE | Cause |
| ----------------------------------------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------ |
| `fs9: permission denied (superuser required)` | `42501` | Non-superuser role attempted an fs9 operation |
| `fs9 resource was not found` | `58P01` | The path does not exist — including the parent directory of a write |
| `fs9 resource has the wrong object type` | `42809` | The path exists but is not the kind of object the call needs (reading a directory, for example) |
| `fs9 resource already exists` | `58P02` | Creating something that is already there — e.g. `fs9_mkdir` on an existing directory |
| `fs9 directory is not empty` | `42809` | `fs9_remove` on a directory that still has entries; pass `true` as the second argument to recurse |
| `permission denied for fs9 resource` | `42501` | The role may use fs9 but not this particular resource |
| `fs9 resource exceeds the supported size limit` | | File exceeds 100 MB |
| `internal error` | `XX000` | Argument validation failures (a negative `fs9_read_at` offset or `fs9_truncate` size) and I/O faults both surface this way, with no detail |
Argument-validation messages are **not** passed through to the client. `fs9_truncate` still builds the text `fs9_truncate: size must be non-negative` internally, but the wire only ever carries `internal error`, so do not match on it.
## Serverless Functions
[Section titled “Serverless Functions”](#serverless-functions)
fs9 is also accessible from Serverless Functions via `ctx.fs9` — you can read and write files from your deployed JavaScript/TypeScript code without going through SQL. See the [Serverless Functions runtime](/docs/functions/runtime/) for details.
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [Analyze Agent Logs with fs9](/docs/guides/analyze-agent-logs-with-fs9/) — Tutorial: write, query, and aggregate agent logs
* [Scheduled Jobs with pg\_cron](/docs/guides/scheduled-jobs-with-pg-cron/) — Automate fs9 reports on a schedule
* [HTTP from SQL](/docs/guides/http-from-sql/) — Fetch external data and store in fs9
* [Extensions Overview](/docs/extensions/) — All 9 built-in extensions
* [Limits and Quotas](/docs/platform/limits-and-quotas/) — All operational limits
* [TypeScript SDK](/docs/sdk/) — Programmatic fs9 access via WebSocket
# Full-Text Search
> Full-text search with language-specific tokenizers including jieba for Chinese and simple for English.
DB9 supports full-text search with language-specific tokenizers.
## Available Tokenizers
[Section titled “Available Tokenizers”](#available-tokenizers)
| Tokenizer | Aliases | Description |
| --------------- | --------------------- | ------------------------------------------------------------------------------------------- |
| `jieba` | `chinese`, `zhparser` | Chinese word segmentation (jieba-rs) |
| `chinese_ngram` | `zhparser_ngram` | Chinese + bigram overlay for multi-char words |
| `simple` | - | Whitespace tokenizer for English/Latin. No stemming, no stopword removal |
| `english` | - | Whitespace tokenizer plus a minimal stopword list (`a`, `an`, `is`, `the`). **No stemming** |
| `english_stem` | - | Snowball stemming plus the full PostgreSQL English stopword list (\~174 words) |
The default configuration is `simple` (`SHOW default_text_search_config`). Any other configuration name — including `french`, `german`, and `spanish` — raises `unknown text search configuration`.
`english` does not stem — use `english_stem` for PostgreSQL-compatible behavior
In PostgreSQL, `to_tsvector('english', ...)` applies Snowball stemming. In DB9 the `english` configuration only strips a minimal stopword list; it does **not** stem. A query for `running` therefore will not match a document containing `runs`.
Use `english_stem` when you want PostgreSQL-compatible English search:
SQL
```sql
-- english: no stemming — 'running' does not match 'runs'
SELECT to_tsvector('english', 'he runs fast') @@ plainto_tsquery('english', 'running') AS english_match;
-- english_stem: stems both sides — 'running' matches 'runs'
SELECT to_tsvector('english_stem', 'he runs fast') @@ plainto_tsquery('english_stem', 'running') AS english_stem_match;
```
▶ Run
Stemming is algorithmic (Snowball), not dictionary-based, so irregular forms are not folded together — `runs` and `running` both stem to `run`, but `ran` stems to `ran` and will not match them.
Compare how each configuration tokenizes the same input:
SQL
```sql
SELECT
to_tsvector('simple', 'the a is database running') AS simple,
to_tsvector('english', 'the a is database running') AS english,
to_tsvector('english_stem', 'the a is database running') AS english_stem;
```
▶ Run
## GIN Indexes for Full-Text Search
[Section titled “GIN Indexes for Full-Text Search”](#gin-indexes-for-full-text-search)
GIN (Generalized Inverted Index) indexes are the standard way to accelerate full-text search queries. Create a GIN index on a `tsvector` column or expression to avoid sequential scans:
SQL
```sql
-- Index on a tsvector column
CREATE INDEX idx_tsv ON documents USING GIN (tsv);
-- Index on an expression
CREATE INDEX idx_content_fts ON documents
USING GIN (to_tsvector('simple', content));
```
The optimizer uses GIN indexes automatically when the `@@` operator matches an indexed expression.
## Chinese Text Search (jieba)
[Section titled “Chinese Text Search (jieba)”](#chinese-text-search-jieba)
SQL
```sql
-- Create index
CREATE INDEX idx_content_fts ON documents
USING gin(to_tsvector('jieba', content));
-- Search
SELECT * FROM documents
WHERE to_tsvector('jieba', content) @@ plainto_tsquery('jieba', '关键词');
```
## English Text Search
[Section titled “English Text Search”](#english-text-search)
Use `simple` for exact-token matching, or `english_stem` when you want stemming and stopword removal (the closest match to PostgreSQL’s `english` configuration):
SQL
```sql
-- Exact tokens (no stemming)
CREATE INDEX idx_content_fts ON documents
USING gin(to_tsvector('simple', content));
SELECT * FROM documents
WHERE to_tsvector('simple', content) @@ to_tsquery('simple', 'keyword');
-- Stemmed search: a query for 'running' also matches 'runs'
CREATE INDEX idx_content_stem ON documents
USING gin(to_tsvector('english_stem', content));
SELECT * FROM documents
WHERE to_tsvector('english_stem', content) @@ plainto_tsquery('english_stem', 'running');
```
Use the **same configuration** on both sides of `@@` and in the index expression. The configurations tokenize differently, so mixing them (for example indexing with `simple` but querying with `english_stem`) changes which rows match.
## Ranking Results
[Section titled “Ranking Results”](#ranking-results)
SQL
```sql
SELECT
content,
ts_rank(to_tsvector('jieba', content), plainto_tsquery('jieba', '搜索词')) as rank
FROM documents
WHERE to_tsvector('jieba', content) @@ plainto_tsquery('jieba', '搜索词')
ORDER BY rank DESC
LIMIT 10;
```
▶ Run
## Query Types
[Section titled “Query Types”](#query-types)
| Function | Description | Example |
| ---------------------- | ----------------- | ---------------------------------------------------- |
| `plainto_tsquery` | Simple phrase | `plainto_tsquery('jieba', '人工智能')` |
| `to_tsquery` | Boolean operators | `to_tsquery('simple', 'cat & dog')` |
| `phraseto_tsquery` | Exact phrase | `phraseto_tsquery('simple', 'hello world')` |
| `websearch_to_tsquery` | Google-style | `websearch_to_tsquery('simple', '"exact" -exclude')` |
## Boolean Search
[Section titled “Boolean Search”](#boolean-search)
SQL
```sql
-- AND: both terms must match
SELECT * FROM documents
WHERE tsv @@ to_tsquery('simple', 'database & performance');
-- OR: either term matches
SELECT * FROM documents
WHERE tsv @@ to_tsquery('simple', 'postgres | mysql');
-- NOT: exclude term
SELECT * FROM documents
WHERE tsv @@ to_tsquery('simple', 'database & !oracle');
-- Prefix matching
SELECT * FROM documents
WHERE tsv @@ to_tsquery('simple', 'data:*');
```
Prefix matching (`:*`) is not supported
The `:*` prefix flag is silently dropped when parsing the query — `to_tsquery('simple', 'data:*')` renders as `'data'` (an exact-match lexeme), not a prefix match. No error is raised, so a query like `WHERE tsv @@ to_tsquery('simple', 'data:*')` will not match documents containing `database` unless they also contain the exact lexeme `data`. Do not rely on prefix matching until this is supported.
## Highlight Search Results
[Section titled “Highlight Search Results”](#highlight-search-results)
SQL
```sql
SELECT
ts_headline('jieba', content, plainto_tsquery('jieba', '数据库'),
'StartSel=, StopSel=, MaxWords=50'
) as highlighted
FROM documents
WHERE to_tsvector('jieba', content) @@ plainto_tsquery('jieba', '数据库');
```
▶ Run
ts\_headline highlighting does not work with the jieba tokenizer
`ts_headline('jieba', ...)` currently returns the input text unchanged — no `` (or other `StartSel`/`StopSel`) tags are inserted, even when `to_tsvector('jieba', ...)` correctly tokenizes the text and `@@` matches. The `simple` tokenizer is not affected — `ts_headline('simple', ...)` highlights correctly.
## Search with Weights
[Section titled “Search with Weights”](#search-with-weights)
SQL
```sql
-- Prioritize title matches over body
ALTER TABLE articles ADD COLUMN tsv tsvector;
UPDATE articles SET tsv =
setweight(to_tsvector('jieba', title), 'A') ||
setweight(to_tsvector('jieba', body), 'B');
-- Search with weighted ranking
SELECT title, ts_rank(tsv, q) as rank
FROM articles, plainto_tsquery('jieba', '搜索词') q
WHERE tsv @@ q
ORDER BY rank DESC;
```
## Limits
[Section titled “Limits”](#limits)
| Limit | Value |
| ------------------------------------ | --------------------------------------------------------------------------------------------------------------------------- |
| Max `tsvector` value size | 1 MB |
| Max lexemes per `tsvector` | \~264,000 |
| Supported text search configurations | 5 (`jieba`, `chinese_ngram`, `simple`, `english`, `english_stem`), plus the aliases `chinese`, `zhparser`, `zhparser_ngram` |
| GIN index build strategy | Sequential (full table scan) |
See [Limits and Quotas](/docs/platform/limits-and-quotas/) for the complete list.
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [RAG with Built-in Embeddings](/docs/guides/rag-with-built-in-embeddings/) — Combine FTS with vector search for hybrid retrieval
* [Vector Search](/docs/extensions/vector/) — Embedding and HNSW indexes for semantic search
* [Extensions Overview](/docs/extensions/) — All 9 built-in extensions
* [Limits and Quotas](/docs/platform/limits-and-quotas/) — All operational limits
# hstore — Key-Value
> PostgreSQL-compatible key-value store type for client compatibility — use JSONB for new code.
`hstore` in DB9 is a **metadata-only shim** — `CREATE EXTENSION hstore` succeeds and registers extension metadata, but the `hstore` type itself does not exist. Any attempt to use `hstore` as a column type or cast to `hstore` will fail with `ERROR: type "hstore" does not exist`.
**For new code, use `JSONB` instead.** JSONB provides a superset of hstore functionality with full operator support, better performance, and broad ecosystem support.
## Installation
[Section titled “Installation”](#installation)
SQL
```sql
CREATE EXTENSION IF NOT EXISTS hstore;
```
▶ Run
## What Is Supported
[Section titled “What Is Supported”](#what-is-supported)
| Feature | Supported |
| ------------------------- | ------------------------------------------------- |
| `CREATE EXTENSION hstore` | Yes — registers extension metadata only |
| `hstore` column type | No — `type "hstore" does not exist` |
| Cast from `text` | No — `'key=>value'::hstore` fails with type error |
| Basic key access (`->`) | No — type does not exist |
| Containment (`@>`) | No — type does not exist |
## What Is Not Supported
[Section titled “What Is Not Supported”](#what-is-not-supported)
| Feature | Status | JSONB Alternative |
| ----------------------------------------- | ------------- | ------------------------------------- |
| `%`, `?`, `?&`, \`? | \` operators | Not supported |
| \`hstore | | hstore\` concatenation |
| `hs - key` deletion | Not supported | `jsonb - key` |
| `akeys()`, `avals()`, `hstore_to_array()` | Not supported | `jsonb_object_keys()`, `jsonb_each()` |
| `hstore_to_json()`, `hstore_to_jsonb()` | Not supported | Cast directly with `::jsonb` |
| `populate_record()`, `hstore_to_record()` | Not supported | `jsonb_populate_record()` |
| `slice()`, `skeys()`, `svals()` | Not supported | `jsonb` operators |
## Migrate to JSONB
[Section titled “Migrate to JSONB”](#migrate-to-jsonb)
If you have existing code using hstore, JSONB provides equivalent functionality with full operator support:
SQL
```sql
-- hstore: CREATE TABLE with hstore column
-- JSONB equivalent:
CREATE TABLE settings (
id SERIAL PRIMARY KEY,
data JSONB
);
-- hstore: INSERT 'key=>value' syntax
-- JSONB equivalent:
INSERT INTO settings (data) VALUES ('{"theme": "dark", "lang": "en"}');
-- hstore: data->'key' (returns TEXT)
-- JSONB equivalent:
SELECT data->>'theme' FROM settings;
-- Returns: 'dark'
-- hstore: data @> 'key=>value'
-- JSONB equivalent:
SELECT * FROM settings WHERE data @> '{"theme": "dark"}';
-- hstore: delete a key data - 'key'
-- JSONB equivalent:
UPDATE settings SET data = data - 'theme';
-- hstore: get all keys akeys(data)
-- JSONB equivalent:
SELECT jsonb_object_keys(data) FROM settings;
```
## Why JSONB
[Section titled “Why JSONB”](#why-jsonb)
| Feature | hstore (DB9) | JSONB |
| ----------------- | ---------------------------- | ----------------------- |
| Key-value storage | Not available (type missing) | Full support |
| Nested values | No | Yes |
| Operator support | None | Full |
| GIN indexing | No | Yes |
| Schema validation | No | Yes (check constraints) |
| Ecosystem support | Limited | Universal |
## Limits
[Section titled “Limits”](#limits)
| Limit | Value |
| ----------------------- | ----------------------------------- |
| hstore type | Does not exist — metadata shim only |
| Operators available | None — type does not exist |
| Recommended alternative | `JSONB` — full operator support |
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [SQL Reference](/docs/sql/) — SQL engine compatibility and data types
* [Extensions Overview](/docs/extensions/) — All 9 built-in extensions
* [Compatibility Matrix](/docs/platform/compatibility-matrix/) — Full supported/unsupported feature surface
# http — HTTP Client
> Make HTTP requests directly from SQL — GET, POST, PUT, DELETE, HEAD, and PATCH.
Make HTTP requests directly from SQL. Functions are available in two calling conventions: **scalar** (returns JSONB) and **table** (returns rows).
## Installation
[Section titled “Installation”](#installation)
The `http` extension is pre-enabled — no `CREATE EXTENSION` required.
## Scalar Functions (JSONB)
[Section titled “Scalar Functions (JSONB)”](#scalar-functions-jsonb)
The scalar form returns a single JSONB value containing the full response. This is the recommended calling convention for new code.
SQL
```sql
-- GET request — returns JSONB
SELECT http_get('https://api.example.com/data');
-- Extract fields from the JSONB response
SELECT (http_get('https://httpbin.org/get'))->>'status' AS status;
-- POST with JSON body
SELECT http_post(
'https://api.example.com/endpoint',
'{"key": "value"}',
'application/json'
);
-- Parse the response content as JSON
SELECT (http_get('https://api.ipify.org?format=json')->>'content')::json->>'ip' AS my_ip;
```
### Scalar Response Shape
[Section titled “Scalar Response Shape”](#scalar-response-shape)
The JSONB object contains:
| Key | Type | Description |
| -------------- | -------- | -------------------------------------------------------------- |
| `status` | `number` | HTTP status code (200, 404, etc.). |
| `content` | `string` | Response body text. |
| `content_type` | `string` | Response Content-Type header. |
| `headers` | `array` | Response headers as `[{"field": "...", "value": "..."}, ...]`. |
### Available Scalar Functions
[Section titled “Available Scalar Functions”](#available-scalar-functions)
| Function | Description |
| ------------------------------------------------ | ------------------------------------------------------ |
| `http_get(url, headers?)` | HTTP GET, returns JSONB. Optional `headers` JSONB. |
| `http_post(url, body, content_type, headers?)` | HTTP POST, returns JSONB. Optional `headers` JSONB. |
| `http_put(url, body, content_type, headers?)` | HTTP PUT, returns JSONB. Optional `headers` JSONB. |
| `http_delete(url, headers?)` | HTTP DELETE, returns JSONB. Optional `headers` JSONB. |
| `http_head(url, headers?)` | HTTP HEAD, returns JSONB. Optional `headers` JSONB. |
| `http_patch(url, body, content_type, headers?)` | HTTP PATCH, returns JSONB. Optional `headers` JSONB. |
| `http(method, url, headers, content_type, body)` | Generic HTTP request with full control, returns JSONB. |
## Table Functions (Row)
[Section titled “Table Functions (Row)”](#table-functions-row)
The table-valued form requires the schema qualifier
Which calling convention you get depends on how you qualify the name:
| Call | Result |
| ---------------------------------------------------- | ----------------------------------------------------------------------------- |
| `SELECT http_get(url)` | Scalar — returns JSONB (used by every example on this page) |
| `SELECT * FROM extensions.http_get(url)` | Table-valued — returns `status`, `content_type`, `headers`, `content` columns |
| `SELECT * FROM http_get(url)` (unqualified) | `ERROR: function http_get(unknown) does not exist` (`42883`) |
| `SELECT extensions.http_get(url)` (qualified scalar) | `ERROR: function extensions.http_get(text) does not exist` (`42883`) |
So use the bare name for the scalar JSONB form, and the `extensions.` prefix for the row form:
SQL
```sql
SELECT status, content_type FROM extensions.http_get('https://example.com');
```
The same schema-qualifier rule applies to `extensions.http_post(...)` and the other verbs.
## Real-World Examples
[Section titled “Real-World Examples”](#real-world-examples)
### POST to Webhook
[Section titled “POST to Webhook”](#post-to-webhook)
SQL
```sql
-- Send alert to webhook
SELECT http_post(
'https://hooks.slack.com/services/xxx/yyy/zzz',
'{"text": "Database backup completed!"}',
'application/json'
);
```
### Check URL Status
[Section titled “Check URL Status”](#check-url-status)
SQL
```sql
-- Health check multiple endpoints
SELECT
url,
(http_head(url)->>'status')::int AS status
FROM (VALUES
('https://api.example.com/health'),
('https://db9.ai'),
('https://google.com')
) t(url);
```
SSRF protection is built-in
DB9 blocks outbound HTTP requests to private IP ranges (RFC 1918: `10.x`, `172.16.x`, `192.168.x`), loopback (`127.x`), and link-local addresses. Only public HTTPS endpoints are reachable. HTTP (non-TLS) is also blocked.
## Custom Headers
[Section titled “Custom Headers”](#custom-headers)
Pass request headers as a JSONB value. Two formats are supported:
SQL
```sql
-- Object format (recommended)
'{"Authorization": "Bearer sk-..."}'::jsonb
-- Array format (pgsql-http compatible)
'[{"field": "Authorization", "value": "Bearer sk-..."}]'::jsonb
```
### Bearer Token Authentication
[Section titled “Bearer Token Authentication”](#bearer-token-authentication)
SQL
```sql
-- GET with Bearer token
SELECT
(http_get(
'https://api.example.com/protected',
'{"Authorization": "Bearer sk-your-token-here"}'::jsonb
))->>'content' AS body;
-- POST with Bearer token
SELECT
(http_post(
'https://api.example.com/data',
'{"event": "user_signup"}',
'application/json',
'{"Authorization": "Bearer sk-your-token-here"}'::jsonb
))->>'status' AS status;
```
### API Key Header
[Section titled “API Key Header”](#api-key-header)
SQL
```sql
-- X-API-Key header
SELECT
(http_post(
'https://api.example.com/webhook',
'{"event": "test"}',
'application/json',
'{"X-API-Key": "my-api-key"}'::jsonb
))->>'status' AS status;
-- Vendor-specific key header (e.g. OpenAI, Anthropic)
SELECT
(http_post(
'https://api.openai.com/v1/chat/completions',
'{"model": "gpt-4o-mini", "messages": [{"role": "user", "content": "Hello"}]}',
'application/json',
'{"Authorization": "Bearer sk-proj-...", "OpenAI-Organization": "org-..."}'::jsonb
))->>'content' AS response;
```
### Multiple Headers
[Section titled “Multiple Headers”](#multiple-headers)
SQL
```sql
-- Pass several headers at once (object format)
SELECT http_get(
'https://api.example.com/data',
'{"Authorization": "Bearer token", "X-Request-ID": "req-001", "Accept": "application/json"}'::jsonb
);
-- Using the generic http() function for full control
SELECT http(
'POST',
'https://api.example.com/endpoint',
'{"Authorization": "Bearer token", "X-Idempotency-Key": "key-123"}'::jsonb,
'application/json',
'{"payload": "data"}'
);
```
### Store a token in a table for reuse
[Section titled “Store a token in a table for reuse”](#store-a-token-in-a-table-for-reuse)
To avoid repeating credentials inline, store them in a table and join at query time:
SQL
```sql
CREATE TABLE api_credentials (
name TEXT PRIMARY KEY,
headers JSONB NOT NULL
);
INSERT INTO api_credentials VALUES (
'my-api',
'{"Authorization": "Bearer sk-your-token"}'::jsonb
);
-- Use stored headers in a request
SELECT http_get('https://api.example.com/data', headers)
FROM api_credentials
WHERE name = 'my-api';
```
> **Security note:** Credentials stored in tables are visible to any role with `SELECT` access. Use row-level security or store secrets outside the database for production workloads.
## Error Handling
[Section titled “Error Handling”](#error-handling)
### Check HTTP status codes
[Section titled “Check HTTP status codes”](#check-http-status-codes)
The `status` field in the JSONB response contains the HTTP status code. Always check it before using the response body:
SQL
```sql
-- Check status before using the response
SELECT
CASE
WHEN (response->>'status')::int = 200 THEN response->>'content'
WHEN (response->>'status')::int = 401 THEN 'Unauthorized — check your API key'
WHEN (response->>'status')::int = 429 THEN 'Rate limited — retry later'
ELSE 'Error: HTTP ' || (response->>'status')::int
END AS result
FROM (SELECT http_get('https://api.example.com/data') AS response) r;
```
### Filter to successful responses only
[Section titled “Filter to successful responses only”](#filter-to-successful-responses-only)
SQL
```sql
-- Only process rows where the API returned 200
SELECT url, (response->>'content')::jsonb AS data
FROM my_urls
CROSS JOIN LATERAL (SELECT http_get(my_urls.url) AS response) r
WHERE (response->>'status')::int = 200;
```
### Raise an exception on failure
[Section titled “Raise an exception on failure”](#raise-an-exception-on-failure)
Use `RAISE EXCEPTION` inside a function or `DO` block to abort on non-200:
SQL
```sql
DO $$
DECLARE
response JSONB;
status_code INT;
BEGIN
response := http_post(
'https://api.example.com/notify',
'{"event": "test"}',
'application/json',
'{"Authorization": "Bearer sk-..."}'::jsonb
);
status_code := (response->>'status')::int;
IF status_code < 200 OR status_code >= 300 THEN
RAISE EXCEPTION 'HTTP request failed with status %: %',
status_code, response->>'content';
END IF;
END $$;
```
### Handle timeouts
[Section titled “Handle timeouts”](#handle-timeouts)
Requests that exceed 5 seconds raise a PostgreSQL error. Catch it with `EXCEPTION`:
SQL
```sql
DO $$
BEGIN
PERFORM http_get('https://slow-api.example.com/data');
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'HTTP request failed: %', SQLERRM;
END $$;
```
### Common error messages
[Section titled “Common error messages”](#common-error-messages)
| Error | Cause |
| ------------------------------------------- | --------------------------------------- |
| `http: insecure http requests are disabled` | Plain HTTP URL — use `https://` |
| `http: host is not allowed` | SSRF protection blocked the hostname |
| `http: ip is not allowed` | SSRF protection blocked the resolved IP |
| `http: timeout` | Request exceeded the 5-second timeout |
| `http: response too large` | Response body exceeded 1 MB limit |
## Safety Boundaries
[Section titled “Safety Boundaries”](#safety-boundaries)
| Limit | Value |
| -------------------------- | ---------------------------------- |
| Protocol | HTTPS only (HTTP blocked) |
| Max requests per statement | 100 |
| Max concurrent requests | 20 |
| Max response size | 1 MB |
| Request timeout | 5 seconds |
| SSRF protection | Private/internal IP ranges blocked |
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [HTTP from SQL Guide](/docs/guides/http-from-sql/) — Tutorial: call APIs, send webhooks, and enrich data from SQL
* [Extensions Overview](/docs/extensions/) — All 9 built-in extensions
* [Scheduled Jobs with pg\_cron](/docs/extensions/pg-cron/) — Combine HTTP calls with scheduled jobs
# Parquet Import
> Import Parquet files into DB9 tables via COPY FROM, from HTTP URLs or fs9 paths.
Import Parquet files into DB9 tables using `COPY ... WITH (FORMAT parquet)`, either from an HTTP URL or from a file stored in [fs9](/docs/extensions/fs9/) using the `fs9://` scheme. No external tools or ETL pipelines are required.
## Installation
[Section titled “Installation”](#installation)
SQL
```sql
CREATE EXTENSION IF NOT EXISTS parquet;
```
▶ Run
read\_parquet() requires the schema qualifier
The unqualified `read_parquet()` is **not** registered as a table-valued function and returns:
```plaintext
ERROR: function read_parquet(unknown) does not exist -- sqlstate 42883
```
Call it schema-qualified instead — `extensions.read_parquet(...)` works:
SQL
```sql
SELECT count(*) FROM extensions.read_parquet('https://example.com/data.parquet');
```
You can also use `COPY FROM ... WITH (FORMAT parquet)` to import Parquet data into an existing table — see below.
## COPY FROM Parquet
[Section titled “COPY FROM Parquet”](#copy-from-parquet)
Use `COPY ... WITH (FORMAT parquet)` to import Parquet data into an existing table, from either source:
SQL
```sql
-- Import from an HTTP URL
COPY my_table FROM 'https://example.com/export.parquet' WITH (FORMAT parquet);
-- Import from a Parquet file stored in fs9
COPY my_table FROM 'fs9:///data/export.parquet' WITH (FORMAT parquet);
```
Columns are matched by name (case-insensitive). Extra columns in the Parquet file are ignored, and target-table columns with no matching Parquet column are filled with `NULL`.
Type mismatches are not always rejected
Many incompatible column pairs are caught at import (`cannot cast type X to Y`, or `invalid input syntax for type X`), but **numeric source columns are not checked against non-numeric targets**. Importing a numeric Parquet column (`INT32`, `INT64`, `FLOAT`, `DOUBLE`) into a `uuid`, `date`, `time`, `timestamp`, `timestamptz`, `interval`, `inet` or `bytea` column currently succeeds and stores the raw value, with no error — a `uuid` column ends up holding integers. Text source columns are checked against these same targets and rejected where the cast is genuinely invalid (`TEXT` into `bytea` is a valid cast and is accepted).
Treat that list as indicative rather than exhaustive: verify the Parquet schema matches your target table rather than relying on the import to reject a mismatch. Tracked in db9-server#4059.
Use the `fs9://` scheme for fs9 files
Sources must be a URL. `http://`, `https://` and `fs9://` are accepted; a **bare** path is not:
```plaintext
ERROR: unsupported Parquet URL scheme
```
Write `fs9:///data/export.parquet` (three slashes — `fs9://` plus the absolute fs9 path) rather than `/data/export.parquet`. The same rule applies to `extensions.read_parquet()`.
## Type Mapping
[Section titled “Type Mapping”](#type-mapping)
Parquet physical types are mapped to PostgreSQL types during COPY FROM:
| Parquet Type | PostgreSQL Type |
| ------------------------ | ------------------ |
| `BOOLEAN` | `BOOLEAN` |
| `INT32` | `INTEGER` |
| `INT64` | `BIGINT` |
| `FLOAT` | `REAL` |
| `DOUBLE` | `DOUBLE PRECISION` |
| `BYTE_ARRAY` (UTF8) | `TEXT` |
| `BYTE_ARRAY` (binary) | `BYTEA` |
| `INT96` (timestamp) | `TIMESTAMP` |
| `DATE` logical type | `DATE` |
| `TIME` logical type | `TIME` |
| `TIMESTAMP` logical type | `TIMESTAMP` |
| `DECIMAL` logical type | `NUMERIC` |
## Limits
[Section titled “Limits”](#limits)
| Limit | Value |
| ---------------------------------------- | ------------------------------------ |
| Max Parquet file size (`fs9://` sources) | 100 MB |
| Supported Parquet versions | v1, v2 |
| Max columns per file | Unlimited (practical limit: storage) |
| Remote URL connect timeout | 10 seconds |
| Remote URL request timeout | 60 seconds |
| Supported compression codecs | Snappy, Gzip, Zstd, uncompressed |
See [Limits and Quotas](/docs/platform/limits-and-quotas/) for the complete list.
## Error Messages
[Section titled “Error Messages”](#error-messages)
Most errors fall into two groups. Those raised while *fetching* the source do not echo the URL or path, so the message alone does not identify the file — check the statement’s source argument. Those raised while *importing* rows do name the table, row group and row. The first row below is neither: it is a function-resolution error raised before either stage.
| Error | Cause |
| --------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `ERROR: function read_parquet(unknown) does not exist` (`42883`) | Unqualified `read_parquet()` is not on the default `search_path`. Call `extensions.read_parquet(...)`, or use `COPY FROM`. See the note below — the unqualified call *does* work in the session that ran `CREATE EXTENSION`. |
| `ERROR: Parquet file was not found` (`58P01`) | The remote URL returned a 404. Verify the URL is accessible. |
| `ERROR: could not resolve Parquet file host` (`58030`) | The host could not be resolved. Check the hostname. |
| `ERROR: could not connect to Parquet file host` (`58030`) | Connection failed or timed out. |
| `ERROR: Parquet HTTP response has no valid Content-Length` (`58030`) | The URL answered `200` but sent no usable `Content-Length` — commonly an HTML page rather than a file. |
| `ERROR: internal error` (`XX000`) | The body was fetched but is not a valid Parquet file, or is truncated. |
| `ERROR: Parquet URL is not allowed by network policy` (`42501`) | The URL points at a private, loopback or otherwise blocked address. Use a publicly reachable host, or `fs9://`. |
| `ERROR: Parquet file host returned HTTP status ` (`58030`) | The host answered with an unexpected status (for example `500`). |
| `ERROR: permission denied for Parquet file` (`42501`) | The remote URL returned 401 or 403. |
| `ERROR: COPY
, row group N, row M: cannot cast type X to Y` | A Parquet column’s type cannot be cast to the target column’s type at all (for example `INTEGER` into `BOOLEAN`). |
| `ERROR: COPY
, row group N, row M: invalid input syntax for type X: "..."` | The target type is parseable in principle but this value is not — for example `TEXT` into `INTEGER`, or `DOUBLE` into `INTEGER` (`"1.5"`). Note the reported row is the first *offending* row, not necessarily row 1. |
| `ERROR: unsupported Parquet URL scheme` (`22023`) | The source was a bare path, or used a scheme other than `http`, `https` or `fs9`. Use `fs9:///path` for fs9 files, or an `http(s)://` URL. |
| `ERROR: fs9 resource was not found` (`58P01`) | The `fs9://` path does not exist. |
| `ERROR: fs9 resource has the wrong object type` (`42809`) | The `fs9://` path exists but is not a file — most often a directory. |
`CREATE EXTENSION parquet` changes your `search_path`
`CREATE EXTENSION ... parquet` appends `extensions` to the current session’s `search_path` (`"$user", public` becomes `"$user", public, extensions`). For the rest of *that* session an unqualified `read_parquet(...)` resolves and works. Every later session starts from the default `search_path` again, where the same call fails with `42883`.
Always write `extensions.read_parquet(...)` in anything you save — a script that worked in the session where you enabled the extension will fail the next time it runs.
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [HTTP from SQL](/docs/extensions/http/) — Fetch Parquet files from external HTTP endpoints
* [Extensions Overview](/docs/extensions/) — All 9 built-in extensions
* [Limits and Quotas](/docs/platform/limits-and-quotas/) — All operational limits
# pg_cron — Scheduled Tasks
> Schedule SQL statements to run on a cron schedule — with upsert, job management, execution history, and CLI support.
pg\_cron runs SQL statements on a recurring schedule. Jobs are persisted in TiKV, executed by a background worker, and tracked with full execution history.
Job execution is unavailable in the current release
`cron.schedule(...)` returns an error in this release — job scheduling and execution require the worker subsystem, which is disabled for the current release (`PreActivationSeal`):
```plaintext
ERROR: feature "cron" is unavailable (PreActivationSeal)
```
`CREATE EXTENSION pg_cron` still succeeds, and the catalog views (`cron.job`, `cron.job_run_details`, `cron.running_jobs`) and management functions (`cron.unschedule`, `cron.cancel`) resolve normally — they just have no jobs to show while scheduling is disabled. Use this page as reference for when the worker engine is enabled; do not build on scheduled execution yet.
pg\_cron is a **default extension** — it is pre-installed when a database is created. You can verify with:
SQL
```sql
SELECT * FROM pg_extension WHERE extname = 'pg_cron';
```
▶ Run
If needed, install manually:
SQL
```sql
CREATE EXTENSION IF NOT EXISTS pg_cron;
```
▶ Run
## Scheduling Jobs
[Section titled “Scheduling Jobs”](#scheduling-jobs)
### 3-argument form (recommended)
[Section titled “3-argument form (recommended)”](#3-argument-form-recommended)
Named jobs are idempotent
The 3-argument form assigns a name to the job. If a job with the same name already exists for the current user, it updates the existing job instead of creating a duplicate (upsert). This is safe to call repeatedly in migration scripts.
SQL
```sql
SELECT cron.schedule('cleanup', '0 3 * * *',
$$DELETE FROM logs WHERE created_at < now() - interval '7 days'$$);
-- Returns: job_id (e.g., 1)
-- Call again with the same name to update schedule or command
SELECT cron.schedule('cleanup', '0 4 * * *',
$$DELETE FROM logs WHERE created_at < now() - interval '30 days'$$);
-- Returns: same job_id (1)
```
### 2-argument form
[Section titled “2-argument form”](#2-argument-form)
Creates an anonymous job (no name, no upsert). Returns the assigned job ID.
SQL
```sql
SELECT cron.schedule('*/5 * * * *', 'SELECT check_alerts()');
```
## Cron Expression Format
[Section titled “Cron Expression Format”](#cron-expression-format)
Minimum granularity is 1 minute
pg\_cron uses standard 5-field cron expressions. Six-field expressions (with seconds) and special strings like `@daily` or `@hourly` are not supported. Jobs cannot run more frequently than once per minute.
pg\_cron uses standard 5-field cron expressions. Six-field expressions (with seconds) and special strings (`@daily`, `@hourly`) are not supported.
Output
```text
┌───────────── minute (0–59)
│ ┌───────────── hour (0–23)
│ │ ┌───────────── day of month (1–31)
│ │ │ ┌───────────── month (1–12)
│ │ │ │ ┌───────────── day of week (1–7, Sunday = 1, Saturday = 7)
│ │ │ │ │
* * * * *
```
Supported operators: `*` (any), `,` (list), `-` (range), `/` (step).
Day-of-week numbering differs from standard cron
DB9 numbers days **1–7 starting at Sunday**, so every numeric day is shifted by one compared to standard cron and upstream pg\_cron:
| Day | Sun | Mon | Tue | Wed | Thu | Fri | Sat |
| ------------- | --- | --- | --- | --- | --- | --- | --- |
| **DB9** | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| Standard cron | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
`0` is rejected outright, before the job is created:
```plaintext
ERROR: invalid cron expression '0 3 * * 0'
Days of Week must be greater than or equal to 1. ('0' specified.)
```
A value copied from a standard crontab will silently run **one day early** — `1-5` means Sunday–Thursday here, not Monday–Friday. Prefer the day names (`SUN`–`SAT`), which are accepted and unambiguous.
| Expression | Runs |
| ----------------- | ---------------------------------- |
| `* * * * *` | Every minute |
| `*/5 * * * *` | Every 5 minutes |
| `0 * * * *` | Every hour |
| `0 0 * * *` | Daily at midnight |
| `0 3 * * 2-6` | Weekdays at 3 AM (Mon–Fri) |
| `0,30 9-17 * * *` | Every 30 min during business hours |
| `0 0 1 * *` | First day of every month |
| `0 0 * * 1` | Every Sunday at midnight |
## Managing Jobs
[Section titled “Managing Jobs”](#managing-jobs)
### View scheduled jobs
[Section titled “View scheduled jobs”](#view-scheduled-jobs)
SQL
```sql
SELECT jobid, jobname, schedule, command, active
FROM cron.job
ORDER BY jobid;
```
### Modify a job
[Section titled “Modify a job”](#modify-a-job)
`cron.alter_job` updates individual fields. Pass `NULL` for fields you do not want to change.
SQL
```sql
-- Change schedule only
SELECT cron.alter_job(1, '0 4 * * *', NULL, NULL, NULL, NULL, NULL);
-- Disable a job
SELECT cron.alter_job(1, NULL, NULL, NULL, NULL, false, NULL);
-- Set max runtime to 30 minutes
SELECT cron.alter_job(1, NULL, NULL, NULL, NULL, NULL, '30min');
```
**Signature:** `cron.alter_job(job_id, schedule, command, database, username, active, max_runtime)`
All arguments after `job_id` accept `NULL` to leave unchanged. The `database` argument must always be `NULL` (cross-database scheduling is not supported). Only superusers can change the `username` (job owner).
**max\_runtime formats:** `'30min'`, `'2h'`, `'60s'`, `'5000ms'`, or an integer (milliseconds). Use `'0'` for no limit.
### Delete a job
[Section titled “Delete a job”](#delete-a-job)
SQL
```sql
-- By name
SELECT cron.unschedule('cleanup');
-- By ID
SELECT cron.unschedule(1);
```
Returns `true` if the job existed and was deleted, `false` if it did not exist. Deleting a job also removes its execution history.
### Cancel a running job
[Section titled “Cancel a running job”](#cancel-a-running-job)
SQL
```sql
SELECT cron.cancel(5);
```
Returns `true` if a running execution was found and a cancel signal was sent. **Superuser only.**
## Execution History
[Section titled “Execution History”](#execution-history)
### View past runs
[Section titled “View past runs”](#view-past-runs)
SQL
```sql
SELECT jobid, runid, status, return_message, start_time, end_time
FROM cron.job_run_details
WHERE jobid = 1
ORDER BY runid DESC
LIMIT 20;
```
### Status values
[Section titled “Status values”](#status-values)
| Status | Meaning |
| ----------- | -------------------------------------- |
| `starting` | Job is about to execute |
| `running` | Execution in progress |
| `succeeded` | Completed successfully |
| `failed` | SQL error, timeout, or orphan recovery |
| `cancelled` | Cancelled via `cron.cancel()` |
### View currently running jobs
[Section titled “View currently running jobs”](#view-currently-running-jobs)
SQL
```sql
SELECT run_id, job_id, username, command, elapsed_ms
FROM cron.running_jobs;
```
**Superuser only** — non-superusers see an empty result set.
### Execution history retention
[Section titled “Execution history retention”](#execution-history-retention)
Run records are automatically cleaned up after 7 days (configurable via `DB9_CRON_RUN_RETENTION_DAYS`).
## Virtual Tables
[Section titled “Virtual Tables”](#virtual-tables)
### cron.job
[Section titled “cron.job”](#cronjob)
| Column | Type | Description |
| ------------- | ------- | --------------------------------------------------------- |
| `jobid` | BIGINT | Unique job identifier |
| `schedule` | TEXT | 5-field cron expression |
| `command` | TEXT | SQL command |
| `nodename` | TEXT | Always `localhost` |
| `nodeport` | BIGINT | Always `5433` |
| `database` | TEXT | Database name |
| `username` | TEXT | Job owner |
| `active` | BOOLEAN | Whether the job is enabled |
| `jobname` | TEXT | Job name (NULL for anonymous jobs) |
| `max_runtime` | TEXT | Formatted max execution time (e.g., `30min`) or `default` |
| `next_run_at` | TEXT | Next scheduled execution (epoch format) |
Non-superusers see only their own jobs.
### cron.job\_run\_details
[Section titled “cron.job\_run\_details”](#cronjob_run_details)
| Column | Type | Description |
| ---------------- | ------ | ------------------------------------------------------------ |
| `jobid` | BIGINT | Job ID |
| `runid` | BIGINT | Unique run identifier |
| `job_pid` | BIGINT | Process ID (NULL if not started) |
| `database` | TEXT | Database name |
| `username` | TEXT | Job owner |
| `command` | TEXT | SQL command executed |
| `status` | TEXT | `starting`, `running`, `succeeded`, `failed`, or `cancelled` |
| `return_message` | TEXT | Output or error message |
| `start_time` | TEXT | Start timestamp (epoch ms) |
| `end_time` | TEXT | End timestamp (epoch ms) |
Non-superusers see only runs from their own jobs.
### cron.running\_jobs
[Section titled “cron.running\_jobs”](#cronrunning_jobs)
| Column | Type | Description |
| ------------ | ------ | -------------------------- |
| `run_id` | BIGINT | Current run identifier |
| `job_id` | BIGINT | Job ID |
| `keyspace` | TEXT | TiKV keyspace |
| `db_id` | BIGINT | Internal database ID |
| `username` | TEXT | Job owner |
| `command` | TEXT | SQL command being executed |
| `started_at` | TEXT | Start timestamp (epoch ms) |
| `elapsed_ms` | BIGINT | Milliseconds since start |
**Superuser only.**
## CLI Commands
[Section titled “CLI Commands”](#cli-commands)
All commands use `db9 db cron ` — the database comes before the subcommand.
### List jobs
[Section titled “List jobs”](#list-jobs)
Terminal
```bash
db9 db cron list
db9 db cron list --json
```
### Create a job
[Section titled “Create a job”](#create-a-job)
Terminal
```bash
# Inline SQL (command is a positional argument)
db9 db cron create '0 3 * * *' 'VACUUM'
# Named job (enables upsert)
db9 db cron create '*/15 * * * *' 'REFRESH MATERIALIZED VIEW mv_stats' --name refresh
# SQL from file
db9 db cron create '0 0 * * *' --name daily-report --file report.sql
```
### Delete a job
[Section titled “Delete a job”](#delete-a-job-1)
Terminal
```bash
db9 db cron delete
```
### View execution history
[Section titled “View execution history”](#view-execution-history)
Terminal
```bash
db9 db cron history
db9 db cron history --job cleanup --limit 50
```
### Enable or disable a job
[Section titled “Enable or disable a job”](#enable-or-disable-a-job)
Terminal
```bash
db9 db cron enable
db9 db cron disable
```
### Check job status
[Section titled “Check job status”](#check-job-status)
Terminal
```bash
db9 db cron status
db9 db cron status
```
## Execution Model
[Section titled “Execution Model”](#execution-model)
* Each job runs in its own temporary database connection with the job owner’s credentials
* Commands auto-commit (no wrapping transaction)
* Output and errors are captured in `return_message`
* The worker polls for due jobs every 60 seconds
* A claim guard prevents the same job from running twice in the same minute
### Timeouts
[Section titled “Timeouts”](#timeouts)
| Timeout | Default | Description |
| --------------------- | ---------- | ----------------------------------------------------- |
| Job execution timeout | 30 minutes | Global default; overridable per job via `max_runtime` |
| Orphan recovery | 5 minutes | Running jobs with no heartbeat are marked failed |
If a job exceeds its timeout, it is terminated and marked `failed` with the message “orphan recovery: execution timed out”.
## Permissions
[Section titled “Permissions”](#permissions)
| Operation | Non-superuser | Superuser |
| ---------------------- | ----------------- | -------------------------- |
| Schedule jobs | Own jobs | Any |
| View jobs (`cron.job`) | Own jobs only | All |
| Alter jobs | Own jobs only | Any; can also change owner |
| Unschedule jobs | Own jobs only | Any |
| Cancel running jobs | No | Yes |
| View running jobs | No (empty result) | Yes |
| View execution history | Own runs only | All |
## Practical Patterns
[Section titled “Practical Patterns”](#practical-patterns)
### Cleanup old data
[Section titled “Cleanup old data”](#cleanup-old-data)
SQL
```sql
SELECT cron.schedule('cleanup-logs', '0 3 * * 1', $$
DELETE FROM audit_logs
WHERE created_at < now() - interval '90 days'
$$);
```
### Periodic API polling with HTTP
[Section titled “Periodic API polling with HTTP”](#periodic-api-polling-with-http)
SQL
```sql
SELECT cron.schedule('sync-rates', '0 * * * *', $$
INSERT INTO exchange_rates (fetched_at, data)
SELECT now(), (http_get('https://api.example.com/rates')->>'content')::jsonb
$$);
```
### Write periodic reports to fs9
[Section titled “Write periodic reports to fs9”](#write-periodic-reports-to-fs9)
SQL
```sql
SELECT cron.schedule('daily-metrics', '0 6 * * *', $$
SELECT extensions.fs9_write(
'/reports/metrics-' || to_char(now(), 'YYYY-MM-DD') || '.csv',
(SELECT string_agg(metric || ',' || value::text, E'\n')
FROM app_metrics WHERE ts > now() - interval '1 day')
)
$$);
```
### Refresh a materialized view
[Section titled “Refresh a materialized view”](#refresh-a-materialized-view)
SQL
```sql
SELECT cron.schedule('refresh-mv', '*/30 * * * *',
'REFRESH MATERIALIZED VIEW mv_dashboard_stats');
```
## Limits
[Section titled “Limits”](#limits)
| Limit | Value |
| ---------------------------- | -------------- |
| Max jobs per database | 50 |
| Max concurrent jobs (global) | 32 |
| Cron expression fields | 5 (no seconds) |
| Default job timeout | 30 minutes |
| Orphan recovery timeout | 5 minutes |
| Execution history retention | 7 days |
| Worker poll interval | 60 seconds |
See [Limits and Quotas](/docs/platform/limits-and-quotas/) for the complete list.
## Error Messages
[Section titled “Error Messages”](#error-messages)
| Error | Cause |
| ------------------------------------------------------------------------------- | ------------------------------------ |
| `extension pg_cron is not installed` | Run `CREATE EXTENSION pg_cron` first |
| `invalid cron expression: 6-field expressions (with seconds) are not supported` | Use 5-field syntax |
| `invalid cron expression: special strings like '@daily' are not supported` | Use `0 0 * * *` instead |
| `cron.schedule: {arg} must not be NULL` | Required argument is NULL |
| `cron.unschedule: must be superuser or owner of the job` | Permission denied |
| `cron.cancel: must be superuser` | Only superusers can cancel |
| `cron.alter_job: cross-database scheduling not supported` | `database` arg must be NULL |
## Scheduling Serverless Functions
[Section titled “Scheduling Serverless Functions”](#scheduling-serverless-functions)
pg\_cron can also be used to schedule [Serverless Functions](/docs/functions/configuration/#cron-scheduling) on a recurring basis — trigger a deployed function from a cron job instead of (or in addition to) running SQL directly.
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [Scheduled Jobs with pg\_cron](/docs/guides/scheduled-jobs-with-pg-cron/) — Tutorial: create, monitor, and manage periodic jobs
* [HTTP from SQL](/docs/guides/http-from-sql/) — Call external APIs from SQL — combine with cron for polling
* [fs9 — File System](/docs/extensions/fs9/) — Write periodic reports to the file system
* [Extensions Overview](/docs/extensions/) — All 9 built-in extensions
* [Limits and Quotas](/docs/platform/limits-and-quotas/) — All operational limits
* [CLI Reference](/docs/cli/) — db9 db cron command details
# uuid-ossp
> Generate universally unique identifiers (UUIDs) in SQL — v4 random and v7 time-ordered.
Generate universally unique identifiers (UUIDs) in SQL.
## Built-in (No Extension Required)
[Section titled “Built-in (No Extension Required)”](#built-in-no-extension-required)
`uuid_generate_v4()` is available by default without any extension:
SQL
```sql
-- Generate a random UUID (v4)
SELECT uuid_generate_v4();
-- Result: 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
-- Use in table definition
CREATE TABLE uuid_demo_users (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
name TEXT
);
-- Insert without specifying id
INSERT INTO uuid_demo_users (name) VALUES ('Alice');
```
▶ Run
## Extension (PostgreSQL Compatibility)
[Section titled “Extension (PostgreSQL Compatibility)”](#extension-postgresql-compatibility)
For full PostgreSQL compatibility, you can also install the extension:
SQL
```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
```
▶ Run
## Available Functions
[Section titled “Available Functions”](#available-functions)
| Function | Description |
| -------------------- | --------------------------------------------------- |
| `uuid_generate_v4()` | Random UUID v4 (most common) |
| `gen_random_uuid()` | Alias for uuid\_generate\_v4() |
| `uuidv7()` | Time-ordered UUID v7 (recommended for primary keys) |
## UUID v7 (Time-Ordered)
[Section titled “UUID v7 (Time-Ordered)”](#uuid-v7-time-ordered)
UUID v7 embeds a timestamp, making it ideal for primary keys where natural ordering by creation time is desired:
SQL
```sql
-- Generate UUID v7
SELECT uuidv7();
-- Result: '018e1c6a-5b00-7abc-8def-1234567890ab'
-- UUIDs are naturally sorted by creation time
CREATE TABLE uuid_demo_events (
id UUID DEFAULT uuidv7() PRIMARY KEY,
name TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
-- Insert multiple rows
INSERT INTO uuid_demo_events (name) VALUES ('first'), ('second'), ('third');
-- UUIDs are already in chronological order
SELECT id, name FROM uuid_demo_events ORDER BY id;
```
▶ Run
**Why UUID v7 over v4?**
* **Time-ordered**: Better for B-tree index performance
* **Sortable**: Natural chronological ordering without timestamp column
* **Unique**: Still globally unique like v4
## Common Patterns
[Section titled “Common Patterns”](#common-patterns)
SQL
```sql
-- Generate multiple UUIDs
SELECT uuid_generate_v4() FROM generate_series(1, 5);
-- UUID as text
SELECT uuid_generate_v4()::text;
-- Check if valid UUID
SELECT 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid;
```
▶ Run
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [Extensions Overview](/docs/extensions/) — All 9 built-in extensions
* [SQL Reference](/docs/sql/) — SQL engine compatibility and data types
* [Compatibility Matrix](/docs/platform/compatibility-matrix/) — Full PostgreSQL compatibility surface
# Vector Search
> Store vector embeddings, build HNSW indexes, and run k-NN similarity search — with optional built-in embedding generation directly in SQL.
DB9 provides pgvector-compatible vector storage and HNSW indexing, plus a built-in embedding service that generates vectors directly in SQL without external API calls.
Two extensions work together:
* **vector** — the `VECTOR` type, distance operators, and HNSW indexes
* **embedding** — the `embedding()` function for server-side text embedding
## Installation
[Section titled “Installation”](#installation)
SQL
```sql
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS embedding; -- optional: enables EMBEDDING() function
```
▶ Run
Both extensions require superuser privileges (the default `admin` role).
## VECTOR Type
[Section titled “VECTOR Type”](#vector-type)
Store fixed-dimension vectors using the `VECTOR(n)` type:
SQL
```sql
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
embedding VECTOR(1024)
);
```
Vectors can be inserted as text literals or cast from arrays:
SQL
```sql
-- Text literal
INSERT INTO documents (content, embedding)
VALUES ('hello world', '[0.1, 0.2, 0.3, ...]');
-- Array cast
INSERT INTO documents (content, embedding)
VALUES ('hello world', ARRAY[0.1, 0.2, 0.3]::vector);
```
Vectors are stored as 32-bit floats (`float4`) internally, matching the pgvector storage format. Each element keeps roughly 7 significant decimal digits — e.g. `[0.123456789]` reads back as `[0.12345679]`. Dimensions must match the column definition for all operations.
## Distance Operators
[Section titled “Distance Operators”](#distance-operators)
| Operator | Metric | Function equivalent |
| -------- | ----------------------- | ------------------------------------- |
| `<->` | L2 (Euclidean) distance | `l2_distance(a, b)` |
| `<=>` | Cosine distance | `cosine_distance(a, b)` |
| `<#>` | Negative inner product | `vector_negative_inner_product(a, b)` |
All operators return `FLOAT8`. Both operands must have the same number of dimensions.
SQL
```sql
-- Cosine similarity search
SELECT content, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY distance
LIMIT 5;
```
Cosine distance returns 0 for identical vectors and approaches 2 for opposite vectors. If either vector has zero norm, it returns `NaN` (cosine similarity is undefined for a zero vector).
The `<#>` operator returns the **negative** dot product (pgvector convention), so lower values indicate higher similarity — `'[1,2,3]' <#> '[4,5,6]'` is `-32`. The `inner_product(a, b)` function returns the plain (positive) dot product — `inner_product('[1,2,3]', '[4,5,6]')` is `32`. Use the `<#>` operator (or `vector_negative_inner_product`) for distance ordering, and `inner_product` when you want the raw dot product.
## Distance and Utility Functions
[Section titled “Distance and Utility Functions”](#distance-and-utility-functions)
| Function | Signature | Description |
| ------------------------------------- | --------------------------- | ---------------------------------------------- |
| `l2_distance(a, b)` | `(VECTOR, VECTOR) → FLOAT8` | Euclidean distance |
| `cosine_distance(a, b)` | `(VECTOR, VECTOR) → FLOAT8` | Cosine distance (1 - cosine similarity) |
| `inner_product(a, b)` | `(VECTOR, VECTOR) → FLOAT8` | Inner product (dot product), e.g. `32` |
| `vector_negative_inner_product(a, b)` | `(VECTOR, VECTOR) → FLOAT8` | Negative dot product; backs the `<#>` operator |
| `vector_dims(v)` | `(VECTOR) → INT4` | Number of dimensions |
| `vector_norm(v)` | `(VECTOR) → FLOAT8` | L2 norm (magnitude) |
| `l2_normalize(v)` | `(VECTOR) → VECTOR` | Unit vector (v / ‖v‖) |
SQL
```sql
SELECT vector_dims(embedding) FROM documents LIMIT 1;
-- Returns: 1024
SELECT vector_norm('[3, 4]'::vector);
-- Returns: 5.0
SELECT l2_normalize('[3, 4]'::vector);
-- Returns: [0.6, 0.8]
```
## HNSW Indexes
[Section titled “HNSW Indexes”](#hnsw-indexes)
HNSW (Hierarchical Navigable Small World) indexes enable fast approximate nearest-neighbor search. Without an index, distance queries require a full table scan.
Queries fall back to an exact sequential scan when no index is present, so all distance operators and functions (`<->`, `<=>`, `<#>`, `l2_distance`, `cosine_distance`, `inner_product`) return correct results either way — the index changes speed, not answers.
HNSW index building is disabled in the current release
HNSW is gated behind a server-side background-worker class that is **not enabled in production today**. The two connection paths fail differently, and neither gives you a working index:
* **Over the PostgreSQL wire protocol (`psql`, ORMs, drivers)** — `CREATE INDEX ... USING hnsw` is rejected outright with `55000`: `ERROR: feature "hnsw_index" is unavailable (DisabledByConfiguration)`
* **Over the HTTP SQL API** — the statement reports `CREATE INDEX` and the index shows up in `pg_indexes`, but it is never used: `EXPLAIN` still returns a `Seq Scan` plan after creating it. Treat this success as cosmetic.
If the table does not meet HNSW’s structural requirements — see below; the most common miss is a table with **no single-column primary key** — you get a bare `XX000` (`internal error`) instead, on both paths, and nothing is created. That error names neither HNSW nor the primary key, so check the table shape before concluding the feature is broken in some other way.
`DB9_BG_LEDGER_ACTIVE_CLASSES` is a server environment variable, not a session GUC, so it cannot be turned on from SQL.
**This affects speed, not correctness.** Exact search still returns correct results at any table size — keep using the distance operators and `ORDER BY ... LIMIT` as shown below. The rest of this section documents HNSW for when the worker class is enabled.
### Creating an index
[Section titled “Creating an index”](#creating-an-index)
SQL
```sql
-- Cosine distance (most common for text embeddings)
CREATE INDEX idx_docs_embedding ON documents
USING hnsw (embedding vector_cosine_ops);
-- L2 distance
CREATE INDEX idx_docs_embedding ON documents
USING hnsw (embedding vector_l2_ops);
-- Inner product
CREATE INDEX idx_docs_embedding ON documents
USING hnsw (embedding vector_ip_ops);
```
### Tuning parameters
[Section titled “Tuning parameters”](#tuning-parameters)
SQL
```sql
-- Custom build parameters
CREATE INDEX idx_docs_embedding ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
```
| Parameter | Default | Description |
| ----------------- | ------- | -------------------------------------------------------------------------------------- |
| `m` | 16 | Connections per layer. Higher = better recall, more memory. Range: 4–32. |
| `ef_construction` | 64 | Search width during build. Higher = better index quality, slower build. Range: 40–512. |
### Search-time tuning
[Section titled “Search-time tuning”](#search-time-tuning)
SQL
```sql
-- Increase search accuracy (higher = better recall, slower)
SET hnsw.ef_search = 100;
-- Query uses the index automatically
SELECT content, embedding <=> query_vec AS distance
FROM documents
ORDER BY distance
LIMIT 10;
```
The default `ef_search` is 40. Higher values improve recall at the cost of latency.
### When the index is used
[Section titled “When the index is used”](#when-the-index-is-used)
The optimizer uses an HNSW index when all of these are true:
* The query has `ORDER BY distance_function(vector_column, constant) ASC`
* A `LIMIT k` clause is present
* An HNSW index exists on the vector column with the matching distance metric
* The index is in `Ready` state
SQL
```sql
-- This uses the HNSW index (ORDER BY + LIMIT + matching metric)
SELECT * FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;
-- This does NOT use the index (no LIMIT)
SELECT * FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector;
```
### Index constraints
[Section titled “Index constraints”](#index-constraints)
Which of these errors you actually see today depends on the transport
These constraints describe HNSW as it behaves when the worker class is enabled. While the gate is on, the wire protocol stops early and the HTTP SQL API keeps validating, so the two paths report different things:
| Violation | PostgreSQL wire protocol | HTTP SQL API |
| ----------------------------------------------- | ------------------------------------------------------------------------- | ---------------------------------------------------------- |
| Multi-column index | `0A000` — `access method "hnsw" does not support multicolumn indexes` | same |
| No PK, composite PK, or partial index (`WHERE`) | bare `XX000` (`internal error`) | same |
| Negative `INTEGER`/`BIGINT` PK | `55000` — the gate fires before the check | `22023` — the named message below **is** returned |
| Valid table | `55000` — `feature "hnsw_index" is unavailable (DisabledByConfiguration)` | `CREATE INDEX`, and a phantom index the planner never uses |
A bare `XX000` means you tripped one of the structural requirements below — that holds on both paths. `55000` is pgwire-only and means the opposite: the table is fine and only the gate is stopping you.
* Single vector column only — `access method "hnsw" does not support multicolumn indexes`
* The indexed column must declare its dimensions (`VECTOR(1024)`, not bare `VECTOR`). Declare the dimension when you create the table:
SQL
```sql
-- Works
CREATE TABLE documents (id SERIAL PRIMARY KEY, embedding VECTOR(1024));
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
-- Cannot be indexed: no declared dimension
CREATE TABLE bad (id SERIAL PRIMARY KEY, embedding VECTOR);
CREATE INDEX ON bad USING hnsw (embedding vector_cosine_ops);
```
* The table must have a **single-column primary key**. A table with no primary key or a composite primary key is rejected with `HNSW indexes require a single-column primary key`.
* If the primary key is `INTEGER` or `BIGINT`, its values must be **non-negative**. A negative key present at build time fails the `CREATE INDEX`; inserting one afterwards fails the `INSERT` with `HNSW index requires non-negative INTEGER primary key`. `UUID` and `TEXT` primary keys have no such restriction.
* No partial indexes — a `WHERE` clause is rejected with `HNSW indexes do not support partial index predicates (WHERE clause)`
* No UNIQUE constraint on vector columns
* `IVFFlat` is not available at all (`access method "ivfflat" does not exist`); HNSW is the only vector index type.
## Built-in Embedding Generation
[Section titled “Built-in Embedding Generation”](#built-in-embedding-generation)
The `embedding` extension provides server-side text embedding. The default model is `text-embedding-v4` with 1024 dimensions.
### embedding()
[Section titled “embedding()”](#embedding)
SQL
```sql
-- Default model and dimensions
SELECT embedding('hello world');
-- Custom model
SELECT embedding('hello world', 'text-embedding-v4');
-- Custom dimensions: use the embedding.dimensions session setting instead
-- of the 3-argument form (see caution below)
SET embedding.dimensions = 512;
SELECT embedding('hello world');
```
▶ Run
| Argument | Type | Required | Description |
| ---------- | ---- | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- |
| text | TEXT | Yes | Text to embed (must not be empty) |
| model | TEXT | No | Model name (default: server-configured) |
| dimensions | INT | — | Wire protocol only — passing a third argument drops the connection over the HTTP SQL API. Set `embedding.dimensions` instead for portable code (see below). |
Returns `VECTOR`. Requires superuser privileges.
Only 256, 512 and 1024 dimensions are accepted
Any other value fails with a bare `XX000` (`internal error`) that does not mention dimensions. This applies on **both** transports via the `embedding.dimensions` session setting, and on the wire protocol via the third argument. (Over the HTTP SQL API the third argument drops the connection regardless of the width you ask for — see the caution below.)
SQL
```sql
SELECT embedding('hello', 'text-embedding-v4', 768); -- XX000 internal error
SET embedding.dimensions = 768; -- accepted
SELECT embedding('hello'); -- XX000 internal error
```
`768` is a common embedding width elsewhere, so this is easy to trip over. The default is `1024`.
3-argument form works only over the wire protocol
Calling `embedding()` with all three arguments (text, model, dimensions) works over the PostgreSQL wire protocol (`psql`, ORMs, drivers), but **drops the connection over the HTTP SQL API** — which is what `db9 db sql` uses:
```plaintext
SELECT embedding('hello world', 'text-embedding-v4', 512);
-- pgwire: returns a 512-dimension VECTOR
-- HTTP: error: connection closed
```
For code that must work on both, use the 2-argument form (`embedding(text, model)`) plus the `embedding.dimensions` session setting instead, as shown above — that path works over both transports.
### embed\_text()
[Section titled “embed\_text()”](#embed_text)
An alternative with explicit model selection:
SQL
```sql
SELECT embed_text('text-embedding-v4', 'hello world');
SELECT embed_text('text-embedding-v4', 'hello world', '{"dimensions": 512}');
```
▶ Run
The third argument is a JSON string with options.
### Auto-embedding distance functions
[Section titled “Auto-embedding distance functions”](#auto-embedding-distance-functions)
These functions embed text and compute distance in one call — useful for search queries where you don’t want to manage the embedding step:
SQL
```sql
-- Embed query text and compute cosine distance against stored vectors
SELECT content
FROM documents
ORDER BY vec_embed_cosine_distance(embedding, 'database for AI agents')
LIMIT 5;
```
▶ Run
| Function | Equivalent to |
| -------------------------------------- | --------------------------------------- |
| `vec_embed_l2_distance(vec, text)` | `l2_distance(vec, embedding(text))` |
| `vec_embed_cosine_distance(vec, text)` | `cosine_distance(vec, embedding(text))` |
| `vec_embed_inner_product(vec, text)` | `inner_product(vec, embedding(text))` |
These functions work with HNSW index scans — the embedding is computed once at plan time, not per row.
### Embedding usage tracking
[Section titled “Embedding usage tracking”](#embedding-usage-tracking)
SQL
```sql
SELECT * FROM extensions.embedding_usage();
```
Returns a single row with:
* `tokens_used` (BIGINT) — cumulative tokens consumed today (UTC)
* `resets_at` (TIMESTAMPTZ) — next UTC midnight
### Session configuration
[Section titled “Session configuration”](#session-configuration)
Override the server-configured embedding settings per session:
SQL
```sql
SET embedding.provider = 'openai';
SET embedding.model = 'text-embedding-3-small';
SET embedding.api_key = 'sk-...';
SET embedding.dimensions = 1536;
```
| Setting | Default | Description |
| ----------------------- | ------------------- | ------------------------------------- |
| `embedding.provider` | `openai` | API provider (`openai` or `bedrock`) |
| `embedding.endpoint` | (server-configured) | API endpoint URL |
| `embedding.api_key` | (server-configured) | API key |
| `embedding.model` | `text-embedding-v4` | Model name |
| `embedding.dimensions` | `1024` | Output dimensions |
| `embedding.max_calls` | `100` | Max embedding calls per SQL statement |
| `embedding.concurrency` | `5` | Max concurrent API calls per tenant |
| `hnsw.ef_search` | `40` | HNSW search expansion factor |
## Document Chunking with CHUNK\_TEXT
[Section titled “Document Chunking with CHUNK\_TEXT”](#document-chunking-with-chunk_text)
For long documents, split text into overlapping chunks before embedding using the built-in `CHUNK_TEXT` table-valued function:
SQL
```sql
-- Chunk a document and embed each chunk
INSERT INTO chunks (doc_id, chunk_index, chunk_text, embedding)
SELECT
1,
c.chunk_index,
c.chunk_text,
embedding(c.chunk_text)
FROM CHUNK_TEXT(
content => (SELECT content FROM documents WHERE id = 1),
max_chars => 1500,
overlap_chars => 200,
title => (SELECT title FROM documents WHERE id = 1)
) AS c;
```
`CHUNK_TEXT` is markdown-aware — it prefers to break at paragraph boundaries, headings, and list items. See [CHUNK\_TEXT](/docs/extensions/chunk-text/) for full reference.
## End-to-End Example
[Section titled “End-to-End Example”](#end-to-end-example)
SQL
```sql
-- Setup
CREATE EXTENSION vector;
CREATE EXTENSION embedding;
-- Create table
CREATE TABLE docs (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
vec VECTOR(1024)
);
-- Insert with server-side embedding
INSERT INTO docs (content, vec) VALUES
('PostgreSQL is a relational database', embedding('PostgreSQL is a relational database')),
('DB9 provides serverless Postgres for AI agents', embedding('DB9 provides serverless Postgres for AI agents')),
('Vector search finds similar documents', embedding('Vector search finds similar documents'));
-- Create HNSW index — see the caution above: this is rejected over the wire
-- protocol (55000) and creates an unused index over the HTTP SQL API.
-- The semantic search below works either way, via exact search.
CREATE INDEX idx_docs_vec ON docs USING hnsw (vec vector_cosine_ops);
-- Semantic search
SELECT content,
vec <=> embedding('database for AI')::vector AS distance
FROM docs
ORDER BY distance
LIMIT 3;
-- Or use the auto-embedding shortcut
SELECT content
FROM docs
ORDER BY vec_embed_cosine_distance(vec, 'database for AI')
LIMIT 3;
```
## Limits
[Section titled “Limits”](#limits)
| Limit | Value |
| ----------------------------------------- | ------------------------------------- |
| Embedding calls per statement | 100 (configurable) |
| Concurrent embedding API calls per tenant | 5 (configurable) |
| HNSW default m | 16 |
| HNSW default ef\_construction | 64 |
| HNSW default ef\_search | 40 |
| Embedding token usage | Tracked daily, resets at UTC midnight |
## Caveats
[Section titled “Caveats”](#caveats)
HNSW index requirements
HNSW indexes require a **single-column primary key**. Tables with no primary key or a composite primary key are rejected. `UUID` and `TEXT` primary keys are supported; if the key is `INTEGER` or `BIGINT`, every value must be **non-negative** — a negative key fails the `CREATE INDEX`, and once the index exists it also fails the `INSERT`. Plan your schema accordingly.
Embedding requires superuser
The `embedding()` and `embed_text()` functions require superuser privileges. Regular database users cannot call them directly. Use a `SECURITY DEFINER` wrapper function to expose embedding to non-superuser roles.
* **IVFFlat is not supported.** Only HNSW indexes are available for vector similarity search.
* **Embedding requires superuser.** Regular database users cannot call `embedding()` or `embed_text()`.
* **HNSW requires a single-column primary key.** `UUID` and `TEXT` keys work; `INTEGER`/`BIGINT` keys must be non-negative. No-PK and composite-PK tables are rejected.
* **HNSW requires a dimensioned column.** Index the column as `VECTOR(n)`; a bare `VECTOR` column drops the connection at `CREATE INDEX`.
* **No partial HNSW indexes.** WHERE clauses on HNSW index creation are not supported.
* **Embedding results are cached** per (model, dimensions, text) within a statement to avoid redundant API calls.
* **Distance operators require matching dimensions.** Comparing vectors of different sizes raises an error.
## Next Steps
[Section titled “Next Steps”](#next-steps)
* [CHUNK\_TEXT](/docs/extensions/chunk-text/) — Split documents into overlapping chunks for RAG
* [RAG with Built-in Embeddings](/docs/guides/rag-with-built-in-embeddings/) — Tutorial: build a complete RAG pipeline
* [Compatibility Matrix](/docs/platform/compatibility-matrix/) — Full PostgreSQL compatibility surface
* [Extensions Overview](/docs/extensions/) — All 9 built-in extensions
* [Limits and Quotas](/docs/platform/limits-and-quotas/) — All operational limits
* [SQL Reference: Functions](/docs/sql/functions/) — Built-in function reference
# Serverless Functions
> Deploy and run JavaScript/TypeScript functions alongside your database — with native SQL access, filesystem scope, secrets, outbound HTTP, and cron scheduling.
DB9 Functions let you deploy JavaScript or TypeScript code that runs **inside your database environment**. Unlike traditional serverless platforms where you connect to a database over the network, DB9 functions have direct, zero-latency access to your SQL engine and filesystem through the `ctx` object.
index.js
```js
module.exports = {
handler: async (input, ctx) => {
// Query your database — no connection strings, no drivers
const stats = await ctx.db.query(`
SELECT count(*) as tables FROM information_schema.tables
WHERE table_schema = 'public'
`);
// Write a report to the filesystem — in the same function
const report = [
`DB9 Function Report — ${new Date().toISOString()}`,
`Tables in public schema: ${stats.rows[0][0]}`,
`Input received: ${JSON.stringify(input)}`,
`Run ID: ${ctx.self.runId}`,
].join("\n");
await ctx.fs9.write("/reports/status.txt", report);
return {
tables: Number(stats.rows[0][0]),
reportSaved: true,
runId: ctx.self.runId
};
}
};
```
▶ Run
**What makes DB9 Functions different:**
* **Native SQL access** — `ctx.db.query()` runs queries directly, no connection strings or drivers
* **Filesystem built in** — `ctx.fs9` reads and writes files in the same function that queries your database
* **No infrastructure** — deploy with one CLI command, no Dockerfiles or build pipelines
* **Secrets management** — bind API keys and tokens without hardcoding them
## Quick Start
[Section titled “Quick Start”](#quick-start)
### 1. Write a function
[Section titled “1. Write a function”](#1-write-a-function)
hello.js
```js
module.exports = {
handler: async (input, ctx) => {
const greeting = `Hello, ${input?.name || "world"}!`;
// Query the database to show native SQL access
const time = await ctx.db.query("SELECT NOW()::text as server_time");
return {
greeting,
serverTime: time.rows[0][0],
runId: ctx.self.runId
};
}
};
```
▶ Run
### 2. Deploy
[Section titled “2. Deploy”](#2-deploy)
Terminal
```bash
db9 functions create hello --db myapp -f hello.js
```
### 3. Invoke
[Section titled “3. Invoke”](#3-invoke)
Terminal
```bash
db9 functions invoke hello --db myapp --payload '{"name":"Alice"}' --json
```
JSON
```json
{
"status": "succeeded",
"result_json": "{\"greeting\":\"Hello, Alice!\",\"serverTime\":\"2026-04-01 17:41:31.458000\",\"runId\":\"5e0be3ff-...\"}"
}
```
Note
The entrypoint must be a named export called `handler`. The runtime uses CommonJS — use `module.exports = { handler: ... }`, not `export default`.
## How Functions Work
[Section titled “How Functions Work”](#how-functions-work)
### Architecture
[Section titled “Architecture”](#architecture)
When you deploy a function, the CLI uploads your code to the DB9 API. Each deploy creates a new **version**. When you invoke a function, the runtime:
1. Loads the active version of your function code
2. Creates an isolated execution context with the `ctx` object
3. Calls your `handler(input, ctx)` with the parsed JSON payload
4. Captures the return value, console output, and execution metadata
5. Stores the result as a **run record** accessible via `history` and `logs`
### Runtime
[Section titled “Runtime”](#runtime)
Functions execute in a sandboxed JavaScript runtime. Each invocation is isolated — there is no shared memory or global state between runs. The runtime provides:
* **CommonJS module system** — `module.exports`, `require` (built-ins only)
* **Global `fetch()`** — outbound HTTP (requires allowlist configuration)
* **Console API** — `console.log()`, `console.error()` captured as logs
* **Standard globals** — `Date`, `JSON`, `Math`, `Promise`, `setTimeout`, `Buffer`, `TextEncoder`, `TextDecoder`
### Execution Role
[Section titled “Execution Role”](#execution-role)
Functions execute SQL as the `authenticated` role, not `admin`. This means:
* Functions **cannot** access tables unless the `authenticated` role has been granted privileges
* Functions **cannot** use extensions that require superuser (like fs9 from SQL)
* Functions **can** use `ctx.fs9` — filesystem access is handled through the runtime API, not SQL
Before deploying functions that read or write tables, grant access from the `admin` role:
SQL
```sql
-- Grant access to all current tables
GRANT ALL ON ALL TABLES IN SCHEMA public TO authenticated;
-- Required for functions that call nextval()/currval() directly (see note below)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO authenticated;
-- Grant access to future tables created by admin
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO authenticated;
```
▶ Run
Sequences behave differently from tables
`ALTER DEFAULT PRIVILEGES ... ON SEQUENCES` is accepted, but it only applies to sequences you create explicitly with `CREATE SEQUENCE`. Sequences created implicitly by a `SERIAL` / `BIGSERIAL` column do not inherit default privileges.
**Sequence privileges are enforced.** A function that calls `nextval()`, `currval()`, or `setval()` directly needs a grant on that sequence, or it fails with `permission denied for sequence` (`42501`). `setval()` additionally requires `UPDATE`.
Inserting into a `SERIAL` / `BIGSERIAL` column is the exception — the column default resolves without a sequence grant, so functions that only insert need the table grant alone.
Tip
If your function creates its own tables, those tables are owned by `authenticated` and accessible by default. The grant is only needed for tables created by the `admin` role (e.g., through `db9 db sql` or migrations).
## Writing Functions
[Section titled “Writing Functions”](#writing-functions)
### JavaScript
[Section titled “JavaScript”](#javascript)
index.js
```js
module.exports = {
handler: async (input, ctx) => {
const result = await doWork(input);
return { status: "ok", result };
}
};
```
### TypeScript
[Section titled “TypeScript”](#typescript)
index.ts
```ts
module.exports = {
handler: async (input: Record, ctx: any) => {
const value: number = 42;
return { result: value * 2, type: typeof value };
}
};
```
The CLI auto-detects `.ts` files and transpiles them before deploying. Type annotations are stripped — there is no type checking at deploy time.
To pipe TypeScript from stdin:
Terminal
```bash
cat handler.ts | db9 functions create my-func --db myapp --ts
```
### Function Signature
[Section titled “Function Signature”](#function-signature)
```plaintext
handler(input: object | null, ctx: Context) => Promise