EntityTS

Enterprise-grade TypeScript ORM and Stored Procedure Engine for Node.js, inspired by EF Core with Prisma-grade developer ergonomics. Powered by a 100% native proprietary SQL compiler (zero Knex, zero runtime query builder bloat), strictly isolated database drivers, and multi-table result sets.

Table of Contents:-

• Key Architectural Highlights
• Installation & Isolated Drivers
• Quick Start in 2 Minutes
  • 1. Define Entity
  • 2. Define DbContext
  • 3. Application Usage (Singleton Pattern)
• Database Providers Configuration
• Stored Procedures (Single & Multiple Tables)
  • Multiple Result Sets (Multiple Tables)
  • Output Parameters & Return Values
  • Sequential Streaming Reader
• DbSet LINQ Querying
  • Filtering, Sorting & Pagination
  • Keyset / Cursor Pagination
  • Native JSON Path Querying
  • Dialect-Aware Full-Text Search
  • Safe Raw SQL ($queryRaw & $executeRaw)
• Relations & Eager Loading (.include)
• Change Tracking & Entity Mutations
• Soft Delete & Audit Fields
• High-Performance Bulk Operations
• Transactions, Savepoints & Resilience
• Framework Integration (Express, Fastify, NestJS)
• Framework Integration (Express, Fastify, NestJS)
• EntityTS CLI Suite
  • Execution Benchmarks
  • Code-First Migrations
  • Database-First Scaffolding
• Execution Performance Benchmarking
• Unit Testing with MockDbAdapter
• Community, Support & Feedback

Key Architectural Highlights

• First-Class Stored Procedures: Native typed input/output parameters, return codes, and multi-table results (.queryMultiple<[T1, T2]>()).
• Zero-Bloat Isolated Drivers: If you use SQL Server (mssql), only mssql is installed—never forces pg, mysql2, or better-sqlite3.
• 100% Native Execution Engine: No Knex, no external query builder dependencies. Proprietary cross-dialect AST SQL compiler.
• Prisma-Grade Developer Ergonomics: Keyset cursor pagination, nested .include() eager loading, $queryRaw, soft deletes, and automatic audit fields.
• Flexible Architectures: Native support for Application-Wide Singleton (one connection pool shared across your server) or Scoped Per-Request instances.
• High-Throughput Execution & Benchmarking: Built-in comprehensive benchmark suite profiling raw SQL, DbSet LINQ, bulk operations, and stored procedures (npm run benchmark or entityTS benchmark).

Installation & Isolated Drivers

Install the core package:

npm install entityts-orm reflect-metadata
# or
pnpm add entityts-orm reflect-metadata
# or
yarn add entityts-orm reflect-metadata
# or
bun add entityts-orm reflect-metadata

Install ONLY the Database Driver You Need

entityTS guarantees driver isolation. It does not install unused database drivers into your project.

Use the built-in CLI tool to install the driver for your specific database:

# Microsoft SQL Server (installs mssql only — NEVER touches pg)
npx entityTS add mssql

# PostgreSQL (installs pg only — NEVER touches mssql)
npx entityTS add postgres

# MySQL / MariaDB (installs mysql2 only)
npx entityTS add mysql

# SQLite (installs better-sqlite3 only)
npx entityTS add sqlite

# Serverless (Turso, Neon, PlanetScale)
npx entityTS add turso
npx entityTS add neon
npx entityTS add planetscale

Driver Isolation Guard: If your DbContext is configured for SQL Server (mssql), running entityTS add postgres will automatically warn and block to prevent accidental package bloat.

Enable TypeScript Decorators

Ensure your tsconfig.json contains:

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

Quick Start in 2 Minutes

1. Define Entity

import { Entity, Table, Column, PrimaryKey, CreatedAt, UpdatedAt, SqlType } from 'entityts-orm';

@Entity()
@Table('users')
export class User {
  @PrimaryKey({ autoIncrement: true })
  @Column({ type: SqlType.Int })
  id!: number;

  @Column({ name: 'full_name', type: SqlType.VarChar, maxLength: 100 })
  name!: string;

  @Column({ name: 'email', unique: true })
  email!: string;

