ADR-007: Neon + Drizzle for Database Layer

Status: Accepted
Date: 2026-02-01
Deciders: Abdisamed Mohamed
Related ADRs: ADR-002 (Edge-First Architecture), ADR-003 (Cost Optimization)

Context

CepatEdge requires a database solution that provides:

• Edge compatibility (low latency connections)
• Serverless scaling (pay-per-compute)
• Type safety (TypeScript integration)
• Performance (fast queries, connection pooling)
• Cost efficiency (free tier utilization)

Traditional databases have limitations:

• Regional databases add latency for global users
• Server-based databases require management overhead
• Heavy ORMs increase bundle size and complexity

Decision

Use Neon Serverless PostgreSQL with Drizzle ORM for the database layer.

Implementation

Database Configuration

// Neon connection (serverless)
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-serverless';

// Environment-based connection
export function createDb(env: Env) {
  const sql = neon(env.DATABASE_URL);
  return drizzle(sql, { schema });
}

Schema Definition

import { pgTable, text, integer, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('User', {
  id: text('id').primaryKey(),
  email: text('email').unique().notNull(),
  name: text('name').notNull(),
  role: text('role').notNull(),
}, (table) => ({
  emailIdx: { name: 'idx_users_email', columns: [table.email] },
  roleIdx: { name: 'idx_users_role', columns: [table.role] },
}));

export const maintenanceRequests = pgTable('MaintenanceRequest', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  status: text('status').default('pending'),
  submittedBy: text('submittedBy'),
}, (table) => ({
  statusIdx: { name: 'idx_requests_status', columns: [table.status] },
  submittedByIdx: { name: 'idx_requests_submitted', columns: [table.submittedBy] },
}));

Query Operations

// Type-safe queries
export async function getUserById(id: string) {
  const db = createDb(env);

  const result = await db
    .select()
    .from(users)
    .where(eq(users.id, id))
    .limit(1);

  return result[0] || null;
}

// Complex queries with joins
export async function getMaintenanceWithDetails(id: string) {
  const db = createDb(env);

  return await db
    .select({
      request: maintenanceRequests,
      employee: users,
      technician: users,
    })
    .from(maintenanceRequests)
    .leftJoin(users, eq(maintenanceRequests.submittedBy, users.id))
    .leftJoin(users, eq(maintenanceRequests.assignedTo, users.id))
    .where(eq(maintenanceRequests.id, id));
}

Migration System

# Generate migrations
npx drizzle-kit generate

# Push schema changes
npx drizzle-kit push

# Check migration status
npx drizzle-kit check

Consequences

Positive

• Edge Compatible: Direct connections from Workers to database
• Serverless: Automatic scaling, pay-per-compute model
• Type Safety: Full TypeScript integration with Drizzle
• Performance: Connection pooling, query optimization
• Developer Experience: Intuitive API, excellent DX

Negative

• PostgreSQL Only: Limited to PostgreSQL dialect
• Learning Curve: New ORM for existing team members
• Migration Complexity: Schema changes require careful planning
• Connection Limits: Neon has connection limits (though generous)

Mitigation

• Documentation: Comprehensive migration guides
• Type Generation: Automatic TypeScript types from schema
• Testing: Extensive test coverage for database operations
• Monitoring: Built-in query performance monitoring

Performance Characteristics

Connection Performance

• Connection Time: <50ms globally
• Connection Pooling: Automatic connection reuse
• Concurrent Connections: Up to 1000+ connections

Query Performance

• Simple Queries: <10ms
• Complex Joins: <50ms
• Bulk Operations: <100ms for 1000 records
• Indexed Queries: <5ms with proper indexing

Scaling

• Compute Scaling: Automatic based on usage
• Storage Scaling: Up to 512GB
• Global Distribution: Data centers worldwide

Cost Optimization

Free Tier Utilization

• Storage: 1GB free
• Compute Hours: 100 hours/month free
• Data Transfer: Generous free allowance

Scaling Costs

Operation          | Free Tier          | Paid Rate
-------------------|-------------------|--------------------
Storage            | 1GB               | $0.0001/GB/hour
Compute Hours      | 100 hours         | $0.0001/second
Data Transfer      | 100GB             | $0.1/GB

Optimization Strategies

// Efficient queries
const optimizedQuery = await db
  .select({ id: users.id, name: users.name }) // Select only needed fields
  .from(users)
  .where(eq(users.active, true)) // Filter early
  .limit(50) // Paginate results
  .orderBy(users.createdAt); // Use indexed columns

Alternatives Considered

PlanetScale

• Pros: Serverless MySQL, branching, insights
• Cons: MySQL dialect, PlanetScale-specific features

Cloudflare D1

• Pros: Edge-native, SQLite-compatible, zero latency
• Cons: SQLite limitations, smaller ecosystem

Supabase

• Pros: Full backend-as-service, real-time features
• Cons: Additional services, vendor lock-in

Traditional PostgreSQL

• Pros: Full PostgreSQL features, mature ecosystem
• Cons: Management overhead, scaling complexity, latency

Migration Strategy

From Existing Database

1. Schema Analysis: Map existing schema to Drizzle schema
2. Data Migration: Export/import data with transformation
3. Query Migration: Convert existing queries to Drizzle API
4. Testing: Comprehensive testing of migrated operations

Incremental Migration

// Phase 1: Read operations
const users = await db.select().from(usersTable);

// Phase 2: Write operations
await db.insert(usersTable).values(newUser);

// Phase 3: Complex operations
await db.update(usersTable)
  .set({ lastLogin: new Date() })
  .where(eq(usersTable.id, userId));

Monitoring & Observability

Query Performance

// Built-in query logging
const db = createDb(env).$withLogger({
  logQuery: (query, params) => {
    console.log('Query:', query, 'Params:', params);
  }
});

Connection Monitoring

• Connection Count: Monitor active connections
• Query Latency: Track query execution times
• Error Rates: Monitor database errors
• Usage Patterns: Analyze query patterns for optimization

References

• Neon Serverless Documentation
• Drizzle ORM Documentation
• PostgreSQL Performance Best Practices
• Edge Database Patterns