Skip to content

rogerpadilla/uql

BranchesTags

Repository files navigation

uql

tests Coverage Status license npm version

UQL is the best ORM for TypeScript and modern JavaScript designed to be fast, safe, and easy to integrate into any application — especially AI and data‑heavy workloads. Built with a unified API across SQL and MongoDB.

const results = await querier.findMany(User, {
  $select: { name: true, profile: { $select: { picture: true } } },
  $where: { name: { $istartsWith: 'a' }, posts: { tags: { name: 'typescript' } } },
  $sort: { createdAt: 'desc' },
  $limit: 10,
});

Quick Start

npm install uql-orm pg

(Example using PostgreSQL; see Install for other databases)

import { PgQuerierPool } from 'uql-orm/postgres';

const pool = new PgQuerierPool({ host: 'localhost', database: 'app' });
const users = await pool.withQuerier((querier) => querier.findMany(User, { $limit: 10 }));

For production setup and migrations, jump to:

Note: For explicit lifecycle control, use manual getQuerier() + release() (shown in Core Query Pattern).

Guide Map

Core path:

Advanced and operations:

Features

Feature Why it matters
Intelligent Querying Deep auto-completion for operators and relations at any depth—no more guessing property names.
Serializable JSON Query objects are valid JSON, which makes them straightforward to transport over HTTP/gRPC/WebSockets.
Unified Dialects Write once, run anywhere. Seamlessly switch between PostgreSQL, MySQL, MariaDB, SQLite, and MongoDB.
Naming Strategies No more camelCase vs snake_case headaches. Map your code to your database automatically.
Smart SQL Engine Zero-allocation SQL generation with top-ranked results in our open benchmark.
Thread-Safe by Design Protect your data integrity with centralized task queues and the @Serialized() decorator.
Declarative Transactions Clean @Transactional() decorators that work beautifully with modern DI frameworks like NestJS.
Lifecycle Hooks Automate validation, timestamps, and computed logic with intuitive class-based decorators.
Aggregate Queries Real-time analytics with GROUP BY, HAVING, and native math operators across all dialects.
Semantic Search Native vector similarity search. Rank results by meaning using standard ORM operators.
Cursor Streaming Process millions of rows with a stable memory footprint using native driver-level cursors.
Modern & Versatile Pure ESM, high-res timing, built-in soft-delete, and first-class JSON/JSONB support.
Database Migrations Entity-First synchronization. DDL is auto-generated by diffing your code against the live DB.
Logging & Monitoring High-visibility debugging with slow-query detection and high-contrast terminal output.
Fullstack Bridge Speak to your database from the browser securely. First-party HttpQuerier removes API boilerplate.

1. Install

Install the core package and the driver for your database:

# Core
npm install uql-orm       # or bun add / pnpm add

Supported Drivers (pick according to your database)

Database Command
PostgreSQL (incl. Neon, Cockroach, Yugabyte) npm install pg
MySQL (incl. TiDB, Aurora) npm install mysql2
MariaDB npm install mariadb
SQLite npm install better-sqlite3
LibSQL (incl. Turso) npm install @libsql/client
MongoDB npm install mongodb
Cloudflare D1 Native (no driver needed)
Bun SQL Native (Incl. Postgres, MySQL, SQLite) Native (via bun:sql)

TypeScript Configuration

If you use the decorator-based approach, make sure your tsconfig.json is configured to support decorators and metadata:

{
  "compilerOptions": {
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true
  }
}

Note: UQL is Modern Pure ESM — ensure your project's module supports ESM imports (e.g., NodeNext, ESNext, Bundler).

2. Define your Entities

Declare your classes with decorators, or use the imperative approach for decorator-free registration. UQL's engine uses this metadata for both type-safe querying and precise DDL generation. This includes the new defineEntity API which works without requiring experimentalDecorators in your tsconfig.json.

Core Decorators

Decorator Purpose
@Entity() Marks a class as a database table/collection.
@Id() Defines the Primary Key with support for onInsert generators.
@Field() Standard column. Use { references: ... } for Foreign Keys.
@Index() Defines a composite or custom index on one or more columns.
@OneToOne Defines a one-to-one relationship.
@OneToMany Defines a one-to-many relationship.
@ManyToOne Defines a many-to-one relationship.
@ManyToMany Defines a many-to-many relationship.
@BeforeInsert Lifecycle hooks fired around database operations.
@AfterLoad Lifecycle hook fired after loading entities.

Type Abstraction: Logical vs. Physical