  @Column({ name: 'score', type: SqlType.Int, defaultValue: 0 })
  score!: number;

  @CreatedAt()
  createdAt!: Date;

  @UpdatedAt()
  updatedAt!: Date;
}

2. Define DbContext

import { DbContext, DbContextOptionsBuilder } from 'entityts-orm';
import { User } from './User';

export class AppDbContext extends DbContext {
  // Register DbSet collections
  public users = this.set(User);

  protected override onConfiguring(options: DbContextOptionsBuilder): void {
    // Connect to SQL Server:
    options.useSqlServer(
      process.env.DATABASE_URL || 'Server=localhost;Database=mydb;User Id=sa;Password=secret;',
    );

    // Or PostgreSQL:
    // options.usePostgres(process.env.DATABASE_URL || 'postgresql://localhost:5432/mydb');

    // Or MySQL:
    // options.useMysql(process.env.DATABASE_URL || 'mysql://root:secret@localhost:3306/mydb');

    // Or SQLite:
    // options.useSqlite('./data.db');
  }
}

3. Application Usage (Singleton Pattern)

In modern Node.js web applications (Express, Fastify, NestJS), you can create one shared instance of AppDbContext for the entire application to reuse the connection pool:

// db.ts
import { AppDbContext } from './AppDbContext';
export const db = new AppDbContext();

// server.ts
import express from 'express';
import { db } from './db';

const app = express();
app.use(express.json());

// Query records
app.get('/users', async (req, res) => {
  const users = await db.users
    .where(u => u.gt('score', 50))
    .orderBy('name', 'asc')
    .toList();

  res.json(users);
});

// Insert record
app.post('/users', async (req, res) => {
  const user = await db.users.add(req.body);
  res.status(201).json(user);
});

app.listen(3000, () => console.log('Server running on http://localhost:3000'));

Database Providers Configuration

DbContextOptionsBuilder offers intuitive configuration for standard and serverless databases:

// SQL Server (mssql)
options.useSqlServer({
  server: 'localhost',
  port: 1433,
  user: 'sa',
  password: 'Password123!',
  database: 'AppDb',
  options: { encrypt: true, trustServerCertificate: true },
  pool: { max: 20, min: 2 },
});

// PostgreSQL (pg)
options.usePostgres('postgresql://user:pass@localhost:5432/app_db');

// MySQL / MariaDB (mysql2)
options.useMysql('mysql://root:secret@localhost:3306/app_db');

// SQLite (better-sqlite3)
options.useSqlite('./data/app.db');

// Turso / libSQL (edge serverless)
options.useTurso({
  url: 'libsql://my-db.turso.io',
  authToken: process.env.TURSO_AUTH_TOKEN,
});

// Neon (serverless postgres)
options.useNeon(process.env.NEON_DATABASE_URL!);

// PlanetScale (serverless mysql)
options.usePlanetScale({
  host: process.env.DATABASE_HOST,
  username: process.env.DATABASE_USERNAME,
  password: process.env.DATABASE_PASSWORD,
});

Stored Procedures (Single & Multiple Tables)

Stored procedures are first-class citizens in entityTS.

1. Basic Procedure Execution

// Returns typed records directly
const topUsers = await db
  .procedure('usp_GetTopUsers')
  .input({ MinScore: 100, DepartmentId: 4 })
  .query<User>();

// Returns single scalar value
const totalSales = await db.procedure('usp_GetTotalSales').input({ Year: 2026 }).scalar<number>();

// Executes non-query procedure and returns rowsAffected and return code
const { rowsAffected, returnValue } = await db
  .procedure('usp_ArchiveInactive')
  .input({ DaysThreshold: 90 })
  .run();

Multiple Result Sets (Multiple Tables)

When a stored procedure executes multiple SELECT statements, entityTS returns the tables as a strongly typed tuple via .queryMultiple<[T1, T2]>():

// Stored procedure executing 3 SELECT queries:
// 1. SELECT * FROM Customers WHERE Id = @Id;
// 2. SELECT * FROM Orders WHERE CustomerId = @Id;
// 3. SELECT * FROM Rewards WHERE CustomerId = @Id;

