NestJS Table Creation Workflow

Relational Table (Foreign Keys & Arrays)

Example: pet.table.ts

import { pgTable, serial, varchar, integer, timestamp, pgEnum, index } from 'drizzle-orm/pg-core';
import { breed } from './breed.table';
import { customer } from './customer.table';

export const pet = pgTable(
  'pet',
  {
    id: serial('id').primaryKey(),
    // ... standard timestamp columns ...
    name: varchar('name', { length: 100 }).notNull(),
    photos: text('photos').array(), // Array type

    // Foreign Keys
    breedId: integer('breed_id')
      .notNull()
      .references(() => breed.id),
    customerId: integer('customer_id')
      .notNull()
      .references(() => customer.id, { onDelete: 'cascade' }),
  },
  (t) => [
    // Index Foreign Keys for performance
    index('pet_customer_id_idx').on(t.customerId),
  ],
);

2. Create Repository (src/repositories/)

The repository handles CRUD and complex queries like pagination.

Example: user.repository.ts

import { Injectable } from '@nestjs/common';
import { eq, ilike, or, and, isNull, count, desc, sql } from 'drizzle-orm';
import { database } from '@db/connection.db';
import { user, UserDTO, userRoleEnum } from '@db/tables/user.table';

@Injectable()
export class UserRepository {
  // Parsing Helper for Enums
  async findAllPaginated(page: number = 1, limit: number = 10, filters?: { search?: string; role?: string; isActive?: string }) {
    const offset = (page - 1) * limit;
    const conditions = [isNull(user.deletedAt)]; // Always exclude deleted

    if (filters?.search) {
      const term = filters.search.trim();
      conditions.push(or(ilike(user.fullName, `%${term}%`), ilike(user.email, `%${term}%`)));
    }

    if (filters?.role) {
      // Cast enum to text for comparison if needed
      conditions.push(eq(sql`${user.role}::text`, filters.role));
    }

    const whereClause = and(...conditions);

    const [{ total }] = await database.select({ total: count() }).from(user).where(whereClause);
    const data = await database.select().from(user).where(whereClause).orderBy(desc(user.createdAt)).limit(limit).offset(offset);

    return { data, total: Number(total) };
  }

  async create(data: UserDTO) {
    const result = await database.insert(user).values(data).returning();
    return result[0];
  }

  // Implement update, delete (soft), findOne...
}

3. Create Seeder (src/seeds/)

Seeds must return the inserted data if other tables depend on them.

Example: user.seed.ts

import { database } from '@db/connection.db';
import { user } from '@db/tables/user.table';
import * as bcrypt from 'bcrypt';

export async function seedUsers() {
  console.log('🌱 seeding users...');
  const password = await bcrypt.hash('123456', 10);

  // .returning() is CRITICAL for relations
  const inserted = await database
    .insert(user)
    .values([
      {
        email: '[email protected]',
        password,
        fullName: 'Admin User',
        role: 'admin',
      },
    ])
    .returning();

  console.log(`✅ ${inserted.length} users inserted.`);
  return inserted; // Return data for dependent seeds
}

4. Register

A. Add to Connection (src/db/connection.db.ts)

Add the table to the schema object so Drizzle knows about it.

const schema = {
  user,
  pet,
  // ...
};

B. Add to Main Seeder (src/db/seed.db.ts)

Run the seed function in the correct order. Pass data if needed.

import { seedUsers } from '@seeds/user.seed';
import { seedPets } from '@seeds/pet.seed';

async function seed() {
  // 1. Run independent seeds
  const users = await seedUsers();
  const customers = await seedCustomers();
  const breeds = await seedBreeds();

  // 2. Run dependent seeds (passing IDs/Objects)
  await seedPets(customers, breeds);
}