PostgreSQL vs SQLite for Your Next.js App: When to Use Each

#webdev #nextjs #prisma #postgres

PostgreSQL vs SQLite for Your Next.js App: When to Use Each

SQLite is underrated for early-stage SaaS. PostgreSQL is essential past a certain scale. Here's the decision framework — and how to switch between them with Prisma.

SQLite: When It's the Right Call

Use SQLite when:

You're in early development or building an MVP
You have a single-server deployment
You want zero infrastructure (no database server to manage)
You're building a tool that runs locally (CLI, desktop app, local server)

SQLite is a file. No connection pooling, no network latency, no server to maintain. For a solo developer shipping an MVP, that simplicity compounds.

The performance ceiling is higher than most people think: SQLite handles ~100k reads/second on modern hardware for typical web workloads.

PostgreSQL: When You Need It

Use PostgreSQL when:

Multiple server instances need to share the same database
You need concurrent writes from multiple processes
Your data volume exceeds a few GB
You need full-text search, JSON operators, or PostGIS
You're deploying to Vercel, Railway, or any serverless environment

Serverless deployments specifically require PostgreSQL — SQLite is a file on disk, and serverless functions don't have persistent disk access.

Prisma Schema: No Change Required

The biggest advantage of Prisma: your schema and query code work with both databases. The only change is the datasource.

SQLite:

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

PostgreSQL:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Everything else — models, relations, queries — stays identical.

What Breaks When Migrating SQLite → PostgreSQL

String types. SQLite is loosely typed. PostgreSQL enforces types strictly. A field defined as String in Prisma may need @db.Text or @db.VarChar(255) explicitly in PostgreSQL.

Boolean handling. SQLite stores booleans as 0/1. PostgreSQL uses true/false. Prisma handles this, but raw SQL queries you've written won't migrate cleanly.

Case sensitivity. SQLite string comparisons are case-insensitive by default. PostgreSQL is case-sensitive. WHERE email = 'User@Example.com' behaves differently.

JSON fields. SQLite stores JSON as text. PostgreSQL has native JSON/JSONB types with query operators. If you're doing json_extract in raw queries, you'll need to rewrite.

Migration Strategy

Start with SQLite locally, migrate to PostgreSQL before launch:

# 1. Change datasource in schema.prisma to PostgreSQL
# 2. Reset migrations for the new database
npx prisma migrate reset
npx prisma migrate dev --name init

# 3. Export data from SQLite (if any)
sqlite3 dev.db .dump > backup.sql

# 4. Import to PostgreSQL (may need manual cleanup for type differences)
psql $DATABASE_URL < backup.sql

For early-stage apps with minimal data, resetting migrations is cleaner than trying to migrate the SQLite dump.

Connection Pooling for Production PostgreSQL

Serverless deployments create a new database connection per request. PostgreSQL has a connection limit (~100 on most hosted plans). Without pooling, you'll hit too many connections under load.

Use PgBouncer (built into Neon, Supabase, and Railway):

# Direct connection (use for migrations only)
DATABASE_URL=postgresql://user:pass@host:5432/db

# Pooled connection (use for the app)
DATABASE_URL=postgresql://user:pass@host:6543/db?pgbouncer=true

In Prisma:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")       // pooled — for queries
  directUrl = env("DIRECT_DATABASE_URL") // direct — for migrations
}

Managed PostgreSQL Options

Provider	Free Tier	Best For
Neon	0.5 GB, 1 compute	Vercel deployments (same region)
Supabase	500 MB, 2 projects	Built-in auth + storage
Railway	$5/mo credit	Self-contained project deployment
PlanetScale	No free tier	High-scale MySQL (not Postgres)