Many-to-Many (M2M) Relationships

This guide covers how to work with many-to-many relationships in @websideproject/nuxt-auto-api.

Many-to-Many (M2M) Relationships

This guide covers how to work with many-to-many relationships in @websideproject/nuxt-auto-api.

Overview

Many-to-many relationships are managed through junction tables. The M2M routes find a junction table by name: for /api/articles/:id/relations/categories they look in your Drizzle schema for an export named after both sides (articleCategories, article_categories, categoryArticles, …) and for the foreign-key columns by name (articleId, article_id, idArticle, …). When your names do not follow that pattern, configure the relation explicitly with m2m.relations — that always wins.

Labels, help text and display fields for the admin UI are configured in the same place.

Quick Start

export default defineNuxtConfig({
  autoApi: {
    m2m: {
      // Optional: labels and display fields for the admin UI
      relations: {
        articles: {
          categories: {
            label: 'Categories',     // Override default label
            help: 'Select article categories',  // Add help text
            displayField: 'name',    // Field shown in dropdown
          }
        }
      }
    }
  }
})

Then use the generated endpoints:

# List categories for article 10
GET /api/articles/10/relations/categories

# Update categories (replace all)
POST /api/articles/10/relations/categories
{ "ids": [7, 8, 9] }

The :relation segment is the related resource's registered name: both articles and categories must be registered resources. The junction table does not have to be one.

How Junction Tables Are Found
Schema Setup
Configuration Options
API Endpoints
Frontend Composables
Permissions
Metadata Columns
Best Practices
Troubleshooting

How Junction Tables Are Found

There are two lookups, and both are name-based — neither reads Drizzle's .references() metadata.

1. The M2M routes

For /api/{resource}/:id/relations/{relation}:

With m2m.relations[resource][relation].junctionTable set, that export of your Drizzle schema is used, with leftKey / rightKey when given.
Otherwise every combination of the two names, singular and plural, camelCase and snake_case, in both orders is tried against the export names of the schema you passed to drizzle(…, { schema }) and the registered tables: articleCategories, articleCategory, article_categories, categoryArticles, categories_articles, …
In that table, the key columns are found by name: articleId, article_id, idArticle (and plural forms). Every other column is a metadata column.

m2m.autoDetect does not affect this lookup.

2. Discovery for the admin UI

GET /api/_m2m/detect/:resource (and /_m2m/junctions, /_m2m/is-junction/:table) tells the admin which relations a resource has. It reports:

every relation configured in m2m.relations for the resource, and
with m2m.autoDetect on (the default), the junctions it finds among the registered resources: a table with no id column, exactly two columns named like foreign keys (…Id, …_id, id…) that match registered resources, and a name made of those two resources.

So a junction table that is not registered is not auto-discovered for the admin — list the relation in m2m.relations instead (no route is exposed for the junction). Registering the junction as a resource also works, but exposes CRUD routes for it; give it a tight authorization if you do.

Drizzle relations

relations() are not needed for the M2M routes. They are needed for ?include= (Drizzle's relational queries), so define them if you also include the junction or its rows.

Schema Setup

Complete Example

// schema.ts
import { sqliteTable, integer, primaryKey, text } from 'drizzle-orm/sqlite-core'

// Resource tables
export const articles = sqliteTable('articles', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
})

export const categories = sqliteTable('categories', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
})

// Junction table: export name `articleCategories`, key columns `articleId` / `categoryId`
export const articleCategories = sqliteTable('article_categories', {
  articleId: integer('article_id')
    .notNull()
    .references(() => articles.id, { onDelete: 'cascade' }),
  categoryId: integer('category_id')
    .notNull()
    .references(() => categories.id, { onDelete: 'cascade' }),
}, (table) => [
  primaryKey({ columns: [table.articleId, table.categoryId] }),
])

Pass the junction in the schema you give drizzle():

initializeDatabase(drizzle(sqlite, { schema }), 'better-sqlite3') // `schema` includes articleCategories

Junction Table Requirements

An export name made of both resource names (or junctionTable configured explicitly)
Key columns named after each resource (or leftKey / rightKey configured explicitly)
A composite primary key (no standalone id) — also required for admin discovery
onDelete: 'cascade' on both foreign keys, so deleting either side cleans up links

Naming Convention Support

Table (export) names

