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

``bash
npm install sqlserver-ts


or

yarn add sqlserver-ts

or

pnpm add sqlserver-ts
`

Quick Start

`typescript
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(
'SELECT * FROM Users WHERE IsActive = @IsActive',
{ IsActive: true }
);

console.log(activeUsers);

// Close connection
await db.close();
`

Core Features

$3

All queries use parameterized execution to prevent SQL injection:

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

$3

`typescript
// Get all results
const users = await db.query('SELECT * FROM Users');

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

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

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

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

$3

`typescript
// 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);
`

$3

`typescript
// 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 });
`

$3

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

$3

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

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

$3

`typescript
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);
`

$3

`typescript
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(
sql,
(user, address) => ({ ...user, address }),
{},
'addressId' // Split point
);
`

$3

`typescript
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('SELECT FirstName, LastName, EmailAddress FROM Users');
`

$3

`typescript
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

`typescript
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;
};
}
`

$3

`typescript
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

$3

`typescript
const filters: Record = {};
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);
`

$3

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

$3

`typescript
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

$3

#### Constructor
`typescript
new Database(config: IDatabaseConfig, logger?: ILogger, convertToCamelCase?: boolean)
`

#### Methods

- query(sql: string, params?: Record, options?: IQueryOptions): Promise
- queryFirst(sql: string, params?: Record, options?: IQueryOptions): Promise
- queryFirstOrDefault(sql: string, params?: Record, options?: IQueryOptions): Promise
- querySingle(sql: string, params?: Record, options?: IQueryOptions): Promise
- querySingleOrDefault(sql: string, params?: Record, options?: IQueryOptions): Promise
- queryMultiple(sql: string, params?: Record): Promise
- execute(sql: string, params?: Record, options?: IQueryOptions): Promise
- executeScalar(sql: string, params?: Record, options?: IQueryOptions): Promise
- transaction(work: (tx: ITransaction) => Promise): Promise
- map(sql: string, mapper: (first: T1, second: T2) => TResult, params?: Record, splitOn?: string): Promise
- insert(tableName: string, data: Record): Promise
- insertAndGetId(tableName: string, data: Record): Promise
- update(tableName: string, data: Record, where: Record): Promise
- delete(tableName: string, where: Record): Promise
- testConnection(): Promise
- close(): Promise

Testing

`bash
npm test
`

Building

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