DB Setup Overview

PreviousNext

Overview of database setup for local development

This section covers everything you need to set up and configure PostgreSQL with pgvector for local development and .

Drizzle ORM

We use Drizzle ORM for database access for several important reasons:

  • Better support for join and complex queries: Drizzle provides excellent support for complex SQL queries including joins, subqueries, and advanced query patterns that are essential for multi-tenant applications
  • PostgreSQL Vector support: Drizzle has strong support for pgvector, which is crucial for applications that need vector similarity search and AI/ML features

These features make Drizzle the ideal choice for our use case, providing both type safety and the flexibility needed for complex database operations.

Database Migrations

Working with Migrations

Drizzle uses migrations to manage database schema changes. Migrations are managed in the packages/db package and automatically applied during Vercel builds.

Generate Migrations

After making changes to your schema files in packages/db/src/schema/, generate migration files:

cd packages/db
npm run db:generate
# or
set -a && source ../../../.env.local && npm run db:generate

This command:

  • Analyzes your schema files
  • Compares them with the current database state
  • Generates migration SQL files in packages/db/drizzle/
  • Creates the necessary migration metadata

Important: Migration SQL files are committed to git so they can be applied during production deployments.

Apply Migrations Locally

For local development, apply migrations manually:

cd packages/db
npm run db:migrate:deploy
# or
set -a && source ../../../.env.local && npm run db:migrate:deploy

This will:

  • Execute pending migrations in order
  • Update the database schema
  • Record migration history in the drizzle.__drizzle_migrations table

Automatic Migrations on Vercel

Migrations are automatically applied during the build process on Vercel. The build script in apps/zooly-app runs migrations before building the Next.js app:

# This happens automatically during `npm run build` in zooly-app
node ../../packages/db/dist/migrate.js && next build

No manual migration step is needed for production deployments - just commit your migration files and push to trigger a new build.

Push Schema Changes (Development Only)

For rapid development, you can push schema changes directly without generating migration files:

cd packages/db
npm run db:push

Development Best Practices

What You'll Learn

  • How to install PostgreSQL on macOS, Windows, or using Docker
  • How to configure and manage your PostgreSQL service
  • How to create and set up your local database
  • How to troubleshoot common issues
  • Useful commands for database management
  • How to work with remote databases

Quick Start

If you're new to PostgreSQL setup, start with installation for your platform and follow the guides in order.

Installation Guides

Choose the installation method for your platform:

Sections