Quick Start Guide

Get up and running with Drizzle ORM in 5 minutes. The Suitsbooks application uses Drizzle for type-safe database operations.

Setup

Add your database connection string to .env:

DATABASE_URL=postgresql://user:password@host:5432/database

Install dependencies and verify the connection:

pnpm install
pnpm db:studio

Drizzle Studio opens at https://local.drizzle.studio for visual database browsing.

Basic Usage

Direct Database Queries

import { db } from '@/src/db';
import * as schema from '@/src/schema';
import { eq } from 'drizzle-orm';

const users = await db.select().from(schema.users);

const user = await db.select()
  .from(schema.users)
  .where(eq(schema.users.email, 'user@example.com'));

const newUser = await db.insert(schema.users).values({
  email: 'new@example.com',
  name: 'New User',
  companyId: 'company-uuid',
}).returning();

await db.update(schema.users)
  .set({ name: 'Updated Name' })
  .where(eq(schema.users.userId, 'user-uuid'));

await db.delete(schema.users)
  .where(eq(schema.users.userId, 'user-uuid'));

Tenant-Scoped Service Layer

Use DrizzleService for automatic tenant scoping:

import { DrizzleService } from '@/lib/db/drizzle-service';
import * as schema from '@/src/schema';
import { eq } from 'drizzle-orm';

const service = new DrizzleService({
  companyId: user.company_id,
  organizationId: user.organization_id,
  userId: user.user_id,
});

const data = await service.fetchData('users', {
  conditions: [eq(schema.users.email, 'user@example.com')],
  limit: 10,
});

await service.insertData('users', {
  email: 'new@example.com',
  name: 'New User',
});

await service.updateData(
  'users',
  schema.users.userId,
  'user-uuid',
  { name: 'Updated Name' }
);

await service.deleteData('users', schema.users.userId, 'user-uuid');

Adding Tables

Define new tables in src/schema.ts:

export const myTable = pgTable("my_table", {
  id: uuid("id").primaryKey().defaultRandom(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow(),
  name: text("name").notNull(),
  description: text("description"),
  companyId: uuid("company_id"),
});

Then use it:

import * as schema from '@/src/schema';
const records = await db.select().from(schema.myTable);

Running Migrations

pnpm db:generate        # Generate from schema changes
pnpm db:migrate:run     # Apply migrations
pnpm db:push            # Push schema directly (no migration files)

Migration from Supabase Client

Drizzle provides type-safe queries with better developer experience:

Before:

const { data, error } = await supabase
  .from('users')
  .select('*')
  .eq('email', 'user@example.com');

After:

const users = await db.select()
  .from(schema.users)
  .where(eq(schema.users.email, 'user@example.com'));

Benefits:

Type-safe queries with full IDE autocomplete
Errors thrown instead of returned
Direct database access for better performance
No { data, error } pattern needed

Project Structure

project/
├── src/
│   ├── db.ts
│   └── schema.ts
├── lib/
│   └── db/
│       └── drizzle-service.ts
├── docs/
│   └── db/
│       └── drizzle/
│           ├── migration-complete.md
│           ├── query-reference.md
│           └── schema-management.md
├── migrations/
│   └── README.md
├── drizzle.config.ts
└── .env

Important Considerations

Schema Limitations
Current schema includes 4 core tables: users, companies, invoices, customers. See schema-management.md to add more.

Tenant Context
Always use DrizzleService for tenant-scoped operations to ensure proper data isolation:

const service = new DrizzleService({
  companyId: user.company_id,
  organizationId: user.organization_id,
  userId: user.user_id,
});
const users = await service.fetchData('users');

Authentication
Supabase Auth remains unchanged - only database queries use Drizzle.

Common Commands

# Database operations
pnpm db:studio          # Visual database browser
pnpm db:generate        # Generate migrations
pnpm db:migrate:run     # Apply migrations
pnpm db:push            # Push schema directly
pnpm db:check           # Check migration status
pnpm db:introspect      # Re-introspect database

# Development
pnpm dev                # Start dev server
pnpm build              # Build for production
pnpm lint               # Run linter

Resources

Essential reading:

query-reference.md - Common query patterns
schema-management.md - Adding/managing tables
migration-complete.md - Full migration details

External:

Frequently Asked Questions

Do I need to change authentication code?
No. Supabase Auth remains unchanged.

Can I use raw SQL?
Yes. Use db.execute(sqlSELECT * FROM ...) or service.executeRawSQL().

How do I add tables?
See schema-management.md for detailed instructions.

Is my data safe?
Yes. Same PostgreSQL database and data - only the query layer changed.

Can I rollback?
Yes. Revert code changes via git - no data migration required.