Why I Switched from MongoDB to Postgres in 8 Months – Lessons Learned

The initial architectural selection for the VirtualRx product was predicated on velocity. MongoDB was selected for its schema-less nature, which allowed the development team to bypass the overhead of traditional database migrations. By avoiding a structured schema at the project outset, engineers could transition immediately from conceptual modeling to functional application code.

The process of initializing the database environment is streamlined by a direct connection approach, which relies on a standard connection string pattern. This architectural choice prioritizes rapid iterative development, as demonstrated by the simplicity of the following lifecycle:

• Initialization: Instantiating the database environment with minimal configuration.
• Connection: Utilizing a standard connection string (e.g., mongodb://...) to establish a link between the application and the data layer.
• Modeling: Defining the first data model as a plain object or class, allowing for immediate persistence without pre-defined table structures or schema versioning tools.

While this approach facilitates rapid deployment, it introduces technical considerations for long-term scalability. As application complexity increases, particularly regarding analytical requirements, developers may encounter limitations with aggregation pipelines—specifically when performing complex data operations such as multi-join aggregations or window functions. These tasks, which are native to relational systems, often require sophisticated query logic in document-based stores.

For systems that eventually require both operational document storage and relational reporting, maintaining parity across data layers is a significant engineering challenge. Approaches that minimize the friction of switching backends, such as abstracting query APIs to support both document and relational interfaces, enable developers to pivot data strategies without refactoring core application logic or maintaining disparate mental models for database operations.

When a product needs both MongoDB for operational workloads and PostgreSQL for analytical queries, the data‑access layer must hide the underlying driver differences without sacrificing type safety or runtime performance.

Prisma

Prisma offers a fluent API that matches the desired db.sale.findMany({ where: … }) shape, but it relies on a code‑generation step. After each schema change developers run prisma generate, which produces a compiled client and a Rust‑based query engine binary. This introduces several friction points:

• Generated files must be committed or ignored, creating a potential mismatch between source and deployed code.
• The binary cannot be bundled for browser or React Native environments, making Prisma unsuitable for client‑side execution.
• Every schema change forces a regeneration cycle, which can stall CI pipelines (e.g., a teammate losing an afternoon to a stale client).

Drizzle

Drizzle is designed around a pure‑SQL surface. Its type‑safe query builder assumes a relational dialect, so it maps directly to PostgreSQL, MySQL, SQLite, etc. Because MongoDB does not expose a SQL‑like language, Drizzle cannot generate equivalent queries for the NoSQL side, making it a poor fit for a dual‑Mongo/Postgres stack.

TypeORM

TypeORM uses decorators and runtime metadata to define entities. While powerful for relational databases, it brings heavy reflection machinery that does not execute in a browser tab. The lack of browser compatibility eliminates the possibility of sharing the same data layer across server and client code.

Why none of the three satisfy a dual‑Mongo/Postgres strategy

• Prisma’s code‑gen and native engine block browser usage.
• Drizzle’s SQL‑only model cannot express MongoDB aggregation pipelines.
• TypeORM’s decorator‑based runtime requires Node‑only APIs, preventing client‑side execution.

A practical alternative is to define models once in TypeScript and let the runtime translate the same query object to either a MongoDB find or a PostgreSQL SELECT. For example:

const adults = await db.user.findMany({
  where: { age: { gte: 18 } },
  orderBy: { name: 'asc' },
  take: 20,
});

This pattern preserves autocomplete, eliminates a build step, and allows a raw‑SQL escape hatch when the abstraction is insufficient, addressing the core requirements of a dual‑database architecture.

VirtualRx illustrates a pragmatic split between operational and analytical workloads by persisting transaction data in MongoDB while materializing reporting aggregates in PostgreSQL. The application defines each entity once in TypeScript using forge‑orm models, and the same findMany calls execute against either database depending on the DATABASE_URL value.

import { createDb, f, model } from 'forge-orm';

const Sale = model('sales', {
  id: f.id(),
  locationId: f.string(),
  postedAt: f.date(),
  total: f.float(),
  customerId: f.string(),
});

const db = await createDb({
  url: process.env.DATABASE_URL!, // mongodb://… or postgres://…
  schema: { sale: Sale },
});

const rows = await db.sale.findMany({
  where: { locationId, postedAt: { gte: start } },
  include: { customer: true },
});

When DATABASE_URL points to MongoDB, the call translates to a find with an aggregation pipeline; when it points to PostgreSQL, the same call becomes a SELECT with JOIN and WHERE clauses. This uniform API yields three concrete benefits:

• Reduced cognitive load: developers maintain a single set of model definitions and helper utilities (soft‑delete, tenant scoping, audit logging) instead of duplicating them for each data store.
• Accelerated iteration: the initial nine‑minute decision to use MongoDB did not become a permanent lock‑in; the codebase could adopt PostgreSQL for analytics without refactoring query logic.
• Controlled escape hatch: complex reporting that exceeds the portable query surface can fall back to raw SQL via db.$queryRaw, preserving performance and expressiveness.

Key lessons for teams confronting early database choices:

• Choose a data‑access layer that abstracts the query shape rather than the storage engine; this keeps the door open for future migrations.
• Prefer libraries that generate types on the fly (e.g., forge‑orm) over code‑generation steps that require manual regeneration and risk stale clients.
• Design the operational schema for write‑heavy, low‑latency use cases (MongoDB) and a separate read model for analytical queries (PostgreSQL), but keep the model definitions shared.

By decoupling the “what” (model definitions) from the “where” (database driver), VirtualRx demonstrates a reproducible pattern for enterprise applications that must evolve their data strategy without incurring massive rewrites.