Indexes

By default, Dyrected stores each document as a single JSON blob. That's flexible — you can query and sort on any field without declaring it first — but reading a value out of JSON is slower than reading a real column, and it adds up on large collections. When you have a field you filter or sort on constantly, you want the database to treat it as a proper column. In Dyrected that's called promotion, and it's the main tool this page is about.

If you're coming from other tools, note there is no index: true field property in Dyrected. The knob you reach for is promoted: true.

Promote the fields you query

On the SQL adapters (Postgres, SQLite, MySQL), set promoted: true on a field to lift it out of the JSON blob and into its own typed column:

import { defineCollection, defineTextField, defineSelectField } from '@dyrected/core'

export const Posts = defineCollection({
  slug: 'posts',
  fields: [
    defineTextField({ name: 'title', label: 'Title', required: true }),
    defineTextField({
      name: 'slug',
      label: 'Slug',
      unique: true,
      promoted: true,
    }),
    defineSelectField({ name: 'status', label: 'Status', options: ['draft', 'published'] }),
  ],
})

On the next startup, Dyrected adds a real slug column to the collection_posts table and starts writing every document's slug there as well. From then on, filtering and sorting by slug reads the column directly instead of extracting it from JSON.

Reach for promotion on fields that are:

• used often in where filters or sort
• keys you look documents up by, like a slug or an external ID
• fields you sort listings by, like a publishedAt date

You don't need to promote everything. Fields you rarely filter on are perfectly fine left in the JSON body, and promoting them just adds columns you don't benefit from.

Creating database indexes

In addition to promotion, Dyrected allows you to declare single-field and composite indexes directly on your collection definition using the indexes array:

import { defineCollection, defineTextField, defineSelectField } from '@dyrected/core'

export const Accounts = defineCollection({
  slug: 'accounts',
  indexes: [
    {
      name: 'uq_accounts_user_currency',
      fields: ['userId', 'currency'],
      unique: true,
    },
    {
      name: 'idx_accounts_status_created',
      fields: ['status', 'createdAt'],
    },
  ],
  fields: [
    defineTextField({ name: 'userId', required: true }),
    defineSelectField({ name: 'currency', options: ['NGN', 'USD', 'EUR'], required: true }),
    defineSelectField({ name: 'status', options: ['active', 'frozen'], defaultValue: 'active' }),
  ],
})

When you declare an index:

1. Every field listed in fields is automatically promoted to a physical column during startup schema sync.
2. In MySQL, string columns with indexes or unique constraints are automatically typed as VARCHAR(191) (rather than unbounded text) to support standard B-Tree indexing. Promoted select, radio and email fields, and single-value relationship fields, also get bounded VARCHAR columns, and money fields get BIGINT.
3. If unique: true is specified, the database creates a true compound unique constraint.

Enforcing uniqueness

Mark a field with unique: true when its values must never repeat across a collection — such as an email, slug, account number, or external reference:

defineTextField({
  name: 'slug',
  label: 'Slug',
  unique: true,
  promoted: true,
})

When unique: true is set:

• Dyrected's database adapters create a physical database unique constraint or unique index (named uniq_<collection>_<field>).
• If an insert or update attempts to write a duplicate value, the database blocks it and throws a normalized DuplicateKeyError (HTTP 409 Conflict) containing the conflicting { field, value }.
• The admin panel displays a "Unique" badge next to the field to inform editors.

MongoDB

MongoDB stores fields natively without JSON extraction. When you declare indexes or unique: true, the MongoDB adapter automatically creates native collection indexes (with unique: true and sparse: true for unique fields). See MongoDB for more details.

What's already fast

Every collection table has an id primary key, so looking a document up by id is fast without any configuration. Documents also carry created_at and updated_at as real columns, so sorting a listing by newest is efficient out of the box.

For the broader performance picture — pagination, query shape, and depth — see Performance and the query APIs.