@royaltics/nestjs-orm

A high-performance, strictly-typed PostgreSQL ORM designed specifically for NestJS applications. Optimized for high-concurrency, multi-tenant architectures and peak developer ergonomics.

🚀 Key Features

• Multi-Tenant First: Native support for Multi-Pool (One Database per Tenant) architecture.
• Strictly Typed: Full TypeScript autocomplete and inference for schemas, queries, and relations.
• SQL Injection Safe: Protected by design using Tagged Template Literals for raw queries.
• Prisma-like Ergonomics: Intuitive API including $query and $execute for raw SQL.
• Lightweight & Fast: Minimal abstractions, optimized for PostgreSQL performance.
• NestJS Native: Designed to be injected and scoped perfectly within the NestJS ecosystem.

📦 Installation

pnpm add @royaltics/nestjs-orm pg
pnpm add -D @types/pg tsx

⌨️ Database CLI (royaldb)

Managed database schema, migrations, and seeding without affecting your application's runtime.

1. Configuration

Create a royal-orm.config.ts in your root directory:

import { schemaMap } from './src/db/schema';

export default {
    schema: './src/db/schema.ts',
    databaseUrl: process.env.DATABASE_URL,
    migrationsDir: './migrations'
};

2. Available Commands

| Command | Description | | :--- | :--- | | pnpm royaldb generate --name <name> | Create a timestamped SQL migration file from your schema. | | pnpm royaldb migrate | Apply all pending .sql migrations from your migrations directory. | | pnpm royaldb sync | Directly push your schema to the database (Destructive sync). | | pnpm royaldb seed --file <path> | Execute a custom seed function. | | pnpm royaldb dto --output <dir> | Generate class-transformer/validator DTOs from schema. | | pnpm royaldb --help | Show full list of options. |

[!TIP] Use generate --create-db <dbname> to include a CREATE DATABASE statement in your initial SQL.

🛠️ Quick Start

1. Define your Schema

import { f, model, defineModels } from '@royaltics/nestjs-orm';

// Enums
export const UserRole = f.enum('user_role', ['admin', 'user', 'moderator']);
export const PostStatus = f.enum('post_status', ['draft', 'published', 'archived']);

// 1. Define Model Instances
const users = model('users', {
    id: f.cuid({ primaryKey: true }),
    email: f.varchar(255, { unique: true, }),
    password: f.text({}),
    fullName: f.varchar(120),
    role: UserRole,
    isActive: f.bool({ default: true }),
    metadata: f.jsonb({ isNull: true }),
    bio: f.text({ isNull: true }),
    points: f.int4({ default: 0 }),
    birthday: f.date(),
    createdAt: f.timestamp({ withZone: true, createAt: true }),
    updatedAt: f.timestamp({ withZone: true, updateAt: true })
}).indexes(['email'], ['role'], ['isActive', 'createdAt']);

const posts = model('posts', {
    id: f.bigint({ primaryKey: true }),
    title: f.varchar(255, {}),
    slug: f.varchar(255, { unique: true }),
    content: f.text(),
    status: PostStatus,
    authorId: f.cuid({}),
    categoryId: f.int4(),
    viewCount: f.int4({ default: 0 }),
    createdAt: f.timestamp({ createAt: true })
}).indexes(['slug', 'status'], ['createdAt']);

const roles = model('roles', {
    id: f.int4({ primaryKey: true }),
    name: f.varchar(50, { unique: true, }),
    permissions: f.jsonb({ default: {} })
});

const categories = model('categories', {
    id: f.int4({ primaryKey: true }),
    name: f.varchar(100, {}),
    slug: f.varchar(120, { unique: true })
}).indexes(['slug']);

const tags = model('tags', {
    id: f.int4({ primaryKey: true }),
    name: f.varchar(50, { unique: true, })
});

const postTags = model('post_tags', {
    postId: f.bigint({ primaryKey: true }),
    tagId: f.int4({ primaryKey: true })
});

// 2. Final Circular Schema (Late Binding & Full Typing)
export const schema = defineModels({ 
    roles, 
    users, 
    categories, 
    posts, 
    tags, 
    postTags
}).relations((m) => ({
    users: {
        posts: f.many(m.posts, 'authorId')
    },
    posts: {
        author: f.one(m.users, 'authorId', { onDelete: 'SET NULL', joinType: 'inner' }),
        category: f.one(m.categories, 'categoryId'), 
        tags: f.many(m.postTags, 'postId')
    },
    postTags: {
        post: f.one(m.posts, 'postId'),
        tag: f.one(m.tags, 'tagId')
    }
}));

// Destructure for export (retains types)
export const { roles, users, categories, posts, tags, postTags } = schema;
export const schemaMap = schema;

