Database

This guide explains how to set up and use the database system in our application using Drizzle ORM with PostgreSQL.

Setup

Prerequisites

  • PostgreSQL database (using Neon.tech)
  • Node.js environment
  • .env.local file

Installation

npm install drizzle-orm @neondatabase/serverless dotenv drizzle-zod zod
npm install -D drizzle-kit

Environment Configuration

DATABASE_URL="your_neon_database_url"

Configuration Files

Drizzle Config

Create a drizzle.config.ts file to configure your database:

import { config } from "dotenv"
import { defineConfig } from "drizzle-kit"
config({ path: ".env.local" })

export default defineConfig({
  schema: "./server/drizzle/index.ts",
  out: "./server/drizzle/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
})

Database Client

Set up your database client in server/drizzle/db.ts:

import * as schema from "@/server/drizzle/index"
import { neon, Pool } from "@neondatabase/serverless"
import { config } from "dotenv"
import { drizzle as drizzleHttp } from "drizzle-orm/neon-http"
import { drizzle as drizzleServerless } from "drizzle-orm/neon-serverless"

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

const sql = neon(process.env.DATABASE_URL!)
export const db = drizzleHttp(sql, { schema })

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
export const dbClient = drizzleServerless(pool)

Schema Organization

Organize your schemas in the server/drizzle/schema/ directory:

export * from "@/server/drizzle/schema/users"
export * from "@/server/drizzle/schema/auth"
export * from "@/server/drizzle/schema/workspaces"
export * from "@/server/drizzle/schema/notifications"
export * from "@/server/drizzle/schema/subscriptions"
export * from "@/server/drizzle/schema/invitations"
export * from "@/server/drizzle/schema/members"
export * from "@/server/drizzle/schema/purchases"
export * from "@/server/drizzle/schema/permissions"
export * from "@/server/drizzle/schema/items"

Database Operations

Server Actions

Example of a server action with proper error handling and validation:

export const createUserAction = async ({
  values,
  isFromInvitation,
}: {
  values: z.infer<typeof userSchema>
  isFromInvitation?: boolean
}) => {
  try {
    // 1. Validate input
    const validateValues = userSchema.safeParse(values)
    if (!validateValues.success) {
      throw new ValidationError(validateValues.error.message)
    }

    // 2. Check authentication
    const { user } = await getCurrentUser()
    if (!user) {
      throw new AuthenticationError()
    }

    // 3. Check rate limit
    const identifier = `ratelimit:create-user:${user.id}`
    const { success } = await ratelimit.limit(identifier)
    if (!success) {
      throw new RateLimitError()
    }

    // Start transaction
    return await dbClient.transaction(async (tx) => {
      // Database operations...
    })
  } catch (error: any) {
    if (error instanceof ApiError) {
      throw error
    }
    throw new DatabaseError("Failed to create workspace")
  }
}

Frontend Integration

Use React Query mutations for database operations:

export const useCreateUser = ({ isFromInvitation }: { isFromInvitation: boolean }) => {
  const router = useRouter()
  const { isPending, mutate } = useMutation({
    mutationFn: createUserAction,
    onSuccess: (data) => {
      router.push(data.hasWorkspace
        ? createRoute("onboarding-invite").href
        : createRoute("onboarding-workspace").href)
      toast.success(data.message)
    },
    onError: (error: any) => {
      toast.error(error?.message || GLOBAL_ERROR_MESSAGE)
    },
  })

  return { isPending, server_createUser: mutate }
}

Best Practices

Error Handling

  • Wrap database operations in try-catch blocks
  • Use proper error types
  • Implement proper validation
  • Handle edge cases

Security

  • Implement rate limiting
  • Use transactions for related operations
  • Validate all inputs
  • Check permissions

Type Safety

  • Leverage TypeScript
  • Use Drizzle's type inference
  • Define proper schemas
  • Use Zod validation