Enhance the Database

Database Schema

The database schema is defined in src/db/schema/ using Drizzle ORM. All tables use PostgreSQL.

Creating a New Table

1. Define the Schema

Create a new file in src/db/schema/ (or add to existing file):

// src/db/schema/product.ts
import { pgTable, text, serial, timestamp } from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'

export const product = pgTable('product', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  createdAt: timestamp('created_at', { mode: 'date' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'date' }).defaultNow(),
})

export const productRelations = relations(product, ({ many }) => ({
  // Define relationships here
}))

2. Add to Schema Index

In src/db/schema/index.ts, export your new table:

export * from './product'

3. Generate Migration

pnpm db:generate

This creates a new migration file in drizzle/.

4. Push to Database

pnpm db:push

Or use migrations:

pnpm db:migrate

Adding Relationships

Use Drizzle's relations() to define relationships:

import { relations } from 'drizzle-orm'

export const userRelations = relations(user, ({ many, one }) => ({
  products: many(product),
  profile: one(userProfile),
}))

export const productRelations = relations(product, ({ one }) => ({
  creator: one(user, {
    fields: [product.createdBy],
    references: [user.id],
  }),
}))

Foreign Keys

Reference other tables with foreign keys:

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core'

export const product = pgTable('product', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  userId: integer('user_id').references(() => user.id),
})

Modifying Existing Tables

To add a column or make other changes:

Edit the table definition in src/db/schema/
Run pnpm db:generate
Run pnpm db:push

Indexes

Add indexes for better query performance:

import { pgTable, index } from 'drizzle-orm/pg-core'

export const product = pgTable(
  'product',
  {
    id: serial('id').primaryKey(),
    userId: integer('user_id'),
    name: text('name'),
  },
  (table) => ({
    userIdx: index('user_idx').on(table.userId),
  })
)

Unique Constraints

Enforce uniqueness at the database level:

import { pgTable, serial, text, unique } from 'drizzle-orm/pg-core'

export const profile = pgTable(
  'profile',
  {
    id: serial('id').primaryKey(),
    handle: text('handle').notNull(),
  },
  (table) => ({
    handleUnique: unique().on(table.handle),
  })
)

Querying

Use Drizzle in server functions:

import { db } from '@/db'
import { product } from '@/db/schema'
import { eq } from 'drizzle-orm'

export const getProduct = createServerFn({ method: 'GET' }).handler(
  async ({ data }: { data: { id: number } }) => {
    return db.query.product.findFirst({
      where: eq(product.id, data.id),
    })
  }
)

Best Practices

Keep sensitive data in the database, not client-side
Use timestamps (createdAt, updatedAt) for auditing
Index frequently queried columns
Use relationships to maintain referential integrity
Keep generated migrations for historical record
Test schema changes on a backup before production

On this page