DEV Community

Roger
Roger

Posted on

UQL v3: The Smartest TypeScript ORM got Smarter!

#webdev #javascript #typescript #programming

Database migrations that actually work. Naming conventions that match your style. One unified API for every database.

Enhanced database migrations with UQL

After weeks of development, I'm excited to announce UQL v3 - the most significant update yet to the UQL ORM.

If you've ever struggled with ORMs that fight your workflow, generate cryptic SQL, or lock you into a single database vendor, UQL was built for you.

What makes UQL different?

Syntax example:

const users = await querier.findMany(User, {
  $select: { email: true, profile: { $select: { picture: true } } },
  $where: { email: { $endsWith: '@domain.com' } },
  $sort: { createdAt: 'desc' },
  $limit: 100,
});
Enter fullscreen mode Exit fullscreen mode

No query builders. No string-based DSLs. Just plain TypeScript objects with full type safety and auto-completion at every level.

 

Features

Feature Description
Context-Aware Queries Deep type-safety for operators and relations at any depth.
Serializable JSON 100% valid JSON queries for easy transport over HTTP/Websockets.
Unified Dialects Write once, run anywhere: PostgreSQL, MySQL, SQLite, MongoDB, and more.
Naming Strategies Pluggable system to translate between TypeScript camelCase and database snake_case.
Smart SQL Engine Optimized sub-queries, placeholders ($1, $2), and minimal SQL generation via QueryContext.
Thread-Safe by Design Centralized task queue and @Serialized() decorator prevent race conditions.
Declarative Transactions Standard @Transactional() and @InjectQuerier() decorators for NestJS/DI.
Modern & Versatile Pure ESM, high-res timing, Soft-delete, and Vector/JSONB/JSON support.
Database Migrations Built-in Entity-First synchronization and a robust CLI for version-controlled schema evolution.
Logging & Monitoring Professional-grade monitoring with slow-query detection and colored output.

 

But what's New in UQL V3?

1. Database Migrations That Actually Work!

One of the most requested features is finally here. UQL now includes a robust migration system with two approaches: CLI-Powered Migrations.

Use the CLI to manage your database schema evolution.

Command Description
generate:from-db Scaffolds Entities from an existing database. Includes Smart Relation Detection.
generate <name> Creates an empty timestamped file for manual SQL migrations (e.g., data backfills).
generate:entities <name> Auto-generates a migration by diffing your entities against the current DB schema.
drift:check Drift Detection: Compares your defined entities against the actual database schema and reports discrepancies.
up Applies all pending migrations.
down Rolls back the last applied migration batch.
status Shows which migrations have been executed and which are pending.

Usage Examples 

# 1. Create a manual migration
npx uql-migrate generate seed_default_roles

# 2. Auto-generate schema changes from your code
npx uql-migrate generate:entities add_profile_table

# 3. Apply changes
npx uql-migrate up

# 4. Check for schema drift (Production Safety)
npx uql-migrate drift:check

# 5. Scaffold entities from an existing DB (Legacy Adoption)
npx uql-migrate generate:from-db --output ./src/entities
Enter fullscreen mode Exit fullscreen mode

Bun Users: If your uql.config.ts uses TypeScript path aliases (e.g., ~app/...), run migrations with the --bun flag to ensure proper resolution:

bun run --bun uql-migrate status

Or add a script to your package.json: "uql": "bun run --bun uql-migrate", then run commands like, e.g., bun run uql status.

3. AutoSync (Development)

Keep your schema in sync without manual migrations. It is Safe by Default: In safe mode (default), it strictly adds new tables and columns but blocks any destructive operations (column drops or type alterations) to prevent data loss. It provides Transparent Feedback by logging detailed warnings for any blocked changes, so you know exactly what remains to be migrated manually.

New Capabilities (v3.8+):

  • Schema AST Engine: Uses a graph-based representation of your schema for 100% accurate diffing, handling circular dependencies and correct topological sort orders for table creation/dropping.
  • Smart Relation Detection: When generating entities from an existing DB, UQL automatically detects relationships (OneToOne, ManyToMany) via foreign key structures and naming conventions (user_id -> User).
  • Bidirectional Index Sync: Indexes defined in @Field({ index: true }) or @Index() are synced to the DB, and indexes found in the DB are reflected in generated entities.

Important: For autoSync to detect your entities, they must be loaded (imported) before calling autoSync.

