Storyie

Type-Safe Database Schema Design with Drizzle ORM and Supabase PostgreSQL

Storyie Engineering Team
••9 min read

Learn how we built a modular, type-safe database schema using Drizzle ORM, Supabase PostgreSQL, and automatic type generation for our Next.js and Expo apps.

Type-Safe Database Schema Design with Drizzle ORM and Supabase PostgreSQL

Database schemas are the foundation of any application, yet they're often the source of the most frustrating bugs: runtime errors from typos in column names, incorrect types passed to queries, and subtle data inconsistencies that slip through testing. At Storyie, we eliminated these issues by building our database schema with Drizzle ORM and Supabase PostgreSQL, achieving end-to-end type safety from database to UI. In this post, we'll show you how we designed a modular, maintainable schema that powers both our Next.js web app and React Native mobile app.

Why Drizzle ORM Over Prisma or TypeORM

When choosing an ORM, we evaluated Prisma, TypeORM, and Drizzle. Each has strengths, but Drizzle won for our use case.

The TypeScript-First Philosophy

Drizzle schemas are TypeScript code, not a custom DSL or schema language. This means:

  • Full IntelliSense: Autocomplete for table names, columns, and relations
  • Compile-time type checking: Catch errors before running code
  • No code generation step required during development
  • Direct SQL mapping: Drizzle stays close to SQL, making it easier to reason about queries

Here's a Drizzle schema definition:

import { pgTable, uuid, text, timestamp } from 'drizzle-orm/pg-core';