const [customers, orders, rewards] = await db
  .procedure('usp_GetCustomerDashboard')
  .input({ Id: 101 })
  .queryMultiple<[Customer[], Order[], Reward[]]>();

console.log(customers[0].name);
console.log(`Customer has ${orders.length} orders`);
console.log(`Reward Tier: ${rewards[0].tier}`);

Multi-Table Query with Output Parameters:

const {
  records: [customers, orders],
  out,
} = await db
  .procedure('usp_GetCustomerDashboard')
  .input({ Id: 101 })
  .output<{ Status: string; ExecutionMs: number }>()
  .queryMultiple<[Customer[], Order[]]>();

console.log(out.Status); // Strongly-typed output parameter
console.log(customers); // Table 1 records
console.log(orders); // Table 2 records

Sequential Streaming Reader

For large result sets, read tables sequentially using .reader():

const reader = await db
  .procedure('usp_GetQuarterlyReport')
  .input({ Quarter: 'Q1', Year: 2026 })
  .reader();

// Read 1st table: Summary
const summary = await reader.read<ReportSummary>();

// Move to next table: Line Items
if (await reader.nextResult()) {
  const items = await reader.read<ReportItem>();
}

// Move to next table: Audit Logs
if (await reader.nextResult()) {
  const auditLogs = await reader.read<AuditEntry>();
}

// Access output params or return value at the end
const returnValue = reader.returnValue;

DbSet LINQ Querying

DbSet<T> provides full LINQ-style query chaining:

const users = await db.users
  .where(q => q.gt('score', 75).and().like('email', '%@company.com'))
  .orderBy('score', 'desc')
  .take(20)
  .skip(0)
  .toList();

Keyset / Cursor Pagination

Cursor-based pagination avoids the performance penalty of high OFFSET values:

// Page 1
const page1 = await db.users.orderBy('id', 'asc').toCursorPage({ limit: 10, cursorColumn: 'id' });

console.log(page1.items); // 10 items
console.log(page1.nextCursor); // Opaque URL-safe token (e.g. 'eyJpZCI6MTB9')
console.log(page1.hasNextPage); // true

// Page 2 (pass cursor from client)
const page2 = await db.users
  .orderBy('id', 'asc')
  .toCursorPage({ limit: 10, cursorColumn: 'id', cursor: page1.nextCursor });

Native JSON Path Querying

Query JSON columns natively across SQL Server, PostgreSQL, MySQL, and SQLite:

// Automatically compiles to:
// - MSSQL:       JSON_VALUE(metadata, '$.address.city') = 'New York'
// - PostgreSQL:  metadata->'address'->>'city' = 'New York'
// - MySQL:       JSON_UNQUOTE(JSON_EXTRACT(metadata, '$.address.city')) = 'New York'
// - SQLite:      json_extract(metadata, '$.address.city') = 'New York'
const results = await db.users.whereJson('metadata', 'address.city', '=', 'New York').toList();

Dialect-Aware Full-Text Search

// Compiles to CONTAINS in MSSQL, to_tsvector in PostgreSQL, MATCH...AGAINST in MySQL:
const articles = await db.articles
  .whereSearch(['title', 'content'], 'typescript enterprise architecture')
  .toList();

Safe Raw SQL ($queryRaw & $executeRaw)

Execute raw SQL safely using tagged template literals. Variables are automatically converted into parameterized placeholders:

const minScore = 80;
const status = 'active';

// Parameterized query execution
const users = await db.$queryRaw<User>`
  SELECT * FROM users 
  WHERE score >= ${minScore} AND status = ${status}
`;

// Parameterized non-query execution
const affected = await db.$executeRaw`
  UPDATE users 
  SET status = 'archived' 
  WHERE last_login < ${cutoffDate}
`;

Relations & Eager Loading (.include)

Define relationships using decorators and eager load them without N+1 query problems:

@Entity()
@Table('orders')
export class Order {
  @PrimaryKey() id!: number;
  @Column() total!: number;
  @Column() userId!: number;

  @BelongsTo(() => User, { foreignKey: 'userId' })
  user?: User;

