Skip to content
NewFrom Mongoose to SQL: migrating MongoDB to PostgreSQL7 min read

Upgrade guide

Releases that may need a change on your side, newest first. Everything else, including what a custom dialect or driver sees, is in the changelog.

The browser client reads rows by the entity it is handed: a Date field is a Date, no longer its ISO text, and a bigint exact, so code that parsed one can stop. The entity has to be registered where the client runs, as importing its class does; a class used only to name the route throws. A cache that persists the client’s rows needs a serializer that keeps a Date and a bigint. The ./browser entry grows from about 2 KB gzipped to about 11 KB, the entity metadata.

uql-orm/browser ships no prebuilt uql-browser.min.js: import the package entry, which every bundler resolves.

Run the codemod first: it rewrites the relation decorators.

A relation decorator takes its target first, then its options, as defineEntity’s rel.* do: @ManyToOne(() => User, { references: (post) => post.authorId }), and @OneToMany(() => Post) where nothing else needs saying. The entity option is gone; in plain JavaScript, a decorator still passing it throws on first read.

PGlite needs 0.5.10 or later: upgrade @electric-sql/pglite, and @electric-sql/pglite-pgvector with it. A statement now binds up to 65535 values, as on a Postgres server.

insertOne, insertMany, saveOne and saveMany resolve to a defined key, so a ! or a check on the result can go. On SQL engines, an insert leaving out a key the entity does not declare a fill for now throws before writing. If only the table states the default, declare it: f.uuid().id().default(sqlgen_random_uuid()).

.id() no longer implies a generated key: a generated key says so, as f.number().autoIncrement(), or f.bigint().autoIncrement(). .autoIncrement() makes the column the key, so it replaces .id() rather than following it, and on a column that is no key, where it never generated anything, it is gone. f.number().id() is now a key the caller supplies, an insert has to name it, and .autoIncrement(false) is gone. Run npx uql-codemod, which rewrites each sole numeric key. On MongoDB, where nothing generates a numeric key, undo that for yours.

An insert has to name a key unless the entity says something fills it. defineEntity reads that off its columns. A decorated class says so itself: keep id!: number and list it in [filledKey]?: 'id', as any other field a default fills, or declare it id?. @Field refuses ? on a numeric or .references() key nothing fills. A text key stays allowed ?, which MongoDB generates. Generated entities carry ? on a key the database fills.

On SQLite, a new key the caller supplies is INT, as INTEGER PRIMARY KEY numbers its own rows. An existing table stays as it is, which sync reads as no drift.

.narrow<T>() is gone: a column’s type argument says it, f.uuid<UserId>(), and a schema-less JSON payload is f.json<Settings>(). For a builder with no type argument, set the SQL type after: f.number<Cents>().columnType('int').

A brand now holds across a foreign key: a column referencing a branded key has to carry the brand, in a relation and on a decorated property. A filter on a branded Date takes the brand, not a plain Date or string.

The DateColumnType, BlobColumnType and VectorColumnType exports are gone; ColumnType covers them.

The deprecated raw export is gone, so raw from uql-orm no longer compiles. Use sql, which uql-codemod renames at the import and every use.

Run the codemod first: it rewrites the upsert calls.

An upsert’s update is its query’s $update, beside $conflict and $where, and the options follow the rows as on every other write: upsertOne(SearchQuery, { $conflict: { term: true }, $update: { uses: { $inc: 1 } } }, row, opts), likewise in upsertMany and a trigger’s upsertInto.

Run the codemod first: it rewrites the upsert calls.

An upsert’s conflict keys are a query, as updateMany’s $where is: upsertOne(User, { $conflict: { email: true } }, row), likewise in upsertMany and a trigger’s upsertInto. A $where beside it limits which found rows take the update. QueryConflictPaths is QueryConflictKeys.

A JSON column’s default is a document: .default('draft') stores the JSON string "draft", and .default('{}') is refused in favour of .default({}).

On SQL Server, a uuid reads back lowercase, as written: compare ids as read, not uppercased.

On MongoDB, a write of several steps no longer opens a transaction of its own, so it runs on a standalone server: an upsert with an update or a $where, and an updateMany or deleteMany that reads its ids first, cascades, or runs hooks. Wrap it in a transaction where it must land whole or not at all.