export const diaries = pgTable('diaries', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull(),
  title: text('title').notNull(),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

Compare this to Prisma's schema language:

model Diary {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  title     String
  createdAt DateTime @default(now()) @map("created_at")
}

While Prisma's DSL is concise, Drizzle's TypeScript approach gives us IDE support without additional tooling.

Drizzle vs Prisma

We chose Drizzle over Prisma because:

Query Control:

  • Drizzle: Thin abstraction over SQL, full control over queries
  • Prisma: Higher-level API, sometimes generates inefficient SQL

Runtime Size:

  • Drizzle: Minimal runtime (~50KB), ideal for edge deployments
  • Prisma: Larger runtime with query engine, not suitable for edge

Migration Workflow:

  • Drizzle: Generates SQL migrations you can review and modify
  • Prisma: Generates and applies migrations with less visibility

Use Case Fit:

  • Drizzle excels for applications that need SQL control
  • Prisma excels for rapid prototyping with less SQL knowledge

For Storyie's Next.js app deploying to AWS via SST (see our Next.js deployment post), Drizzle's small runtime and edge compatibility were crucial.

Why Supabase PostgreSQL

Supabase provides managed PostgreSQL with powerful extras:

  • PostgreSQL 15: Battle-tested database with JSONB, full-text search, and extensions
  • Row Level Security (RLS): Database-level authorization without writing middleware
  • Real-time subscriptions: Optional feature for live updates
  • PostgREST API: Auto-generated REST API (we don't use this, but it's there)
  • Management dashboard: Easy database administration

The RLS feature alone saved us weeks of development time.

Modular Schema Architecture

We organize our database schema into 10 separate files, each owning a specific domain:

packages/database/src/schema/
├── index.ts              # Exports all schemas
├── diaries.ts            # Diary entries (main content)
├── users.ts              # User accounts (Supabase Auth integration)
├── profiles.ts           # Public user profiles (slugs, bios)
├── tags.ts               # Diary tags
├── diary-tags.ts         # Many-to-many join table
├── view-logs.ts          # Diary view tracking
├── activities.ts         # User activity log
├── public-profiles.ts    # Additional public profile data
└── user-settings.ts      # User preferences (font size, theme)

This modular approach provides:

  • Easier reasoning: Each file has a clear purpose
  • Better collaboration: Team members can work on different schemas without conflicts
  • Clearer dependencies: Foreign key relationships are explicit

One Schema File Per Domain

Each schema file defines tables for a single domain. For example, diaries.ts owns all diary-related tables:

// packages/database/src/schema/diaries.ts
import {
  pgTable,
  uuid,
  text,
  timestamp,
  jsonb,
  integer,
  pgEnum,
} from 'drizzle-orm/pg-core';
import { users } from './users';

export const visibilityEnum = pgEnum('visibility', ['private', 'public']);

export const diaries = pgTable('diaries', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id')
    .notNull()
    .references(() => users.id),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  content: jsonb('content').notNull(), // Lexical EditorState
  visibility: visibilityEnum('visibility').notNull().default('private'),
  viewCount: integer('view_count').notNull().default(0),
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

Notice the foreign key: references(() => users.id). This creates a database constraint and provides type safety for joins.

Shared Index File

The index.ts file re-exports all schemas for easy importing:

// packages/database/src/schema/index.ts
export * from './diaries';
export * from './users';
export * from './profiles';
export * from './tags';
export * from './diary-tags';
export * from './view-logs';
export * from './activities';
export * from './public-profiles';
export * from './user-settings';

Now apps can import everything from one place:

import { diaries, users, profiles } from '@storyie/database/schema';

This keeps imports clean and makes refactoring easier.

Defining Schemas with Drizzle

Let's dive deeper into how we define tables, columns, and relationships.

Table Definitions

A Drizzle table definition specifies columns, types, constraints, and defaults:

export const diaries = pgTable('diaries', {
  // Primary key (UUID, auto-generated)
  id: uuid('id').primaryKey().defaultRandom(),

  // Foreign key to users table
  userId: uuid('user_id')
    .notNull()
    .references(() => users.id),

  // Text columns
  title: text('title').notNull(),
  slug: text('slug').notNull(),

  // JSONB column for Lexical editor state
  content: jsonb('content').notNull(),

  // Enum column (constrained to specific values)
  visibility: visibilityEnum('visibility').notNull().default('private'),

  // Integer column with default
  viewCount: integer('view_count').notNull().default(0),

  // Timestamps with automatic defaults
  createdAt: timestamp('created_at').notNull().defaultNow(),
  updatedAt: timestamp('updated_at').notNull().defaultNow(),
});

Drizzle supports all PostgreSQL column types: text, integer, boolean, uuid, timestamp, jsonb, numeric, and more.

Enums for Type Safety

PostgreSQL enums enforce values at the database level:

export const visibilityEnum = pgEnum('visibility', ['private', 'public']);

export const diaries = pgTable('diaries', {
  visibility: visibilityEnum('visibility').notNull().default('private'),
});

TypeScript now knows visibility can only be "private" or "public". This prevents typos like "privat" or "publlic".

Relations and References

Foreign keys define relationships between tables:

// In diary-tags.ts (many-to-many join table)
export const diaryTags = pgTable('diary_tags', {
  id: uuid('id').primaryKey().defaultRandom(),
  diaryId: uuid('diary_id')
    .notNull()
    .references(() => diaries.id, {
      onDelete: 'cascade', // Delete tags when diary is deleted
    }),
  tagId: uuid('tag_id')
    .notNull()
    .references(() => tags.id, {
      onDelete: 'cascade',
    }),
  createdAt: timestamp('created_at').notNull().defaultNow(),
});

The onDelete: "cascade" option ensures referential integrity: when a diary is deleted, its tags are automatically removed from the join table.

The Diaries Schema: A Deep Dive

Let's examine the diaries table in detail, as it's the core of our application.

JSONB for Lexical Content

We store diary content as JSONB (JSON Binary), not plain text:

content: jsonb("content").notNull(),

Why JSONB instead of TEXT?

  • Structured storage: Lexical's EditorState is JSON, matching the column type
  • Querying capability: PostgreSQL can query inside JSONB (e.g., search for images)
  • Validation: We can add JSON schema constraints at the database level
  • Performance: JSONB is indexed and queryable efficiently

Here's what diary content looks like in the database:

{
  "root": {
    "children": [
      {
        "type": "paragraph",
        "children": [
          { "type": "text", "text": "Today was amazing!", "format": 0 }
        ]
      },
      {
        "type": "image",
        "src": "https://cdn.storyie.app/image.jpg",
        "alt": "Sunset"
      }
    ]
  }
}

This JSON represents our Lexical editor state (see our Lexical editor architecture post for details).

Visibility Enum

The visibility column uses an enum to enforce privacy:

export const visibilityEnum = pgEnum("visibility", ["private", "public"]);

visibility: visibilityEnum("visibility").notNull().default("private"),

This provides:

  • Database constraint: PostgreSQL rejects invalid values
  • TypeScript type: IntelliSense suggests only "private" or "public"
  • Default privacy: New diaries default to private for user safety

Timestamps and Defaults

We use defaultNow() for automatic timestamps:

createdAt: timestamp("created_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().defaultNow(),

The database sets these values automatically on insert. For updatedAt, we update it on every modification via a trigger or application code.

Type Generation Workflow

Drizzle generates TypeScript types from our schema, ensuring type safety across the stack.

Drizzle Kit Configuration

Configuration lives in drizzle.config.ts:

// packages/database/drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/schema/index.ts',
  out: './migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

This tells Drizzle Kit:

  • Where to find schemas (./src/schema/index.ts)
  • Where to output migrations (./migrations)
  • Which database dialect (postgresql)
  • How to connect (DATABASE_URL environment variable)

Running Type Generation

Our monorepo includes a type generation command:

pnpm --filter @storyie/database type:gen

This runs drizzle-kit generate, which:

  1. Reads TypeScript schema files
  2. Compares to current database state
  3. Generates migration SQL
  4. Updates generated TypeScript types

For example, adding a view_count column generates:

-- Migration: 0016_curly_menace.sql
ALTER TABLE "diaries" ADD COLUMN "view_count" integer DEFAULT 0 NOT NULL;
CREATE INDEX "diaries_view_count_idx" ON "diaries" ("view_count");

And updates the Diary type:

export type Diary = {
  id: string;
  userId: string;
  title: string;
  slug: string;
  content: SerializedEditorState;
  visibility: 'private' | 'public';
  viewCount: number; // ← New field
  createdAt: Date;
  updatedAt: Date;
};

Using Generated Types

Apps import types directly from the schema package:

// In Next.js or Expo app
import type { Diary } from '@storyie/database/schema';

function DiaryCard({ diary }: { diary: Diary }) {
  return (
    <div>
      <h3>{diary.title}</h3>
      <p>Views: {diary.viewCount}</p>
    </div>
  );
}

TypeScript catches errors if you mistype a property or pass the wrong type.

Type-Safe Queries with Drizzle

Drizzle's query builder provides full type safety and autocomplete.

Select Queries

Fetching public diaries with joins:

// apps/web/lib/db/queries/diaries.ts
import { db } from '@storyie/database';
import { diaries, users } from '@storyie/database/schema';
import { eq, and, desc } from 'drizzle-orm';

export async function getPublicDiaries() {
  return await db
    .select({
      id: diaries.id,
      title: diaries.title,
      slug: diaries.slug,
      content: diaries.content,
      author: users.name,
      authorSlug: users.slug,
      createdAt: diaries.createdAt,
    })
    .from(diaries)
    .innerJoin(users, eq(diaries.userId, users.id))
    .where(eq(diaries.visibility, 'public'))
    .orderBy(desc(diaries.createdAt))
    .limit(20);
}

The returned type is inferred as:

Array<{
  id: string;
  title: string;
  slug: string;
  content: SerializedEditorState;
  author: string;
  authorSlug: string;
  createdAt: Date;
}>;

Joins and Relations

Drizzle supports all SQL join types with full type inference:

// Inner join (only diaries with matching users)
.innerJoin(users, eq(diaries.userId, users.id))

// Left join (all diaries, even if user is missing)
.leftJoin(users, eq(diaries.userId, users.id))

// Right join (all users, even if they have no diaries)
.rightJoin(users, eq(diaries.userId, users.id))

For left/right joins, joined columns become nullable in the returned type, preventing null reference errors.

Insert and Update

Type-safe inserts with return values:

export async function createDiary({
  userId,
  title,
  content,
  visibility = 'private',
}: {
  userId: string;
  title: string;
  content: SerializedEditorState;
  visibility?: 'private' | 'public';
}) {
  const [diary] = await db
    .insert(diaries)
    .values({
      userId,
      title,
      slug: generateSlug(title),
      content,
      visibility,
      viewCount: 0,
    })
    .returning();

  return diary;
}

Drizzle validates that all required columns are provided and types match the schema.

Transactions

Atomic operations with automatic rollback on error:

export async function createDiaryWithTags({
  userId,
  title,
  content,
  tagNames,
}: {
  userId: string;
  title: string;
  content: SerializedEditorState;
  tagNames: string[];
}) {
  return await db.transaction(async (tx) => {
    // Create diary
    const [diary] = await tx
      .insert(diaries)
      .values({ userId, title, slug: generateSlug(title), content })
      .returning();

    // Find or create tags
    const tagRecords = await Promise.all(
      tagNames.map(async (name) => {
        const [tag] = await tx
          .insert(tags)
          .values({ name, slug: generateSlug(name) })
          .onConflictDoUpdate({ target: tags.slug, set: { name } })
          .returning();
        return tag;
      })
    );

    // Create diary-tag associations
    await tx.insert(diaryTags).values(
      tagRecords.map((tag) => ({
        diaryId: diary.id,
        tagId: tag.id,
      }))
    );

    return diary;
  });
}

If any step fails, the entire transaction rolls back, leaving the database in a consistent state.

Migration Management

Drizzle Kit automates migration generation and application.

Generating Migrations

When you change the schema, generate a migration:

pnpm --filter @storyie/database drizzle-kit generate

Drizzle Kit compares your TypeScript schema to the database and generates SQL:

-- Migration: 0016_curly_menace.sql
-- Add view_count column to diaries table
ALTER TABLE "diaries" ADD COLUMN "view_count" integer DEFAULT 0 NOT NULL;

-- Create index for view_count sorting
CREATE INDEX "diaries_view_count_idx" ON "diaries" ("view_count");

-- Create diary_view_logs table
CREATE TABLE "diary_view_logs" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "diary_id" uuid NOT NULL,
  "user_id" uuid NOT NULL,
  "viewed_at" timestamp DEFAULT now() NOT NULL
);

Migration files are timestamped and version-controlled with Git.

Migration Files

Migrations live in packages/database/migrations/:

migrations/
├── 0001_first_migration.sql
├── 0002_add_visibility.sql
├── 0003_add_tags.sql
...
├── 0016_curly_menace.sql
└── meta/
    └── _journal.json

The _journal.json tracks applied migrations, preventing duplicate runs.

Applying Migrations

Push migrations to Supabase PostgreSQL:

pnpm --filter @storyie/database drizzle-kit push

Drizzle Kit applies unapplied migrations in order. If a migration fails, it rolls back and shows the error.

For production, we use CI/CD to apply migrations automatically on deployment.

Row Level Security (RLS) with Supabase

Supabase's Row Level Security moves authorization logic to the database.

What is RLS?

RLS policies attach to tables and filter rows based on the current user:

  • Enforced at query time: No middleware needed
  • Works for all clients: REST API, GraphQL, direct SQL
  • Impossible to bypass: Even raw SQL obeys RLS

Private Diary Policy

Users can only view their own private diaries:

-- Supabase dashboard SQL editor
CREATE POLICY "Users can view own private diaries"
ON diaries
FOR SELECT
USING (
  visibility = 'public'
  OR (visibility = 'private' AND user_id = auth.uid())
);

This policy says:

  • Public diaries are visible to everyone
  • Private diaries are visible only to their owner

auth.uid() is a Supabase function that returns the authenticated user's ID from the JWT token.

Update and Delete Policies

Users can only update/delete their own diaries:

CREATE POLICY "Users can update own diaries"
ON diaries
FOR UPDATE
USING (user_id = auth.uid());

CREATE POLICY "Users can delete own diaries"
ON diaries
FOR DELETE
USING (user_id = auth.uid());

These policies prevent users from modifying each other's content.

Supabase Auth Integration

Supabase Auth automatically sets auth.uid() from the JWT token in the request. Our Next.js server creates a Supabase client with the user's session:

// apps/web/lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';

export async function createClient() {
  const cookieStore = await cookies();
  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll: () => cookieStore.getAll(),
        setAll: (cookiesToSet) => {
          cookiesToSet.forEach(({ name, value, options }) =>
            cookieStore.set(name, value, options)
          );
        },
      },
    }
  );
}

