MongoDB

Your entities and queries run on MongoDB unchanged. This page covers only what works differently there.

Install and connect

npm install uql-orm mongodb

import { MongodbQuerierPool } from 'uql-orm/mongodb';

export const pool = new MongodbQuerierPool(process.env.MONGO_URL!, {
  maxPoolSize: 10,
});

The second argument takes the driver’s MongoClientOptions, and the third takes UQL’s extra options. Call pool.end() to close the client.

Ids

Your key field (.id()) is MongoDB’s _id. An ObjectId reaches your code as a hex string; any other id, such as a UUID, is stored as you give it. A foreign key works the same way, whether f.references() or a relation declares it. Composite keys are not supported.

Generated keys

MongoDB can only generate ObjectIds, so a key you leave to the database must be a String:

Declaration	On MongoDB
f.string().id()	MongoDB generates an ObjectId
f.string().id().onInsert(uuidv7)	your code generates it, on every engine
f.number().id()	yours to supply, or an onInsert

Relations

$populate runs as a $lookup. A query on the entity’s own fields is a plain find; filtering on a relation, a relation aggregate or sorting by a related field makes it an aggregation pipeline instead.

To sort by a related field, $populate every relation along its path: UQL never adds a $lookup you didn’t ask for.

Some updateMany and deleteMany calls read the matching ids first, or write a cascade: those filtered through a relation, paged, cascading, running hooks, or using returning. They run as their steps; open a transaction where the whole must land or none of it, which needs a replica set.

A @Computed field written in SQL can’t run on MongoDB. Naming it in $select, $where or $sort throws; anywhere else it is skipped.

Transactions need a replica set

MongoDB runs transactions only on a replica set or a sharded cluster. For development, one node is enough:

docker run -p 27017:27017 mongo --replSet rs0 --bind_ip_all
# then, once: mongosh --eval 'rs.initiate()'

There are no savepoints, so a nested transaction() throws, and isolationLevel is ignored. To make a raw driver call part of the transaction, pass it the querier’s session:

await pool.transaction(async (querier) => {
  await querier.insertOne(Order, order);
  await querier.db
    .collection('audit')
    .insertOne({ order: order.id }, { session: querier.session });
});

Search and streaming

Full-text search needs a text index, which a fulltext index creates. The index sets the fields, weights and language, so $text ignores $fields. $sort: { $text } ranks by textScore.

Vector search runs Atlas $vectorSearch over a type: 'vectorSearch' index, which migrations create, and $candidates sets numCandidates. Atlas cannot filter by distance, so $near throws: project the distance with the $sort’s $project (lower is nearer, as on SQL) and filter on it instead. Ranking by a related row’s vector works without Atlas.

findManyStream reads through the same cursor as findMany, populated relations included.

Checks and enums

Checks and enums become the collection’s validator:

import { f } from 'uql-orm';
@Entity({ checks: (task, check) => [check({ priority: { $gte: 0 } })] })
class Task {
  @Field(f.string().id()) id?: string;
  @Field(f.enum(['open', 'closed']).nullable()) status?:
    | 'open'
    | 'closed'
    | null;
  @Field(f.number().nullable()) priority?: number | null;
}
// validator: { $and: [{ priority: { $gte: 0 } }, { status: { $in: ['open', 'closed', null] } }] }

An enum lets a field be missing or null; a check doesn’t. The check above rejects a document with no priority, so where the field is optional, write $or: [{ priority: null }, { priority: { $gte: 0 } }]. A check can’t use sql or compare a Date, bigint or ObjectId. A rejected write throws a checkViolation error.

UQL owns the validator: generate:entities and sync with safe: false replace or remove it, while safe mode only adds one. It applies to new writes, not to documents already stored.

Migrations

Migrations use the same commands, written against the database handle:

• generate scaffolds a migration typed on MongoQuerier. generate:entities writes collections, validators and indexes as driver calls, a partial index’s where becoming its partialFilterExpression.
• History is kept in the uql_migrations collection (set by tableName), which also holds the lock.
• Migrations don’t run in a transaction. One that fails halfway keeps what it did and is not recorded, so write each step to be safe to rerun.
• The migration builder supports createTable (indexes only), dropTable, renameTable, createIndex and dropIndex. Columns, foreign keys and sql throw.
• drift:check isn’t supported; sync --dry-run prints the index and validator changes instead.

import { defineBuilderMigration } from 'uql-orm/migrate';
import type { MongoQuerier } from 'uql-orm/mongodb';

export default defineBuilderMigration<MongoQuerier>({
  async up(m) {
    await m.renameTable('users', 'members');
    await m.createIndex('members', ['email'], { unique: true });
  },
  async down(m) {
    await m.dropIndex('members', 'members__email_idx');
    await m.renameTable('members', 'users');
  },
});

