Database Migrations for Antigravity

# Database Migrations for Google Antigravity

Proper database migration management is essential for maintaining data integrity across environments. This guide covers migration strategies optimized for Google Antigravity development workflows.

## Prisma Migration Setup

Configure Prisma for robust migration management:

```typescript
// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

model User {
  id            String    @id @default(cuid())
  email         String    @unique
  name          String?
  emailVerified DateTime?
  image         String?
  role          Role      @default(USER)
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  posts         Post[]
  accounts      Account[]
  sessions      Session[]

  @@index([email])
  @@map("users")
}

model Post {
  id          String   @id @default(cuid())
  title       String   @db.VarChar(255)
  slug        String   @unique
  content     String   @db.Text
  published   Boolean  @default(false)
  authorId    String
  author      User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  categories  Category[]
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([authorId])
  @@index([slug])
  @@map("posts")
}

enum Role {
  USER
  ADMIN
  MODERATOR
}
```

## Migration Commands and Scripts

Create migration management scripts:

```typescript
// scripts/migrate.ts
import { execSync } from "child_process";

type MigrationAction = "create" | "deploy" | "reset" | "status";

async function runMigration(action: MigrationAction, name?: string) {
  const commands: Record<MigrationAction, string> = {
    create: `prisma migrate dev --name ${name || "migration"}`,
    deploy: "prisma migrate deploy",
    reset: "prisma migrate reset --force",
    status: "prisma migrate status",
  };

  console.log(`Running migration: ${action}`);
  
  try {
    execSync(commands[action], { stdio: "inherit" });
    console.log(`Migration ${action} completed successfully`);
  } catch (error) {
    console.error(`Migration ${action} failed:`, error);
    process.exit(1);
  }
}

// Run from command line
const action = process.argv[2] as MigrationAction;
const name = process.argv[3];
runMigration(action, name);
```

## Drizzle Migration Alternative

For Drizzle ORM users:

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

export const roleEnum = pgEnum("role", ["user", "admin", "moderator"]);

export const users = pgTable("users", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  email: text("email").unique().notNull(),
  name: text("name"),
  emailVerified: timestamp("email_verified"),
  image: text("image"),
  role: roleEnum("role").default("user"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const posts = pgTable("posts", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: varchar("title", { length: 255 }).notNull(),
  slug: text("slug").unique().notNull(),
  content: text("content"),
  published: boolean("published").default(false),
  authorId: text("author_id").references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

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

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

// drizzle/migrate.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Pool } from "pg";

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

  const db = drizzle(pool);

  console.log("Running migrations...");
  await migrate(db, { migrationsFolder: "./drizzle/migrations" });
  console.log("Migrations completed");

  await pool.end();
}

runMigrations().catch(console.error);
```

## Database Seeding

Create comprehensive seed scripts:

```typescript
// prisma/seed.ts
import { PrismaClient, Role } from "@prisma/client";
import { hash } from "bcryptjs";

const prisma = new PrismaClient();

async function main() {
  console.log("Starting seed...");

  // Create admin user
  const adminPassword = await hash("admin123", 12);
  const admin = await prisma.user.upsert({
    where: { email: "admin@example.com" },
    update: {},
    create: {
      email: "admin@example.com",
      name: "Admin User",
      role: Role.ADMIN,
    },
  });

  // Create sample posts
  const posts = await Promise.all(
    Array.from({ length: 10 }, (_, i) =>
      prisma.post.upsert({
        where: { slug: `sample-post-${i + 1}` },
        update: {},
        create: {
          title: `Sample Post ${i + 1}`,
          slug: `sample-post-${i + 1}`,
          content: `Content for sample post ${i + 1}`,
          published: i < 5,
          authorId: admin.id,
        },
      })
    )
  );

  console.log(`Created ${posts.length} posts`);
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());
```

## Best Practices

When managing migrations in Antigravity projects, always review generated SQL before applying, use transactions for complex migrations, create backup scripts for production, test migrations in staging first, version control all migration files, document breaking changes, and implement rollback procedures for critical migrations.