sqlserver-ts

A lightweight, high-performance TypeScript ORM-like data access library for Microsoft SQL Server, inspired by Dapper (C#).

📚 Documentation

Document	Description
Quick Start Guide	Get started in 5 minutes
Express Server Guide	Complete guide for using with Express.js
Type Safety Quick Reference	Quick reference for all type-safe methods
Type Safety Report	Detailed type safety verification and analysis
Certification	Production readiness certification
Project Summary	Technical overview and architecture
Changelog	Version history and updates

Features

⚡ Minimal overhead, near raw performance
🧩 Simple, type-safe API
🔒 Secure with parameterized queries
💡 Async/await everywhere
🧠 Focused on Microsoft SQL Server

Installation

npm install sqlserver-ts
# or
yarn add sqlserver-ts
# or
pnpm add sqlserver-ts

Quick Start

import { Database } from 'sqlserver-ts';

// Define your model
interface User {
  Id: number;
  Name: string;
  Email: string;
  IsActive: boolean;
}

// Create database instance
const db = new Database({
  server: 'localhost',
  database: 'MyAppDB',
  user: 'sa',
  password: 'YourStrong@Passw0rd',
  options: {
    encrypt: true,
    trustServerCertificate: true, // For local development
  },
});

// Query with parameters
const activeUsers = await db.query<User>(
  'SELECT * FROM Users WHERE IsActive = @IsActive',
  { IsActive: true }
);

console.log(activeUsers);

// Close connection
await db.close();

Core Features

1. Parameterized Queries

All queries use parameterized execution to prevent SQL injection:

const users = await db.query<User>(
  'SELECT * FROM Users WHERE Age > @Age AND Country = @Country',
  { Age: 25, Country: 'USA' }
);

2. Query Methods

// Get all results
const users = await db.query<User>('SELECT * FROM Users');

// Get first result
const user = await db.queryFirst<User>(
  'SELECT * FROM Users WHERE Id = @Id',
  { Id: 1 }
);

// Get first result or null
const user = await db.queryFirstOrDefault<User>(
  'SELECT * FROM Users WHERE Id = @Id',
  { Id: 999 }
);

// Get single result (throws if 0 or >1)
const user = await db.querySingle<User>(
  'SELECT * FROM Users WHERE Email = @Email',
  { Email: 'john@example.com' }
);

// Execute scalar query
const count = await db.executeScalar<number>(
  'SELECT COUNT(*) FROM Users WHERE IsActive = @IsActive',
  { IsActive: true }
);

3. Execute Commands

// INSERT, UPDATE, DELETE
const affected = await db.execute(
  'UPDATE Users SET IsActive = @IsActive WHERE Id = @Id',
  { IsActive: true, Id: 10 }
);

console.log(`${affected} rows affected`);

4. CRUD Helper Methods

// Insert
const userId = await db.insertAndGetId('Users', {
  Name: 'John Doe',
  Email: 'john@example.com',
  IsActive: true,
  CreatedAt: new Date(),
});

// Update
await db.update(
  'Users',
  { Name: 'Jane Doe' }, // SET
  { Id: 1 } // WHERE
);

// Delete
await db.delete('Users', { Id: 1 });

5. Transactions

await db.transaction(async (tx) => {
  // Deduct from account 1
  await tx.execute(
    'UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @Id',
    { Amount: 100, Id: 1 }
  );

  // Add to account 2
  await tx.execute(
    'UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @Id',
    { Amount: 100, Id: 2 }
  );

  // Transaction is automatically committed if no errors occur
  // Automatically rolled back if any error is thrown
});

6. Stored Procedures

// Call stored procedure
const users = await db.query<User>(
  'GetActiveUsers',
  { MinAge: 18 },
  { commandType: 'StoredProcedure' }
);

// Execute stored procedure
const affected = await db.execute(
  'UpdateUserStatus',
  { UserId: 1, Status: 'Active' },
  { commandType: 'StoredProcedure' }
);

7. Multiple Result Sets

const [users, orders] = await db.queryMultiple<[User[], Order[]]>(`
  SELECT * FROM Users WHERE IsActive = 1;
  SELECT * FROM Orders WHERE OrderDate > @Date;
`, { Date: new Date('2024-01-01') });

console.log(`Found ${users.length} users and ${orders.length} orders`);

8. Multi-Mapping (JOINs)

interface User {
  userId: number;
  userName: string;
}

interface Address {
  addressId: number;
  city: string;
}

const sql = `
  SELECT 
    u.Id as userId, u.Name as userName,
    a.Id as addressId, a.City as city
  FROM Users u
  JOIN Addresses a ON u.AddressId = a.Id
`;

const usersWithAddresses = await db.map<User, Address, User & { address: Address }>(
  sql,
  (user, address) => ({ ...user, address }),
  {},
  'addressId' // Split point
);

9. CamelCase Conversion

const db = new Database(config, logger, true); // Enable camelCase conversion

// SQL returns: FirstName, LastName, EmailAddress
// Result will be: firstName, lastName, emailAddress
interface User {
  firstName: string;
  lastName: string;
  emailAddress: string;
}

const users = await db.query<User>('SELECT FirstName, LastName, EmailAddress FROM Users');

10. Custom Logger

import { Database, ILogger } from 'sqlserver-ts';

class CustomLogger implements ILogger {
  log(message: string, data?: any): void {
    console.log(`[${new Date().toISOString()}] ${message}`, data);
  }

  error(message: string, error?: any): void {
    console.error(`[${new Date().toISOString()}] ${message}`, error);
  }
}

const db = new Database(config, new CustomLogger());

Configuration

interface IDatabaseConfig {
  server: string;
  database: string;
  user?: string;
  password?: string;
  port?: number;
  options?: {
    encrypt?: boolean;
    trustServerCertificate?: boolean;
    enableArithAbort?: boolean;
    instanceName?: string;
    useUTC?: boolean;
    connectTimeout?: number;
    requestTimeout?: number;
  };
  pool?: {
    max?: number;
    min?: number;
    idleTimeoutMillis?: number;
  };
}

Example Configuration

const db = new Database({
  server: 'localhost',
  database: 'MyAppDB',
  user: 'sa',
  password: 'YourStrong@Passw0rd',
  port: 1433,
  options: {
    encrypt: true,
    trustServerCertificate: false,
    enableArithAbort: true,
    connectTimeout: 15000,
    requestTimeout: 30000,
  },
  pool: {
    max: 20,
    min: 5,
    idleTimeoutMillis: 30000,
  },
});

Advanced Examples

Dynamic Query Building

const filters: Record<string, any> = {};
const whereClauses: string[] = [];

if (searchName) {
  whereClauses.push('Name LIKE @Name');
  filters.Name = `%${searchName}%`;
}

if (isActive !== undefined) {
  whereClauses.push('IsActive = @IsActive');
  filters.IsActive = isActive;
}

const whereClause = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';
const query = `SELECT * FROM Users ${whereClause}`;

const results = await db.query(query, filters);

Bulk Operations

await db.transaction(async (tx) => {
  const users = [
    { Name: 'User1', Email: 'user1@test.com' },
    { Name: 'User2', Email: 'user2@test.com' },
    { Name: 'User3', Email: 'user3@test.com' },
  ];

  for (const user of users) {
    await tx.execute(
      'INSERT INTO Users (Name, Email, CreatedAt) VALUES (@Name, @Email, GETDATE())',
      user
    );
  }
});

One-to-Many Relationships

const sql = `
  SELECT 
    u.Id as userId, u.Name as userName,
    o.Id as orderId, o.Amount as amount
  FROM Users u
  LEFT JOIN Orders o ON u.Id = o.UserId
`;

const results = await db.query(sql);

// Group orders by user
const usersWithOrders = new Map();
for (const row of results) {
  if (!usersWithOrders.has(row.userId)) {
    usersWithOrders.set(row.userId, {
      user: { userId: row.userId, userName: row.userName },
      orders: [],
    });
  }
  if (row.orderId) {
    usersWithOrders.get(row.userId).orders.push({
      orderId: row.orderId,
      amount: row.amount,
    });
  }
}

API Reference

Database Class

Constructor

new Database(config: IDatabaseConfig, logger?: ILogger, convertToCamelCase?: boolean)

Methods

• query<T>(sql: string, params?: Record<string, any>, options?: IQueryOptions): Promise<T[]>
• queryFirst<T>(sql: string, params?: Record<string, any>, options?: IQueryOptions): Promise<T>
• queryFirstOrDefault<T>(sql: string, params?: Record<string, any>, options?: IQueryOptions): Promise<T | null>
• querySingle<T>(sql: string, params?: Record<string, any>, options?: IQueryOptions): Promise<T>
• querySingleOrDefault<T>(sql: string, params?: Record<string, any>, options?: IQueryOptions): Promise<T | null>
• queryMultiple<T>(sql: string, params?: Record<string, any>): Promise<T>
• execute(sql: string, params?: Record<string, any>, options?: IQueryOptions): Promise<number>
• executeScalar<T>(sql: string, params?: Record<string, any>, options?: IQueryOptions): Promise<T | null>
• transaction<T>(work: (tx: ITransaction) => Promise<T>): Promise<T>
• map<T1, T2, TResult>(sql: string, mapper: (first: T1, second: T2) => TResult, params?: Record<string, any>, splitOn?: string): Promise<TResult[]>
• insert(tableName: string, data: Record<string, any>): Promise<number>
• insertAndGetId(tableName: string, data: Record<string, any>): Promise<number>
• update(tableName: string, data: Record<string, any>, where: Record<string, any>): Promise<number>
• delete(tableName: string, where: Record<string, any>): Promise<number>
• testConnection(): Promise<boolean>
• close(): Promise<void>

Testing

npm test

Building

npm run build

License

MIT

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Support

For issues and questions, please use the GitHub Issues page.

Acknowledgments

Inspired by Dapper - the king of Micro-ORMs for .NET.