On MongoDB, a foreign key only a relation declares (in defineEntity’s .relations(), or a plain @Field a @ManyToOne joins by) is now stored as an ObjectId, as an f.references() one already was, so $populate and $count find its rows. Convert the documents written before, per collection and field: db.posts.updateMany({ author: { $regex: '^[0-9a-fA-F]{24}$' } }, [{ $set: { author: { $toObjectId: '$author' } } }]).

A JSON column’s payload is a Standard Schema, f.json(schema): the shape option (f.json({ lang: String })) is gone. The codemod leaves a shape it cannot write as a builder, so write the schema by hand or with any Standard Schema library. It also converts a class registered with defineEntity(Class, options) into a defineEntity({ name, fields }) call, and leaves a class it cannot convert as it is, with the reason.

defineEntity has no .references() section. A foreign key is a relation in .relations(), which checks its key type at compile time; the column never names its target. f.references() on a decorated field is unchanged.

Run the codemod first: it rewrites what follows where it can and reports the rest.

A column is a builder: @Field(f.string().nullable().length(80)) replaces @Field({ type: String, nullable: true, length: 80 }). It is NOT NULL unless .nullable(), a key is .id() (@Id is gone), and a computed field is @Computed((post) => f.string().as(sql`...`)). A builder has no method for an option its type cannot take, so f.number().length(10) does not compile. The migration builder takes the same methods: table.string('email').length(80).nullable(), .default() for .defaultValue(), .id() for .primaryKey() and .as() for .computed().

@Index, @Filter and @Trigger are gone: list them in @Entity({ indexes, filters, triggers }). An index, check or trigger entry is written by the helper its callback hands over, indexes: (post, index) => [index([post.title], 'by_title')]. A base class carrying them without being a table is @Entity({ abstract: true, ... }). The codemod folds each decorator in, its comments with it.

A JSON index entry reads its path off the column’s ref, as the $where key it serves: index([user.settings.theme]) for text, { column: user.settings.rating, type: Number } for any other value, and type: [String] for an array’s elements. jsonPath and jsonArray are gone. An unnamed JSON index is now named after its path too, so the next migration rebuilds each one once. In the migration builder an expression entry is { expression: sql`lower(email)` }. An entry’s length is prefixLength on a column and charLength on a JSON path.

An entity is a decorated class or defineEntity({ name, fields }) with its other options as chained sections. defineEntity(Class, options), defineField, defineId, defineRelation, defineHook, defineIndex, defineTrigger and defineFilter are gone, and so are @Entity’s fields, relations, hooks and extends. The codemod rewrites a class defineEntity registered as export const Post = defineEntity({ name: 'Post', ... }), a table it named kept as table, folding the same class’s definers in; it reports a class it cannot convert as it is, such as one with methods. A decorated class names its base by extending it.

A foreign key column holding its own type must hold the type of the key it references, and f.decimal(precision, scale) carries its digits into its type: tsc names each mismatch.

$exclude is gone: write $select: { password: false }, which the codemod rewrites it to. A server answers a request still sending it with a 400, as it does any $ key a query does not take.

A $sort that projects a distance or a score puts it on the row’s type, so drop the as WithProjection<...> cast: WithProjection is gone. An empty $project throws on every SQL engine.

On MongoDB Atlas, a vector $sort’s $project holds the distance, lower nearer, as on the SQL engines, not Atlas’s score: flip a check like score > 0.8 into a cut-off on the distance.

A read captures its whole query, so QueryFindResult takes the field names or the query: QueryFindResult<User, 'id' | 'name'> keeps working, and QueryFindResult<User, typeof query> replaces the forms that named $select values, $exclude, $populate and $count key by key. QueryProjected, QueryOneProjected, QueryKeysetProjected and ProjectedQuery are gone: a function forwarding a query types it Captured<Query<E>, Q>, as the querier page shows. ReturningResult is WriteResults. tsc names every use to change.

NeonQuerierPool takes Neon’s ClientConfig: drop pool options such as max and idleTimeoutMillis. It holds no connection, so build it once at module scope, Cloudflare Workers included, and drop the pool.end() a handler ran for it.

