Drizzle Database Layer Patterns

# Drizzle Database Layer Patterns for Google Antigravity

Drizzle ORM provides a lightweight, type-safe approach to database access with SQL-like syntax. Google Antigravity's Gemini 3 helps you build efficient database layers with intelligent schema suggestions and query optimization.

## Schema Definition

Define your database schema with full TypeScript support:

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

export const roleEnum = pgEnum("role", ["user", "admin", "moderator"]);
export const statusEnum = pgEnum("status", ["draft", "published", "archived"]);

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 100 }).notNull(),
  role: roleEnum("role").default("user").notNull(),
  avatarUrl: text("avatar_url"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

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

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

export const postCategories = pgTable("post_categories", {
  postId: uuid("post_id").references(() => posts.id, { onDelete: "cascade" }).notNull(),
  categoryId: uuid("category_id").references(() => categories.id, { onDelete: "cascade" }).notNull(),
});

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

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

export const categoriesRelations = relations(categories, ({ many }) => ({
  posts: many(postCategories),
}));

export const postCategoriesRelations = relations(postCategories, ({ one }) => ({
  post: one(posts, {
    fields: [postCategories.postId],
    references: [posts.id],
  }),
  category: one(categories, {
    fields: [postCategories.categoryId],
    references: [categories.id],
  }),
}));
```

## Database Connection

Set up the Drizzle client:

```typescript
// db/index.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const connectionString = process.env.DATABASE_URL!;

const queryClient = postgres(connectionString);
export const db = drizzle(queryClient, { schema });

export const migrationClient = postgres(connectionString, { max: 1 });
```

## Type-Safe Queries

Implement repository patterns with Drizzle:

```typescript
// db/repositories/users.ts
import { eq, ilike, or, desc, sql } from "drizzle-orm";
import { db } from "@/db";
import { users, posts } from "@/db/schema";

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

export const userRepository = {
  async findById(id: string) {
    return db.query.users.findFirst({
      where: eq(users.id, id),
      with: {
        posts: true,
      },
    });
  },

  async findByEmail(email: string) {
    return db.query.users.findFirst({
      where: eq(users.email, email),
    });
  },

  async findMany({
    page = 1,
    limit = 10,
    search = "",
  }: {
    page?: number;
    limit?: number;
    search?: string;
  }) {
    const offset = (page - 1) * limit;

    const whereClause = search
      ? or(
          ilike(users.name, `%${search}%`),
          ilike(users.email, `%${search}%`)
        )
      : undefined;

    const [data, countResult] = await Promise.all([
      db.query.users.findMany({
        where: whereClause,
        limit,
        offset,
        orderBy: [desc(users.createdAt)],
        with: {
          posts: {
            columns: { id: true },
          },
        },
      }),
      db
        .select({ count: sql<number>`count(*)` })
        .from(users)
        .where(whereClause),
    ]);

    return {
      data,
      total: Number(countResult[0]?.count ?? 0),
      page,
      limit,
    };
  },

  async create(data: NewUser) {
    const [user] = await db.insert(users).values(data).returning();
    return user;
  },

  async update(id: string, data: Partial<NewUser>) {
    const [user] = await db
      .update(users)
      .set({ ...data, updatedAt: new Date() })
      .where(eq(users.id, id))
      .returning();
    return user;
  },

  async delete(id: string) {
    await db.delete(users).where(eq(users.id, id));
  },
};
```

## Transactions

Handle complex operations atomically:

```typescript
// db/services/user-service.ts
import { db } from "@/db";
import { users, posts } from "@/db/schema";
import { eq } from "drizzle-orm";

export async function createUserWithWelcomePost(
  userData: { email: string; name: string },
  welcomeMessage: string
) {
  return db.transaction(async (tx) => {
    const [user] = await tx
      .insert(users)
      .values(userData)
      .returning();

    const [post] = await tx
      .insert(posts)
      .values({
        title: "Welcome!",
        content: welcomeMessage,
        authorId: user.id,
        status: "published",
      })
      .returning();

    return { user, post };
  });
}

export async function transferPosts(fromUserId: string, toUserId: string) {
  return db.transaction(async (tx) => {
    await tx
      .update(posts)
      .set({ authorId: toUserId, updatedAt: new Date() })
      .where(eq(posts.authorId, fromUserId));

    await tx.delete(users).where(eq(users.id, fromUserId));
  });
}
```

## Migrations

Set up database migrations:

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

export default {
  schema: "./db/schema.ts",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;
```

```typescript
// db/migrate.ts
import { migrate } from "drizzle-orm/postgres-js/migrator";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const migrationClient = postgres(process.env.DATABASE_URL!, { max: 1 });

async function main() {
  await migrate(drizzle(migrationClient), { migrationsFolder: "./drizzle" });
  await migrationClient.end();
}

main();
```

## Best Practices

1. **Use query builder for complex queries** - More flexible than ORM methods
2. **Leverage relations** - Define relationships for automatic joins
3. **Use transactions** - Ensure data consistency for multi-step operations
4. **Type inference** - Use $inferSelect and $inferInsert for type safety
5. **Run migrations in CI/CD** - Automate schema updates

Drizzle ORM with Google Antigravity enables efficient, type-safe database operations with intelligent query suggestions.