Drizzle Studio

Drizzle Studio is a powerful database management interface that makes working with your database intuitive and efficient. Learn how to use it for development, debugging, and data management.

Getting Started with Drizzle Studio

Launch Studio

# Start Drizzle Studio
pnpm db:studio

# Output:
# Drizzle Studio is running on http://localhost:4983

Studio Interface Overview

Main sections:

• Left Sidebar: Table list with search and filtering
• Main Panel: Data grid with editing capabilities
• Query Tab: SQL query interface
• Schema Tab: Visual schema explorer
• Relationships: Interactive relationship diagram

Drizzle Studio Interface:

Browsing and Filtering Data

Table Navigation

📊 Tables (in sidebar)
├── 👥 users (45 rows)
├── 🏢 organizations (12 rows)
├── 👤 members (67 rows)
├── 📝 projects (23 rows)
├── ✅ tasks (156 rows)
└── 📧 notifications (234 rows)

Search: "user"
Results:
├── 👥 users
├── ⚙️ user_settings
└── 🔔 user_notifications

Filter examples:
- email LIKE '%@gmail.com'
- created_at > '2024-01-01'
- role = 'admin'
- name IS NOT NULL

Data Grid Features

Column operations:

• Sort: Click column headers to sort ascending/descending
• Resize: Drag column borders to adjust width
• Hide/Show: Right-click headers to hide/show columns
• Search: Use the search bar to find specific values

Row operations:

• Select: Click rows to select (supports multi-select with Ctrl/Cmd)
• Edit: Double-click cells to edit values inline
• Insert: Use the "+" button to add new rows
• Delete: Select rows and use Delete key

Editing Data

Inline Editing

Before: John Doe
After:  John Smith ✏️

✅ Valid: "user@example.com" (email format)
❌ Invalid: "not-an-email" (constraint violation)

Adding New Records

1. Click the "Add Row" button (+)
2. Fill in the required fields
3. Studio auto-generates UUIDs for ID fields
4. Foreign key fields show dropdown with valid options
5. Save with Enter or click outside the row

Example - Creating a new user:

id: [auto-generated UUID]
name: "Jane Developer"
email: "jane@company.com"
role: "user" [dropdown: user, admin, moderator]
created_at: [auto-set to now()]

Bulk Operations

Select multiple rows:

• Ctrl+Click (Cmd+Click) for individual selection
• Shift+Click for range selection
• Ctrl+A (Cmd+A) to select all visible rows

Bulk actions:

• Delete: Select rows → Delete key → Confirm
• Export: Select rows → Export button → Choose format (JSON, CSV)
• Duplicate: Select row → Duplicate button → Modify values

Relationship Visualization

Exploring Relations

Click relationship arrows to navigate:

users table
├── → members (user_id)
│   └── → organizations (organization_id)
│       └── → projects (organization_id)
│           └── → tasks (project_id)
├── → user_settings (user_id)
└── → notifications (user_id)

Relationship Panel

The relationship panel shows:

• One-to-Many: User → Multiple Organizations
• Many-to-Many: Posts ↔ Hashtags (through junction table)
• One-to-One: User → User Settings

Interactive features:

• Click related record counts to filter
• Hover relationships to highlight connections
• Right-click to create new related records

SQL Query Interface

Custom Queries

-- Find all admin users
SELECT name, email, created_at
FROM users
WHERE role = 'admin'
ORDER BY created_at DESC;

-- Count users by role
SELECT role, COUNT(*) as count
FROM users
GROUP BY role;

-- Users with their organizations
SELECT
  u.name as user_name,
  u.email,
  o.name as org_name,
  m.role as member_role
FROM users u
JOIN members m ON u.id = m.user_id
JOIN organizations o ON m.organization_id = o.id
WHERE u.active = true;

-- Project completion statistics
SELECT
  p.name as project,
  COUNT(t.id) as total_tasks,
  COUNT(CASE WHEN t.status = 'completed' THEN 1 END) as completed,
  ROUND(
    COUNT(CASE WHEN t.status = 'completed' THEN 1 END) * 100.0 / COUNT(t.id),
    2
  ) as completion_percentage
FROM projects p
LEFT JOIN tasks t ON p.id = t.project_id
GROUP BY p.id, p.name
ORDER BY completion_percentage DESC;

Query Features

Syntax highlighting: Full SQL syntax highlighting and autocomplete Execution: Run queries with Ctrl+Enter (Cmd+Enter) Results: View results in formatted tables Export: Export query results to JSON, CSV, or SQL History: Access previous queries from history panel

Studio Development Workflow

Database Seeding

View seed data results:

# Run seed command
pnpm db:seed