When we query diaries, RLS automatically filters based on the authenticated user—no manual filtering needed.

Performance Optimization

Database performance requires careful index and query design.

Indexing Strategy

We create indexes for common query patterns:

1. Primary Key Indexes (automatic):

  • diaries.id (UUID lookups)

2. Foreign Key Indexes (must create manually):

CREATE INDEX "diaries_user_id_idx" ON "diaries" ("user_id");

This speeds up queries like "get all diaries for user X".

3. Composite Indexes:

CREATE INDEX "diaries_user_id_created_at_idx"
ON "diaries" ("user_id", "created_at" DESC);

This optimizes "get user's latest diaries" queries.

4. Partial Indexes:

CREATE INDEX "diaries_public_created_at_idx"
ON "diaries" ("created_at" DESC)
WHERE visibility = 'public';

This index only includes public diaries, making public diary queries faster.

Query Optimization

Use EXPLAIN ANALYZE to understand query performance:

EXPLAIN ANALYZE
SELECT * FROM diaries
WHERE user_id = 'user-uuid'
ORDER BY created_at DESC
LIMIT 20;

Look for:

  • Seq Scan: Bad (full table scan)
  • Index Scan: Good (using an index)
  • Execution time: Target < 10ms for common queries

If you see Seq Scan, add an index.