Using Your Config (Recommended)

If you follow the unified configuration pattern, your entities are already imported. Simply reuse it:

import { Migrator } from '@uql/core/migrate';
import config from './uql.config.js';

const migrator = new Migrator(config.pool, {
  entities: config.entities,
});
await migrator.autoSync({ logging: true });
Enter fullscreen mode Exit fullscreen mode

Configuration

Both approaches use a simple configuration file:

// uql.config.ts
import { PgQuerierPool } from '@uql/core/postgres';
import { User, Post, Profile } from './shared/models/index.js';

export default {
  pool: new PgQuerierPool({
    host: 'localhost',
    user: 'theUser',
    password: 'thePassword',
    database: 'theDatabase',
  }),
  entities: [User, Post, Profile],
  migrationsPath: './migrations',
};
Enter fullscreen mode Exit fullscreen mode

See more about Migrations here.

2. Naming Strategies - Your Code, Your Conventions

Ever had to manually translate between createdAt in TypeScript and created_at in your database? Those days are over.

UQL pluggable naming strategies system

UQL now supports pluggable naming strategies that automatically translate between your code conventions and your database schema:

import { SnakeCaseNamingStrategy } from '@uql/core';
import { PgQuerierPool } from '@uql/core/postgres';

export const pool = new PgQuerierPool(
  {
    host: 'localhost',
    database: 'my_db',
    ...
  },
  {
    // optional, defaults to same names as in properties.
    namingStrategy: new SnakeCaseNamingStrategy()
  }
);
Enter fullscreen mode Exit fullscreen mode

With SnakeCaseNamingStrategy:

TypeScript Database
UserAccount (class) user_account (table)
createdAt (property) created_at (column)
firstName (property) first_name (column)

The translation is bidirectional - it works for both queries and schema generation. Your TypeScript stays idiomatic, your database follows SQL conventions.

Custom Naming Strategies
Need something different? Just extend the base class:

import { DefaultNamingStrategy } from '@uql/core';

export class PrefixedNamingStrategy extends DefaultNamingStrategy {
  override tableName(entityName: string): string {
    return `tbl_${super.tableName(entityName)}`;
  }

  override columnName(propertyName: string): string {
    return `col_${super.columnName(propertyName)}`;
  }
}
Enter fullscreen mode Exit fullscreen mode

See more about Naming Strategies here.

 

Architecture Improvements

Beyond these new features, V3 includes significant internal improvements:

Context-Object SQL Generation

UQL now uses a sophisticated QueryContext pattern for SQL generation. This ensures perfectly indexed placeholders ($1, $2, etc. for PostgreSQL) even in complex nested sub-queries:

const items = await querier.findMany(Item, {
  $select: { id: true, name: true },
  $where: {
    $exists: raw(({ ctx, dialect, escapedPrefix }) => {
      dialect.find(
        ctx,
        User,
        {
          $select: { id: true },
          $where: {
            companyId: raw(({ ctx }) => {
              ctx.append(`${escapedPrefix}"companyId"`);
            }),
          },
        },
        { autoPrefix: true },
      );
    }),
  },
});
Enter fullscreen mode Exit fullscreen mode

No more out-of-sync parameter indices. No more debugging cryptic SQL errors.

Sticky Connections

Connection pooling is now optimized with "sticky connections." A Querier instance holds its connection until explicitly released, giving you predictable behavior in complex transaction scenarios:

const querier = await pool.getQuerier();

try {
  // All operations use the same connection
  await querier.insertOne(User, { … });
  await querier.insertOne(Profile, { … });
  await querier.updateMany(Stats, { … });
} finally {
  // Always release when done
  await querier.release();
}
Enter fullscreen mode Exit fullscreen mode

Thread-Safe by Default

The new Serialized decorator ensures database operations are race-condition-free:

@Serialized()
async createUserWithProfile(data: CreateUserDto) {
  // Operations are serialized automatically
}
Enter fullscreen mode Exit fullscreen mode

 

Why Another ORM?

I wrote about the Search of the Perfect ORM a while back. The core problems haven't changed!

UQL is an open-source project driven by the community and proudly sponsored by variability.ai - "Video Intelligence in Every Fluctuation".

If you're building with TypeScript and SQL databases, give UQL a spin. I'd love to hear your feedback!

Happy new coding year ahead! 🚀

Top comments (0)