FTS5 API Reference

FTS5 API Endpoints

Documentation for FTS5 full-text search API endpoints.

Base Path

All FTS5 endpoints are prefixed with /api/fts5/:databaseId

Endpoints Overview

Method	Endpoint	Description
GET	/api/fts5/:dbId/list	List all FTS5 tables
POST	/api/fts5/:dbId/create	Create new FTS5 table
POST	/api/fts5/:dbId/create-from-table	Convert existing table to FTS5
GET	/api/fts5/:dbId/:tableName/config	Get FTS5 table configuration
DELETE	/api/fts5/:dbId/:tableName	Delete FTS5 table
POST	/api/fts5/:dbId/:tableName/rebuild	Rebuild FTS5 index
POST	/api/fts5/:dbId/:tableName/optimize	Optimize FTS5 index
POST	/api/fts5/:dbId/:tableName/search	Search FTS5 table
GET	/api/fts5/:dbId/:tableName/stats	Get FTS5 statistics

---

List FTS5 Tables

Get all FTS5 virtual tables in a database.

Endpoint: GET /api/fts5/:dbId/list

Parameters:

• dbId (path): Database ID

Response:

{
  "result": [
    {
      "name": "articles_fts",
      "type": "fts5",
      "columns": ["title", "content", "author"],
      "tokenizer": {
        "type": "porter",
        "parameters": {
          "remove_diacritics": 1
        }
      },
      "contentTable": null,
      "rowCount": 1250,
      "indexSize": 524288,
      "prefixIndex": {
        "enabled": true,
        "lengths": [2, 3]
      }
    }
  ],
  "success": true
}

---

Create FTS5 Table

Create a new FTS5 virtual table from scratch.

Endpoint: POST /api/fts5/:dbId/create

Parameters:

• dbId (path): Database ID

Request Body:

{
  "tableName": "articles_fts",
  "columns": ["title", "content", "author"],
  "tokenizer": {
    "type": "porter",
    "parameters": {
      "remove_diacritics": 1
    }
  },
  "prefixIndex": {
    "enabled": true,
    "lengths": [2, 3, 4]
  },
  "unindexed": ["author"]
}

Fields:

• tableName (string, required): Name for the FTS5 table
• columns (string[], required): Columns to index
• tokenizer (object, required): Tokenizer configuration
  • type (string): unicode61, porter, trigram, or ascii
  • parameters (object, optional): Tokenizer-specific parameters
• prefixIndex (object, optional): Prefix index configuration
  • enabled (boolean)
  • lengths (number[]): Prefix lengths to index
• unindexed (string[], optional): Columns to store but not index
• contentTable (string, optional): External content table name
• contentRowId (string, optional): ROWID column in content table

Response:

{
  "result": {
    "tableName": "articles_fts",
    "created": true
  },
  "success": true
}

---

Create FTS5 from Existing Table

Convert an existing table to FTS5 with optional triggers.

Endpoint: POST /api/fts5/:dbId/create-from-table

Parameters:

• dbId (path): Database ID

Request Body:

{
  "sourceTable": "articles",
  "ftsTableName": "articles_fts",
  "columns": ["title", "content"],
  "tokenizer": {
    "type": "unicode61",
    "parameters": {
      "remove_diacritics": 1
    }
  },
  "prefixIndex": {
    "enabled": true,
    "lengths": [2, 3]
  },
  "externalContent": true,
  "createTriggers": true
}

Fields:

• sourceTable (string, required): Source table name
• ftsTableName (string, required): Name for FTS5 table
• columns (string[], required): Columns to index
• tokenizer (object, required): Tokenizer configuration
• prefixIndex (object, optional): Prefix index config
• externalContent (boolean, optional): Use external content mode (default: false)
• createTriggers (boolean, optional): Create sync triggers (default: false)

Response:

{
  "result": {
    "ftsTableName": "articles_fts",
    "created": true,
    "triggersCreated": true
  },
  "success": true
}

---

Get FTS5 Configuration

Retrieve the configuration of an FTS5 table.

Endpoint: GET /api/fts5/:dbId/:tableName/config

Parameters:

• dbId (path): Database ID
• tableName (path): FTS5 table name

Response:

{
  "result": {
    "tableName": "articles_fts",
    "columns": ["title", "content", "author"],
    "tokenizer": {
      "type": "porter",
      "parameters": {
        "remove_diacritics": 1
      }
    },
    "prefixIndex": {
      "enabled": true,
      "lengths": [2, 3]
    },
    "contentTable": "articles",
    "contentRowId": "rowid",
    "unindexed": ["author"]
  },
  "success": true
}

---

Delete FTS5 Table

Drop an FTS5 virtual table.

Endpoint: DELETE /api/fts5/:dbId/:tableName

Parameters:

• dbId (path): Database ID
• tableName (path): FTS5 table name

Response:

{
  "success": true
}

Note: Deleting an FTS5 table with external content does not affect the source table.

---

Rebuild FTS5 Index

Completely rebuild the FTS5 index.

Endpoint: POST /api/fts5/:dbId/:tableName/rebuild

Parameters:

• dbId (path): Database ID
• tableName (path): FTS5 table name

Response:

{
  "result": {
    "rebuilt": true
  },
  "success": true
}

Use Case: Run after major bulk updates or data imports.

---

Optimize FTS5 Index