On MongoDB, an updateMany or deleteMany that is paged, filtered through a relation, hooked, cascading or returning runs in a transaction, so it needs a replica set. A single-node one is enough for development.

uql-orm/migrate and uql-orm/nestjs no longer export the schema introspector classes or UqlContextInterceptor; the migrator and the Nest module still use them.

raw is renamed sql, which editors highlight as SQL: sql`...` , sql.join, sql.text. raw still works but is deprecated; uql-codemod renames it.

uql-orm/migrate, uql-orm/http and the driver entries export only what the docs use. tsc names any import that is gone.

Run uql-codemod first. It moves imports to the renamed entries (uql-orm/mariadb, uql-orm/mongodb, uql-orm/bun-sql) and off the removed ones (uql-orm/type, uql-orm/dialect, uql-orm/entity, uql-orm/querier, uql-orm/namingStrategy, now uql-orm), and renames Sqlite3QuerierPool to SqliteQuerierPool and IMigrationBuilder-style types to MigrationBuilder. Then:

  • Upgrade to TypeScript 5.7 or newer, and keep each driver on a supported major (pg 8, mongodb 6 or 7, …).
  • Import only what the docs show from uql-orm: the abstract dialect and querier classes, SqlQueryContext and helpers such as fieldOf are internal, and uql-orm/util is gone (the codemod moves raw, refs and the other names the root still exports).
  • all and run are tags, pool.all`SELECT ... ${id}` , which bind each value. The codemod rewrites a literal statement into one, its ? or $1 values included, and wraps SQL built at run time in sql.text(...). Wrap a SQL string you interpolate into a tag in sql.text too, or it is bound as a value (building a statement). An interpolated undefined throws, and a JSON document is bound as ${JSON.stringify(doc)}.
  • A pool call inside a pool.transaction() callback joins the transaction, uncommitted writes included. To read what is committed instead, use a querier of its own, await using querier = await pool.getQuerier(), on an engine with more than one connection (not SQLite, PGlite or local Turso).
  • In /http, HookContext and Hook are RequestHookContext and RequestHook.
  • A decimal is a string. Declare type: Number to keep a number.
  • Json<unknown> is a bare Json.
  • upsertOne resolves to the id and upsertMany to the ids: read ids[0] for firstId, and drop created and changes.
  • An undefined in $where or $having throws. Leave the key out, or write null.
  • A nested transaction() is a savepoint, and is refused on MongoDB.
  • A to-many update removes the children the payload leaves out. A to-one holding the key cascades 'persist' only (relations).
  • migrator.down() with no options reverts one migration; pass { step: Infinity } for all.
  • Replace pending() and executed() with status(). Drop the storage option and the migration storages (JsonMigrationStorage, MongoMigrationStorage).
  • generate:entities renames a column in place only when the two names differ by case or underscores (firstName, first_name). Write any other rename into the migration yourself, as renameColumn, before it drops the column.
  • Catch UqlUsageError where you caught UqlLockUsageError.
  • On MongoDB, the next migration or sync gives each collection with checks or enums a validator, replacing a hand-written one. Stored documents are checked on their next update.
  • A migration file that fails to load, or lacks up and down, stops up and down before anything runs. Move helper modules out of the migrations directory.
  • Migrator.pending() returns names. Replace getMigrations, getMigrationFiles, loadMigration and isMigration with status(), up() and down().
  • $regex is case-sensitive on MySQL and MariaDB too. Spell both cases where you relied on the collation: '^[Jj]ohn'.
  • Checks and enums are diffed and installed as _uql_<table>__<name>_<hash>. The next migration or sync adds each under its new name; drop the old ones by hand (each is warned about), or a widened enum keeps rejecting its new values.
  • On SQLite, D1 and Turso, that change rebuilds each table with a check or an enum. On D1 or remote libSQL, a referenced table needs foreign keys off to rebuild.
  • A custom MigrationStorage does not need ensureStorage.
  • Migrator.logger is read-only: pass logger in the Migrator options.
  • CreateSchemaOptions.foreignKeys is gone; a schema always carries its foreign keys.
  • drift:check on MongoDB fails where it reported drift. Use sync --dry-run to see index changes.
  • Turso needs @tursodatabase/database 0.8.2+ and @tursodatabase/serverless 1.4.1+.
  • The next sync indexes a foreign key whose only covering index is partial, fulltext, vector or a MySQL prefix one.
  • An upsert takes its update as the fourth argument, upsertOne(Entity, paths, row, { uses: { $inc: 1 } }), not { update: ... }. uql-codemod rewrites it.
  • An empty update {} leaves a conflicting row as it is. The codemod cannot tell it from an old empty options object, so delete such a {} yourself.
  • upsertOne, upsertMany, and saveOne/saveMany naming a key write cascaded relations, replacing a found row’s.
  • A statement on the querier inside its own findManyStream loop throws UqlUsageError. Run it on another connection, or after the loop on SQLite and PGlite (streaming).
  • findManyStream runs @AfterLoad per row, so a hook querying through its querier is refused there too.
  • A statement binding more values than its engine allows is refused: 100 on D1, 2098 on SQL Server, 32766 on SQLite, 32767 on PGlite, 65535 on MySQL and MariaDB. Split a long $in; lists UQL builds are split for you.
  • The migration builder’s expr is gone. uql-codemod rewrites each default as an entity spells it (expr.now() is currentTimestamp).
  • expr.onUpdateNow() has no rewrite: use m.raw(...), or a stamp.
  • A Date field without precision stores milliseconds on every engine (TIMESTAMPTZ(3), DATETIME2(3)). Generate a migration or sync, which rounds past the millisecond; precision: 6 keeps a column.
  • On MongoDB, a $select without the key does not return id. Name it.
  • Over HTTP, an update or delete answers its changed row count in data, not ids. A DELETE naming no rows is a 400.
  • uql-orm/browser drops getQuerier, getQuerierPool, setQuerierPool and ClientQuerierPool. Create new HttpQuerier('/api') where you use it; uql-codemod points at each use.
  • An insert must include every field declared !, except a single-column id and the version. Declare it ? if a defaultValue or onInsert fills it (required fields).
  • A type: 'time' field is a string, not a Date.
  • Import MongoQuerier, isMongoQuerier and MongoSchemaIntrospector from the MongoDB entry (uql-orm/mongodb since 0.100.0), including in each MongoDB migration. uql-codemod moves them.
  • On SQL Server, a trigger with of or where refuses a body of your own SQL. Write it with insertInto, updateTable and deleteFrom, joined in one sql where there are several.
  • A $where mixing a relation’s $size with a condition on its rows, and a $text $fields naming a non-string field, do not compile.
  • sync and generate:entities refuse a newly required field with no defaultValue on a table with rows. Declare a default, or add it nullable, fill it, then require it.
  • On SQLite, sync rebuilds a table to add a relation’s foreign key: read a planSync() before the next one.
  • On SQL Server, a trigger with of gets a new name, which sync installs in place of the old one.
  • uql-orm/betterAuth is removed until it returns as its own package.
  • createFetchHandler, createRequestHandler and querierMiddleware require include and serve only those entities: createFetchHandler({ pool, include: [User, Post] }).
  • exclude is gone. Leave those entities out of include.

