database-patterns

Database Patterns & Best Practices

Schema Design Patterns

Table Creation Pattern

Use the createTable function with consistent naming:

const createTable = pgTableCreator((name) => `${env?.DB_PREFIX ?? ""}_${name}`);

export const waitlistEntries = createTable(
  "waitlist_entry",
  {
    id: serial("id").primaryKey(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    // ... other fields
  },
  (table) => ({
    emailIdx: index("waitlist_email_idx").on(table.email),
    createdAtIdx: index("waitlist_created_at_idx").on(table.createdAt),
  })
);

Field Naming Conventions

• Use snake_case for database columns
• Use camelCase for TypeScript properties
• Include length constraints for varchar fields
• Use descriptive index names

Required Field Patterns

// ✅ Good patterns
id: serial("id").primaryKey()
email: varchar("email", { length: 255 }).notNull().unique()
createdAt: timestamp("created_at", { withTimezone: true })
  .default(sql`CURRENT_TIMESTAMP`)
  .notNull()
updatedAt: timestamp("updated_at", { withTimezone: true })
  .$onUpdate(() => new Date())

// Optional fields
company: varchar("company", { length: 255 })  // No .notNull()
metadata: text("metadata").default("{}")      // Default JSON

Index Strategy

Performance Indexes

Create indexes for common query patterns:

(table) => ({
  // Single column indexes
  emailIdx: index("waitlist_email_idx").on(table.email),
  createdAtIdx: index("waitlist_created_at_idx").on(table.createdAt),

  // Conditional indexes for boolean fields
  isNotifiedIdx: index("waitlist_is_notified_idx").on(table.isNotified),

  // Composite indexes for multi-column queries
  userCompanyIdx: index("user_company_idx").on(table.userId, table.company),
})

Index Naming Convention

• Format: {table}_{column}_{type}_idx
• Examples: waitlist_email_idx, user_created_at_idx

Type Generation

Infer Types from Schema

export type WaitlistEntry = typeof waitlistEntries.$inferSelect;
export type NewWaitlistEntry = typeof waitlistEntries.$inferInsert;

// Use in service functions
export async function addWaitlistEntry(
  data: Omit<NewWaitlistEntry, "id" | "createdAt" | "updatedAt">
): Promise<WaitlistEntry> {
  // implementation
}