UQL separates the intent of your data from its storage. Both properties are optional; if omitted, UQL performs a best-effort inference using the TypeScript types from your class.

Property Purpose Values
type Logical Type (Abstraction). Used for runtime behavior and automatic SQL mapping. String, Number, Boolean, Date, BigInt, or semantic strings: 'uuid', 'json', 'vector', 'halfvec', 'sparsevec'.
columnType Physical Type (Implementation). Highest Priority. Bypasses UQL's inference for exact SQL control. Raw SQL types: 'varchar(100)', 'decimal(10,2)', 'smallint', etc. 
// Automatic inference from TypeScript types
@Field() name?: string;           // → TEXT (Postgres), VARCHAR(255) (MySQL)
@Field() age?: number;            // → INTEGER
@Field() isActive?: boolean;      // → BOOLEAN
@Field() createdAt?: Date;        // → TIMESTAMP

// Semantic types - portable across all databases
@Field({ type: 'uuid' })          // → UUID (Postgres), CHAR(36) (MySQL), TEXT (SQLite)
externalId?: string;

@Field({ type: 'json' })          // → JSONB (Postgres), JSON (MySQL), TEXT (SQLite)
metadata?: Json<{ theme?: string }>;

// Logical types with constraints - portable with control
@Field({ type: 'varchar', length: 500 })
bio?: string;

@Field({ type: 'decimal', precision: 10, scale: 2 })
price?: number;

// Exact SQL type - when you need dialect-specific control
@Field({ columnType: 'smallint' })
statusCode?: number;

Note: Use the Relation<T> utility type for relationship properties. It prevents TypeScript circular dependency errors while maintaining full type-safety throughout your app.

import { v7 as uuidv7 } from 'uuid';
import { Entity, Id, Field, OneToOne, OneToMany, ManyToOne, ManyToMany, type Relation, type Json } from 'uql-orm';

@Entity()
export class User {
  @Id({ type: 'uuid', onInsert: () => uuidv7() })
  id?: string;

  @Field({
    index: true,
  })
  name?: string;

  @Field({
    unique: true,
    comment: 'User login email',
  })
  email?: string;

  @OneToOne({
    entity: () => Profile,
    mappedBy: (profile) => profile.user,
    cascade: true,
  })
  profile?: Relation<Profile>;

  @OneToMany({
    entity: () => Post,
    mappedBy: (post) => post.author,
  })
  posts?: Relation<Post>[];
}

@Entity()
export class Profile {
  @Id({ type: 'uuid', onInsert: () => uuidv7() })
  id?: string;

  @Field()
  bio?: string;

  @Field({ references: () => User, foreignKey: 'fk_profile_user' })
  userId?: string;

  @OneToOne({ entity: () => User })
  user?: User;
}

@Entity()
export class Post {
  @Id()
  id?: number;

  @Field()
  title?: string;

  @Field({ references: () => User })
  authorId?: string;

  @ManyToOne({ entity: () => User })
  author?: User;

  @ManyToMany({
    entity: () => Tag,
    through: () => PostTag,
  })
  tags?: Tag[];
}

@Entity()
export class Tag {
  @Id({ type: 'uuid', onInsert: () => uuidv7() })
  id?: string;

  @Field()
  name?: string;
}

@Entity()
export class PostTag {
  @Id({ type: 'uuid', onInsert: () => uuidv7() })
  id?: string;

  @Field({ references: () => Post })
  postId?: number;

  @Field({ references: () => Tag })
  tagId?: string;
}

### Alternative: Imperative (Decorator-free) Definition

If you prefer a functional approach or are in an environment where decorators are disabled (e.g., some edge runtimes or specific build pipelines), you can register entities imperatively using `defineEntity`. **Crucially, this approach works without requiring `experimentalDecorators` or `emitDecoratorMetadata` in your `tsconfig.json`.**

This API supports bulk configuration of `fields`, `relations`, `indexes`, and `hooks` directly within the options object. UQL also exports helper functions like `defineField`, `defineId`, and `defineRelation` to assist with type-safe imperative setup.

```ts
import { defineEntity } from 'uql-orm';

class User {}

defineEntity(User, {
  name: 'users',
  fields: {
    id: { type: 'uuid', isId: true },
    name: { type: String },
    email: { type: String, unique: true },
  },

  indexes: [
    { columns: ['email'], unique: true },
  ],
  // You can also define hooks and relations here
});


## 3. Set up a pool

A pool manages connections (queriers). Initialize it once at application bootstrap (e.g., in `server.ts`).

```ts
import { SnakeCaseNamingStrategy, type Config } from 'uql-orm';
import { PgQuerierPool } from 'uql-orm/postgres'; // or mysql2, sqlite, bunSql, etc.
import { User, Profile, Post } from './entities';

