Drizzle ORM Patterns

# Drizzle ORM Patterns for Google Antigravity

Build type-safe database applications with Drizzle ORM using Google Antigravity's Gemini 3 engine. This guide covers schema definition, migrations, queries, and performance optimization patterns.

## Schema Definition

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

export const orderStatusEnum = pgEnum('order_status', [
  'pending',
  'processing',
  'shipped',
  'delivered',
  'cancelled',
]);

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  name: varchar('name', { length: 100 }).notNull(),
  passwordHash: text('password_hash').notNull(),
  role: varchar('role', { length: 20 }).default('user').notNull(),
  emailVerified: boolean('email_verified').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  deletedAt: timestamp('deleted_at'),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
  roleIdx: index('users_role_idx').on(table.role),
}));

export const products = pgTable('products', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 200 }).notNull(),
  description: text('description'),
  price: decimal('price', { precision: 10, scale: 2 }).notNull(),
  stock: integer('stock').default(0).notNull(),
  categoryId: uuid('category_id').references(() => categories.id),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  categoryIdx: index('products_category_idx').on(table.categoryId),
  priceIdx: index('products_price_idx').on(table.price),
}));

export const categories = pgTable('categories', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  slug: varchar('slug', { length: 100 }).notNull().unique(),
  parentId: uuid('parent_id'),
});

export const orders = pgTable('orders', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  status: orderStatusEnum('status').default('pending').notNull(),
  totalAmount: decimal('total_amount', { precision: 10, scale: 2 }).notNull(),
  shippingAddress: text('shipping_address').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  userIdx: index('orders_user_idx').on(table.userId),
  statusIdx: index('orders_status_idx').on(table.status),
}));

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

export const ordersRelations = relations(orders, ({ one, many }) => ({
  user: one(users, {
    fields: [orders.userId],
    references: [users.id],
  }),
  items: many(orderItems),
}));
```

## Database Client Setup

```typescript
// db/index.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

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

// Type exports
export type DB = typeof db;
export type User = typeof schema.users.$inferSelect;
export type NewUser = typeof schema.users.$inferInsert;
export type Product = typeof schema.products.$inferSelect;
export type Order = typeof schema.orders.$inferSelect;
```

## Query Patterns

```typescript
// services/product.service.ts
import { db } from '@/db';
import { products, categories, orderItems } from '@/db/schema';
import { eq, and, gte, lte, ilike, desc, sql, inArray } from 'drizzle-orm';

interface ProductFilters {
  categoryId?: string;
  minPrice?: number;
  maxPrice?: number;
  search?: string;
  page?: number;
  limit?: number;
}

export async function getProducts(filters: ProductFilters) {
  const { page = 1, limit = 20, ...where } = filters;
  const offset = (page - 1) * limit;

  const conditions = [];

  if (where.categoryId) {
    conditions.push(eq(products.categoryId, where.categoryId));
  }
  if (where.minPrice) {
    conditions.push(gte(products.price, where.minPrice.toString()));
  }
  if (where.maxPrice) {
    conditions.push(lte(products.price, where.maxPrice.toString()));
  }
  if (where.search) {
    conditions.push(ilike(products.name, `%${where.search}%`));
  }

  const [items, countResult] = await Promise.all([
    db
      .select({
        id: products.id,
        name: products.name,
        price: products.price,
        stock: products.stock,
        category: {
          id: categories.id,
          name: categories.name,
        },
      })
      .from(products)
      .leftJoin(categories, eq(products.categoryId, categories.id))
      .where(and(...conditions))
      .orderBy(desc(products.createdAt))
      .limit(limit)
      .offset(offset),

    db
      .select({ count: sql<number>`count(*)` })
      .from(products)
      .where(and(...conditions)),
  ]);

  return {
    items,
    total: countResult[0].count,
    page,
    limit,
    pages: Math.ceil(countResult[0].count / limit),
  };
}

export async function getTopSellingProducts(limit = 10) {
  return db
    .select({
      product: products,
      totalSold: sql<number>`sum(${orderItems.quantity})`.as('total_sold'),
    })
    .from(products)
    .innerJoin(orderItems, eq(products.id, orderItems.productId))
    .groupBy(products.id)
    .orderBy(desc(sql`sum(${orderItems.quantity})`))
    .limit(limit);
}
```

## Transaction Pattern

```typescript
// services/order.service.ts
import { db } from '@/db';
import { orders, orderItems, products } from '@/db/schema';
import { eq, sql } from 'drizzle-orm';

export async function createOrder(
  userId: string,
  items: Array<{ productId: string; quantity: number }>,
  shippingAddress: string
) {
  return db.transaction(async (tx) => {
    // Fetch products and lock rows
    const productIds = items.map(i => i.productId);
    const productList = await tx
      .select()
      .from(products)
      .where(sql`${products.id} = ANY(${productIds}) FOR UPDATE`);

    const productMap = new Map(productList.map(p => [p.id, p]));

    // Validate stock and calculate total
    let totalAmount = 0;
    for (const item of items) {
      const product = productMap.get(item.productId);
      if (!product || product.stock < item.quantity) {
        throw new Error(`Insufficient stock for product`);
      }
      totalAmount += Number(product.price) * item.quantity;
    }

    // Create order
    const [order] = await tx
      .insert(orders)
      .values({
        userId,
        totalAmount: totalAmount.toString(),
        shippingAddress,
      })
      .returning();

    // Create order items
    await tx.insert(orderItems).values(
      items.map(item => ({
        orderId: order.id,
        productId: item.productId,
        quantity: item.quantity,
        price: productMap.get(item.productId)!.price,
      }))
    );

    // Update stock
    for (const item of items) {
      await tx
        .update(products)
        .set({ stock: sql`${products.stock} - ${item.quantity}` })
        .where(eq(products.id, item.productId));
    }

    return order;
  });
}
```

## Best Practices

Google Antigravity's Gemini 3 engine recommends these Drizzle patterns: Define schemas with proper indexes for query performance. Use relations for type-safe joins. Leverage prepared statements for repeated queries. Implement transactions for multi-table operations. Use the query builder for complex dynamic queries.