Relationships | nextjs-cms

Link sections to each other. One-to-many with a select, many-to-many with a join table, and category trees.

nextjs-cms has no special "relation" field. Relationships are built from selectField and selectMultipleField pointed at another section with section, optionally stored in a join table with destinationDb.

Relationship	Example	Field	Storage
Many-to-one	Each post has one author	`selectField({ section })`	Column on the post (`author_id`)
Many-to-many	Posts have many tags, tags have many posts	`selectMultipleField({ section, destinationDb })`	Join table (`post_tags`)
Many-to-one, hierarchical	A product in `Electronics › Phones`	`selectField({ section: categories, destinationDb })`	Join table, one row per level
Free-form many-to-many	Keywords editors invent	`tagsField({ destinationDb })`	Join table of strings

Many-to-one

sections/authors.section.ts

const name = textField({ name: 'name', required: true })

export default hasItemsSection({
    name: 'authors',
    order: 5,
    title: { section: 'Authors', singular: 'Author', plural: 'Authors' },
    headingField: name, // ← becomes the option label elsewhere
    db: { table: 'authors' },
    fields: [name, photoField({ name: 'avatar' })],
})

sections/posts.section.ts

import authorsSection from './authors.section'

const author = selectField({
    name: 'author_id',
    label: 'Author',
    section: authorsSection,
    required: true,
    checkValueExists: true,
})

posts.author_id is an INT holding authors.id.
The dropdown lists authors by name (their headingField).
checkValueExists rejects ids that don't exist (tampered or deleted in the meantime).

Query with a join on your website:

import { eq } from 'drizzle-orm'

const rows = await db
    .select({ post: PostsTable, author: AuthorsTable })
    .from(PostsTable)
    .leftJoin(AuthorsTable, eq(AuthorsTable.id, PostsTable.authorId))

Warning:

There are no database foreign keys: deleting an author leaves author_id pointing at nothing. Block it with a beforeDelete hook, or treat missing authors as "unknown" in your website.

Many-to-many

sections/posts.section.ts

import tagsSection from './tags.section'

const tags = selectMultipleField({
    name: 'tags',
    section: tagsSection,
    destinationDb: {
        table: 'post_tags',
        itemIdentifier: 'post_id',
        selectIdentifier: 'tag_id',
    },
})

update-sections creates post_tags (post_id, tag_id) with a composite primary key. Each save rewrites the post's rows.

// Tags of one post
const postTags = await db
    .select({ tag: TagsTable })
    .from(PostTagsTable)
    .innerJoin(TagsTable, eq(TagsTable.id, PostTagsTable.tagId))
    .where(eq(PostTagsTable.postId, postId))

// Posts with a tag
const tagged = await db
    .select({ post: PostsTable })
    .from(PostTagsTable)
    .innerJoin(PostsTable, eq(PostsTable.id, PostTagsTable.postId))
    .where(eq(PostTagsTable.tagId, tagId))

Without a join table

Leave out destinationDb and the ids are stored comma-separated in a VARCHAR(255) column ("3,8,12"). It's quicker to set up, but can't be joined or indexed, and long selections overflow. Use it only for small, display-only lists.

Hierarchical categories

A select pointed at a category section with depth > 1 renders one dropdown per level. Store the path with destinationDb:

const category = selectField({
    name: 'category',
    section: categoriesSection, // depth: 3
    required: true,
    destinationDb: { table: 'product_categories', itemIdentifier: 'product_id', selectIdentifier: 'category_id' },
})

A product in Electronics (1) › Phones (2) › Android (3) gets three rows. "Everything in Electronics" is then WHERE category_id = 1, no recursion needed.

Free-form tags

When editors should create values on the fly instead of picking from a managed list:

tagsField({
    name: 'keywords',
    destinationDb: { table: 'post_keywords', itemIdentifier: 'post_id', selectIdentifier: 'keyword' },
    autoCompletion: true,
})

Lookup tables outside the CMS

Point a select at any table with db when the list isn't content editors manage (countries, currencies, time zones):

selectField({
    name: 'currency',
    db: { table: 'currencies', identifier: 'code', label: 'name', orderBy: 'name' },
    defaultValue: 'USD',
})

update-sections creates currencies (code, name) if missing; you fill it once with SQL.

Choosing identifiers

Everything that references an item (select columns, join tables, gallery and translation rows) uses the target section's identifier type. With the default auto-increment id, references are INT. If the target section uses a text identifier (a SKU, a code), references are VARCHAR. Decide early: changing an identifier's type later means migrating every referencing column.