Database Options | nextjs-cms

Control a section's table through the db option. Identifier, ordering, primary key, unique constraints, and indexes.

The db option describes the section's table. Only table is required.

db: {
    table: 'posts',          // required
    identifier: sku,         // defaults to an auto-increment `id`
    orderByField: position,  // admin list order (defaults to created_at)
    primaryKey: [sku],       // defaults to [identifier]
    unique: [{ columns: [slug] }],
    index: [{ columns: [publishedAt] }],
    fulltext: [{ columns: [title, excerpt] }],
}

Good to know:

Simple sections only accept db.table. Everything below applies to has-items and category sections. After changing any db option, run update-sections.

Reference

Option	Type	Default	Description
`table`	`string`	required	Table name. Must not end with `_locales`.
`identifier`	text or number field	auto `id`	The column that identifies an item: used in URLs, relationships, gallery and translation rows.
`orderByField`	field	`created_at`	Column the admin list sorts by, descending. Category children are sorted by it too (falling back to the identifier).
`primaryKey`	`FieldConfig[]`	`[identifier]`	Primary key columns.
`unique`	`{ columns: FieldConfig[]; name?: string }[]`	`[]`	Unique constraints. Checked by the CMS before saving, with a readable error.
`index`	`{ columns: FieldConfig[]; name?: string }[]`	none	Regular indexes.
`fulltext`	`{ columns: FieldConfig[]; name?: string }[]`	none	MySQL `FULLTEXT` indexes.
`foreignKeys`	`{ columns; foreignColumns; name }[]`	none	Reserved. Accepted, but not created by `update-sections` yet.

`identifier`

When you don't set one, the section gets this field, prepended to your fields:

numberField({ name: 'id', label: 'ID', required: true, hasAutoIncrement: true })

A text identifier

Use a natural key when items have one, like a SKU, an ISO country code, or a username:

const code = textField({ name: 'code', label: 'Country code', required: true, maxLength: 2 })

export default hasItemsSection({
    name: 'countries',
    // ...
    headingField: name,
    db: { table: 'countries', identifier: code },
    fields: [code, name],
})

Must be a textField or numberField, and must be in fields.
It's added to the unique constraints automatically.
It is read-only on the edit form: it can't change once the item exists.
It can't be localized.
Anything that references items (select values, destination tables, gallery rows, {table}_locales.parent_id) uses its type, so a text identifier makes those VARCHAR columns.

A number identifier without auto-increment

const legacyId = numberField({ name: 'legacy_id', required: true })

db: { table: 'imported_records', identifier: legacyId }

Editors type the id themselves. Useful when mirroring records from another system.

`orderByField`

const position = numberField({ name: 'position', defaultValue: 0 })

db: { table: 'slides', orderByField: position }

The admin list shows the highest value first. Common choices:

Goal	`orderByField`
Newest first (default)	omit it (`created_at`)
By publish date	a `dateField`
Manual order	a `numberField` editors set
Alphabetical	not supported (sort is always descending); use search instead

`unique`

const slug = slugField({ name: 'slug', for: title })
const email = textField({ name: 'email' })
const tenant = textField({ name: 'tenant_id' })

db: {
    table: 'pages',
    unique: [
        { columns: [slug] },                              // slug unique on its own
        { columns: [tenant, email], name: 'tenant_email' }, // unique together
    ],
}

Before every create and update, the CMS queries for an existing row with the same values (excluding the item being edited) and rejects the save with a message naming the field. The same constraint is also created in MySQL as a safety net.

For localized fields, a unique constraint whose columns are all localized is enforced per locale in the {table}_locales table. Two languages can share a slug, but two English posts can't.

Indexes

db: {
    table: 'products',
    index: [
        { columns: [categoryId] },
        { columns: [status, publishedAt], name: 'status_published' },
    ],
    fulltext: [{ columns: [name, description], name: 'product_search' }],
}

Add an index for columns your website filters or sorts on (WHERE category_id = ?, ORDER BY published_at).
A fulltext index enables MATCH(...) AGAINST(...) queries on your website. The admin's search box uses LIKE, not full-text search.
name is optional. Without it, MySQL picks one.

`primaryKey`

The primary key defaults to the identifier. Override it only for unusual tables, for example a composite key:

db: {
    table: 'translations_memory',
    identifier: key,
    primaryKey: [key, language],
}