Database Design for Modern Web Apps: Postgres, SQLite, or Edge?
Back to Blog

Database Design for Modern Web Apps: Postgres, SQLite, or Edge?

March 21, 20263 min read1 views

The database landscape for modern web applications is fragmenting in interesting ways. Postgres remains the reliable workhorse, but SQLite is experiencing a renaissance with Turso, LiteFS, and Cloudflare D1. Meanwhile, edge databases promise global low-latency access.

PostgreSQL: The Reliable Workhorse

Postgres is the default choice for good reason: mature, feature-rich, excellent tooling, and handles most workloads well.

// Drizzle + Postgres example
import { drizzle } from 'drizzle-orm/node-postgres'
import { Pool } from 'pg'

const pool = new Pool({ connectionString: process.env.DATABASE_URL })
export const db = drizzle(pool)

// Full SQL power when needed
await db.execute(sql`
  SELECT u.*, COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON p.author_id = u.id
  GROUP BY u.id
  HAVING COUNT(p.id) > 10
`)

Choose Postgres when: Complex queries, ACID transactions, full SQL power, mature ecosystem, team familiarity.

SQLite Revolution: Turso, LiteFS, D1

SQLite at the edge is having a moment. These platforms replicate SQLite globally for low-latency reads.

// Turso (libSQL) example
import { createClient } from '@libsql/client'

const turso = createClient({
  url: process.env.TURSO_DATABASE_URL,
  authToken: process.env.TURSO_AUTH_TOKEN,
})

// Reads from nearest replica
const result = await turso.execute('SELECT * FROM users WHERE id = ?', [userId])

Choose Edge SQLite when: Read-heavy workloads, global users, simple queries, latency-sensitive.

Edge Databases: Global Low-Latency

Platforms like Cloudflare D1, Turso, and PlanetScale offer globally distributed data.

Traditional centralized DB:
User (Tokyo) → API (US-East) → DB (US-East) → Back
Round trip: ~200ms

Edge-replicated DB:
User (Tokyo) → Edge (Tokyo) → Replica (Tokyo) → Back
Round trip: ~20ms

Trade-offs:

  • Reads: Fast globally
  • Writes: Still go to primary (latency for writes)
  • Consistency: Eventually consistent for replicas
  • Features: Subset of full Postgres

Hybrid Approaches

// Global reads, regional writes pattern
const readDb = createTursoClient({ url: TURSO_READ_URL })  // Edge replica
const writeDb = createTursoClient({ url: TURSO_PRIMARY_URL })  // Primary

async function getUser(id: string) {
  return readDb.execute('SELECT * FROM users WHERE id = ?', [id])
}

async function updateUser(id: string, data: Partial<User>) {
  // Writes go to primary, then replicate
  await writeDb.execute(
    'UPDATE users SET name = ?, updated_at = ? WHERE id = ?',
    [data.name, new Date(), id]
  )
}

Schema Design Principles

For Postgres: Normalize thoughtfully, use constraints, leverage JSON for flexibility.

For Edge SQLite: Denormalize for read performance, minimize joins, design for replication.

-- Edge-optimized: denormalized for fast reads
CREATE TABLE posts (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  author_id TEXT NOT NULL,
  author_name TEXT NOT NULL,  -- Denormalized
  author_avatar TEXT,          -- Denormalized
  created_at INTEGER NOT NULL
);

Decision Matrix

Complex queries, transactions → Postgres
Simple app, low-latency reads → Edge SQLite
Global users, read-heavy → Turso/D1
Microservices, scaling needs → PlanetScale
Embedded/local-first → SQLite + sync

Conclusion

The "right" database depends on your specific requirements: query complexity, latency targets, consistency needs, and operational preferences. Often, a hybrid approach—Postgres for complex operations, edge for fast reads—provides the best of both worlds.

Share this article