JSONB Performance

PostgreSQL supports querying inside JSONB columns:

// Find diaries containing images
await db
  .select()
  .from(diaries)
  .where(sql`content @> '{"type": "image"}'`);

For better performance, add a GIN index:

CREATE INDEX "diaries_content_gin_idx" ON "diaries" USING GIN ("content");

GIN indexes enable fast JSONB queries.

Monorepo Integration

Our database package lives in our pnpm monorepo (see our monorepo architecture post).

Shared Database Package

Both web and mobile apps import from @storyie/database:

// apps/web/package.json
{
  "dependencies": {
    "@storyie/database": "workspace:*"
  }
}
// apps/expo/package.json
{
  "dependencies": {
    "@storyie/database": "workspace:*"
  }
}

The workspace:* protocol ensures apps always use the local package, not a published npm version.

Build Order

Packages must build in dependency order:

# Build packages first, then apps
pnpm build:packages && pnpm build:web

Our root package.json encodes this:

{
  "scripts": {
    "build:packages": "pnpm --filter './packages/*' build",
    "build:web": "pnpm build:packages && pnpm --filter @storyie/web build"
  }
}

This ensures the database package builds before apps that depend on it.

Lessons Learned

What Worked Well

  1. Modular schema files: 10 separate files are easier to navigate than one giant schema
  2. Drizzle's TypeScript-first approach: Caught errors at compile time, not runtime
  3. Supabase RLS: Simplified authorization logic significantly
  4. Type generation workflow: pnpm type:gen kept types perfectly in sync with database

