Drizzle ORM

Beztack uses Drizzle ORM as its database solution, providing complete type-safety and an excellent developer experience with PostgreSQL.

Why Drizzle ORM?

We chose Drizzle ORM because:

Type-Safety: Automatic TypeScript type inference
Performance: No overhead, optimized pure SQL
Developer Experience: Intuitive and easy-to-use API
SQL-like: Syntax close to native SQL
Migrations: Robust migration system with Drizzle Kit
Lightweight: No heavy dependencies

Configuration

File Structure

apps/api/
├── db/
│   ├── db.ts           # Database connection instance
│   └── schema.ts       # Table schema definition
├── drizzle/            # Generated migration files
└── drizzle.config.ts   # Drizzle Kit configuration

Database Connection

The connection is configured in apps/api/db/db.ts:

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const connectionString = process.env.DATABASE_URL || "";
const client = postgres(connectionString);
export const db = drizzle({ client });

Drizzle Kit Configuration

The drizzle.config.ts file defines how Drizzle Kit generates and applies migrations:

import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  schema: "./db/schema.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL || "",
  },
});

Schema Definition

The schema is defined in apps/api/db/schema.ts using the Drizzle API:

Example: User Table

import { pgTable, text, timestamp, boolean } from "drizzle-orm/pg-core";

export const user = pgTable("user", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: boolean("email_verified").default(false).notNull(),
  image: text("image"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at")
    .defaultNow()
    .$onUpdate(() => new Date())
    .notNull(),
});

Relations

Drizzle supports relations through references:

export const session = pgTable("session", {
  id: text("id").primaryKey(),
  userId: text("user_id")
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
  // ... other fields
});

Migrations

Generate Migrations

After modifying the schema, generate migrations:

pnpm run db:generate

This command:

Compares the current schema with the database
Generates SQL files in the drizzle/ folder
Maintains a change history

Apply Migrations

To apply migrations to the database:

pnpm run db:migrate

Check Migration Status

To review which migrations have been applied:

pnpm run db:status

Open Drizzle Studio

Drizzle Studio is a visual interface to explore and modify your database:

pnpm run db:studio

This will open a web interface at https://local.drizzle.studio

Queries

Basic CRUD Operations

SELECT

import { db } from "~/db/db";
import { user } from "~/db/schema";
import { eq } from "drizzle-orm";

// Get all users
const users = await db.select().from(user);

// Get a user by ID
const specificUser = await db
  .select()
  .from(user)
  .where(eq(user.id, "user-123"));

// Get with specific fields
const userEmails = await db
  .select({ email: user.email, name: user.name })
  .from(user);

INSERT

// Insert a user
const newUser = await db
  .insert(user)
  .values({
    id: "user-123",
    name: "John Doe",
    email: "john@example.com",
  })
  .returning();

// Insert multiple records
const newUsers = await db
  .insert(user)
  .values([
    { id: "1", name: "Alice", email: "alice@example.com" },
    { id: "2", name: "Bob", email: "bob@example.com" },
  ])
  .returning();

UPDATE

// Update a user
const updated = await db
  .update(user)
  .set({ name: "Jane Doe" })
  .where(eq(user.id, "user-123"))
  .returning();

DELETE

// Delete a user
await db
  .delete(user)
  .where(eq(user.id, "user-123"));

Queries with JOINs

import { member, organization } from "~/db/schema";

const membersWithOrgs = await db
  .select({
    memberId: member.id,
    memberRole: member.role,
    orgName: organization.name,
  })
  .from(member)
  .innerJoin(organization, eq(member.organizationId, organization.id));

Query Operators

Drizzle provides operators to build conditions:

import { eq, ne, gt, gte, lt, lte, like, and, or } from "drizzle-orm";

// Equality
await db.select().from(user).where(eq(user.email, "john@example.com"));

// Greater/Less than
await db.select().from(user).where(gt(user.createdAt, new Date("2024-01-01")));

// LIKE
await db.select().from(user).where(like(user.name, "%John%"));

// Multiple conditions
await db
  .select()
  .from(user)
  .where(
    and(
      eq(user.emailVerified, true),
      gt(user.createdAt, new Date("2024-01-01"))
    )
  );

Best Practices

1. Type Safety

Take advantage of TypeScript type inference:

import type { InferSelectModel, InferInsertModel } from "drizzle-orm";
import { user } from "~/db/schema";

// Type for reading (SELECT)
type User = InferSelectModel<typeof user>;

// Type for inserting (INSERT)
type NewUser = InferInsertModel<typeof user>;

2. Transactions

For operations that must be atomic:

await db.transaction(async (tx) => {
  const newUser = await tx.insert(user).values({
    id: "user-123",
    name: "John Doe",
    email: "john@example.com",
  });

  await tx.insert(session).values({
    id: "session-456",
    userId: "user-123",
    token: "token-789",
  });
});

3. Prepared Statements

For repeated queries with better performance:

const getUserById = db
  .select()
  .from(user)
  .where(eq(user.id, sql.placeholder("id")))
  .prepare("get_user_by_id");

// Use the prepared query
const user1 = await getUserById.execute({ id: "user-123" });
const user2 = await getUserById.execute({ id: "user-456" });

4. Pagination

const page = 1;
const pageSize = 10;

const users = await db
  .select()
  .from(user)
  .limit(pageSize)
  .offset((page - 1) * pageSize);

Better Auth Integration

Drizzle ORM is integrated with Better Auth through the adapter:

import { drizzleAdapter } from "better-auth/adapters/drizzle";
import { db } from "~/db/db";
import * as schema from "~/db/schema";

export const auth = betterAuth({
  database: drizzleAdapter(db, {
    provider: "pg",
    schema,
  }),
  // ... other options
});