export const pool = new PgQuerierPool(
  { host: 'localhost', database: 'uql_app', max: 10 },
  {
    logger: ['error', 'warn', 'migration'],
    namingStrategy: new SnakeCaseNamingStrategy(),
    slowQuery: { threshold: 1000 },
  }
);

export default {
  pool,
  entities: [User, Profile, Post],
  migrationsPath: './migrations',
} satisfies Config;

Notes:

  • Reuse one pool for both app queries and migrations to keep behavior (for example naming strategy) consistent.
  • If your architecture spans backend + browser, HttpQuerier reduces custom API mapping and keeps query semantics aligned.
  • Upgrading: use pool.dialect (not pool.dialectInstance), read pool.dialect.dialectName, and remove any top-level dialect from migrate config (see CHANGELOG.md).

Driver → pool → dialect class

Migrations and the CLI read pool.dialect.dialectName only (there is no separate top-level dialect in config). Pick the pool (and thus the dialect class) for your driver.

Import path Pool class Dialect class (on the pool) Notes
uql-orm/postgres PgQuerierPool PgDialect Node pg; base Postgres capabilities (native arrays, $n::jsonb).
uql-orm/postgres — PostgresDialect Base Postgres AST; subclass for other Postgres wire drivers.
uql-orm/neon NeonQuerierPool NeonDialect @neondatabase/serverless; extends PostgresDialect with Neon-oriented defaults.
uql-orm/cockroachdb CrdbQuerierPool CockroachDialect dialectName is cockroachdb; extends PostgresDialect.
uql-orm/mysql Mysql2QuerierPool MySql2Dialect mysql2 driver.
uql-orm/maria MariadbQuerierPool MariaDialect mariadb driver.
uql-orm/sqlite Sqlite3QuerierPool BetterSqlite3Dialect better-sqlite3 (or Bun SQLite in that pool).
uql-orm/libsql LibsqlQuerierPool LibsqlDialect @libsql/client.
uql-orm/d1 D1QuerierPool D1SqliteDialect Cloudflare D1.
uql-orm/mongo MongodbQuerierPool MongodbNativeDialect Official mongodb driver.
uql-orm/bunSql BunSqlQuerierPool inferred Dialect class chosen from SQL.Options (Postgres / MySQL / MariaDB / SQLite / Cockroach).

For wire-level differences on PostgreSQL (e.g. Bun SQL vs pg), BunSqlPostgresDialect merges POSTGRES_WIRE_DRIVER_CAPABILITIES from uql-orm/postgres/wireCapabilities (array literals + JSON text cast); PgDialect does not.

4. Manipulate the Data

UQL provides a straightforward API to interact with your data. Always ensure queriers are released back to the pool.

Core Query Pattern

const querier = await pool.getQuerier();
try {
  const results = await querier.findMany(User, {
    $select: {
      name: true,
      profile: { $select: { bio: true }, $required: true }, // INNER JOIN
    },
    $where: {
      status: 'active',
      name: { $istartsWith: 'a' },
    },
    $limit: 10,
  });
} finally {
  await querier.release(); // Always release back to the pool
}

Generated SQL (PostgreSQL):

SELECT "User"."name", "profile"."id" AS "profile_id", "profile"."bio" AS "profile_bio"
FROM "User"
INNER JOIN "Profile" AS "profile" ON "profile"."userId" = "User"."id"
WHERE "User"."status" = 'active' AND "User"."name" ILIKE 'a%'
LIMIT 10 OFFSET 0

Advanced Query Patterns

Modern Indexing: Semantic Search

AI-driven applications require ranking results by meaning. UQL treats vector similarity as a first-class citizen, allowing you to perform semantic search without raw SQL or proprietary extensions.

const results = await querier.findMany(Item, {
  $select: { id: true, title: true },
  $sort: { $vector: { embedding: queryVector } },
  $limit: 10,
});

Advanced: Virtual Fields & Raw SQL

Define complex logic directly in your entities using raw functions. These are resolved during SQL generation for peak efficiency.

@Entity()
export class Item {
  @Field({
    virtual: raw(({ ctx, dialect, escapedPrefix }) => {
      ctx.append('(');
      dialect.count(ctx, ItemTag, {
        $where: { itemId: raw(({ ctx }) => ctx.append(`${escapedPrefix}.id`)) }
      }, { autoPrefix: true });
      ctx.append(')');
    })
  })
  tagsCount?: number;
}

