Database Setup

Database Setup

1. Verify PostgreSQL is Running

macOS:

pg_isready
# Should output: /tmp/.s.PGSQL.5432: accepting connections

Windows:

# Check if service is running
Get-Service postgresql*
# Or test connection
psql -U postgres -c "SELECT version();"

Docker:

# Check if container is running
docker-compose ps
# Or if using docker run:
docker ps | grep postgres

# Test connection
docker exec -it zooly-postgres pg_isready -U postgres
# Or if using docker run:
docker exec -it postgres-pgvector pg_isready -U postgres

# Test connection with psql
docker exec -it zooly-postgres psql -U postgres -c "SELECT version();"

2. Create PostgreSQL User (macOS)

If you encounter an error like role "your_username" does not exist when running createdb, you need to create a PostgreSQL user that matches your macOS username. You have two options:

Option 1: Create a PostgreSQL role matching your macOS username (Recommended)

This allows you to use createdb without specifying -U postgres:

# Connect to PostgreSQL as the postgres superuser
psql -U postgres

# Create a role matching your macOS username (replace 'your_username' with your actual macOS username)
CREATE ROLE your_username WITH LOGIN SUPERUSER CREATEDB CREATEROLE;

# Exit psql
\q

# Now you can use createdb without the -U flag
createdb zooly2_local

Option 2: Use the postgres user directly

Alternatively, you can always use the postgres user when creating databases:

createdb -U postgres zooly2_local

Or use psql directly:

psql -U postgres -c "CREATE DATABASE zooly2_local;"

3. Create the Database

macOS:

# If you created a role matching your username (Option 1):
createdb zooly2_local

# Or if using the postgres user (Option 2):
createdb -U postgres zooly2_local

Windows:

# Using psql
psql -U postgres -c "CREATE DATABASE zooly2_local;"

# Or using createdb (if in PATH)
createdb -U postgres zooly2_local

Docker:

# If using docker-compose with POSTGRES_DB set, database is created automatically
# Otherwise, create it manually:
docker exec -it zooly-postgres psql -U postgres -c "CREATE DATABASE zooly2_local;"
# Or if using docker run:
docker exec -it postgres-pgvector psql -U postgres -c "CREATE DATABASE zooly2_local;"

4. Enable pgvector Extension

After creating the database, enable the pgvector extension:

macOS:

psql -U postgres -d zooly2_local -c "CREATE EXTENSION IF NOT EXISTS vector;"

Windows:

psql -U postgres -d zooly2_local -c "CREATE EXTENSION IF NOT EXISTS vector;"

Docker:

docker exec -it zooly-postgres psql -U postgres -d zooly2_local -c "CREATE EXTENSION IF NOT EXISTS vector;"
# Or if using docker run:
docker exec -it postgres-pgvector psql -U postgres -d zooly2_local -c "CREATE EXTENSION IF NOT EXISTS vector;"

Or connect to the database and run:

CREATE EXTENSION IF NOT EXISTS vector;

5. Verify Database Creation

# macOS
psql -l | grep zooly2_local

# Windows
psql -U postgres -l | findstr zooly2_local

List all databases:

# macOS
psql -l

# Windows
psql -U postgres -l

# Docker
docker exec -it zooly-postgres psql -U postgres -l
# Or if using docker run:
docker exec -it postgres-pgvector psql -U postgres -l

6. Configure Connection String

Update your .env.local file with the correct connection string:

# macOS (no password required for local user with Homebrew)
DATABASE_URL="postgres://postgres:password@localhost:5432/zooly2_local?sslmode=disable"

# Windows (use the password you set during installation)
DATABASE_URL="postgres://postgres:YOUR_PASSWORD@localhost:5432/zooly2_local?sslmode=disable"

# Docker (uses password from docker-compose.yml or docker run command)
DATABASE_URL="postgres://postgres:password@localhost:5432/zooly2_local?sslmode=disable"

7. Run Migrations

# Navigate to the app that manages the database
cd apps/zooly-app
npm run migrate