Database

SaaS Pack uses Drizzle ORM for type-safe database operations with support for multiple databases.

Supported Databases

• PostgreSQL (recommended)
• MySQL
• SQLite (development)
• Cloudflare D1

Configuration

Environment Variables

# PostgreSQL
DATABASE_URL=postgresql://user:password@localhost:5432/dbname

# Or for Cloudflare D1
# Database binding is configured in wrangler.toml

Schema Definition

Define your database schema using Drizzle:

// server/database/schema.ts
import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow()
})

Migrations

Generate and run migrations:

# Generate migration
pnpm db:generate

# Apply migration
pnpm db:migrate

# Drop migration
pnpm db:drop

Querying Data

// Select all users
const users = await db.select().from(users)

// Select with where clause
const user = await db
  .select()
  .from(users)
  .where(eq(users.email, '[email protected]'))

// Insert data
await db.insert(users).values({
  email: '[email protected]',
  name: 'New User'
})

// Update data
await db
  .update(users)
  .set({ name: 'Updated Name' })
  .where(eq(users.id, userId))

// Delete data
await db.delete(users).where(eq(users.id, userId))

Relations

Define relationships between tables:

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id),
  title: text('title').notNull(),
  content: text('content')
})

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts)
}))

Best Practices

1. Always use prepared statements
2. Use transactions for related operations
3. Index frequently queried columns
4. Validate data before database operations
5. Use migrations for schema changes

Next Steps

• Authentication Setup