Drizzle ORM Advanced Patterns

# Drizzle ORM Advanced Patterns for Google Antigravity

Master advanced Drizzle ORM patterns in your Google Antigravity IDE projects. This comprehensive guide covers schema design, relations, transactions, and query optimization techniques optimized for Gemini 3 agentic development.

## Schema Definition

Define type-safe database schemas with Drizzle:

```typescript
// db/schema.ts
import {
  pgTable,
  text,
  varchar,
  timestamp,
  integer,
  boolean,
  jsonb,
  uuid,
  index,
  uniqueIndex,
  primaryKey,
} from 'drizzle-orm/pg-core';
import { relations, sql } from 'drizzle-orm';

// Users table
export const users = pgTable(
  'users',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    email: varchar('email', { length: 255 }).notNull().unique(),
    name: varchar('name', { length: 255 }),
    image: text('image'),
    emailVerified: timestamp('email_verified', { mode: 'date' }),
    role: varchar('role', { length: 50 }).default('user').notNull(),
    subscriptionTier: varchar('subscription_tier', { length: 50 }),
    subscriptionStatus: varchar('subscription_status', { length: 50 }),
    metadata: jsonb('metadata').$type<UserMetadata>(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  (table) => ({
    emailIdx: uniqueIndex('users_email_idx').on(table.email),
    roleIdx: index('users_role_idx').on(table.role),
  })
);

// Prompts table
export const prompts = pgTable(
  'prompts',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    slug: varchar('slug', { length: 255 }).notNull().unique(),
    title: varchar('title', { length: 255 }).notNull(),
    description: text('description').notNull(),
    content: text('content').notNull(),
    tags: text('tags').array().notNull().default(sql`ARRAY[]::text[]`),
    authorId: uuid('author_id').references(() => users.id),
    isApproved: boolean('is_approved').default(false).notNull(),
    viewCount: integer('view_count').default(0).notNull(),
    starCount: integer('star_count').default(0).notNull(),
    copyCount: integer('copy_count').default(0).notNull(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
  },
  (table) => ({
    slugIdx: uniqueIndex('prompts_slug_idx').on(table.slug),
    authorIdx: index('prompts_author_idx').on(table.authorId),
    approvedIdx: index('prompts_approved_idx').on(table.isApproved),
    tagsIdx: index('prompts_tags_idx').using('gin', table.tags),
  })
);

// Stars table (many-to-many)
export const stars = pgTable(
  'stars',
  {
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    promptId: uuid('prompt_id')
      .notNull()
      .references(() => prompts.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at').defaultNow().notNull(),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.userId, table.promptId] }),
    userIdx: index('stars_user_idx').on(table.userId),
    promptIdx: index('stars_prompt_idx').on(table.promptId),
  })
);

// Define relations
export const usersRelations = relations(users, ({ many }) => ({
  prompts: many(prompts),
  stars: many(stars),
}));

export const promptsRelations = relations(prompts, ({ one, many }) => ({
  author: one(users, {
    fields: [prompts.authorId],
    references: [users.id],
  }),
  stars: many(stars),
}));

export const starsRelations = relations(stars, ({ one }) => ({
  user: one(users, {
    fields: [stars.userId],
    references: [users.id],
  }),
  prompt: one(prompts, {
    fields: [stars.promptId],
    references: [prompts.id],
  }),
}));

// TypeScript types
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Prompt = typeof prompts.$inferSelect;
export type NewPrompt = typeof prompts.$inferInsert;
```

## Database Connection

Configure database connection with connection pooling:

```typescript
// db/index.ts
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import * as schema from './schema';

const sql = neon(process.env.DATABASE_URL!);

export const db = drizzle(sql, { schema });

// For transactions, use the pool client
import { drizzle as drizzlePool } from 'drizzle-orm/neon-serverless';
import { Pool } from '@neondatabase/serverless';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const dbPool = drizzlePool(pool, { schema });
```

## Advanced Queries

Implement complex queries with type safety:

