Create Database Table

Overview

This guide walks you through creating a Drizzle ORM database table with proper structure, indexes, and Row-Level Security (RLS) policies. Tables are defined in code and synced to PostgreSQL using Drizzle's schema management.

All tables use RLS to ensure users can only access data they own or have permission to view.

Prerequisites

Before creating a table, determine:

Step 1: Table Name

Plural, kebab-case name (e.g., notifications, projects, api-keys)

Step 2: Columns

List all columns with types and constraints (name, description, status, etc.)

Step 3: Owner Relationship

Determine ownership model:

User-owned - userId foreign key
Organization-owned - organizationId foreign key
Standalone - No owner (rare)

Step 4: RLS Policies

Define who can read/write (owner only, organization members, public, etc.)

Step 1: Create Table File

Create src/db/tables/<table-name>.ts:

import { commonColumns, createTable } from "@/db/table-utils";
import { users } from "@/db/tables";
import {
  adminRole,
  allowAll,
  authenticatedRole,
  isOwner,
  serviceRole,
} from "@/db/rls";
import {
  boolean,
  index,
  integer,
  pgPolicy,
  text,
  timestamp,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export const <tableName> = createTable(
  "<table_name>", // Database table name (snake_case)
  {
    // Common columns (id, createdAt, updatedAt)
    ...commonColumns,
    
    // Custom columns
    name: varchar({ length: 255 }).notNull(),
    description: text(),
    
    // Owner reference (for user-owned tables)
    userId: uuid()
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
  },
  (t) => [
    // ---------- INDEXES ----------
    index("<table>_user_id_idx").on(t.userId),
    
    // ---------- RLS POLICIES ----------
    
    // Users can view their own records
    pgPolicy("<table>-select-own", {
      for: "select",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
    
    // Users can create records (ownership verified on insert)
    pgPolicy("<table>-insert-own", {
      for: "insert",
      to: authenticatedRole,
      withCheck: isOwner(t.userId),
    }),
    
    // Users can update their own records
    pgPolicy("<table>-update-own", {
      for: "update",
      to: authenticatedRole,
      using: isOwner(t.userId),
      withCheck: isOwner(t.userId),
    }),
    
    // Users can delete their own records
    pgPolicy("<table>-delete-own", {
      for: "delete",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
    
    // Service role has full access (for system operations)
    pgPolicy("<table>-all-service", {
      for: "all",
      to: serviceRole,
      using: allowAll,
      withCheck: allowAll,
    }),
  ],
);

Table Naming:

TypeScript export: camelCase or PascalCase
Database name (first arg): snake_case
Example: export const apiKeys = createTable("api_keys", ...)

Step 2: Column Types Reference

String Columns

// Fixed-length varchar (recommended for names, emails)
name: varchar({ length: 255 }).notNull(),
email: varchar({ length: 320 }).notNull().unique(),

// Unlimited text (for descriptions, content)
description: text(),
content: text().notNull(),

Number Columns

// Integer
count: integer().notNull().default(0),
age: integer(),

// With constraints
price: integer().notNull(), // Store cents, not dollars
quantity: integer().notNull().default(1),

Boolean Columns

// Boolean with default
enabled: boolean().notNull().default(true),
isPublic: boolean().notNull().default(false),

// Nullable boolean
isVerified: boolean(),

Date/Time Columns

// Timestamp with timezone (recommended)
createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow(),

// Nullable timestamp (for optional dates)
publishedAt: timestamp({ withTimezone: true }),
expiresAt: timestamp({ withTimezone: true }),

UUID Columns

// Primary key (handled by commonColumns)
id: uuid().primaryKey().defaultRandom(),

// Foreign key reference
userId: uuid()
  .notNull()
  .references(() => users.id, { onDelete: "cascade" }),

// Optional foreign key
parentId: uuid().references(() => items.id, { onDelete: "set null" }),

JSON Columns

// JSON stored as text (use for simple key-value data)
metadata: text(), // Store JSON.stringify() data
settings: text().notNull().default("{}"),

// For Zod validation, use z.string() and transform
// settings: z.string().transform(val => JSON.parse(val))

Enum Columns

// Define enum first
export const statusEnum = pgEnum("status", ["draft", "published", "archived"]);

// Use in table
status: statusEnum().notNull().default("draft"),

Step 3: Foreign Key Patterns

User-Owned Table

userId: uuid()
  .notNull()
  .references(() => users.id, { onDelete: "cascade" }),

On Delete Options:

cascade - Delete records when user is deleted (recommended)
set null - Set to null when user is deleted
restrict - Prevent user deletion if records exist

Organization-Owned Table

import { organizations } from "@/db/tables";

organizationId: uuid()
  .notNull()
  .references(() => organizations.id, { onDelete: "cascade" }),

Many-to-One Relationship

// Projects belong to organizations
projectId: uuid()
  .notNull()
  .references(() => projects.id, { onDelete: "cascade" }),

Self-Referencing (Parent/Child)

// Comments can reply to other comments
parentId: uuid().references(() => comments.id, { onDelete: "cascade" }),

Step 4: Indexes

Add indexes for columns frequently used in WHERE clauses or JOINs:

(t) => [
  // Single column index
  index("<table>_user_id_idx").on(t.userId),
  index("<table>_status_idx").on(t.status),
  
  // Composite index (order matters!)
  index("<table>_user_status_idx").on(t.userId, t.status),
  
  // Unique index
  index("<table>_email_unique_idx").on(t.email).unique(),
]

Index Naming: Use pattern <table>_<column(s)>_idx for consistency.

Step 5: RLS Policies

Owner-Only Access (Most Common)

// Select own records
pgPolicy("<table>-select-own", {
  for: "select",
  to: authenticatedRole,
  using: isOwner(t.userId),
}),

// Insert own records
pgPolicy("<table>-insert-own", {
  for: "insert",
  to: authenticatedRole,
  withCheck: isOwner(t.userId),
}),

// Update own records
pgPolicy("<table>-update-own", {
  for: "update",
  to: authenticatedRole,
  using: isOwner(t.userId),
  withCheck: isOwner(t.userId),
}),

// Delete own records
pgPolicy("<table>-delete-own", {
  for: "delete",
  to: authenticatedRole,
  using: isOwner(t.userId),
}),

Organization-Based Access

import { isOrgMember } from "@/db/rls";

// Select if member of organization
pgPolicy("<table>-select-org", {
  for: "select",
  to: authenticatedRole,
  using: isOrgMember(t.organizationId),
}),

Admin Full Access

import { isAdmin } from "@/db/rls";

// Admins can do everything
pgPolicy("<table>-all-admin", {
  for: "all",
  to: adminRole,
  using: isAdmin,
  withCheck: isAdmin,
}),

Public Read, Owner Write

// Anyone can read
pgPolicy("<table>-select-public", {
  for: "select",
  to: authenticatedRole,
  using: allowAll,
}),

// Only owner can modify
pgPolicy("<table>-update-own", {
  for: "update",
  to: authenticatedRole,
  using: isOwner(t.userId),
  withCheck: isOwner(t.userId),
}),

Service Role (Always Include)

// Service role bypasses all policies (for system operations)
pgPolicy("<table>-all-service", {
  for: "all",
  to: serviceRole,
  using: allowAll,
  withCheck: allowAll,
}),

Always include service role policy - Required for migrations, seeds, and system operations.

Step 6: Add Relations (Optional)

For TypeScript type inference in joins, define relations in a separate file:

Create src/db/tables/<table-name>.relations.ts:

import { relations } from "drizzle-orm";
import { <tableName>, users } from "@/db/tables";

export const <tableName>Relations = relations(<tableName>, ({ one }) => ({
  // One-to-one or many-to-one
  user: one(users, {
    fields: [<tableName>.userId],
    references: [users.id],
  }),
}));

Step 7: Export Table

In src/db/tables/index.ts:

export * from "./<table-name>";
// export * from "./<table-name>.relations"; // If using relations

Step 8: Add Cache Tag (For Pagination)

If using table pagination with listTable(), add a cache tag in src/db/tags.ts:

export enum TableTags {
  // ...existing tags
  <tableName> = "<table-name>",
}

Step 9: Push Schema to Database

Apply schema changes to your database:

npm run db:push

This will:

Create the table if it doesn't exist
Add/modify columns
Create indexes
Apply RLS policies

Production Migrations: Use npm run db:generate and npm run db:migrate for production deployments instead of db:push.

Complete Example: Notifications Table

import { commonColumns, createTable } from "@/db/table-utils";
import { users } from "@/db/tables";
import {
  authenticatedRole,
  isOwner,
  serviceRole,
  allowAll,
} from "@/db/rls";
import {
  boolean,
  index,
  pgPolicy,
  text,
  timestamp,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

// Define enum
export const notificationTypeEnum = pgEnum("notification_type", [
  "info",
  "success",
  "warning",
  "error",
]);

export const notifications = createTable(
  "notifications",
  {
    ...commonColumns,
    
    // Content
    title: varchar({ length: 255 }).notNull(),
    message: text().notNull(),
    type: notificationTypeEnum().notNull().default("info"),
    
    // Metadata
    read: boolean().notNull().default(false),
    readAt: timestamp({ withTimezone: true }),
    
    // References
    userId: uuid()
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    
    // Optional link
    link: text(),
  },
  (t) => [
    // Indexes
    index("notifications_user_id_idx").on(t.userId),
    index("notifications_user_read_idx").on(t.userId, t.read),
    index("notifications_created_at_idx").on(t.createdAt),
    
    // RLS Policies
    pgPolicy("notifications-select-own", {
      for: "select",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
    pgPolicy("notifications-update-own", {
      for: "update",
      to: authenticatedRole,
      using: isOwner(t.userId),
      withCheck: isOwner(t.userId),
    }),
    pgPolicy("notifications-delete-own", {
      for: "delete",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
    pgPolicy("notifications-all-service", {
      for: "all",
      to: serviceRole,
      using: allowAll,
      withCheck: allowAll,
    }),
  ],
);

Common Patterns

Soft Delete

deletedAt: timestamp({ withTimezone: true }),

// RLS: Exclude soft-deleted records
using: sql`${isOwner(t.userId)} AND ${t.deletedAt} IS NULL`,

Versioning

version: integer().notNull().default(1),

Ordering

order: integer().notNull().default(0),

// Index for sorting
index("<table>_order_idx").on(t.order),

Timestamps for State Changes

publishedAt: timestamp({ withTimezone: true }),
archivedAt: timestamp({ withTimezone: true }),
completedAt: timestamp({ withTimezone: true }),

Post-Creation Checklist

Table Definition
- Table file created in src/db/tables/
- Common columns included (...commonColumns)
- All custom columns defined
- Foreign keys properly referenced
Indexes & Policies
- Indexes added for foreign keys
- Indexes added for frequently queried columns
- RLS policies defined (select, insert, update, delete)
- Service role policy included
Export & Tags
- Table exported in src/db/tables/index.ts
- Cache tag added in src/db/tags.ts (if using pagination)
Database
- Run npm run db:push successfully
- Verify table exists in database
- Test RLS policies work correctly
Integration
- Feature functions.ts can query the table
- Schema types match table structure

Troubleshooting

Migration Fails

# Reset local database
npm run db:reset

# Try push again
npm run db:push

RLS Policies Block Queries

Check that:

User context is set (setUserId() called)
Policies match ownership column names
Service role policy exists for system operations

Type Errors

# Regenerate types
npm run db:generate

Real-World Examples

See these tables for reference:

src/db/tables/api-keys.ts - User-owned with complex fields
src/db/tables/organizations.ts - Standalone entity
src/db/tables/memberships.ts - Join table with composite key

Table created! Your database structure is ready. Next, create functions to interact with the table.

Overview

All tables use RLS to ensure users can only access data they own or have permission to view.

Prerequisites

Before creating a table, determine:

Step 1: Table Name

Plural, kebab-case name (e.g., notifications, projects, api-keys)

Step 2: Columns

List all columns with types and constraints (name, description, status, etc.)

Step 3: Owner Relationship

Determine ownership model:

User-owned - userId foreign key
Organization-owned - organizationId foreign key
Standalone - No owner (rare)

Step 4: RLS Policies

Define who can read/write (owner only, organization members, public, etc.)

Step 1: Create Table File

Create src/db/tables/<table-name>.ts:

import { commonColumns, createTable } from "@/db/table-utils";
import { users } from "@/db/tables";
import {
  adminRole,
  allowAll,
  authenticatedRole,
  isOwner,
  serviceRole,
} from "@/db/rls";
import {
  boolean,
  index,
  integer,
  pgPolicy,
  text,
  timestamp,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export const <tableName> = createTable(
  "<table_name>", // Database table name (snake_case)
  {
    // Common columns (id, createdAt, updatedAt)
    ...commonColumns,
    
    // Custom columns
    name: varchar({ length: 255 }).notNull(),
    description: text(),
    
    // Owner reference (for user-owned tables)
    userId: uuid()
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
  },
  (t) => [
    // ---------- INDEXES ----------
    index("<table>_user_id_idx").on(t.userId),
    
    // ---------- RLS POLICIES ----------
    
    // Users can view their own records
    pgPolicy("<table>-select-own", {
      for: "select",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
    
    // Users can create records (ownership verified on insert)
    pgPolicy("<table>-insert-own", {
      for: "insert",
      to: authenticatedRole,
      withCheck: isOwner(t.userId),
    }),
    
    // Users can update their own records
    pgPolicy("<table>-update-own", {
      for: "update",
      to: authenticatedRole,
      using: isOwner(t.userId),
      withCheck: isOwner(t.userId),
    }),
    
    // Users can delete their own records
    pgPolicy("<table>-delete-own", {
      for: "delete",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
    
    // Service role has full access (for system operations)
    pgPolicy("<table>-all-service", {
      for: "all",
      to: serviceRole,
      using: allowAll,
      withCheck: allowAll,
    }),
  ],
);

Table Naming:

TypeScript export: camelCase or PascalCase
Database name (first arg): snake_case
Example: export const apiKeys = createTable("api_keys", ...)

Step 2: Column Types Reference

String Columns

// Fixed-length varchar (recommended for names, emails)
name: varchar({ length: 255 }).notNull(),
email: varchar({ length: 320 }).notNull().unique(),

// Unlimited text (for descriptions, content)
description: text(),
content: text().notNull(),

Number Columns

// Integer
count: integer().notNull().default(0),
age: integer(),

// With constraints
price: integer().notNull(), // Store cents, not dollars
quantity: integer().notNull().default(1),

Boolean Columns

// Boolean with default
enabled: boolean().notNull().default(true),
isPublic: boolean().notNull().default(false),

// Nullable boolean
isVerified: boolean(),

Date/Time Columns

// Timestamp with timezone (recommended)
createdAt: timestamp({ withTimezone: true }).notNull().defaultNow(),
updatedAt: timestamp({ withTimezone: true }).notNull().defaultNow(),

// Nullable timestamp (for optional dates)
publishedAt: timestamp({ withTimezone: true }),
expiresAt: timestamp({ withTimezone: true }),

UUID Columns

// Primary key (handled by commonColumns)
id: uuid().primaryKey().defaultRandom(),

// Foreign key reference
userId: uuid()
  .notNull()
  .references(() => users.id, { onDelete: "cascade" }),

// Optional foreign key
parentId: uuid().references(() => items.id, { onDelete: "set null" }),

JSON Columns

// JSON stored as text (use for simple key-value data)
metadata: text(), // Store JSON.stringify() data
settings: text().notNull().default("{}"),

// For Zod validation, use z.string() and transform
// settings: z.string().transform(val => JSON.parse(val))

Enum Columns

// Define enum first
export const statusEnum = pgEnum("status", ["draft", "published", "archived"]);

// Use in table
status: statusEnum().notNull().default("draft"),

Step 3: Foreign Key Patterns

User-Owned Table

userId: uuid()
  .notNull()
  .references(() => users.id, { onDelete: "cascade" }),

On Delete Options:

cascade - Delete records when user is deleted (recommended)
set null - Set to null when user is deleted
restrict - Prevent user deletion if records exist

Organization-Owned Table

import { organizations } from "@/db/tables";

organizationId: uuid()
  .notNull()
  .references(() => organizations.id, { onDelete: "cascade" }),

Many-to-One Relationship

// Projects belong to organizations
projectId: uuid()
  .notNull()
  .references(() => projects.id, { onDelete: "cascade" }),

Self-Referencing (Parent/Child)

// Comments can reply to other comments
parentId: uuid().references(() => comments.id, { onDelete: "cascade" }),

Step 4: Indexes

Add indexes for columns frequently used in WHERE clauses or JOINs:

(t) => [
  // Single column index
  index("<table>_user_id_idx").on(t.userId),
  index("<table>_status_idx").on(t.status),
  
  // Composite index (order matters!)
  index("<table>_user_status_idx").on(t.userId, t.status),
  
  // Unique index
  index("<table>_email_unique_idx").on(t.email).unique(),
]

Index Naming: Use pattern <table>_<column(s)>_idx for consistency.

Step 5: RLS Policies

Owner-Only Access (Most Common)

// Select own records
pgPolicy("<table>-select-own", {
  for: "select",
  to: authenticatedRole,
  using: isOwner(t.userId),
}),

// Insert own records
pgPolicy("<table>-insert-own", {
  for: "insert",
  to: authenticatedRole,
  withCheck: isOwner(t.userId),
}),

// Update own records
pgPolicy("<table>-update-own", {
  for: "update",
  to: authenticatedRole,
  using: isOwner(t.userId),
  withCheck: isOwner(t.userId),
}),

// Delete own records
pgPolicy("<table>-delete-own", {
  for: "delete",
  to: authenticatedRole,
  using: isOwner(t.userId),
}),

Organization-Based Access

import { isOrgMember } from "@/db/rls";

// Select if member of organization
pgPolicy("<table>-select-org", {
  for: "select",
  to: authenticatedRole,
  using: isOrgMember(t.organizationId),
}),

Admin Full Access

import { isAdmin } from "@/db/rls";

// Admins can do everything
pgPolicy("<table>-all-admin", {
  for: "all",
  to: adminRole,
  using: isAdmin,
  withCheck: isAdmin,
}),

Public Read, Owner Write

// Anyone can read
pgPolicy("<table>-select-public", {
  for: "select",
  to: authenticatedRole,
  using: allowAll,
}),

// Only owner can modify
pgPolicy("<table>-update-own", {
  for: "update",
  to: authenticatedRole,
  using: isOwner(t.userId),
  withCheck: isOwner(t.userId),
}),

Service Role (Always Include)

// Service role bypasses all policies (for system operations)
pgPolicy("<table>-all-service", {
  for: "all",
  to: serviceRole,
  using: allowAll,
  withCheck: allowAll,
}),

Always include service role policy - Required for migrations, seeds, and system operations.

Step 6: Add Relations (Optional)

For TypeScript type inference in joins, define relations in a separate file:

Create src/db/tables/<table-name>.relations.ts:

import { relations } from "drizzle-orm";
import { <tableName>, users } from "@/db/tables";

export const <tableName>Relations = relations(<tableName>, ({ one }) => ({
  // One-to-one or many-to-one
  user: one(users, {
    fields: [<tableName>.userId],
    references: [users.id],
  }),
}));

Step 7: Export Table

In src/db/tables/index.ts:

export * from "./<table-name>";
// export * from "./<table-name>.relations"; // If using relations

Step 8: Add Cache Tag (For Pagination)

If using table pagination with listTable(), add a cache tag in src/db/tags.ts:

export enum TableTags {
  // ...existing tags
  <tableName> = "<table-name>",
}

Step 9: Push Schema to Database

Apply schema changes to your database:

npm run db:push

This will:

Create the table if it doesn't exist
Add/modify columns
Create indexes
Apply RLS policies

Production Migrations: Use npm run db:generate and npm run db:migrate for production deployments instead of db:push.

Complete Example: Notifications Table

import { commonColumns, createTable } from "@/db/table-utils";
import { users } from "@/db/tables";
import {
  authenticatedRole,
  isOwner,
  serviceRole,
  allowAll,
} from "@/db/rls";
import {
  boolean,
  index,
  pgPolicy,
  text,
  timestamp,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

// Define enum
export const notificationTypeEnum = pgEnum("notification_type", [
  "info",
  "success",
  "warning",
  "error",
]);

export const notifications = createTable(
  "notifications",
  {
    ...commonColumns,
    
    // Content
    title: varchar({ length: 255 }).notNull(),
    message: text().notNull(),
    type: notificationTypeEnum().notNull().default("info"),
    
    // Metadata
    read: boolean().notNull().default(false),
    readAt: timestamp({ withTimezone: true }),
    
    // References
    userId: uuid()
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    
    // Optional link
    link: text(),
  },
  (t) => [
    // Indexes
    index("notifications_user_id_idx").on(t.userId),
    index("notifications_user_read_idx").on(t.userId, t.read),
    index("notifications_created_at_idx").on(t.createdAt),
    
    // RLS Policies
    pgPolicy("notifications-select-own", {
      for: "select",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
    pgPolicy("notifications-update-own", {
      for: "update",
      to: authenticatedRole,
      using: isOwner(t.userId),
      withCheck: isOwner(t.userId),
    }),
    pgPolicy("notifications-delete-own", {
      for: "delete",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
    pgPolicy("notifications-all-service", {
      for: "all",
      to: serviceRole,
      using: allowAll,
      withCheck: allowAll,
    }),
  ],
);

Common Patterns

Soft Delete

deletedAt: timestamp({ withTimezone: true }),

// RLS: Exclude soft-deleted records
using: sql`${isOwner(t.userId)} AND ${t.deletedAt} IS NULL`,

Versioning

version: integer().notNull().default(1),

Ordering

order: integer().notNull().default(0),

// Index for sorting
index("<table>_order_idx").on(t.order),

Timestamps for State Changes

publishedAt: timestamp({ withTimezone: true }),
archivedAt: timestamp({ withTimezone: true }),
completedAt: timestamp({ withTimezone: true }),

Post-Creation Checklist

Table Definition
- Table file created in src/db/tables/
- Common columns included (...commonColumns)
- All custom columns defined
- Foreign keys properly referenced
Indexes & Policies
- Indexes added for foreign keys
- Indexes added for frequently queried columns
- RLS policies defined (select, insert, update, delete)
- Service role policy included
Export & Tags
- Table exported in src/db/tables/index.ts
- Cache tag added in src/db/tags.ts (if using pagination)
Database
- Run npm run db:push successfully
- Verify table exists in database
- Test RLS policies work correctly
Integration
- Feature functions.ts can query the table
- Schema types match table structure

Troubleshooting

Migration Fails

# Reset local database
npm run db:reset

# Try push again
npm run db:push

RLS Policies Block Queries

Check that:

User context is set (setUserId() called)
Policies match ownership column names
Service role policy exists for system operations

Type Errors

# Regenerate types
npm run db:generate

Real-World Examples

See these tables for reference:

src/db/tables/api-keys.ts - User-owned with complex fields
src/db/tables/organizations.ts - Standalone entity
src/db/tables/memberships.ts - Join table with composite key

Table created! Your database structure is ready. Next, create functions to interact with the table.

Create Database Table

On this page

Create Database Table

On this page