A security filter guards writes as well as reads. A write outside it throws UqlSecurityError (403 over HTTP).

  • An insert gets the filter’s fields from the context; naming another value throws. Delete any onInsert tenant fill.
  • An update changing those fields throws. Move rows between tenants under a trusted context resolving to {}.
  • A save or upsert naming another tenant’s row fails on its key. On a guarded entity it reads conflicting rows first, so it runs two or three statements.
  • A filter that is not plain field equalities ($in, $or) refuses writes. Write those rows under a {} context.
  • UqlUsageError is not a TypeError; all three UQL errors extend UqlError. Test queryErrorKind(err) === 'usage' instead of instanceof TypeError.

Every SQL engine binds and reads a Date as UTC. Dates written from a non-UTC process read shifted until their columns are converted, so generate a migration:

  • Postgres, CockroachDB: Date columns become TIMESTAMPTZ. Run the migration with the session TimeZone set to the zone the app wrote in (usually UTC). columnType: 'timestamp' keeps a column zoneless. A DATE reads as UTC midnight.
  • MySQL, MariaDB: DATETIME becomes DATETIME(3); precision: 0 keeps whole seconds. Sessions run in UTC. A raw read on MariaDB returns a DATETIME as text.
  • SQLite, libSQL, Turso, D1: dates are stored as UTC text, not epoch milliseconds. Convert rows with UPDATE t SET col = strftime('%Y-%m-%d %H:%M:%f', col / 1000.0, 'unixepoch') WHERE col NOT LIKE '%-%'.
  • On SQL engines, $ne excludes rows where the column is NULL, like $nin, $not and $nor. MongoDB keeps them. To include them: { $or: [{ status: { $ne: 'draft' } }, { status: null }] }.
  • length, precision and scale on type: 'varchar' or 'decimal', and dimensions on columnType: 'vector', reach the column. Existing columns read as drift and are altered: check the values fit.
  • Rename UqlLockUsageError to UqlUsageError, thrown by every misuse. The old name still resolves to it.
  • Over HTTP, a call the API cannot carry out answers 400, not 500.
  • On an entity with a version field, updateMany over many rows is refused. Update each row by id.
  • Rename WithDistance and WithScore to WithProjection.
  • On SQLite, libSQL and Turso, a vector column created before 0.71.0 is TEXT and reported by drift:check. Recreate its table in a migration with the column as F32_BLOB, which libSQL’s vector index needs.
  • Run the codemod to append | null to each nullable column’s property. Give a column that never holds null nullable: false instead.
  • An updateMany or deleteMany naming no rows throws. Pass { unfiltered: true } for the whole table.
  • A write payload naming a readonly field does not compile.
  • Replace $sumDistinct and $avgDistinct with a $group on the field.
  • An RPC contract takes WireQuery<E>.
  • A sql fragment or Uint8Array in a browser-client query or payload does not compile, and a cast past the types throws. Keep both server-side.
  • Run the codemod to rename D1Database to D1Queryable.
  • A query type takes its SQL type before its key set: QueryWhere<E, Sql, K>.
  • On PostgreSQL and CockroachDB, generate a migration for each numeric jsonPath index, whose expression changed.
  • $elemMatch with one $eq or $in compares by JSON type, so '5' does not match 5. An object in $all matches an element holding its keys.
  • Run the codemod: every to-one names its foreign key in references and declares the column. For a composite key, declare a column per key and pair them.
  • tsc then reports what registration refused at startup: a skipping security filter, and a mappedBy or references column that cannot hold the key it joins.
  • Run the codemod, then give each junction column references.
  • Declare a relation for any @Field({ references }) column you $populate.
  • Read pool.dialect.dialectName instead of migrator.dialectName.
  • Write an index expression as sql in the list, (user) => [sql`lower(${user.email})`].
  • Move SQLite off the Bun SQL entry to SqliteQuerierPool.
  • Upgrade @tursodatabase/serverless to 1.3+, and pass a libSQL client you built to LibsqlQuerierPool.
  • SQLite reads an integer past 2^53 as text.
  • Run the codemod for the renamed options and col(), which is refs(Entity).
  • SQL an entity declares reads columns off a callback’s refs: (user) => sql`lower(${user.email})` .
  • A builder migration gets the builder first and the querier second: up(m, querier). Move querier.run calls to defineMigration.