JSON Operators & Relation Filtering

Query nested JSON fields using type-safe dot-notation with full operator support. Wrap fields with Json<T> to get IDE autocompletion for valid paths. UQL generates native SQL per dialect.

// Filter by nested JSONB field paths
const items = await querier.findMany(Company, {
  $where: {
    'settings.isArchived': { $ne: true },
    'settings.priority': { $gte: 5 },
  },
});

PostgreSQL: WHERE ("settings"->>'isArchived') IS DISTINCT FROM $1 AND (("settings"->>'priority'))::numeric >= $2 MySQL: WHERE (\settings`->>'isArchived') <> ? AND CAST((`settings`->>'priority') AS DECIMAL) >= ?**MariaDB:**WHERE JSON_VALUE(`settings`, '$.isArchived') <> ? AND CAST(JSON_VALUE(`settings`, '$.priority') AS DECIMAL) >= ?**SQLite:**WHERE json_extract("settings", '$.isArchived') IS NOT ? AND CAST(json_extract("settings", '$.priority') AS REAL) >= ?`

Atomic JSON updates support $merge, $unset, and $push:

await querier.updateOneById(Company, id, {
  settings: { $merge: { theme: 'dark' }, $push: { tags: 'orm' }, $unset: ['deprecated'] },
});

Modern DB Baselines used by docs/examples: PostgreSQL 16+, MySQL 8.4+ (LTS), MariaDB 12.2+, SQLite 3.45+. Full generated SQL examples: JSON / JSONB docs.

Filter parent entities by their ManyToMany or OneToMany relations using automatic EXISTS subqueries:

// Find posts that have a tag named 'typescript'
const posts = await querier.findMany(Post, {
  $where: { tags: { name: 'typescript' } },
});

PostgreSQL: WHERE EXISTS (SELECT 1 FROM "PostTag" WHERE "PostTag"."postId" = "Post"."id" AND "PostTag"."tagId" IN (SELECT "Tag"."id" FROM "Tag" WHERE "Tag"."name" = $1))

Note: Wrap JSON fields with Json<T> to get autocompletion for valid dot-notation paths.

Aggregate Queries

Use querier.aggregate() for GROUP BY analytics with $count, $sum, $avg, $min, $max, and full $having support.

const results = await querier.aggregate(Order, {
  $group: {
    status: true,
    total: { $sum: 'amount' },
    count: { $count: '*' },
  },
  $having: { count: { $gt: 5 } },
  $sort: { total: -1 },
  $limit: 10,
});

Generated SQL (PostgreSQL):

SELECT "status", SUM("amount") "total", COUNT(*) "count"
FROM "Order"
GROUP BY "status"
HAVING COUNT(*) > $1
ORDER BY SUM("amount") DESC
LIMIT 10

For SELECT DISTINCT, add $distinct: true to any find query:

const names = await querier.findMany(User, {
  $select: { name: true },
  $distinct: true,
});
// → SELECT DISTINCT "name" FROM "User"

Learn more: See the full Aggregate Queries guide for $having operators, MongoDB pipeline details, and advanced patterns.

Cursor-Based Streaming

For large result sets, use findManyStream() to iterate row-by-row without loading everything into memory. Each driver uses its optimal native cursor API.

for await (const user of querier.findManyStream(User, { $where: { active: true } })) {
  process.stdout.write(JSON.stringify(user) + '\n');
}

Thread-Safe Transactions

UQL is one of the few ORMs with a centralized serialization engine. Transactions are guaranteed to be race-condition free.

Option A: Manual (Functional)

const result = await pool.transaction(async (querier) => {
  const user = await querier.findOne(User, { $where: { email: '...' } });
  await querier.insertOne(Profile, { userId: user.id, bio: '...' });
});

Option B: Declarative (Decorators)

Perfect for NestJS and other Dependency Injection frameworks. Use @Transactional() to wrap a method and @InjectQuerier() to access the managed connection.

import { Transactional, InjectQuerier, type Querier } from 'uql-orm';

export class UserService {
  @Transactional()
  async register({picture, ...user}: UserProfile, @InjectQuerier() querier?: Querier) {
    const userId = await querier.insertOne(User, user);
    await querier.insertOne(Profile, { userId, picture });
  }
}

Option C: Imperative

For granular control over the transaction lifecycle, manage begin, commit, rollback, and release yourself.

const querier = await pool.getQuerier();
try {
  await querier.beginTransaction();

  const userId = await querier.insertOne(User, { name: '...' });
  await querier.insertOne(Profile, { userId, picture: '...' });

  await querier.commitTransaction();
} catch (error) {
  await querier.rollbackTransaction();
  throw error;
} finally {
  await querier.release();
}

5. Migrations & Synchronization

UQL takes an Entity-First approach. You modify your TypeScript classes, and UQL handles the heavy lifting—auto-generating migration files by diffing your code against the live database.

# 1. Update your entity (add a field, change a type, etc.)
# 2. Auto-generate the migration
npx uql-migrate generate:entities add_user_nickname

# 3. Review and apply
npx uql-migrate up

Note: Keep entities as the source of truth to minimize drift between code and database schema.

1. Unified Configuration

Reuse the same uql.config.ts for your app and the CLI to ensure consistent settings (naming strategies, entities, pool):

// uql.config.ts
import type { Config } from 'uql-orm';
import { PgQuerierPool } from 'uql-orm/postgres';
import { User, Profile, Post } from './entities';

export default {
  pool: new PgQuerierPool({ /* ... */ }),
  entities: [User, Profile, Post],
  migrationsPath: './migrations',
} satisfies Config;

2. Manage via CLI

Use the CLI to manage your database schema evolution.

Command Description
generate:entities <name> Auto-generates a migration by diffing your entities against the current DB schema.
generate <name> Creates an empty timestamped file for manual SQL migrations (e.g., data backfills).
generate:from-db Scaffolds Entities from an existing database. Includes Smart Relation Detection.
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. Auto-generate schema changes from your entities
npx uql-migrate generate:entities add_profile_table

# 2. Apply changes
npx uql-migrate up

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

# 4. Scaffold entities from an existing DB (Legacy Adoption)
npx uql-migrate generate:from-db --output ./src/entities

# 5. Create a manual migration (for data backfills or custom SQL)
npx uql-migrate generate seed_default_roles

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 adds new tables/columns and blocks destructive changes (e.g., dropping a column or changing a VARCHAR to an INTEGER). Blocked actions are logged so you can migrate them 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-orm/migrate';
import config from './uql.config.js';

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

Explicit Entities

Alternatively, pass entities directly if you want to be explicit about which entities to sync:

import { Migrator } from 'uql-orm/migrate';
import { User, Profile, Post } from './entities/index.js';

const migrator = new Migrator(pool, {
  entities: [User, Profile, Post],
});
await migrator.autoSync({ logging: true });

Note: In development, autoSync accelerates iteration while still protecting data by blocking destructive schema changes.

6. Operations

6.1 Logging & Monitoring

UQL includes a structured logging system for query visibility and performance monitoring.

Log Levels

Level Description
query Standard Queries: Beautifully formatted SQL/Command logs with execution time.
slowQuery Bottleneck Alerts: Dedicated logging for queries exceeding your threshold. Use logParams: false to omit sensitive data.
error / warn System Health: Detailed error traces and potential issue warnings.
migration Audit Trail: Step-by-step history of schema changes.
skippedMigration Safety: Logs blocked unsafe schema changes during autoSync.
schema / info Lifecycle: Informative logs about ORM initialization and sync events.

Visual Feedback

The DefaultLogger provides high-contrast, colored output for quick debugging:

query: SELECT * FROM "user" WHERE "id" = $1 -- [123] [2ms]
slow query: UPDATE "post" SET "title" = $1 -- ["New Title"] [1250ms]
error: Failed to connect to database: Connection timeout

Note: In production, keep logs lean with logger: ['error', 'warn', 'slowQuery'].

Learn more about UQL at uql-orm.dev for details on:

Deep Dive: Tests & Technical Resources

For those who want to see the "engine under the hood," check out these resources in the source code:

Built with ❤️ and supported by

UQL is an open-source project proudly sponsored by Variability.ai.

About

The fastest TypeScript ORM

uql-orm.dev

Topics

postgres framework typescript orm migrations sqlite neon postgresql javascript-orm orm-framework node-orm orm-javascript orm-nodejs typescript-orm bun-orm cloudflare-d1 orm-typescript turso

Resources

Readme

License

MIT license
Activity

Stars

102 stars

Watchers

4 watching

Forks

2 forks
Report repository

Sponsor this project

  •  
Learn more about GitHub Sponsors

Packages

 
 
 

Contributors

Languages