SaaS Box

Database & ORM

SaaS Box leverages Neon Tech for serverless PostgreSQL and Drizzle ORM for type-safe database interactions. This combination ensures high performance, minimal latency, and an exceptional developer experience.

Architecture Philosophy

The data layer is designed around the concept of Type-Safe SQL . While many boilerplates use Prisma, I chose Drizzle because it is headless and non-obstructive. It acts as a thin wrapper over standard SQL, allowing for extreme performance and zero runtime overhead in serverless environments.

Engine

Headless Execution

Drizzle does not rely on a heavy Rust binary. It is pure TypeScript, which eliminates cold-start penalties in Next.js 16 Edge and Lambda functions.

Efficiency

Neon Autoscaling

The infrastructure scales to zero when not in use. You only pay for the exact compute used during active database requests.

Neon Infrastructure

Neon Tech provides the Postgres engine for SaaS Box. Use these steps to initialize your project.

1

Project Creation

Create a free account at neon.tech and spin up a new PostgreSQL project. I recommend choosing a region that matches your primary deployment target (e.g., AWS us-east-1).

2

Connection Strings

Neon provides a Pooled connection string via PgBouncer. This is required for serverless apps to prevent exhausting the database connection limit.

postgresql://user:pass@ep-cool-lake-123-pooler.us-east-1.aws.neon.tech/neondb?sslmode=require

Environment Config

The boilerplate requires the database URL in the .env file. The application uses a validation layer in src/env.ts to catch configuration errors before the server boots.

DATABASE_URL="postgresql://..."

Schema Architecture

The data structure is defined in pure TypeScript inside src/db/schema.ts.

The schema uses the Drizzle PG-Core library. This approach allows me to keep the database logic entirely within the TypeScript environment, providing autocomplete and type validation for every table and column.

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(),
role: text("role").default("user"),
subscriptionId: text("subscriptionId"),
createdAt: timestamp("createdAt").notNull().defaultNow(),
});

Core Tables Breakdown

The boilerplate includes pre-configured tables that work natively with Better-Auth and Polar.

1. User Table

Contains the profile data, administrative role, and billing references. I have added columns like subscriptionId and role to manage access control levels.

2. Auth Tables

The Account , Session , and Verification tables handle OAuth linkages, secure session tokens, and email verification hashes. These are managed by the internal auth engine.

Database Client

The client is initialized in src/db/index.ts. It uses the Neon HTTP driver , which is the most resilient way to connect to Postgres from serverless environments. This driver bypasses the limitations of traditional WebSockets or TCP connections in edge functions.

import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import * as schema from "./schema";

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

Development Workflow

How to move schema changes from code to your live database.

The Prototyping Flow (Push)

During initial development, you can use pnpm drizzle-kit push. This command instantly synchronizes your local TypeScript schema with the Neon database without creating migration files.

The Production Flow (Migrations)

For production deployments, I recommend using Migrations . Run pnpm drizzle-kit generate to create SQL files that can be applied during CI/CD to ensure a reliable history of changes.

The Query API

Patterns for fetching data within React Server Components.

Single Records

The findFirst method allows for fast retrieval with full type safety for the returned object.

const profile = await db.query.user.findFirst({
where: (user, { eq }) => eq(user.id, userId),
});

Filtering & Sorting

const admins = await db.query.user.findMany({
where: (user, { eq }) => eq(user.role, "admin"),
orderBy: (user, { desc }) => [desc(user.createdAt)],
columns: { name: true, email: true },
});

Mutations (CRUD)

Standardized patterns for modifying data in Server Actions.

Inserting Data

import { user } from "@/db/schema";

await db.insert(user).values({
id: crypto.randomUUID(),
name: "New User",
email: "user@example.com",
}).returning();

Updating & Deleting

await db.update(user)
.set({ name: "Updated Name" })
.where(eq(user.id, userId));

await db.delete(user).where(eq(user.id, userId));

Relationships

I have utilized the Drizzle Relations API to provide a simpler way to handle nested data. By defining relations in the schema, you can use the with keyword to perform deep-nested selects without writing complex JOIN statements.

const userWithFullData = await db.query.user.findFirst({
with: {
accounts: true,
sessions: true,
},
});

Transactions

When multiple operations must succeed or fail as a single unit—such as registering a user and creating an initial organization record—I use Transactions . This ensures data integrity and prevents partial data insertion.

await db.transaction(async (tx) => {
const [newUser] = await tx.insert(user).values({ ... }).returning();
await tx.insert(account).values({ userId: newUser.id, ... });
});

Drizzle Studio

SaaS Box is fully compatible with Drizzle Studio, a visual explorer for your SQL data. It provides a spreadsheet-like interface to view, filter, and edit rows directly in your Neon database.

Launch the Studio

Run this command in a new terminal window to open the GUI in your browser.

pnpm drizzle-kit studio

Frequently Asked Questions

Can I switch to a local Postgres instance?

Yes. Simply install PostgreSQL on your machine and update the DATABASE_URL in your .env to point to your local host (e.g., postgresql://localhost:5432/mydb).

How do I add a new table?

Define the table using pgTable in src/db/schema.ts, then run the push command. If you want to use the Drizzle query API, don't forget to add the table to the relations object as well.

Official Documentation

Explore the core toolsets for advanced query optimization and architectural deep-dives.

Neon

Neon Tech

Serverless PostgreSQL infrastructure

Drizzle

Drizzle ORM

TypeScript-first database access

← Previous SectionAuthenticationNext Section →Payments