packages/db/
├── src/
│   ├── schema/              # Table definitions
│   │   ├── index.ts         # Exports all tables
│   │   └── [name].table.ts  # Individual tables
│   └── index.ts             # DB client export
├── drizzle/                 # Generated migrations
└── drizzle.config.ts        # Configuration

pnpm db:generate   # Generate migrations from schema changes
pnpm db:migrate    # Apply migrations to database
pnpm db:push       # Push schema directly (dev only)
pnpm db:studio     # Open Drizzle Studio GUI

// packages/db/src/schema/channel.table.ts
import {
  pgTable,
  text,
  timestamp,
  uuid,
  integer,
  boolean,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { userTable } from "./user.table";

export const channelTable = pgTable(
  "channel",
  {
    // Primary key
    id: uuid("id").primaryKey().defaultRandom(),

    // Foreign key
    userId: uuid("user_id")
      .references(() => userTable.id, { onDelete: "cascade" })
      .notNull(),

    // Text fields
    youtubeId: text("youtube_id").notNull().unique(),
    name: text("name").notNull(),
    description: text("description"),

    // Numbers
    subscriberCount: integer("subscriber_count").default(0),
    videoCount: integer("video_count").default(0),

    // Booleans
    isActive: boolean("is_active").default(true),

    // Timestamps
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => [
    // Indexes
    t.uniqueIndex("channel_youtube_id_idx").on(table.youtubeId),
  ]
);

// Relations
export const channelRelations = relations(channelTable, ({ one, many }) => ({
  user: one(userTable, {
    fields: [channelTable.userId],
    references: [userTable.id],
  }),
  videos: many(videoTable),
}));

// Type exports
export type ChannelRow = typeof channelTable.$inferSelect;
export type ChannelInsert = typeof channelTable.$inferInsert;

import {
  text, // Unlimited text
  varchar, // varchar("col", { length: 255 })
  integer, // Integer
  bigint, // Big integer
  boolean, // Boolean
  timestamp, // Datetime with timezone
  date, // Date only
  uuid, // UUID
  jsonb, // JSON binary
  pgEnum, // Custom enum
  real, // Float
  doublePrecision, // Double
} from "drizzle-orm/pg-core";

import { db } from "@repo/db";
import { channelTable } from "@repo/db/schema";
import { eq, and, or, desc, asc, like, gt, lt, isNull } from "drizzle-orm";

// Find one
const channel = await db.query.channelTable.findFirst({
  where: eq(channelTable.id, id),
});

// Find many with conditions
const channels = await db.query.channelTable.findMany({
  where: and(eq(channelTable.userId, userId), eq(channelTable.isActive, true)),
  orderBy: desc(channelTable.createdAt),
  limit: 20,
});

// With relations
const channelWithVideos = await db.query.channelTable.findFirst({
  where: eq(channelTable.id, id),
  with: {
    videos: {
      limit: 10,
      orderBy: desc(videoTable.viewCount),
    },
  },
});

// Raw SQL when needed
const results = await db
  .select({
    name: channelTable.name,
    totalViews: sql<number>`sum(${videoTable.viewCount})`,
  })
  .from(channelTable)
  .leftJoin(videoTable, eq(channelTable.id, videoTable.channelId))
  .groupBy(channelTable.id);

// Single insert
const [channel] = await db
  .insert(channelTable)
  .values({
    youtubeId: "UC123",
    name: "My Channel",
    userId: userId,
  })
  .returning();

// Bulk insert
const channels = await db
  .insert(channelTable)
  .values([
    { youtubeId: "UC123", name: "Channel 1", userId },
    { youtubeId: "UC456", name: "Channel 2", userId },
  ])
  .returning();

// Upsert (insert or update on conflict)
await db
  .insert(channelTable)
  .values({ youtubeId: "UC123", name: "Updated", userId })
  .onConflictDoUpdate({
    target: channelTable.youtubeId,
    set: { name: "Updated" },
  });

// Update with conditions
await db
  .update(channelTable)
  .set({
    name: "New Name",
    updatedAt: new Date(),
  })
  .where(and(eq(channelTable.id, id), eq(channelTable.userId, userId)));

// Delete with conditions
await db
  .delete(channelTable)
  .where(and(eq(channelTable.id, id), eq(channelTable.userId, userId)));

import { pgEnum } from "drizzle-orm/pg-core";

// Define enum
export const videoStatusEnum = pgEnum("video_status", [
  "pending",
  "processing",
  "ready",
  "failed",
]);

// Use in table
status: videoStatusEnum("status").default("pending"),

// Define typed JSON
metadata: (jsonb("metadata").$type<{
  thumbnail: string;
  duration: number;
  tags: string[];
}>(),
  // Query JSON
  await db.query.videoTable.findMany({
    where: sql`${videoTable.metadata}->>'duration' > 600`,
  }));