@uql/core

v3.8.5

Published

3 days ago

One Language. Frontend to Backend.

UQL is the smartest ORM for TypeScript. It is engineered to be fast, safe, and universally compatible.

Runs Everywhere: Node.js, Bun, Deno, Cloudflare Workers, Electron, React Native, and the Browser.
Unified API: A consistent query interface for PostgreSQL (incl. CockroachDB, YugabyteDB), MySQL (incl. TiDB, Aurora), MariaDB, SQLite, LibSQL, Neon, Cloudflare D1, and MongoDB.

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

Why UQL?

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. |

1. Install

Install the core package and the driver for your database:

# Core
npm install @uql/core       # or bun add / pnpm add

Supported Drivers (pick according to your database)

TypeScript Configuration

Ensure your tsconfig.json is configured to support decorators and metadata:

{
  "compilerOptions": {
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    "module": "NodeNext",
    "target": "ESNext"
  }
}

Note: ES2020+ will work for target as well.

2. Define the Entities

Annotate your classes with decorators. UQL's engine uses this metadata for both type-safe querying and precise DDL generation.

Core Decorators

| Decorator | Purpose | | :-------------- | :----------------------------------------------------------------------------- | | @Entity() | Marks a class as a database table/collection. | | @Id() | Defines the Primary Key with support for onInsert generators (UUIDs, etc). | | @Field() | Standard column. Use { reference: ... } 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. | | @Virtual() | Defines a read-only field calculated via SQL (see Advanced). |

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 (provided emitDecoratorMetadata is enabled).

@Field() // Inference: Maps to TEXT (Postgres) or VARCHAR(255) (MySQL) automatically.
name?: string;

@Field({ type: 'uuid' }) // Recommended: Cross-database abstraction for UUIDs.
id?: string;

@Field({ columnType: 'varchar(500)' }) // Control: Explicitly forces a specific SQL type.
bio?: string;

import { v7 as uuidv7 } from 'uuid';
import { Entity, Id, Field, OneToOne, OneToMany, ManyToOne, ManyToMany, type Relation } from '@uql/core';

@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({ reference: () => User, foreignKey: 'fk_profile_user' })
  userId?: string;

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

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

  @Field()
  title?: string;

  @Field({ reference: () => 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({ reference: () => Post })
  postId?: number;

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

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

3. Set up a pool

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

import { SnakeCaseNamingStrategy, type Config } from '@uql/core';
import { PgQuerierPool } from '@uql/core/postgres'; // or mysql2, sqlite, etc.
import { User, Profile, Post } from './entities';

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

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

Pro Tip: Reusing the same connection pool for both your application and migrations is recommended. It reduces connection overhead and ensures consistent query settings (like naming strategies).

4. Manipulate the Data

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

const querier = await pool.getQuerier();
try {
  const users = await querier.findMany(User, {
    $select: {
      name: true,
      profile: { $select: ['bio'], $required: true } // INNER JOIN
    },
    $where: {
      status: 'active',
      name: { $istartsWith: 'a' } // Case-insensitive search
    },
    $limit: 10,
    $skip: 0
  });
} finally {
  await querier.release(); // Essential for pool health
}

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: 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;
}

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/core';

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 });
  }
}

5. Migrations & Synchronization

1. Unified Configuration

Ideally, use the same uql.config.ts for your application bootstrap and the CLI:

// uql.config.ts
import type { Config } from '@uql/core';

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

Why? Using a single config for both your app and the CLI is recommended for consistency. It prevents bugs where your runtime uses one naming strategy (e.g. camelCase) but your migrations use another (e.g. snake_case), or where the CLI isn't aware of all your entities. It enforces a Single Source of Truth for your database connection and schema.

2. Manage via CLI

Use the CLI to manage your database schema evolution.

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

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 });

Explicit Entities

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

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

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

6. Logging & Monitoring

UQL features a professional-grade, structured logging system designed for high visibility and sub-millisecond performance monitoring.

Log Levels

Visual Feedback

The DefaultLogger provides high-contrast, colored output out of the box:

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

Pro Tip: Even if you disable general query logging in production (logger: ['error', 'warn', 'slowQuery']), UQL stays silent until a query exceeds your threshold.

Learn more about UQL at uql.app 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:

Entity Mocks: See how complex entities and virtual fields are defined in entityMock.ts.
Core Dialect Logic: The foundation of our context-aware SQL generation in abstractSqlDialect.ts.
Comprehensive Test Suite:
- Abstract SQL Spec: Base test suite for all dialects.
- PostgreSQL | MySQL | SQLite specs.
- Querier Integration Tests: SQL generation & connection management tests.

Built with ❤️ and supported by

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