Members are named by callbacks and keys, never strings, and the codemod rewrites all of it:

  • Definitions: @Index((post) => [post.title]), mappedBy: (post) => post.author, references.
  • aggregate() takes computed columns in $select, not $agg, and $text takes $fields: { title: true }.
await pool.aggregate(Order, {
$group: { status: true },
$select: { total: { $sum: { amount: true } } },
});
  • A to-many $populate and $count read in the parent’s statement, which needs MySQL 8.0.14+ and SQLite 3.44+.
  • Each table reads under its own name, relation key or join path, so raw SQL naming a related table uses that alias.
  • Every foreign key gets an index unless one leads with it (index: false opts out). The next migration adds them.
  • A BIGINT past 2^53 reads as exact text. Declare type: BigInt for a typed exact integer.
  • The codemod renames CrdbQuerier/NeonQuerier to PgQuerier and LibsqlQuerier/TursoQuerier to HranaQuerier.
  • Raw access on Bun SQL is pool.sql.
  • virtual and the raw('...') call are gone; the codemod rewrites both.
  • An after* hook’s this is a copy of the row as written, so mutating it leaves your object alone.

$where takes only a map. An id, a list of ids or a bare SQL fragment there does not compile, and answers 400 over HTTP. Name the key, and put SQL fragments in $and. The codemod renames QueryWhereMap to QueryWhere.