Column Options

| Option | Description | | :--- | :--- | | primaryKey | Marks column as Primary Key. | | isNull | Allows NULL (Columns are NOT NULL by default). | | unique | Adds a UNIQUE constraint. | | default | Static value or a function e.g. () => generateCuid(). | | createAt | Automatic CURRENT_TIMESTAMP on insertion. | | updateAt | Automatic CURRENT_TIMESTAMP on any update. | | withZone | For timestamps, uses TIMESTAMPTZ. |

2. Create your Database Service

import { Injectable, Scope, Inject } from '@nestjs/common';
import { REQUEST } from '@nestjs/core';
import { RoyalDbService, TypedRoyalDbService } from '@royaltics/nestjs-orm';
import { schemaMap } from './schema';

// Export an interface for full autocomplete
export interface DbService extends TypedRoyalDbService<typeof schemaMap> {}

@Injectable({ scope: Scope.REQUEST })
export class DbService extends RoyalDbService<typeof schemaMap> {
  constructor(
    @Inject('PG_POOL_SERVICE') poolService: any,
    @Inject(REQUEST) request: any,
  ) {
    // Resolve tenant pool dynamically
    const tenantId = request.headers['tenant-id'] || 'public';
    const pool = poolService.getPool(tenantId);
    
    super(pool, schemaMap, { debug: true });
  }
}

3. Use in Controllers

@Controller('users')
export class UserController {
  constructor(private readonly db: DbService) {}

  // 1. Find Many with filtering, sorting, and relations
  @Get()
  async findAll() {
    return this.db.users.find({
      where: { 
        isActive: true,
        email: { like: '%@gmail.com', mode: 'insensitive' } 
        posts: {
          some: {
            title: { like: '%@gmail.com', mode: 'insensitive' }
          }
        }
      },
      select: { id: true, email: true, name: true }, // Selective fields
      with: { posts: true }, // Eager load relations
      orderBy: { createdAt: 'desc' },
      limit: 20,
      offset: 0
    });
  }

  // 2. Find Unique or First
  @Get(':email')
  async findByEmail(@Param('email') email: string) {
    return this.db.users.findUnique({ where: { email } });
  }

  // 3. Create
  @Post()
  async create(@Body() dto: any) {
    return this.db.users.create(dto);
  }

  // 4. Update
  @Patch(':id')
  async update(@Param('id') id: string, @Body() data: any) {
    return this.db.users.update({
      where: { id: BigInt(id) },
      data
    });
  }

  // 5. Upsert (Update or Create)
  @Put(':email')
  async upsert(@Param('email') email: string, @Body() data: any) {
    return this.db.users.upsert({
      where: { email },
      update: { name: data.name },
      create: { email, name: data.name }
    });
  }

  // 6. Delete
  @Delete(':id')
  async remove(@Param('id') id: string) {
    return this.db.users.delete({ where: { id: BigInt(id) } });
  }

  // 7. Aggregates & Counts
  @Get('stats/count')
  async getStats() {
    const total = await this.db.users.count({ where: { isActive: true } });
    const avgSalary = await this.db.users.aggregate({
      sum: { salary: true },
      avg: { salary: true },
      where: { isActive: true }
    });
    return { total, avgSalary };
  }

  // 8. Transactions
  @Post('register')
  async register(@Body() data: any) {
    return this.db.transaction(async (tx) => {
      const user = await tx.users.create({ email: data.email, name: data.name });
      await tx.posts.create({ 
        title: 'Welcome Post', 
        authorId: user.id 
      });
      return user;
    });
  }
}

🛡️ Raw SQL & Safety

Royal-ORM provides safe raw execution methods using the sql tag to prevent SQL injection.

import { sql } from '@royaltics/nestjs-orm';

// Safe Query (returns rows)
const results = await db.$query<User[]>(sql`
  SELECT * FROM users WHERE email = ${untrustedInput}
`);

// Safe Execute (returns affected row count)
const updatedCount = await db.$execute(sql`
  UPDATE users SET is_active = false WHERE last_login < ${thresholdDate}
`);

🏗️ Architecture

Royal-ORM enforces a strict layer separation to maintain high cohesion and low coupling:

• Controller: Request handling.
• Service: Business logic.
• Repository/ORM: Data access via DbService.
• Domain: Schema definitions and models.

📊 Performance Benchmarks

Typical pipeline performance (10k rows, 10 concurrency) across multi-tenant pools:

• Insert: ~35ms
• Update: ~1ms
• Select: ~1ms
• Delete: ~0.3ms

Benchmarks performed on local PostgreSQL instance.

📜 License

MIT © Royaltics Solutions