Optimize the FTS5 index to reduce fragmentation.

Endpoint: POST /api/fts5/:dbId/:tableName/optimize

Parameters:

• dbId (path): Database ID
• tableName (path): FTS5 table name

Response:

{
  "result": {
    "optimized": true
  },
  "success": true
}

Use Case: Run periodically to maintain query performance.

---

Search FTS5 Table

Perform a full-text search with ranking and highlighting.

Endpoint: POST /api/fts5/:dbId/:tableName/search

Parameters:

• dbId (path): Database ID
• tableName (path): FTS5 table name

Request Body:

{
  "query": "sqlite database",
  "columns": ["title", "content"],
  "limit": 50,
  "offset": 0,
  "rankingFunction": "bm25",
  "bm25_k1": 1.2,
  "bm25_b": 0.75,
  "includeSnippet": true,
  "snippetOptions": {
    "startMark": "<mark>",
    "endMark": "</mark>",
    "ellipsis": "...",
    "tokenCount": 32
  }
}

Fields:

• query (string, required): FTS5 MATCH query
• columns (string[], optional): Search in specific columns only
• limit (number, optional): Max results (default: 50)
• offset (number, optional): Pagination offset (default: 0)
• rankingFunction (string, optional): bm25 or bm25custom (default: bm25)
• bm25_k1 (number, optional): Term frequency saturation (default: 1.2)
• bm25_b (number, optional): Length normalization (default: 0.75)
• includeSnippet (boolean, optional): Include highlighted snippets (default: false)
• snippetOptions (object, optional): Snippet configuration

Response:

{
  "result": {
    "results": [
      {
        "row": {
          "rowid": 1,
          "title": "Getting Started with SQLite",
          "content": "SQLite is a powerful database...",
          "author": "John Doe"
        },
        "rank": -1.234,
        "snippet": "Getting Started with <mark>SQLite</mark>... <mark>SQLite</mark> is a powerful <mark>database</mark>..."
      }
    ],
    "total": 42,
    "executionTime": 12.5,
    "meta": {
      "rowsScanned": 1250,
      "tokenizerUsed": "porter"
    }
  },
  "success": true
}

Response Fields:

• results (array): Search result objects
  • row (object): Full row data
  • rank (number): BM25 relevance score (lower/negative is better)
  • snippet (string, optional): Highlighted snippet
• total (number): Total matching rows
• executionTime (number): Query time in milliseconds
• meta (object): Query metadata

Search Query Operators:

• AND, OR, NOT: Boolean operators
• "exact phrase": Phrase matching
• prefix*: Prefix wildcard (requires prefix index)
• {column}: term: Column-specific search
• NEAR(term1 term2, N): Proximity search

Examples:

// Simple search
{"query": "sqlite"}

// Boolean search
{"query": "sqlite AND database NOT mysql"}

// Phrase search
{"query": "\"full text search\""}

// Column-specific
{"query": "{title}: tutorial"}

// Prefix search
{"query": "datab*"}

// Combined
{"query": "(sqlite OR postgres) AND tutorial"}

---

Get FTS5 Statistics

Get statistics about an FTS5 table.

Endpoint: GET /api/fts5/:dbId/:tableName/stats

Parameters:

• dbId (path): Database ID
• tableName (path): FTS5 table name

Response:

{
  "result": {
    "tableName": "articles_fts",
    "rowCount": 1250,
    "indexSize": 524288,
    "averageRowSize": 419.4,
    "fragmentation": 15,
    "lastOptimize": "2025-11-04T12:00:00Z"
  },
  "success": true
}

Response Fields:

• tableName (string): FTS5 table name
• rowCount (number): Number of indexed rows
• indexSize (number): Index size in bytes
• averageRowSize (number): Average row size in bytes
• fragmentation (number, optional): Fragmentation percentage (0-100)
• lastOptimize (string, optional): Last optimize timestamp

---

Error Codes

HTTP Status	Error	Description
400	Invalid tokenizer config	Tokenizer type or parameters invalid
400	Table is not FTS5	Attempted FTS5 operation on regular table
404	Table not found	FTS5 table doesn't exist
500	Query failed	FTS5 query execution error

---

Examples

Create FTS5 Table with Unicode61

curl -X POST https://d1.yourdomain.com/api/fts5/abc123/create \
  -H "Content-Type: application/json" \
  -d '{
    "tableName": "products_fts",
    "columns": ["name", "description"],
    "tokenizer": {
      "type": "unicode61",
      "parameters": {
        "remove_diacritics": 1
      }
    }
  }'

Search with Highlighting

curl -X POST https://d1.yourdomain.com/api/fts5/abc123/products_fts/search \
  -H "Content-Type: application/json" \
  -d '{
    "query": "laptop computer",
    "limit": 10,
    "includeSnippet": true,
    "snippetOptions": {
      "tokenCount": 40
    }
  }'

Convert Existing Table

curl -X POST https://d1.yourdomain.com/api/fts5/abc123/create-from-table \
  -H "Content-Type: application/json" \
  -d '{
    "sourceTable": "articles",
    "ftsTableName": "articles_fts",
    "columns": ["title", "body"],
    "tokenizer": {"type": "porter"},
    "externalContent": true,
    "createTriggers": true
  }'

---

See Also

• FTS5 Full-Text Search Guide
• Main API Reference
• SQLite FTS5 Documentation