azure-sql.service.ts)¶

Overview¶

The AzureSqlService provides a simplified interface for interacting with Azure SQL databases in the BidScript platform. This service handles connection management, query execution, transaction support, and data transformations, enabling seamless integration with Microsoft's cloud-based relational database service.

Dependencies¶

import { Injectable, Logger, OnModuleInit, OnModuleDestroy } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { Connection, ConnectionPool, Request, TYPES, Transaction } from 'tedious';
import { v4 as uuidv4 } from 'uuid';

Key Features¶

Connection pooling for efficient database access
Parameterized query execution with SQL injection protection
Transaction support for atomic operations
Automatic connection management
Type conversion between JavaScript and SQL Server types
Error handling and retry logic
Query result transformations
Batched operations for performance
Database metadata inspection

Core Methods¶

query¶

async query<T = any>(
  sql: string,
  params: Record<string, any> = {},
  options: {
    transform?: (row: Record<string, any>) => T;
    timeout?: number;
  } = {}
): Promise<T[]>

Executes a SQL query and returns the results.

Parameters: - sql: The SQL query string with named parameters (e.g., @paramName) - params: Object mapping parameter names to values - options: Query options - transform: Function to transform each row - timeout: Query timeout in milliseconds

Returns: - Array of query results, optionally transformed by the transform function

Example:

// Query with parameters
const users = await azureSqlService.query<User>(
  'SELECT * FROM Users WHERE department = @dept AND active = @isActive',
  { dept: 'Engineering', isActive: true }
);

// With transformation
const userSummaries = await azureSqlService.query<UserSummary>(
  'SELECT id, name, email FROM Users WHERE role = @role',
  { role: 'admin' },
  {
    transform: row => ({
      id: row.id,
      displayName: `${row.name} <${row.email}>`,
      email: row.email
    })
  }
);

queryOne¶

async queryOne<T = any>(
  sql: string,
  params: Record<string, any> = {},
  options: {
    transform?: (row: Record<string, any>) => T;
    timeout?: number;
  } = {}
): Promise<T | null>

Executes a SQL query and returns the first result or null.

Parameters: - Same as query method

Returns: - First result row or null if no results

Example:

const user = await azureSqlService.queryOne<User>(
  'SELECT * FROM Users WHERE id = @userId',
  { userId: '123' }
);

if (user) {
  console.log(`Found user: ${user.name}`);
} else {
  console.log('User not found');
}

execute¶

async execute(
  sql: string,
  params: Record<string, any> = {},
  options: { timeout?: number } = {}
): Promise<{ rowsAffected: number }>

Executes a SQL statement that doesn't return rows (INSERT, UPDATE, DELETE).

Parameters: - sql: The SQL statement - params: Object mapping parameter names to values - options: Execution options - timeout: Statement timeout in milliseconds

Returns: - Object containing the number of rows affected

Example:

// Insert a new record
const result = await azureSqlService.execute(
  'INSERT INTO Projects (id, name, description, createdAt) VALUES (@id, @name, @desc, @created)',
  {
    id: uuidv4(),
    name: 'New Project',
    desc: 'Project description',
    created: new Date()
  }
);

console.log(`Inserted ${result.rowsAffected} row(s)`);

// Update records
const updateResult = await azureSqlService.execute(
  'UPDATE Projects SET status = @status WHERE dueDate < @date',
  { status: 'overdue', date: new Date() }
);

console.log(`Updated ${updateResult.rowsAffected} projects`);

transaction¶

async transaction<T>(
  callback: (transaction: Transaction) => Promise<T>
): Promise<T>

Executes operations within a transaction.

Parameters: - callback: Async function that receives a transaction object and returns a result

Returns: - The result of the callback function

Example:

const result = await azureSqlService.transaction(async (transaction) => {
  // Perform multiple operations in a transaction
  await azureSqlService.execute(
    'INSERT INTO Orders (id, customerId, total) VALUES (@id, @customerId, @total)',
    { id: orderId, customerId, total },
    { transaction }
  );

  await azureSqlService.execute(
    'INSERT INTO OrderItems (orderId, productId, quantity, price) VALUES (@orderId, @productId, @qty, @price)',
    { orderId, productId, qty: 2, price: 29.99 },
    { transaction }
  );

  await azureSqlService.execute(
    'UPDATE Inventory SET stock = stock - @qty WHERE productId = @productId',
    { productId, qty: 2 },
    { transaction }
  );

  return { orderId };
});

console.log(`Order ${result.orderId} created successfully`);

bulkInsert¶

async bulkInsert<T>(
  tableName: string,
  columns: Array<{
    name: string;
    type: string;
  }>,
  rows: T[],
  options: {
    batchSize?: number;
    timeout?: number;
  } = {}
): Promise<{ rowsAffected: number }>

Efficiently inserts multiple rows into a table.