To change field data, write the migration with the driver:

import { defineMigration } from 'uql-orm/migrate';
import type { MongoQuerier } from 'uql-orm/mongodb';

export default defineMigration<MongoQuerier>({
  async up(querier) {
    await querier.db
      .collection('users')
      .updateMany({ active: { $exists: false } }, { $set: { active: true } });
  },
  async down(querier) {
    await querier.db
      .collection('users')
      .updateMany({}, { $unset: { active: '' } });
  },
});

Moving to SQL

The same entities and queries run on a SQL pool, and moving off MongoDB changes no query. You mirror the writes, copy the data, compare the reads, then switch. The walkthrough does it for a Mongoose app.

Declare the entities over the documents as they are stored. A Mongoose ref keeps its stored name on the reference field beside the relation, so the documents need no rewrite:

import { ObjectId } from 'mongodb';
import { f, Entity, Field, ManyToOne } from 'uql-orm';

@Entity({ name: 'users' })
export class User {
  @Field(
    f
      .string()
      .id()
      .onInsert(() => new ObjectId().toHexString()),
  )
  id?: string;
  @Field(f.string().nullable()) name?: string | null;
}

@Entity({ name: 'posts' })
export class Post {
  @Field(
    f
      .string()
      .id()
      .onInsert(() => new ObjectId().toHexString()),
  )
  id?: string;
  @Field(
    f
      .references(() => User)
      .nullable()
      .name('author'),
  )
  authorId?: string | null;
  @ManyToOne(() => User, { references: (post) => post.authorId })
  author?: User;
}

Create the tables with sync or a migration, then send every write to both databases. MongoDB stays the source of truth, and a failure on SQL is only logged:

import type { QuerierPool } from 'uql-orm';
import { MongodbQuerierPool } from 'uql-orm/mongodb';
import { PgQuerierPool } from 'uql-orm/postgres';
import { Post, User } from './entities.js';

const mongo = new MongodbQuerierPool(process.env.MONGO_URL!);
const pg = new PgQuerierPool({ connectionString: process.env.DATABASE_URL });

async function mirror<T>(write: (pool: QuerierPool) => Promise<T>): Promise<T> {
  const result = await write(mongo);
  await write(pg).catch((error) => console.warn('mirror failed', error));
  return result;
}

await mirror((pool) =>
  pool.insertOne(User, { id: new ObjectId().toHexString(), name: 'Ada' }),
);

Inserts name their id up front, and both databases store that id. Port a collection’s writes before mirroring it, since a mirror only sees writes that go through UQL.

Then copy each collection, parents before the collections that reference them. saveMany upserts each row by its id, which makes the copy safe to run again at any time. Each run repairs whatever a failed mirror missed.

async function copy<E extends object>(entity: new () => E, batchSize = 1000) {
  let batch: E[] = [];
  for await (const row of mongo.findManyStream(entity, {})) {
    batch.push(row);
    if (batch.length === batchSize) {
      await pg.saveMany(entity, batch);
      batch = [];
    }
  }
  if (batch.length) {
    await pg.saveMany(entity, batch);
  }
}

await copy(User);
await copy(Post);

Ids keep their ObjectId hex text, so references and URLs still work, and the key’s onInsert gives a row written on SQL an id of the same form.

Before moving reads, run each one on both databases and compare the results:

import { isDeepStrictEqual } from 'node:util';

// MongoDB omits a missing field and SQL returns null: drop both.
const plain = (rows: unknown) =>
  JSON.parse(JSON.stringify(rows, (_key, value) => value ?? undefined));

async function shadow<T>(read: (pool: QuerierPool) => Promise<T>): Promise<T> {
  const [served, shadowed] = await Promise.all([read(mongo), read(pg)]);
  if (!isDeepStrictEqual(plain(served), plain(shadowed))) {
    console.warn('shadow read differs', { served, shadowed });
  }
  return served;
}

const posts = await shadow((pool) =>
  pool.findMany(Post, {
    $select: { id: true, title: true },
    $populate: { author: { $select: { name: true } } },
    $where: { title: { $startsWith: 'Launch' } },
    $sort: { id: 'asc' },
  }),
);

Sort every compared read: neither database guarantees an order otherwise. Once the results agree under real traffic, serve reads from SQL. Then swap the two pools in mirror. SQL takes each write first, and MongoDB stays current in case you need to roll back. Retire the collections once nothing reads them. Expect two differences along the way: null comparisons, and SQL foreign keys rejecting references MongoDB left dangling.