```typescript
// lib/queries/prompts.ts
import { db } from '@/db';
import { prompts, users, stars } from '@/db/schema';
import { eq, and, desc, sql, ilike, or, arrayContains } from 'drizzle-orm';

export async function getPromptWithAuthor(slug: string) {
  const result = await db.query.prompts.findFirst({
    where: eq(prompts.slug, slug),
    with: {
      author: {
        columns: {
          id: true,
          name: true,
          image: true,
        },
      },
      stars: true,
    },
  });

  return result;
}

export async function searchPrompts({
  query,
  tags,
  limit = 20,
  offset = 0,
}: {
  query?: string;
  tags?: string[];
  limit?: number;
  offset?: number;
}) {
  const conditions = [eq(prompts.isApproved, true)];

  if (query) {
    conditions.push(
      or(
        ilike(prompts.title, `%${query}%`),
        ilike(prompts.description, `%${query}%`)
      )!
    );
  }

  if (tags && tags.length > 0) {
    conditions.push(arrayContains(prompts.tags, tags));
  }

  const results = await db
    .select({
      prompt: prompts,
      author: {
        id: users.id,
        name: users.name,
        image: users.image,
      },
      starCount: sql<number>`count(${stars.userId})::int`,
    })
    .from(prompts)
    .leftJoin(users, eq(prompts.authorId, users.id))
    .leftJoin(stars, eq(prompts.id, stars.promptId))
    .where(and(...conditions))
    .groupBy(prompts.id, users.id)
    .orderBy(desc(prompts.createdAt))
    .limit(limit)
    .offset(offset);

  return results;
}

export async function getTrendingPrompts(days: number = 7) {
  const since = new Date(Date.now() - days * 24 * 60 * 60 * 1000);

  return db
    .select({
      prompt: prompts,
      recentStars: sql<number>`
        count(${stars.userId}) filter (where ${stars.createdAt} > ${since})
      `::int,
    })
    .from(prompts)
    .leftJoin(stars, eq(prompts.id, stars.promptId))
    .where(eq(prompts.isApproved, true))
    .groupBy(prompts.id)
    .orderBy(desc(sql`recent_stars`))
    .limit(10);
}
```

## Transactions

Handle complex operations with transactions:

```typescript
// lib/mutations/prompts.ts
import { dbPool } from '@/db';
import { prompts, stars, notifications } from '@/db/schema';
import { eq, sql } from 'drizzle-orm';

export async function starPrompt(userId: string, promptId: string) {
  return dbPool.transaction(async (tx) => {
    // Check if already starred
    const existing = await tx.query.stars.findFirst({
      where: and(
        eq(stars.userId, userId),
        eq(stars.promptId, promptId)
      ),
    });

    if (existing) {
      // Remove star
      await tx.delete(stars).where(
        and(eq(stars.userId, userId), eq(stars.promptId, promptId))
      );

      await tx
        .update(prompts)
        .set({ starCount: sql`${prompts.starCount} - 1` })
        .where(eq(prompts.id, promptId));

      return { starred: false };
    }

    // Add star
    await tx.insert(stars).values({ userId, promptId });

    const [prompt] = await tx
      .update(prompts)
      .set({ starCount: sql`${prompts.starCount} + 1` })
      .where(eq(prompts.id, promptId))
      .returning();

    // Create notification for author
    if (prompt.authorId && prompt.authorId !== userId) {
      await tx.insert(notifications).values({
        userId: prompt.authorId,
        type: 'star',
        title: 'New star on your prompt',
        message: `Someone starred your prompt "${prompt.title}"`,
        metadata: { promptId, promptSlug: prompt.slug },
      });
    }

    return { starred: true };
  });
}
```

## Best Practices

1. **Use relations** for type-safe eager loading
2. **Create indexes** for frequently queried columns
3. **Use transactions** for multi-step operations
4. **Leverage prepared statements** for repeated queries
5. **Implement soft deletes** for important data
6. **Use database-level constraints** for data integrity
7. **Monitor query performance** with explain analyze