Database Setup

Overview

AI Studio uses PostgreSQL as its primary database and Drizzle ORM for type-safe database operations. This guide covers database setup, schema management, and common operations.

Database Stack

Database: PostgreSQL 15+
ORM: Drizzle ORM 0.45+
Hosting: Supabase (recommended) or self-hosted PostgreSQL
Connection: Transaction pooler (port 6543) for serverless compatibility

Quick Setup

Configure database connection

Add your PostgreSQL connection string to .env.local:

DATABASE_URL=postgresql://postgres.xxxx:[email protected]:6543/postgres

For Supabase, use the Transaction pooler URL (port 6543).

Push schema to database

Create all database tables from the Drizzle schema:

pnpm db:push

This command reads lib/db/schema.ts and syncs it to your database.

Verify setup

Open Drizzle Studio to inspect your database:

pnpm db:studio

This launches a web UI at https://local.drizzle.studio

Drizzle Configuration

AI Studio’s Drizzle configuration is defined in drizzle.config.ts:

drizzle.config.ts

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./lib/db/schema.ts",
  out: "./drizzle",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Configuration breakdown:

dialect: Database type (PostgreSQL)
schema: Location of schema definitions
out: Directory for generated migrations
dbCredentials.url: Connection string from environment

Database Client

The database client is configured in lib/db/index.ts:

lib/db/index.ts

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

// Supabase Transaction mode (port 6543) requires prepare: false
const client = postgres(process.env.DATABASE_URL!, { prepare: false });

export const db = drizzle(client, { schema });

The prepare: false option is required when using Supabase’s Transaction pooler (port 6543) because prepared statements are not supported in transaction pooling mode.

Why prepare: false?

Supabase Transaction pooler doesn’t support prepared statements
Required for serverless environments (Vercel, Netlify)
Minimal performance impact for typical query patterns

Database Commands

AI Studio provides several npm scripts for database operations:

Schema Push (Development)

pnpm db:push

What it does:

Reads schema from lib/db/schema.ts
Applies changes directly to database
Creates/modifies tables, columns, indexes
No migration files generated

When to use:

Local development and prototyping
Quick schema iterations
Initial database setup

Schema push does not create migration files. For production deployments, use migration-based workflow with db:generate and db:migrate.

Generate Migrations

pnpm db:generate

What it does:

Compares lib/db/schema.ts to current database state
Generates SQL migration files in drizzle/ directory
Creates timestamped migration files
Preserves migration history

When to use:

Before deploying to production
When working in teams (commit migrations to Git)
When you need rollback capability

Example output:

drizzle/
├── 0000_init_schema.sql
├── 0001_add_video_table.sql
└── 0002_add_workspace_members.sql

Apply Migrations

pnpm db:migrate

What it does:

Runs pending migrations from drizzle/ directory
Updates database schema to latest version
Tracks applied migrations in database

When to use:

Production deployments
CI/CD pipelines
Team environments with shared migrations

Drizzle Studio

pnpm db:studio

What it does:

Launches web-based database GUI
Provides visual schema explorer
Allows data browsing and editing
Shows table relationships

Features:

View all tables and relationships
Browse and filter data
Run custom SQL queries
Edit records inline
Export data as JSON/CSV

Drizzle Studio is excellent for debugging and exploring your database schema during development.

Database Schema

The complete database schema is defined in lib/db/schema.ts. Here’s an overview of the main tables:

Core Tables

// Users (managed by Better Auth)
- id: text (primary key)
- email: text (unique)
- name: text
- createdAt: timestamp
- updatedAt: timestamp

// Workspaces
- id: text (primary key)
- name: text
- slug: text (unique)
- ownerId: text (foreign key → users)
- createdAt: timestamp

// Projects
- id: text (primary key)
- name: text
- workspaceId: text (foreign key → workspaces)
- userId: text (foreign key → users)
- status: text (pending, processing, completed, failed)
- createdAt: timestamp

