Portable Database Architecture
The worst time to migrate a database is when you're under pressure to scale. Portable architecture lets you start simple and switch without rewriting everything.
- Portable = your code speaks to an abstract interface, not "Postgres" or "SQLite" directly
- SQLite: single file, zero config, perfect for MVP and local dev
- Postgres: concurrent, cloud-ready, production scale
- Migration: export from SQLite, import to Postgres. Swap connection string. Done.
- The key: no database-specific features in business logic. Stick to standard SQL.
Real-world example
You built an MVP with SQLite. Now you need 10 users hitting it at once.
SQLite locks on writes. One user running a report blocks another. You need Postgres—but your queries are sprinkled with SQLite-specific syntax.
- Non-portable: Used SQLite's AUTOINCREMENT, datetime("now"), and other SQLite-only features. Migration = find and replace, hope you didn't miss any.
- Portable: Used an abstract DB interface. Queries use standard SQL. Connection comes from config. SQLite for dev, Postgres for prod.
- Migration: Export SQLite to SQL dump. Import into Postgres. Change connection string. Restart. Same application code.
- The portable path: a few hours. The non-portable path: days of debugging subtle differences.
Portable database design is insurance. You might not need to switch—but when you do, it's a config change, not a rewrite.
- Abstract interface: Code calls "execute query," not "call Postgres."
- Standard SQL: Avoid dialect-specific extensions (SQLite datetime, Postgres arrays) in business logic.
- Connection from config: No hardcoded drivers. Swap at deploy time.
- Migration path: Documented export/import. SQLite → SQL dump → Postgres works.
SQLite: Single writer, low concurrency, local or single-instance. Perfect for MVP, demos, local dev, embedded apps. Zero setup—just a file.
Postgres: Multiple concurrent connections, cloud deployment, replication. When you have more than a few users or need reliability guarantees.
Export SQLite: pg_dump-style dump or script that reads and writes INSERTs. Import to Postgres: run the dump. Swap connection string in env. The schema and data move; the application code stays the same.
- Using database-specific types (JSONB, ARRAY) in core logic—hard to port
- Storing file paths or assumptions about filesystem—SQLite is a file, Postgres isn't
- No abstraction—queries written directly against driver. Migration = grep and pray
- Waiting until scale crisis to think about portability—retrofit is painful
See it in action
Planning an MVP that might need to scale?