Database

Set up and use the database system using Drizzle ORM with PostgreSQL in The Boring Template.

This template uses Drizzle ORM with PostgreSQL. For more information, visit the Drizzle ORM docs.

Setup

Prerequisites

PostgreSQL

🐘 Database using Neon.tech (or any other PostgreSQL provider)

Node.js

⚙️ JavaScript runtime environment

.env.local

🔐 Environment configuration file

Installation

Install the required packages:

Terminal

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

Environment Configuration

Add your database URL to your .env.local file:

.env.local

DATABASE_URL="your_neon_database_url"

Configuration Files

Drizzle Config

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

drizzle.config.ts

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:

server/db/config/database.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);

HTTP vs Serverless

The template provides two database clients: one for HTTP and one for serverless environments. Use db for regular HTTP operations and dbClient for server actions with transactions.

Schema Organization

Organize your schemas in the server/drizzle/schema/ directory and export them:

lib/schemas.ts

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:

server/actions/users.ts

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");
  }
};

Always use transactions when performing multiple related database operations to ensure data consistency.

Frontend Integration

Use React Query mutations for database operations:

hooks/use-create-user.ts

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 };
};

Database Schema Example

Here's an example of a database schema definition:

server/drizzle/schema/users.ts

import { pgTable, text, timestamp, uuid } from "drizzle-orm/pg-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { z } from "zod";
 
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  emailVerified: timestamp("email_verified", { mode: "date" }),
  image: text("image"),
  createdAt: timestamp("created_at", { mode: "date" }).defaultNow(),
  updatedAt: timestamp("updated_at", { mode: "date" }).defaultNow(),
});
 
// Schemas for validation with Zod
export const insertUserSchema = createInsertSchema(users);
export const selectUserSchema = createSelectSchema(users);
export const userSchema = z.object({
  name: z.string().min(1, "Name is required"),
  email: z.string().email("Invalid email format"),
});