// ✅ all found for articles ↔ categories
export const articleCategories = sqliteTable(…)   // camelCase, plural right side
export const articleCategory = sqliteTable(…)     // camelCase, singular
export const article_categories = sqliteTable(…)  // snake_case export name
export const categoryArticles = sqliteTable(…)    // reversed order

What counts is the export name (the key in your schema object), not the SQL table name passed to sqliteTable().

Key column names

articleId     // camelCase suffix
article_id    // snake_case suffix
idArticle     // prefix pattern
articlesId    // plural forms are tried too

Column names here are the property keys of the Drizzle table (articleId), not the database column names (article_id).

Singular and plural

Names are tried in singular and plural forms (article / articles, category / categories, class / classes, a few irregular plurals). Anything else — people ↔ person, prefixed resource names — needs explicit configuration.

Verification

Check what was detected with the discovery endpoints (they need a signed-in caller; detect/:resource also needs read on the resource):

# List all detected junction tables
GET /api/_m2m/junctions
# Response: { "junctions": ["articleCategories", "articleTags"], "count": 2 }

# Check if specific table is detected as junction
GET /api/_m2m/is-junction/articleCategories
# Response: { "table": "articleCategories", "isJunction": true }

# Detect M2M relationships for a resource
GET /api/_m2m/detect/articles
# Response: { "relationships": [...] }

Manual Override (If Needed)

If auto-detection doesn't work for a specific table, you can still manually configure it:

// nuxt.config.ts
autoAdmin: {
  resources: {
    customJunction: {
      type: 'junction'  // Force hide from sidebar
    },
    orderItems: {
      type: 'resource',  // Show in sidebar despite junction pattern
      displayName: 'Order Items',
    }
  }
}

Configuration Options

Minimal Setup (Recommended)

Let auto-detection handle structure, only customize UI labels:

export default defineNuxtConfig({
  autoApi: {
    m2m: {
      autoDetect: true,  // Default, can be omitted

      // Optional: Override UI labels only
      relations: {
        articles: {
          categories: {
            label: 'Categories',
            help: 'Select categories for this article',
            displayField: 'name',
          }
        }
      }
    }
  }
})

Explicit Structure (Advanced)

Override auto-detection for specific relations:

export default defineNuxtConfig({
  autoApi: {
    m2m: {
      autoDetect: true,

      relations: {
        articles: {
          categories: {
            // Explicit structure (overrides auto-detection)
            junctionTable: 'articleCategories',
            leftKey: 'articleId',
            rightKey: 'categoryId',

            // UI customization
            label: 'Categories',
            help: 'Select categories',
            displayField: 'name',
            metadataColumns: ['sortOrder'],
          }
        }
      }
    }
  }
})

Fully Explicit (No Auto-Detection)

autoDetect: false turns off discovery for the admin UI: only the relations listed here are reported. (The M2M routes still look up a junction by name when a relation is not listed.)

export default defineNuxtConfig({
  autoApi: {
    m2m: {
      autoDetect: false,  // Disable auto-detection

      // Must configure every M2M relation explicitly
      relations: {
        articles: {
          categories: {
            junctionTable: 'articleCategories',
            leftKey: 'articleId',
            rightKey: 'categoryId',
            label: 'Categories',
            displayField: 'name',
          },
          tags: {
            junctionTable: 'articleTags',
            leftKey: 'articleId',
            rightKey: 'tagId',
            label: 'Tags',
            displayField: 'name',
          }
        }
      }
    }
  }
})

Configuration Reference

Property	Type	Default	Description
`autoDetect`	`boolean`	`true`	Discovery (`/api/_m2m/*`, used by the admin) also reports junctions it finds among registered resources
`junctionTable`	`string`	Auto	Junction table name (schema export)
`leftKey`	`string`	Auto	FK column to source resource
`rightKey`	`string`	Auto	FK column to related resource
`label`	`string`	Auto	Display label for admin UI
`help`	`string`	-	Help text for admin UI
`displayField`	`string`	`'name'`	Field shown in dropdown
`metadataColumns`	`string[]`	`[]`	Reported to the admin by discovery. The routes treat every junction column other than the two keys as metadata

When to Use Each Approach

Approach	Use When	Benefits
Auto-detection only	Junctions named after both sides, registered (for admin discovery)	Minimal config
Auto + UI overrides	Need custom labels/help text	Auto structure + custom UX
Explicit structure	Non-standard naming or complex junctions	Full control, predictable behavior

