Cloudflare D1 Full-Text Search: Real Benchmark on 100K Rows

TL;DR: SQLite's FTS5 extension is available in D1, providing full-text search capabilities without external search services. This guide covers FTS5 virtual table creation, search queries with ranking, highlighting results, and integration with server functions.

Introduction

Search is a core feature for most applications. While you could use Algolia or Meilisearch, D1's FTS5 extension provides capable full-text search without additional infrastructure or cost. For most SaaS applications with millions of documents, FTS5 is sufficient. To optimize your D1 queries alongside FTS5, see our Cloudflare D1 Query Optimization guide.

FTS5 Virtual Table

-- Create FTS5 index on blog posts
CREATE VIRTUAL TABLE posts_fts USING fts5(
  title,
  content,
  excerpt,
  content='posts',
  content_rowid='id',
  tokenize='porter unicode61'
);

-- Populate the index
INSERT INTO posts_fts(rowid, title, content, excerpt)
SELECT id, title, content, excerpt FROM posts;

Search Query

-- Full-text search with ranking
SELECT p.id, p.title, p.excerpt,
  rank AS relevance
FROM posts_fts
JOIN posts p ON posts_fts.rowid = p.id
WHERE posts_fts MATCH ?
ORDER BY rank DESC
LIMIT 20;

Server Function Integration

export const searchPostsFn = createServerFn({ method: 'GET' })
  .validator(z.object({ query: z.string().min(1) }))
  .handler(async ({ data }) => {
    const results = await db
      .selectFrom('posts_fts')
      .innerJoin('posts', 'posts_fts.rowid', 'posts.id')
      .where('posts_fts', 'match', data.query)
      .orderBy('rank', 'desc')
      .limit(20)
      .execute()
    return { results }
  })

FTS5 Features

Conclusion

FTS5 in D1 provides production-quality full-text search without external services. For most SaaS applications, it's a cost-effective alternative to dedicated search platforms. For a deeper understanding of D1 in production, check out our Cloudflare D1 Production Guide. And for building a full search service on D1, see Build a Search-as-a-Service with D1.