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 module provides intelligent auto-detection with optional configuration overrides:

Auto-Detection (RECOMMENDED) - Automatically detects junction tables from Drizzle schema
Configuration Overrides - Customize labels, help text, and display fields

The auto-detection system reads Drizzle's .references() metadata to accurately identify M2M relationships without relying on naming conventions.

Quick Start

M2M relationships work automatically with minimal configuration:

export default defineNuxtConfig({
  autoApi: {
    m2m: {
      // Auto-detection enabled by default
      autoDetect: true,

      // Optional: Customize labels and display fields
      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] }

How Auto-Detection Works
Schema Setup
Configuration Options
API Endpoints
Frontend Composables
Permissions
Metadata Columns
Best Practices
Troubleshooting

How Auto-Detection Works

The module automatically detects M2M relationships by analyzing your Drizzle schema using a two-phase approach:

Phase 1: Drizzle FK References (Primary)

The system reads .references() calls in your junction tables to extract the exact target resources:

export const articleCategories = sqliteTable('article_categories', {
  articleId: integer('article_id')
    .references(() => articles.id, { onDelete: 'cascade' }),  // ← Reads this
  categoryId: integer('category_id')
    .references(() => categories.id, { onDelete: 'cascade' }), // ← And this
}, (table) => ({
  pk: primaryKey({ columns: [table.articleId, table.categoryId] })
}))

The detection extracts:

articles from the first reference
categories from the second reference
Automatically creates articles ↔ categories M2M relationship

Phase 2: Heuristic Fallback

If FK references aren't available, the system falls back to column name pattern matching:

Columns ending with Id (camelCase): articleId, categoryId
Columns ending with _id (snake_case): article_id, category_id
Columns starting with id: idArticle, idCategory

Detection Criteria

A table is recognized as a junction table if:

✅ Has exactly 2 foreign key columns
✅ Has NO standalone id column (uses composite primary key)
✅ Table name matches resource pair pattern (e.g., articleCategories)

Drizzle Relations (Required)

For accurate detection, define Drizzle relations in your schema:

import { relations } from 'drizzle-orm'

export const articlesRelations = relations(articles, ({ many }) => ({
  articleCategories: many(articleCategories),
  articleTags: many(articleTags),
}))

export const categoriesRelations = relations(categories, ({ 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],
  }),
}))

These relations enable:

Accurate FK target extraction
Drizzle's relational query API
Type-safe queries with include= parameter

Schema Setup

Complete Example

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

// 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 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) => ({
  pk: primaryKey({ columns: [table.articleId, table.categoryId] })
}))

// Relations (required for auto-detection)
export const articlesRelations = relations(articles, ({ many }) => ({
  articleCategories: many(articleCategories),
}))

export const categoriesRelations = relations(categories, ({ 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],
  }),
}))

Junction Table Requirements

✅ Required for auto-detection:

Exactly 2 foreign key columns with .references()
Composite primary key (no standalone id)
Cascade deletes for cleanup

✅ Supported naming conventions:

camelCase: articleCategories, articleId
snake_case: article_categories, article_id
Mixed: Any combination

Naming Convention Support

The M2M auto-detection system supports both camelCase and snake_case naming conventions, as well as mixed conventions and plural forms.

Supported Table Name Patterns

All of these table names will be auto-detected correctly:

// ✅ camelCase
export const articleCategories = sqliteTable('articleCategories', { ... })

// ✅ snake_case
export const articleCategories = sqliteTable('article_categories', { ... })

// ✅ Plural forms
export const articleCategories = sqliteTable('articlesCategories', { ... })
export const articleCategories = sqliteTable('articles_categories', { ... })

// ✅ Mixed conventions
export const articleCategories = sqliteTable('article_categories', {
  articleId: integer('articleId'),  // camelCase column
  categoryId: integer('category_id'),  // snake_case column
})

Supported Column Name Patterns

Foreign key columns are extracted using these patterns:

// ✅ Suffix patterns (most common)
articleId → article       // camelCase suffix
article_id → article      // snake_case suffix

// ✅ Prefix patterns
idArticle → article       // prefix pattern

// ✅ Variations
articlesId → articles     // plural handling
articles_id → articles    // snake_case plural

Resource Name Matching

After extracting the resource name from a column (e.g., article from article_id), the system tries to match it against registered resources:

// For extracted name "article", tries:
[
  'article',      // exact match
  'articles',     // add 's'
  'articl',       // remove last char
  // ... more variations
]

// Matches 'articles' in registry ✅

Example: snake_case Schema

This schema works automatically without any configuration:

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

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

Result: Auto-detects as junction table linking articles ↔ categories ✅

Verification

Test auto-detection with these debug endpoints:

# 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)

Disable auto-detection completely:

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`	Enable auto-detection
`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[]`	`[]`	Additional junction columns

When to Use Each Approach

Approach	Use When	Benefits
Auto-detection only	Standard schema with Drizzle relations	Minimal config, automatic updates
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)
limit - Limit results
offset - Offset for pagination

Example:

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

Response:

{
  "ids": [7, 8, 9],
  "records": [
    { "id": 7, "name": "Technology" },
    { "id": 8, "name": "Business" },
    { "id": 9, "name": "Science" }
  ],
  "total": 3
}

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

Control M2M operations via resource permissions:

// modules/blog/auth.ts
export const articlesAuth = {
  async canUpdate(user, context) {
    // Controls regular updates AND M2M operations
    return user?.role === 'admin'
  },

  // Optional: Specific M2M permission
  async canUpdateM2M(user, context) {
    // Falls back to canUpdate if not provided
    return user?.role === 'editor' || user?.role === 'admin'
  }
}

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

Always define relations for both resource tables and junction tables:

// ✅ 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. Disable Auto-Detection for Complex Schemas

If your schema has non-standard junction tables, disable auto-detection:

m2m: {
  autoDetect: false,  // Disable auto-detection
  relations: {
    // Explicitly configure every M2M relation
    articles: {
      categories: {
        junctionTable: 'article_category_links',  // Non-standard name
        leftKey: 'article_id',
        rightKey: 'category_id',
      }
    }
  }
}

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 in Schema

Problem: "Junction table not found in schema"

Solution: Register the junction table:

// modules/blog/index.ts
nuxt.hook('autoApi:registerSchema', (registry) => {
  registry.register('articleCategories', {
    schema: createModuleImport('./schema', 'articleCategories')
  })
})

Auto-Detection Not Finding Relations

Problem: M2M relations not detected automatically

Solution:

Verify .references() is defined on FK columns
Check Drizzle relations are exported
Ensure composite primary key (no standalone id)
Verify junction table follows naming pattern

Debug endpoint:

GET /api/_m2m/debug-detection

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