Drizzle ORM Complete Guide

# Drizzle ORM Complete Guide for Google Antigravity

Master type-safe database operations with Drizzle ORM using Google Antigravity IDE.

## Schema Definition

```typescript
// db/schema.ts
import { pgTable, uuid, text, timestamp, boolean, integer, pgEnum, jsonb } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";

export const userRoleEnum = pgEnum("user_role", ["admin", "user", "moderator"]);

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  name: text("name").notNull(),
  role: userRoleEnum("role").default("user").notNull(),
  avatarUrl: text("avatar_url"),
  metadata: jsonb("metadata").$type<{ preferences: Record<string, unknown> }>(),
  emailVerified: boolean("email_verified").default(false),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull()
});

export const posts = pgTable("posts", {
  id: uuid("id").primaryKey().defaultRandom(),
  authorId: uuid("author_id").references(() => users.id, { onDelete: "cascade" }).notNull(),
  title: text("title").notNull(),
  content: text("content").notNull(),
  published: boolean("published").default(false),
  viewCount: integer("view_count").default(0),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull()
});

// Relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts)
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id]
  })
}));
```

## Query Patterns

```typescript
// db/queries.ts
import { db } from "./client";
import { users, posts } from "./schema";
import { eq, and, or, like, desc, asc, sql, count, avg, inArray } from "drizzle-orm";

// Basic CRUD operations
export async function createUser(data: typeof users.$inferInsert) {
  const [user] = await db.insert(users).values(data).returning();
  return user;
}

export async function getUserById(id: string) {
  return db.query.users.findFirst({
    where: eq(users.id, id),
    with: {
      posts: {
        where: eq(posts.published, true),
        orderBy: desc(posts.createdAt),
        limit: 5
      }
    }
  });
}

export async function updateUser(id: string, data: Partial<typeof users.$inferInsert>) {
  const [updated] = await db
    .update(users)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(users.id, id))
    .returning();
  return updated;
}

// Complex queries
export async function searchPosts(query: string, page = 1, limit = 10) {
  const offset = (page - 1) * limit;
  
  const results = await db
    .select({
      post: posts,
      author: {
        id: users.id,
        name: users.name
      },
      commentCount: count()
    })
    .from(posts)
    .leftJoin(users, eq(posts.authorId, users.id))
    .where(
      and(
        eq(posts.published, true),
        or(
          like(posts.title, `%${query}%`),
          like(posts.content, `%${query}%`)
        )
      )
    )
    .groupBy(posts.id, users.id)
    .orderBy(desc(posts.createdAt))
    .limit(limit)
    .offset(offset);

  return results;
}

// Transactions
export async function createPostWithTags(
  postData: typeof posts.$inferInsert,
  tagIds: string[]
) {
  return db.transaction(async (tx) => {
    const [post] = await tx.insert(posts).values(postData).returning();
    return post;
  });
}
```

## Best Practices

1. **Define relations** for type-safe joins
2. **Use transactions** for multi-table operations
3. **Leverage query builder** for complex queries
4. **Use $inferInsert and $inferSelect** for types
5. **Implement soft deletes** with deletedAt column
6. **Add indexes** for frequently queried columns
7. **Use prepared statements** for repeated queries

Google Antigravity provides intelligent Drizzle ORM suggestions and query optimization.