// Images
- id: text (primary key)
- projectId: text (foreign key → projects)
- originalUrl: text
- editedUrl: text (nullable)
- status: text
- styleTemplate: text
- createdAt: timestamp

Relationships

users
  ├── workspaces (one-to-many)
  ├── projects (one-to-many)
  └── workspace_members (one-to-many)

workspaces
  ├── projects (one-to-many)
  ├── members (many-to-many via workspace_members)
  └── invitations (one-to-many)

projects
  └── images (one-to-many)

Common Operations

Querying Data

Example: Fetch user projects

import { db } from "@/lib/db";
import { projects, images } from "@/lib/db/schema";
import { eq } from "drizzle-orm";

// Get all projects for a user
const userProjects = await db
  .select()
  .from(projects)
  .where(eq(projects.userId, userId));

// Get project with images (with relation)
const projectWithImages = await db.query.projects.findFirst({
  where: eq(projects.id, projectId),
  with: {
    images: true,
  },
});

Inserting Data

Example: Create new project

import { db } from "@/lib/db";
import { projects } from "@/lib/db/schema";

const newProject = await db
  .insert(projects)
  .values({
    id: nanoid(),
    name: "Modern Living Room",
    workspaceId: workspaceId,
    userId: userId,
    status: "pending",
  })
  .returning();

Updating Data

Example: Update project status

import { db } from "@/lib/db";
import { projects } from "@/lib/db/schema";
import { eq } from "drizzle-orm";

await db
  .update(projects)
  .set({ status: "completed" })
  .where(eq(projects.id, projectId));

Deleting Data

Example: Delete project

import { db } from "@/lib/db";
import { projects } from "@/lib/db/schema";
import { eq } from "drizzle-orm";

await db
  .delete(projects)
  .where(eq(projects.id, projectId));

Migration Workflow

Development Workflow

For rapid local development:

Modify schema

Edit lib/db/schema.ts to add/modify tables:

export const videos = pgTable("videos", {
  id: text("id").primaryKey(),
  projectId: text("project_id").references(() => projects.id),
  // ... more columns
});

Push changes

pnpm db:push

Changes are applied immediately to your local database.

Verify in Studio

pnpm db:studio

Inspect the new table structure.

Production Workflow

For team collaboration and production deployments:

Modify schema

Edit lib/db/schema.ts with your changes.

Generate migration

pnpm db:generate

Creates a migration file in drizzle/ directory.

Review migration

Check the generated SQL in drizzle/0001_*.sql:

CREATE TABLE IF NOT EXISTS "videos" (
  "id" text PRIMARY KEY NOT NULL,
  "project_id" text,
  -- ...
);

Apply migration

pnpm db:migrate

Runs the migration against your database.

Commit to Git

git add drizzle/ lib/db/schema.ts
git commit -m "feat: add videos table"

Database Providers

Supabase (Recommended)

Setup
Benefits
Pricing

Create project

Go to supabase.com/dashboard
Click “New Project”
Choose a name, password, and region
Wait for provisioning (~2 minutes)

Get connection string

Go to Settings → Database
Scroll to “Connection string”
Select “Transaction” mode (port 6543)
Copy the connection string

Configure AI Studio

Add to .env.local:

DATABASE_URL=postgresql://postgres.[ref]:[password]@[region].pooler.supabase.com:6543/postgres

Local PostgreSQL

macOS
Linux
Docker

Using Homebrew:

brew install postgresql@15
brew services start postgresql@15
createdb proppi_dev

Connection string:

DATABASE_URL=postgresql://localhost:5432/proppi_dev

Using apt:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo -u postgres createdb proppi_dev

Connection string:

DATABASE_URL=postgresql://postgres:password@localhost:5432/proppi_dev

Using Docker Compose:

docker-compose.yml

version: '3.8'
services:
  postgres:
    image: postgres:15-alpine
    environment:
      POSTGRES_DB: proppi_dev
      POSTGRES_PASSWORD: password
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
volumes:
  postgres_data:

docker-compose up -d

Connection string:

DATABASE_URL=postgresql://postgres:password@localhost:5432/proppi_dev

Troubleshooting

Connection Errors

Error: “connect ECONNREFUSED”

Verify PostgreSQL is running
Check port in connection string (6543 for Supabase, 5432 for local)
Ensure no firewall blocking the connection

Error: “password authentication failed”

Verify password in DATABASE_URL
URL-encode special characters: @ → %40, # → %23
For Supabase, reset database password in dashboard

Error: “prepared statement not supported”

Add { prepare: false } to postgres client options
Verify you’re using Transaction pooler (port 6543) not Session pooler

Schema Sync Issues

Schema push fails with “relation already exists”

# Drop and recreate database (local only!)
dropdb proppi_dev
createb proppi_dev
pnpm db:push

Migration conflicts

# Reset migrations (local only!)
rm -rf drizzle/
pnpm db:generate
pnpm db:migrate

Performance Issues

Slow queries

Add indexes to frequently queried columns
Use EXPLAIN ANALYZE to debug query plans
Enable connection pooling (Supabase Transaction mode)

Connection pool exhausted

Increase pool size in Supabase dashboard
Close connections properly in server actions
Consider using Session pooler for long-running queries

Best Practices

Use migrations for production

Always generate and commit migrations for production changes
Test migrations on staging before production
Keep migrations small and focused
Never edit migration files after they’ve been applied

Type safety

Let Drizzle infer types from schema
Use typeof schema.tableName.$inferSelect for row types
Use typeof schema.tableName.$inferInsert for insert types
Avoid manual type casting

Query optimization

Use with for eager loading relations
Add indexes on foreign keys and frequently queried columns
Use limit() for pagination
Batch operations when possible

Schema design

Use text for IDs (nanoid) instead of auto-increment
Add createdAt and updatedAt timestamps
Use notNull() for required fields
Define foreign key constraints for data integrity

Next Steps

API Reference

Learn how to use database queries in API routes and server actions

Deployment

Deploy your database and application to production

Setup

Architecture

Guides

Overview

Database Stack

Quick Setup

Drizzle Configuration

Database Client

Database Commands

Schema Push (Development)

Generate Migrations

Apply Migrations

Drizzle Studio

Database Schema

Core Tables

Relationships

Common Operations

Querying Data

Inserting Data

Updating Data

Deleting Data

Migration Workflow

Development Workflow

Production Workflow

Database Providers

Supabase (Recommended)

Local PostgreSQL

Troubleshooting

Connection Errors

Schema Sync Issues

Performance Issues

Best Practices

Next Steps

API Reference

Deployment

Build docs developers (and LLMs) love

Setup

Architecture

Guides

​Overview

​Database Stack

​Quick Setup

​Drizzle Configuration

​Database Client

​Database Commands

​Schema Push (Development)

​Generate Migrations

​Apply Migrations

​Drizzle Studio

​Database Schema

​Core Tables

​Relationships

​Common Operations

​Querying Data

​Inserting Data

​Updating Data

​Deleting Data

​Migration Workflow

​Development Workflow

​Production Workflow

​Database Providers

​Supabase (Recommended)

​Local PostgreSQL

​Troubleshooting

​Connection Errors

​Schema Sync Issues

​Performance Issues

​Best Practices

​Next Steps

API Reference

Deployment

Build docs developers (and LLMs) love

Overview

Database Stack

Quick Setup

Drizzle Configuration

Database Client

Database Commands

Schema Push (Development)

Generate Migrations

Apply Migrations

Drizzle Studio

Database Schema

Core Tables

Relationships

Common Operations

Querying Data

Inserting Data

Updating Data

Deleting Data

Migration Workflow

Development Workflow

Production Workflow

Database Providers

Supabase (Recommended)

Local PostgreSQL

Troubleshooting

Connection Errors

Schema Sync Issues

Performance Issues

Best Practices

Next Steps