What We'd Do Differently

  1. Earlier investment in indexing: We added many indexes after launch when queries slowed down
  2. More granular RLS policies: Our initial policies were too permissive
  3. Better test coverage for migrations: We occasionally broke migrations and had to debug in production

Future Improvements

  1. Soft deletes: Add deleted_at column instead of hard deleting diaries
  2. Audit log: Track all changes to diaries for compliance and rollback
  3. GraphQL API layer: Consider Hasura or PostGraphile for a GraphQL API on top of PostgreSQL

Conclusion and Key Takeaways

Building Storyie's database schema with Drizzle ORM and Supabase PostgreSQL gave us end-to-end type safety and a maintainable architecture. Here are the key takeaways:

  1. Drizzle ORM provides true end-to-end type safety from database to UI with TypeScript-first schemas
  2. Modular schema design (10 separate files) improves maintainability and team collaboration
  3. Type generation workflow (pnpm type:gen) automatically keeps TypeScript types in sync with the database
  4. Supabase RLS provides database-level authorization, eliminating the need for authorization middleware
  5. JSONB columns enable flexible storage for rich content like Lexical editor state
  6. Monorepo architecture allows sharing the database schema across web and mobile apps

If you're building a TypeScript application, we highly recommend Drizzle ORM + Supabase PostgreSQL for a modern, type-safe database stack.

Related Posts

Try Storyie

Experience our type-safe database architecture in action. Sign up at storyie.com and create a diary—every save goes through our Drizzle ORM queries with full type safety.