Parameters: - tableName: The target table name - columns: Array of column definitions with name and SQL type - rows: Array of data objects to insert - options: Bulk insert options - batchSize: Number of rows per batch - timeout: Operation timeout in milliseconds

Returns: - Object containing the number of rows affected

Example:

const products = [
  { id: 'p1', name: 'Product 1', price: 19.99, category: 'electronics' },
  { id: 'p2', name: 'Product 2', price: 29.99, category: 'electronics' },
  { id: 'p3', name: 'Product 3', price: 39.99, category: 'accessories' }
];

const result = await azureSqlService.bulkInsert(
  'Products',
  [
    { name: 'id', type: TYPES.VarChar(50) },
    { name: 'name', type: TYPES.NVarChar(100) },
    { name: 'price', type: TYPES.Money },
    { name: 'category', type: TYPES.VarChar(50) }
  ],
  products,
  { batchSize: 1000 }
);

console.log(`Inserted ${result.rowsAffected} products`);

Implementation Details¶

Connection Pool Management¶

private pool: ConnectionPool;
private connectionConfig: any;

constructor(private configService: ConfigService) {
  const connectionString = this.configService.get<string>('AZURE_SQL_CONNECTION_STRING');

  if (!connectionString) {
    throw new Error('Azure SQL connection string not configured');
  }

  this.connectionConfig = this.parseConnectionString(connectionString);

  this.pool = new ConnectionPool({
    server: this.connectionConfig.server,
    authentication: {
      type: 'default',
      options: {
        userName: this.connectionConfig.user,
        password: this.connectionConfig.password
      }
    },
    options: {
      database: this.connectionConfig.database,
      encrypt: true,
      trustServerCertificate: false,
      connectTimeout: 30000,
      requestTimeout: 30000,
      maxRetriesOnTransientErrors: 3,
      connectionRetryInterval: 500
    }
  });
}

async onModuleInit() {
  await this.pool.connect();
  this.logger.log('Connected to Azure SQL Database');
}

async onModuleDestroy() {
  await this.pool.close();
  this.logger.log('Disconnected from Azure SQL Database');
}

Parameter Handling¶

private buildRequest(
  sql: string,
  params: Record<string, any>,
  options: {
    timeout?: number;
    transaction?: Transaction;
  } = {}
): Request {
  const request = new Request(sql, (err, rowCount) => {
    if (err) {
      this.logger.error(`SQL error: ${err.message}`);
    }
  });

  // Set timeout
  if (options.timeout) {
    request.setTimeout(options.timeout);
  }

  // Add transaction if provided
  if (options.transaction) {
    request.transaction = options.transaction;
  }

  // Add parameters
  for (const [name, value] of Object.entries(params)) {
    const paramName = name.startsWith('@') ? name : `@${name}`;
    const sqlType = this.getSqlTypeForValue(value);

    if (value === null || value === undefined) {
      request.addParameter(paramName, sqlType, null);
    } else {
      request.addParameter(paramName, sqlType, value);
    }
  }

  return request;
}

private getSqlTypeForValue(value: any) {
  if (value === null || value === undefined) {
    return TYPES.NVarChar;
  }

  switch (typeof value) {
    case 'string':
      return TYPES.NVarChar;
    case 'number':
      return Number.isInteger(value) ? TYPES.Int : TYPES.Decimal;
    case 'boolean':
      return TYPES.Bit;
    case 'object':
      if (value instanceof Date) {
        return TYPES.DateTime;
      } else if (Buffer.isBuffer(value)) {
        return TYPES.VarBinary;
      } else {
        return TYPES.NVarChar;
      }
    default:
      return TYPES.NVarChar;
  }
}

Integration with Other Services¶

The AzureSqlService integrates with:

UserService: For user profile and permission storage
ProjectService: For project metadata and settings
DocumentService: For document metadata storage
Analytics Service: For reporting and analytics

Error Handling¶

The service includes comprehensive error handling for:

Connection errors: Issues connecting to the database
Authentication errors: Invalid credentials
Query timeout errors: Long-running queries
Constraint violations: Primary key and foreign key constraints
Transient errors: Temporary connection issues with automatic retry
Transaction errors: Transaction rollback on failure

Logging¶

The service uses NestJS Logger for detailed logging:

private readonly logger = new Logger(AzureSqlService.name);

// Usage examples
this.logger.log('Executing query: SELECT * FROM Users');
this.logger.debug(`Query parameters: ${JSON.stringify(params)}`);
this.logger.error(`Database error: ${error.message}`, error.stack);

Configuration¶

Required environment variables:

AZURE_SQL_CONNECTION_STRING=Server=your-server.database.windows.net;Database=your-database;User Id=your-username;Password=your-password;

Optional configuration:

AZURE_SQL_POOL_MIN=5
AZURE_SQL_POOL_MAX=10
AZURE_SQL_IDLE_TIMEOUT_MS=30000
AZURE_SQL_QUERY_TIMEOUT_MS=30000