Database Management

The application uses PostgreSQL as its primary database, with two main components:

• Drizzle ORM for the main application schema
• Payload CMS for content management

Schema Organization

The database is organized into multiple schemas:

• public (default): Contains the main application tables managed by Drizzle ORM
• payload: Contains Payload CMS tables
• drizzle: Contains migration metadata

Configuration

Drizzle Configuration

The Drizzle ORM configuration is defined in drizzle.config.ts:

import type { Config } from "drizzle-kit";

export default {
  schema: "./src/server/db/schema.ts",
  dialect: "postgresql",
  dbCredentials: {
    url: env.DATABASE_URL,
  },
  tablesFilter: [`${env.DB_PREFIX}_*`],
  out: "./src/migrations",
  verbose: true,
  strict: true,
} satisfies Config;

Environment Variables

Required environment variables for database configuration:

DATABASE_URL="postgresql://user:password@localhost:5432/database"
DB_PREFIX="shipkit"  # Prefix for all database tables

Database Commands

Generate Migrations

Generate new migrations based on schema changes:

pnpm db.generate

Push Schema Changes

Push schema changes directly to the database:

pnpm db.push

Sync Database

Synchronize both Drizzle and Payload schemas:

pnpm db.sync

The sync process:

1. Runs Drizzle migrations
2. Initializes Payload CMS
3. Optionally runs database seeding (if SEED_DB=true)

Database Studio

Launch Drizzle Studio to view and manage database:

pnpm db.studio

Migrations

Migrations are stored in the src/migrations directory and are managed by Drizzle Kit.

Migration Process

1. Make changes to the schema in src/server/db/schema.ts
2. Generate migrations:

   pnpm db.generate
   

3. Review generated migrations in src/migrations
4. Apply migrations:

   pnpm db.sync
   

Migration Files

• SQL migrations: src/migrations/*.sql
• Migration metadata: src/migrations/meta/_journal.json

Schema Management

Main Schema File

The main schema is defined in src/server/db/schema.ts and includes:

• Table definitions
• Relations
• Indexes
• Enums

Schema Conventions

1. Use timestamps for all temporal fields:

   createdAt: timestamp("created_at").defaultNow(),
   updatedAt: timestamp("updated_at").$onUpdate(() => new Date()),
   

2. Use UUIDs for primary keys:

   id: varchar("id").$defaultFn(() => crypto.randomUUID()),
   

3. Use soft deletes where appropriate:

   deletedAt: timestamp("deleted_at"),
   

Error Handling

The sync process includes error handling for common scenarios:

try {
  await migrate(db, {
    migrationsFolder: path.join(process.cwd(), "src/migrations"),
  });
} catch (error) {
  if (error instanceof Error && error.message.includes("already exists")) {
    console.warn("⚠️ Some database objects already exist, continuing...");
  } else {
    throw error;
  }
}

Best Practices

1. Schema Changes

   • Always generate migrations for schema changes
   • Review migrations before applying
   • Test migrations in development first

2. Data Safety

   • Use soft deletes when possible
   • Add appropriate indexes for frequently queried fields
   • Include proper foreign key constraints

3. Performance

   • Keep migrations small and focused
   • Add indexes for frequently used queries
   • Use appropriate field types

4. Development Workflow

   • Use db.push for rapid development
   • Use proper migrations for production changes
   • Always backup database before migrations

Troubleshooting

Common Issues

1. Migration Conflicts

   • Error: "relation already exists"
   • Solution: Drop the conflicting objects or handle "already exists" errors

2. Schema Sync Issues

   • Error: "type already exists"
   • Solution: The sync process handles this automatically

3. Connection Issues

   • Check DATABASE_URL format
   • Verify database permissions
   • Check network connectivity

Debug Commands

# View current schema
pnpm db.studio

# Check migration status
pnpm drizzle-kit check

# Drop all tables (development only)
pnpm drizzle-kit drop

Database Reset

Drop All Tables

To completely reset your database and start fresh:

# Drop all tables (development only)
pnpm db.drop

# Then re-sync the database
pnpm db.sync

⚠️ WARNING: This will permanently delete all data. Use with caution and never in production.

The reset process:

1. Drops all tables in both Drizzle and Payload schemas
2. Removes migration history
3. Re-runs all migrations
4. Re-initializes Payload CMS
5. Optionally runs seeds if SEED_DB=true

Quick Reset Script

For development, you can use this one-liner to reset and resync:

pnpm db.drop && pnpm db.sync