  @HasMany(() => OrderItem, { foreignKey: 'orderId' })
  items?: OrderItem[];
}

// Eager load related orders and order items
const usersWithOrders = await db.users.include('orders.items').toList();

// Prisma-style boolean inclusion object
const users = await db.users.include({ orders: true, profile: false }).toList();

Change Tracking & Entity Mutations

entityTS features transparent Proxy-based change tracking:

// 1. Fetch and track an entity
const user = await db.users.track(1);

// 2. Mutate properties directly — Proxy tracks mutations automatically
user.score += 50;
user.name = 'Jane Doe';

// 3. Queue new entities or removals
db.changeTracker.add(new User({ name: 'Bob', email: '[email protected]' }));
db.changeTracker.remove(oldUser);

// 4. Save all modifications in a single atomic transaction
const changes = await db.saveChanges();
console.log(`Persisted ${changes} modifications.`);

Soft Delete & Audit Fields

Enable soft deletion and auditing with clean decorators:

@Entity()
@Table('products')
@SoftDelete('deleted_at')
export class Product {
  @PrimaryKey() id!: number;
  @Column() name!: string;

  @CreatedAt() createdAt!: Date;
  @UpdatedAt() updatedAt!: Date;
  @CreatedBy() createdBy?: string;

  @Column({ name: 'deleted_at', nullable: true })
  deletedAt?: Date;
}

// Queries automatically filter out soft-deleted records:
const active = await db.products.toList();

// Include soft-deleted rows:
const all = await db.products.withDeleted().toList();

// Query only soft-deleted rows:
const deleted = await db.products.onlyDeleted().toList();

// Soft-delete a record (sets deleted_at timestamp):
await db.products.remove(10);

// Hard-delete permanently from database:
await db.products.hardRemove(10);

High-Performance Bulk Operations

Execute batch operations that bypass row-by-row overhead:

// Bulk Insert with automatic batching
await db.products.bulkInsert(newProducts, {
  batchSize: 1000,
  ignoreDuplicates: true,
});

// Bulk Update matching primary keys
await db.products.bulkUpdate(
  [
    { id: 1, price: 19.99 },
    { id: 2, price: 29.99 },
  ],
  { keys: ['id'], update: ['price'] },
);

// Bulk Upsert (Insert or Update on conflict)
await db.products.bulkUpsert(records, {
  conflictKeys: ['sku'],
  update: ['price', 'name'],
});

// Bulk Delete by predicate
await db.products.bulkDelete({ discontinued: true });

Transactions, Savepoints & Resilience

Managed Transactions

await db.useTransaction(async tx => {
  // Execute DbSet operations in transaction:
  await db.accounts.inTransaction(tx).update(fromId, { balance: fromBalance - 100 });
  await db.accounts.inTransaction(tx).update(toId, { balance: toBalance + 100 });

  // Execute Stored Procedure in the same transaction:
  await db
    .procedure('usp_LogTransfer')
    .input({ FromId: fromId, ToId: toId, Amount: 100 })
    .inTransaction(tx)
    .run();
}); // Commits automatically, or rolls back completely if an error is thrown.

Resilient Retry Strategy

Handle transient network blips and deadlocks automatically:

// In onConfiguring:
options.withExecutionStrategy({
  maxRetryCount: 3,
  maxDelayMs: 5000,
  retryableErrorCodes: ['ETIMEOUT', 'ECONNRESET', '1205'], // SQL Server deadlock 1205
});

Framework Integration (Express, Fastify, NestJS)

Express (Singleton vs Scoped)

// Pattern 1: Application-wide Singleton (Recommended)
import { db } from './db';
app.get('/users', async (req, res) => {
  res.json(await db.users.toList());
});

// Pattern 2: Scoped Per-Request Middleware
import { dbContextMiddleware } from 'entityts-orm';
import { AppDbContext } from './AppDbContext';

app.use(dbContextMiddleware(AppDbContext));
app.get('/users', async (req, res) => {
  const scopedDb = req.dbContext as AppDbContext;
  res.json(await scopedDb.users.toList());
});

NestJS Dynamic Module

