# PostgreSQL

> The starter kit uses PostgreSQL with Drizzle ORM for type-safe database operations.

The starter kit uses **PostgreSQL** as the database, managed through **Drizzle ORM** for excellent developer experience and type safety.

## Database Provider

You can use PostgreSQL from various providers:

- **Neon** - Serverless PostgreSQL with generous free tier
- **Supabase** - Open source Firebase alternative with PostgreSQL
- **Railway** - Simple PostgreSQL hosting
- **Render** - Managed PostgreSQL databases
- **Local PostgreSQL** - For development

## Drizzle ORM

The starter kit uses [Drizzle ORM](https://orm.drizzle.team/) instead of raw SQL for several benefits:

- **Type safety** - Full TypeScript support with auto-completion
- **Migrations** - Automated schema migrations
- **Relations** - Easy relationship management
- **Developer experience** - Intuitive query builder

## Database Schema

All database schemas are located in `packages/layer-auth/server/db/schema/`:

- `auth.ts` - User, session, account tables (better-auth)
- `testimonial.ts` - Testimonials feature
- `waitlist.ts` - Waitlist management
- `file.ts` - File storage metadata
- `banner.ts` - App banner configuration

## Database Utilities

Import from layer-auth server utils:

```typescript
import { and, desc, eq, useDb } from 'layer-auth/server/utils/db'

const db = useDb()
const users = await db.select()
  .from(schema.user)
  .where(eq(schema.user.email, email))
```

## Commands

Run these commands from the **monorepo root**:

```bash
# Generate migrations from schema changes
pnpm db:generate

# Apply migrations to database
pnpm db:migrate

# Open Drizzle Studio (database GUI)
pnpm db:studio
```

## Environment Variables

Set your PostgreSQL connection string in `.env`:

```bash
NUXT_PRIVATE_DATABASE_URL="postgresql://user:password@host:5432/dbname"
```

For local development, you might use:

```bash
NUXT_PRIVATE_DATABASE_URL="postgresql://postgres:postgres@localhost:5432/nuxtstarterkit"
```

## Migration Workflow

1. Make changes to schema files in `packages/layer-auth/server/db/schema/`
2. Run `pnpm db:generate` to create migration files
3. Review generated migrations in `packages/layer-auth/server/db/migrations/`
4. Run `pnpm db:migrate` to apply migrations
5. Commit both schema and migration files

## Drizzle Studio

Drizzle Studio provides a visual interface to browse and edit your database:

```bash
pnpm db:studio
```

This opens a web interface where you can:

- Browse tables and data
- Run queries
- Edit records
- View relationships

## Type Safety

Drizzle generates TypeScript types from your schema. Import and use them:

```typescript
import { session, user } from 'layer-auth/server/db/schema'
import type { Session, User } from 'layer-auth/shared/types/db'

// Use typed queries
const users: Array<User> = await db.select().from(user)
```

## Best Practices

- Always use migrations - never modify the database schema directly
- Keep schema files organized by feature
- Use meaningful column and table names
- Add indexes for frequently queried columns
- Use transactions for multiple related operations