import { sql } from 'uql-orm';
await pool.findMany(User, { $where: { id: [1, 2] } });
await pool.findMany(User, { $where: { $and: [sql`"createdAt" > now()`] } });
  • BunSqlPostgresDialect, BunSqlCockroachDialect and BunSqliteDialect are gone; the pool builds the dialect.
  • saveOne/saveMany upsert on the key a row names. A stale id writes the row, and composite keys work.
  • A save naming its key fires @BeforeUpsert/@AfterUpsert: move logic from @BeforeUpdate.
  • Every write reports its id as the column value, or the key map on a composite.
  • upsertOne and upsertMany return id and ids (payload order), not firstId.
  • A key not called id, _id or uuid, and every composite, needs the idKey brand, which the codemod writes:
import { Entity, Field, f, idKey } from 'uql-orm';
@Entity()
export class Enrolment {
[idKey]?: 'studentId' | 'courseId';
@Field(f.number().id()) studentId!: number;
@Field(f.string().id()) courseId?: string;
}
  • On MongoDB, a key you supply is the document’s _id; older documents keep their minted id. Ids read as hex strings, and a database-generated key must be one MongoDB can mint.
  • virtual is renamed to computed; giving both throws. The codemod rewrites it.
  • sync applies foreign keys (except on SQLite): read a planSync() before the first one.
  • On MySQL and MariaDB, generated keys are not UNSIGNED, and an UNSIGNED key refuses constraints pointing at it. On a database created earlier, run this (under safe: false) before adding foreign keys, as a migration rather than at boot, since it rebuilds the table under a metadata lock:
MySQL
ALTER TABLE `Company` MODIFY COLUMN `id` BIGINT AUTO_INCREMENT;
  • A key filled by onInsert is not auto-increment.
  • serial, bigserial and smallserial are gone as columnType. Use @Id({ type: Number }) or @Id({ type: Number, columnType: 'int' }).
  • A naming strategy does not rewrite a table you named, so check what @Entity({ name: 'UserProfile' }) maps to.
  • migrator.autoSync, syncForce and syncEntity are one migrator.sync(options).

An option a column cannot use is an error. Delete:

  • length, precision, scale, autoIncrement, dimensions and distance where the type has no use for them.
  • DDL or generator options on a virtual field, and onUpdate beside updatable: false.
  • nullable: true on a key.
  • A defaultValue that is not the stored value (a JSON column keeps its SQL literal, defaultValue: '{}').
  • Keys and indexes are recognised by their columns, never by name, so nothing in your database is renamed.
  • Adding a key column to a table with rows fails until you fill it.
  • A second @Id composes a two-column PRIMARY KEY instead of replacing the first. Narrowing an inherited key is unaffected.
  • EntityMeta.id is ids, a list.
  • The pool runs every operation, so a writing function can take UniversalQuerier.
  • A hand-written QuerierPool must implement insertMany, updateMany, upsertOne, upsertMany and saveMany.

Decorators are the standard TC39 ones. The codemod drops experimentalDecorators, emitDecoratorMetadata and reflect-metadata, and reports what is left. Then:

  • Set target to anything but esnext, which leaves decorators untransformed.
  • Build with esbuild, SWC, Babel version: '2023-11', Bun or tsc. Oxc does not transform them, so Vite 8 needs one of the others.
  • Run uql.config.ts with bun or node --import tsx if it imports entities.
  • On NestJS, use defineEntity: Nest’s DI needs parameter decorators (NestJS).
  • Use Node 22.18 or newer (24 before 0.101.0).