// app.module.ts
import { Module } from '@nestjs/common';
import { DbContextModule } from 'entityts-orm';
import { AppDbContext } from './AppDbContext';

@Module({
  imports: [
    DbContextModule.forRoot({
      context: AppDbContext,
    }),
  ],
})
export class AppModule {}

// users.service.ts
import { Injectable } from '@nestjs/common';
import { InjectDbContext } from 'entityts-orm';
import { AppDbContext } from './AppDbContext';

@Injectable()
export class UsersService {
  constructor(@InjectDbContext(AppDbContext) private readonly db: AppDbContext) {}

  async findAll() {
    return this.db.users.toList();
  }
}

EntityTS CLI Suite

entityTS provides a full-featured CLI tool for migrations, benchmarks, and reverse engineering.

entityTS <command> [options]

1. Driver Management

# Install ONLY the package required for your database
npx entityTS add mssql
npx entityTS add postgres
npx entityTS add mysql
npx entityTS add sqlite

# Initialize project with DbContext and install chosen driver
npx entityTS init --db mssql

2. Execution Benchmarks

Run the built-in execution benchmark suite to measure query compilation, hydration throughput (ops/sec), and latency:

# Run execution benchmarks
npm run benchmark

# Or via CLI
npx entityTS benchmark --iterations 500

# Filter specific scenario categories
npx entityTS benchmark --filter "Raw SQL|DbSet Query"

# Output in JSON format
npx entityTS benchmark --json

Includes:

• Raw SQL execution: queryRaw, queryScalar, and tagged template literal timing.
• DbSet queries: toList(), first(), .where(), .orderBy(), .take(), and .cache().
• Mutations & Bulk: add(), addRange(), bulkInsert(), update(), and bulkUpdate().
• Change Tracking: Proxy mutation detection and saveChanges() batch flushing.
• Stored Procedures: Parameter binding and execution.
• Resilience: DefaultExecutionStrategy retry overhead profiling.

3. Code-First Schema Management

# Push entity metadata directly to database (ideal for development)
npx entityTS db:push --context src/database/AppDbContext.ts

# Dry run — inspect DDL statements without applying
npx entityTS db:push --context src/database/AppDbContext.ts --dry-run

# Generate migration file from entity changes
npx entityTS db:migrate:generate AddUserColumns --context src/database/AppDbContext.ts

# Scaffold a blank migration file
npx entityTS db:migrate:create CustomDataMigration

4. Database-First Scaffolding

Reverse-engineer an existing database into TypeScript entity classes and a DbContext:

npx entityTS db:scaffold --context src/database/AppDbContext.ts --output src/entities

Unit Testing with MockDbAdapter

Write fast, deterministic unit tests without running a database server or container:

import { MockDbAdapter } from 'entityts-orm';
import { AppDbContext } from './AppDbContext';

describe('UserService', () => {
  it('fetches active users from mock adapter', async () => {
    const mock = new MockDbAdapter({
      tables: {
        users: [
          { id: 1, full_name: 'Alice', email: '[email protected]', score: 95 },
          { id: 2, full_name: 'Bob', email: '[email protected]', score: 40 },
        ],
      },
      procedures: {
        usp_GetCustomerDashboard: {
          records: [
            [{ id: 1, name: 'Alice' }], // Table 1 (Customer)
            [{ id: 101, total: 450.0 }], // Table 2 (Orders)
          ],
          returnValue: 0,
        },
      },
    });

    const db = new AppDbContext({ adapter: mock });
    const topUsers = await db.users.where(u => u.gt('score', 50)).toList();

    expect(topUsers).toHaveLength(1);
    expect(topUsers[0].name).toBe('Alice');
  });
});

Community, Support & Feedback

We'd love to hear how you're using EntityTS and what features you'd like to see next!

• Found a bug or have a feature request? Open an issue on GitHub Issues using our interactive issue templates.
• General discussions & questions: Join the conversation on GitHub Discussions.
• Documentation & Guides: Browse the complete guides and API reference at nitishprajapati5.github.io/EntityTS.
• Direct contact / Commercial support: Reach out to Nitish Prajapati via LinkedIn or email at [email protected].

License

MIT (c) Nitish Mahendra Prajapati