API Endpoints

List Relations

Get all related records:

GET /api/{resource}/{id}/relations/{relation}

Query Parameters:

includeRecords - Include full records (default: false)
includeMetadata - Include metadata columns (default: false)
filter / sort / fields - Applied to the related records (readable columns only)
limit - Page size (defaults to and capped by pagination.maxLimit)
offset - Offset for pagination

Only related rows the caller may see are listed (the related resource's tenant scope, listFilter, soft delete and objectLevel), and filter narrows them further — it can never widen the list beyond the linked rows. records are returned without hidden fields and without fields the caller may not read.

Example:

GET /api/articles/10/relations/categories?includeRecords=true

Response:

{
  "ids": [7, 8, 9],
  "total": 3,
  "meta": { "limit": 100, "offset": 0, "hasMore": false },
  "records": [
    { "id": 7, "name": "Technology" },
    { "id": 8, "name": "Business" },
    { "id": 9, "name": "Science" }
  ]
}

total is the number of ids on this page; meta.hasMore says whether another page follows.

Sync Relations (Replace All)

Replace all relations with new set:

POST /api/{resource}/{id}/relations/{relation}

Body:

{
  "ids": [7, 8, 9],
  "metadata": [...]  // Optional
}

Response:

{
  "success": true,
  "added": 2,
  "removed": 1,
  "total": 3
}

Add Relations

Add without removing existing:

POST /api/{resource}/{id}/relations/{relation}/add

Body:

{
  "ids": [10, 11]
}

Remove Relations

Remove specific relations:

DELETE /api/{resource}/{id}/relations/{relation}/remove

Body:

{
  "ids": [7, 8]
}

Batch Sync

Sync multiple relations atomically:

POST /api/{resource}/{id}/relations/batch

Body:

{
  "relations": {
    "categories": { "ids": [7, 8, 9] },
    "tags": { "ids": [1, 2, 3] }
  }
}

Composables (Frontend)

useM2MRelation

Query related records:

<script setup>
const articleId = ref(10)

const { data, isLoading } = useM2MRelation(
  'articles',
  articleId,
  'categories',
  { includeRecords: true }
)

// data.value = { ids: [...], records: [...], total: N }
</script>

useM2MSync

Replace all relations:

<script setup>
const { mutate: sync } = useM2MSync('articles', articleId, 'categories', {
  onSuccess: () => toast.success('Updated!'),
})

function updateCategories() {
  sync({ ids: [7, 8, 9] })
}
</script>

useM2MAdd

Add relations:

<script setup>
const { mutate: add } = useM2MAdd('articles', articleId, 'categories')

function addMore() {
  add({ ids: [10, 11] })
}
</script>

useM2MRemove

Remove relations:

<script setup>
const { mutate: remove } = useM2MRemove('articles', articleId, 'categories')

function removeCategories() {
  remove({ ids: [7, 8] })
}
</script>

Permissions

Both sides are checked, before any row is looked up:

Side	List	Sync / Add / Remove / Batch
The resource (`articles`)	`read`	`update`
The related resource (`categories`)	`read`	`read` (or `update`, see below)

Then the article itself must be visible to the caller (tenant, listFilter, not trashed) and pass its objectLevel, and every id being linked must be a row of the related resource the caller can see — ids that are missing or invisible are both reported as not found (404), so linking cannot be used to discover rows or to attach another tenant's rows. Unlinking (remove) only touches the article's own junction rows.

Stricter or custom rules live under permissions.m2m on the left resource. Both keys are relation names — the :relation route segment, which is always the related resource's registered name:

export const articlesAuth: ResourceAuthConfig = {
  permissions: {
    read: true,
    update: 'editor',
    m2m: {
      // Require `update` (not just `read`) on the related resource to link it:
      requireUpdateOnRelated: ['categories'], // or requireUpdateToLink: true for every relation
      // Custom per-relation check — receives both sides' rows:
      relations: {
        tags: { check: ({ right, user }) => right.records!.every(t => !t.locked || user?.role === 'admin') },
      },
    },
  },
}

Hooks

beforeM2MSync / beforeM2MAdd receive (relation, ids, ctx) and may return a replacement list of ids; beforeM2MRemove receives (relation, ids, ctx); the after* variants receive (relation, result, ctx). See Lifecycle Hooks.

Metadata Columns

Junction tables can include extra data:

// Schema with metadata
export const articleCategories = sqliteTable('article_categories', {
  articleId: integer('article_id').notNull().references(() => articles.id),
  categoryId: integer('category_id').notNull().references(() => categories.id),
  sortOrder: integer('sort_order').default(0),  // ✅ Metadata
  isPrimary: integer('is_primary', { mode: 'boolean' }),  // ✅ Metadata
}, (table) => ({
  pk: primaryKey({ columns: [table.articleId, table.categoryId] })
}))

// Config
m2m: {
  relations: {
    articles: {
      categories: {
        junctionTable: 'articleCategories',
        leftKey: 'articleId',
        rightKey: 'categoryId',
        metadataColumns: ['sortOrder', 'isPrimary'],  // Declare metadata
      }
    }
  }
}

// Usage
POST /api/articles/10/relations/categories
{
  "ids": [7, 8, 9],
  "metadata": [
    { "sortOrder": 1, "isPrimary": true },
    { "sortOrder": 2, "isPrimary": false },
    { "sortOrder": 3, "isPrimary": false }
  ]
}

Best Practices

1. Define Drizzle Relations (for `?include=`)

The M2M routes don't need them; ?include= does. Define them when you include the linked rows:

// ✅ GOOD - Complete relations
export const articlesRelations = relations(articles, ({ many }) => ({
  articleCategories: many(articleCategories),
}))

export const articleCategoriesRelations = relations(articleCategories, ({ one }) => ({
  article: one(articles, {
    fields: [articleCategories.articleId],
    references: [articles.id],
  }),
  category: one(categories, {
    fields: [articleCategories.categoryId],
    references: [categories.id],
  }),
}))

2. Use Cascade Deletes

articleId: integer('article_id')
  .references(() => articles.id, { onDelete: 'cascade' })  // ✅

3. Use Composite Primary Keys

// ✅ GOOD
primaryKey({ columns: [table.articleId, table.categoryId] })

// ❌ BAD
id: integer('id').primaryKey()  // Allows duplicates

4. Customize UI Labels

Override auto-generated labels for better UX:

relations: {
  articles: {
    categories: {
      label: 'Categories',
      help: 'Select categories for this article',
      displayField: 'name',  // or 'title', 'email', etc.
    }
  }
}

5. Configure Non-Standard Junctions Explicitly

When a junction's export name or key columns don't follow the naming pattern, configure the relation:

m2m: {
  relations: {
    articles: {
      categories: {
        junctionTable: 'articleCategoryLinks', // the schema EXPORT name, not the SQL table name
        leftKey: 'article',                    // property keys of the Drizzle table
        rightKey: 'category',
      }
    }
  }
}

Troubleshooting

Relations Not Found

Problem: M2M endpoints return 404

Solution:

Check config in nuxt.config.ts
Verify junctionTable matches schema export name
Confirm column names match leftKey/rightKey

Junction Table Not Found

Problem: "Junction table not found"

The junction table does not need to be a registered resource (registering it would expose CRUD routes for it). It must be exported from the Drizzle schema you pass to drizzle(), and — for auto-detection — be named after both sides (see Naming Convention Support); otherwise configure it explicitly with m2m.relations.

Auto-Detection Not Finding Relations

Problem: M2M relations not detected automatically

Solution:

For the admin: discovery only looks at registered resources — list the relation in m2m.relations (or register the junction with a tight authorization)
Ensure composite primary key (no standalone id)
Verify the junction's export name and key columns follow the naming pattern (.references() and Drizzle relations are not used for detection)

The discovery endpoints (/api/_m2m/junctions, /api/_m2m/is-junction/:table, /api/_m2m/detect/:resource) show what was detected. They require a signed-in caller, and detect/:resource requires read on that resource.

If auto-detection doesn't work for your schema, use explicit configuration:

m2m: {
  autoDetect: false,
  relations: {
    articles: {
      categories: {
        junctionTable: 'articleCategories',
        leftKey: 'articleId',
        rightKey: 'categoryId',
      }
    }
  }
}

Many-to-Many (M2M) Relationships

Many-to-Many (M2M) Relationships

Need a Landing Page?

Build Your MVP

Deployment Help

Suggest a SaaS Tool

Need a Landing Page?

Build Your MVP

Deployment Help

Suggest a SaaS Tool