Drizzle ORM Migration Patterns

# Drizzle ORM Migration Patterns for Google Antigravity IDE

Manage database schemas with Drizzle ORM's type-safe migrations using Google Antigravity IDE. This comprehensive guide covers schema design, migration strategies, seeding, and multi-database support for production applications.

## Schema Definition

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

// Enums for type safety
export const userRoleEnum = pgEnum("user_role", ["admin", "user", "guest"]);
export const subscriptionStatusEnum = pgEnum("subscription_status", [
  "active", "canceled", "past_due", "trialing"
]);

// Users table
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: varchar("email", { length: 255 }).notNull(),
  name: varchar("name", { length: 100 }).notNull(),
  passwordHash: text("password_hash"),
  role: userRoleEnum("role").default("user").notNull(),
  emailVerified: boolean("email_verified").default(false).notNull(),
  avatarUrl: text("avatar_url"),
  stripeCustomerId: varchar("stripe_customer_id", { length: 255 }),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex("users_email_idx").on(table.email),
  stripeIdx: index("users_stripe_idx").on(table.stripeCustomerId),
  roleIdx: index("users_role_idx").on(table.role),
}));

// Subscriptions table
export const subscriptions = pgTable("subscriptions", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: uuid("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  stripeSubscriptionId: varchar("stripe_subscription_id", { length: 255 }).notNull(),
  stripePriceId: varchar("stripe_price_id", { length: 255 }).notNull(),
  status: subscriptionStatusEnum("status").notNull(),
  currentPeriodStart: timestamp("current_period_start", { withTimezone: true }).notNull(),
  currentPeriodEnd: timestamp("current_period_end", { withTimezone: true }).notNull(),
  cancelAtPeriodEnd: boolean("cancel_at_period_end").default(false).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  userIdx: index("subscriptions_user_idx").on(table.userId),
  stripeSubIdx: uniqueIndex("subscriptions_stripe_idx").on(table.stripeSubscriptionId),
}));

// Define relations
export const usersRelations = relations(users, ({ many, one }) => ({
  subscriptions: many(subscriptions),
  posts: many(posts),
  profile: one(profiles),
}));

export const subscriptionsRelations = relations(subscriptions, ({ one }) => ({
  user: one(users, {
    fields: [subscriptions.userId],
    references: [users.id],
  }),
}));
```

## Migration Configuration

```typescript
// drizzle.config.ts
import type { Config } from "drizzle-kit";
import * as dotenv from "dotenv";

dotenv.config({ path: ".env.local" });

export default {
  schema: "./src/db/schema/*",
  out: "./drizzle/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
  // Migration table configuration
  migrations: {
    table: "__drizzle_migrations",
    schema: "public",
  },
} satisfies Config;
```

## Migration Commands and Seeding

```typescript
// scripts/seed.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "../src/db/schema";
import { users, posts, categories } from "../src/db/schema";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

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

async function seed() {
  console.log("Seeding database...");

  // Clear existing data in correct order
  await db.delete(posts);
  await db.delete(users);
  await db.delete(categories);

  // Insert categories
  const [tech, lifestyle] = await db.insert(categories).values([
    { name: "Technology", slug: "technology" },
    { name: "Lifestyle", slug: "lifestyle" },
  ]).returning();

  // Insert users
  const [admin, user] = await db.insert(users).values([
    {
      email: "admin@example.com",
      name: "Admin User",
      role: "admin",
      emailVerified: true,
    },
    {
      email: "user@example.com",
      name: "Regular User",
      role: "user",
      emailVerified: true,
    },
  ]).returning();

  // Insert posts with relations
  await db.insert(posts).values([
    {
      title: "Getting Started with Drizzle",
      slug: "getting-started-drizzle",
      content: "Drizzle ORM is a TypeScript ORM...",
      authorId: admin.id,
      categoryId: tech.id,
      published: true,
    },
  ]);

  console.log("Seeding complete!");
}

seed()
  .catch(console.error)
  .finally(() => pool.end());
```

## Best Practices for Google Antigravity IDE

When using Drizzle with Google Antigravity, define schemas in separate files by domain. Use enums for type-safe status fields. Add appropriate indexes for query performance. Implement soft deletes with deletedAt columns. Use transactions for multi-table operations. Let Gemini 3 generate migrations from schema changes automatically.

Google Antigravity's agent mode can analyze your queries and suggest optimal indexes for your schema.