Drizzle-Seed Junction Table Workaround: Avoiding Duplicate Key Violations

README.md

Temporary Drizzle-Seed Junction Table Workaround

This is a temporary solution for seeding databases with Drizzle ORM (drizzle-seed) when junction tables cause duplicate key violations. It seeds main tables first using seed, then manually seeds junction tables with fetched IDs to ensure uniqueness.

Usage

1. Replace schema and db imports with your own schema and database connection.
2. Update mainTables with your main entity tables (e.g., users, products).
3. Add your junction tables in the seeding section (e.g., userProducts).
4. Run with your preferred runtime (e.g., bun run seed.ts).

Example Schema

// schema.ts
import { pgTable, uuid, text, primaryKey } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
});

export const categories = pgTable('categories', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
});

export const userCategories = pgTable('user_categories', {
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  categoryId: uuid('category_id').notNull().references(() => categories.id),
}, (table) => [primaryKey({ columns: [table.userId, table.categoryId] })]);

seedDatabase.ts

import { reset, seed } from 'drizzle-seed';
import { sql } from 'drizzle-orm';
import { NodePgDatabase } from 'drizzle-orm/node-postgres';
import { performance } from 'perf_hooks';
import * as schema from './schema'; // Replace with your schema file
import { db } from './db'; // Replace with your DB connection

// Define schema type
type AppSchema = typeof schema;

const typedDb = db as NodePgDatabase<AppSchema>;

// Result type for seeding
type SeedingResult = {
  success: boolean;
  skipped?: boolean;
  time?: number;
  error?: string;
};

const MAX_RETRIES = 3;
const RETRY_DELAY_MS = 1000;

// Simple schema validation
function validateSchema(schema: AppSchema): void {
  const requiredTables = ['users'] as const; // Adjust as needed
  const missingTables = requiredTables.filter((table) => !(table in schema));
  if (missingTables.length > 0) {
    throw new Error(`Missing required tables: ${missingTables.join(', ')}`);
  }
}

/**
 * Seeds the database by first generating main tables with drizzle-seed,
 * then manually seeding junction tables to avoid duplicate key violations.
 */
export async function seedDatabase(): Promise<SeedingResult> {
  const startTime = performance.now();
  const log = (message: string) => console.log(message);

  try {
    log('🔍 Validating schema...');
    validateSchema(schema);
    log('✅ Schema validated');

    log('🔌 Testing DB connection...');
    await typedDb.execute(sql`SELECT 1`);
    log('✅ DB connection OK');

    log('🔎 Checking for existing data...');
    const hasData = await checkDatabaseHasData(typedDb);
    if (hasData) {
      log('⏩ Data exists - skipping seeding');
      return { success: true, skipped: true, time: performance.now() - startTime };
    }
    log('ℹ️ No data found - proceeding with seeding');

    for (let attempt = 0; attempt < MAX_RETRIES; attempt++) {
      try {
        log(`🚀 Seeding database (attempt ${attempt + 1}/${MAX_RETRIES})...`);
        await typedDb.transaction(async (tx) => {
          // Step 1: Reset the database
          log('🧹 Resetting database...');
          await reset(tx, schema);

          // Step 2: Seed main tables
          log('🌱 Seeding main tables...');
          const mainTables = {
            users: schema.users, // Example main table
            categories: schema.categories, // Example main table
            // Add your main tables here
          };
          await seed(tx, mainTables);

          // Step 3: Fetch generated IDs
          log('🔍 Fetching data for junction tables...');
          const users = await tx.select().from(schema.users).limit(3);
          const categories = await tx.select().from(schema.categories).limit(3);

          if (users.length === 0 || categories.length === 0) {
            throw new Error('Insufficient data for junction tables');
          }

          // Step 4: Seed junction tables
          log('🌿 Seeding junction tables...');

          // Example junction table: userCategories
          const userCategoriesData = [
            { userId: users[0].id, categoryId: categories[0].id },
            { userId: users[0].id, categoryId: categories[1].id },
            { userId: users[1].id, categoryId: categories[0].id },
          ];
          await tx.insert(schema.userCategories).values(userCategoriesData).onConflictDoNothing();
          log('✅ Seeded userCategories');
        });

        const totalTime = performance.now() - startTime;
        log(`🎉 Seeding completed in ${Math.round(totalTime)}ms`);
        return { success: true, time: totalTime };
      } catch (error) {
        const errorMessage = error instanceof Error ? error.message : String(error);
        log(`⚠️ Failed on attempt ${attempt + 1}: ${errorMessage}`);
        if (attempt === MAX_RETRIES - 1) {
          log('❌ All attempts failed');
          return { success: false, error: errorMessage, time: performance.now() - startTime };
        }
        log(`⏳ Retrying in ${RETRY_DELAY_MS}ms...`);
        await new Promise((resolve) => setTimeout(resolve, RETRY_DELAY_MS));
      }
    }

    // Fallback (shouldn’t reach here)
    throw new Error('Unexpected exit from retry loop');
  } catch (error) {
    const errorMessage = error instanceof Error ? error.message : String(error);
    log(`❌ Seeding setup failed: ${errorMessage}`);
    return { success: false, error: errorMessage, time: performance.now() - startTime };
  }
}

// Check if database already has data
async function checkDatabaseHasData(db: NodePgDatabase<AppSchema>): Promise<boolean> {
  const tables = ['users'] as const; // Adjust as needed
  for (const table of tables) {
    try {
      const result = await db.execute<{ exists: boolean }>(sql`
        SELECT EXISTS(SELECT 1 FROM ${sql.identifier(table)}) as exists
      `);
      if (result.rows[0]?.exists) {
        console.log(`ℹ️ Found data in '${table}'`);
        return true;
      }
    } catch (error) {
      console.warn(`⚠️ Error checking '${table}': ${error instanceof Error ? error.message : String(error)}`);
    }
  }
  return false;
}

// Run the seeder
seedDatabase().then((result) => console.log('Seeding Result:', result));