Skip to main content

Indexes

Indexes speed up document lookups by maintaining a sorted data structure on a field. jcell supports both in-memory indexes (Cache strategy) and native SQL indexes (Delegate strategy).

Schema-Level Indexes​

The simplest way to create an index is in your schema definition:

const userSchema = schema({
  email: t.string().index({ unique: true }),   // unique index
  name: t.string().index(),                     // simple index
  role: t.enum(['admin', 'user'] as const),
})

// Indexes are automatically created when the collection is first used
const db = createDB({ adapter: sqliteAdapter() })
const users = db.collection('users', userSchema)

Programmatic Indexes​

You can also create and drop indexes at runtime:

// Create index
await collection.createIndex('email', { unique: true })
await collection.createIndex('name')

// Drop index
await collection.dropIndex('email')

Adapter Support​

FeatureFileMemorySQLiteD1
Schema-level indexes✅ (in-memory)✅ (in-memory)✅ (SQL)✅ (SQL)
Programmatic create✅✅✅✅
{ unique: true }✅✅✅✅
Drop index✅✅✅✅

Cache Strategy (File, Memory)​

In-memory indexes use Map<value, Set<id>> for O(1) lookups:

const userSchema = schema({
  email: t.string().index({ unique: true }),
})

// Lookup via index
const user = await users.first({ email: 'alice@example.com' })
// Uses the in-memory index for fast lookup

Delegate Strategy (SQLite, D1)​

SQL-level indexes generate real CREATE INDEX statements:

CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON "users" ("email")
CREATE INDEX IF NOT EXISTS idx_users_name ON "users" ("name")

Important Notes​

  • File/Memory adapters: Indexes are rebuilt from in-memory data on startup. They speed up first() and find() operations.
  • SQLite/D1 adapters: Indexes are persisted in the database. They speed up all query operations through native SQL indexing.
  • Unique indexes enforce uniqueness at the index level. Inserting a duplicate value throws DuplicateError.

Best Practices​

  1. Index fields you query by — especially fields used in where() and first() filters
  2. Use unique indexes for emails, usernames, slugs — enforce uniqueness at the database level
  3. Don't over-index — each index adds write overhead (the index must be updated on every insert/update)
  4. Schema-level vs programmatic — use schema-level indexes for permanent indexes, programmatic for temporary ones