# Then open Studio to verify:
pnpm db:studio

Inspect seeded data:

• Users: Check demo accounts (admin@example.com, user@example.com)
• Organizations: Verify sample organizations with members
• Projects: Review sample projects and tasks
• Content: Check blog posts with translations

Schema Exploration

Use Studio to understand your schema:

Testing Data Changes

Before code changes:

1. Note current data state in Studio
2. Make your code changes
3. Test the application
4. Verify data changes in Studio
5. Reset database if needed: pnpm db:reset-seed

Debugging with Studio

Finding Data Issues

Common debugging scenarios:

-- Find tasks without projects
SELECT t.*
FROM tasks t
LEFT JOIN projects p ON t.project_id = p.id
WHERE p.id IS NULL;

-- Find members without users
SELECT m.*
FROM members m
LEFT JOIN users u ON m.user_id = u.id
WHERE u.id IS NULL;

-- Find duplicate emails
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Find duplicate organization names
SELECT name, COUNT(*)
FROM organizations
GROUP BY name
HAVING COUNT(*) > 1;

-- Check for NULL values in required fields
SELECT COUNT(*) as null_emails
FROM users
WHERE email IS NULL;

-- Verify foreign key integrity
SELECT
  m.id,
  m.user_id,
  m.organization_id
FROM members m
LEFT JOIN users u ON m.user_id = u.id
LEFT JOIN organizations o ON m.organization_id = o.id
WHERE u.id IS NULL OR o.id IS NULL;

Data Verification

After running migrations or updates:

SELECT
  'users' as table_name, COUNT(*) as count FROM users
UNION ALL
SELECT
  'organizations', COUNT(*) FROM organizations
UNION ALL
SELECT
  'projects', COUNT(*) FROM projects;

-- Check for recent changes
SELECT table_name, COUNT(*) as count
FROM (
  SELECT 'users' as table_name, created_at FROM users
  WHERE created_at > NOW() - INTERVAL '1 hour'
  UNION ALL
  SELECT 'projects', created_at FROM projects
  WHERE created_at > NOW() - INTERVAL '1 hour'
) recent_data
GROUP BY table_name;

Advanced Features

Data Export and Import

Export options:

• JSON: Complete data structure with nested relationships
• CSV: Flat table format for spreadsheet applications
• SQL: INSERT statements for data migration

# Export specific tables
# Select rows in Studio → Export button → Choose format

# Example exports:
users_export.json        # User data with relationships
projects_export.csv      # Project data for analysis
tasks_backup.sql         # SQL INSERT statements

Query Templates

Save frequently used queries:

-- Template: Active users by role
SELECT role, COUNT(*) as count
FROM users
WHERE active = true
  AND created_at > '{{start_date}}'
GROUP BY role
ORDER BY count DESC;

-- Template: Project completion report
SELECT
  o.name as organization,
  p.name as project,
  COUNT(t.id) as total_tasks,
  COUNT(CASE WHEN t.status = 'completed' THEN 1 END) as completed
FROM organizations o
JOIN projects p ON o.id = p.organization_id
LEFT JOIN tasks t ON p.id = t.project_id
WHERE p.created_at > '{{project_start_date}}'
GROUP BY o.id, o.name, p.id, p.name;

Performance Monitoring

Use Studio to identify slow queries:

-- Check table sizes
SELECT
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY n_distinct DESC;

-- Analyze query performance
EXPLAIN ANALYZE
SELECT u.*, COUNT(p.id) as project_count
FROM users u
LEFT JOIN members m ON u.id = m.user_id
LEFT JOIN organizations o ON m.organization_id = o.id
LEFT JOIN projects p ON o.id = p.organization_id
GROUP BY u.id;

Studio Configuration

Environment Variables

Studio uses the same database configuration as your application:

# .env.local
DATABASE_URL="postgresql://user:pass@host:port/db"

# Optional: Custom Studio port
DRIZZLE_STUDIO_PORT=4983

Studio Options

# Start on different port
pnpm db:studio --port 5000

# Start in verbose mode
pnpm db:studio --verbose

# Start with specific config
pnpm db:studio --config ./custom-drizzle.config.ts

Best Practices

Studio Best Practices

Data Safety

Production considerations:

Safe practices:

• Only connect to development/staging databases
• Use read-only database users for data exploration
• Always backup before making bulk changes
• Test data migrations on copies of production data

Performance Tips

For large databases:

• Use filters to limit data displayed
• Avoid loading tables with millions of rows
• Use custom queries instead of browsing large tables
• Consider pagination for data review

Next Steps

• Best Practices - Learn performance optimization and security guidelines
• Repository Pattern - Return to repository patterns with your new Studio knowledge