# Azure Adapters for USSSA Core Library

This directory contains adapter modules for Azure services, providing a consistent interface for interacting with various Azure resources.

## Table of Contents

- [Overview](#overview)
- [Available Adapters](#available-adapters)
- [Architecture](#architecture)
- [MS SQL Adapter](#ms-sql-adapter)
  - [Features](#features)
  - [Configuration](#configuration)
  - [Usage Examples](#usage-examples)
  - [API Reference](#api-reference)
- [Best Practices](#best-practices)
- [Error Handling](#error-handling)

## Overview

The Azure adapters provide a standardized way to interact with Azure services, following the same adapter pattern used throughout the core library. These adapters abstract away the complexities of the underlying Azure SDKs and provide a consistent interface for common operations.

## Available Adapters

Currently, the following Azure adapters are available:

- **MS SQL Adapter**: For connecting to and performing CRUD operations on Azure SQL databases

## Architecture

The Azure adapters follow a functional approach rather than a class-based implementation. This makes the code more testable and maintainable. The adapters are organized as follows:

```
src/adapters/azure/
├── index.ts                # Exports all Azure adapters
├── mssql/                  # MS SQL adapter
│   └── index.ts            # MS SQL adapter implementation
└── __tests__/              # Test files for Azure adapters
    └── mssql-adapter.spec.ts
```

```mermaid
graph TD
    A[Core Library] --> B[Adapters]
    B --> C[GCP Adapters]
    B --> D[Azure Adapters]
    D --> E[MS SQL Adapter]
    E --> F[Connection Pool Management]
    E --> G[CRUD Operations]
    E --> H[Transaction Support]
    E --> I[Batch Operations]

    F --> J[Service Type Mapping]
    G --> K[Query]
    G --> L[GetRow/GetRows]
    G --> M[InsertRow]
    G --> N[UpdateRow]
    G --> O[DeleteRow]
```

## MS SQL Adapter

The MS SQL adapter provides a set of functions for connecting to and performing operations on Azure SQL databases. It supports multiple database connections based on service types, allowing you to map different microservices to different databases.

### Features

- **Service Type Mapping**: Connect to different databases based on service type
- **Connection Pooling**: Efficiently manage database connections
- **Parameterized Queries**: Secure SQL operations with parameterized queries
- **Transaction Support**: Execute multiple operations in a transaction
- **Batch Operations**: Run multiple queries in a single request
- **Error Handling**: Consistent error handling using the core library's ErrorUtils

### Configuration

Before using the MS SQL adapter, you need to initialize it with configuration for your databases:

```typescript
import { initializeSQLAdapter } from '@usssa/core/adapters/azure/mssql';

initializeSQLAdapter({
  dbConfig: {
    users: {
      server: 'users-db.azure.com',
      database: 'users_db',
      user: 'username',
      password: 'password',
      options: {
        encrypt: true,
        trustServerCertificate: false
      }
    },
    teams: {
      server: 'teams-db.azure.com',
      database: 'teams_db',
      user: 'username',
      password: 'password',
      options: {
        encrypt: true
      }
    }
  },
  defaultConfig: {
    server: 'default-db.azure.com',
    database: 'default_db',
    user: 'username',
    password: 'password',
    options: {
      encrypt: true
    }
  }
});
```

### Usage Examples

#### Basic Query

```typescript
import { query } from '@usssa/core/adapters/azure/mssql';

const results = await query({
  serviceType: 'users',
  query: 'SELECT * FROM Users WHERE department = @dept',
  params: {
    dept: 'Engineering'
  }
});
```

#### Get a Single Row

```typescript
import { getRow } from '@usssa/core/adapters/azure/mssql';

const user = await getRow({
  serviceType: 'users',
  table: 'Users',
  where: { id: 123 }
});
```

#### Get Multiple Rows with Filtering and Pagination

```typescript
import { getRows } from '@usssa/core/adapters/azure/mssql';

const users = await getRows({
  serviceType: 'users',
  table: 'Users',
  columns: ['id', 'name', 'email', 'department'],
  where: { active: true },
  orderBy: ['lastName', 'firstName'],
  limit: 10,
  offset: 20
});
```

#### Insert a Row

```typescript
import { insertRow } from '@usssa/core/adapters/azure/mssql';

const newUserId = await insertRow({
  serviceType: 'users',
  table: 'Users',
  data: {
    name: 'John Doe',
    email: 'john@example.com',
    department: 'Engineering',
    active: true
  },
  returnInserted: true
});
```

#### Update a Row

```typescript
import { updateRow } from '@usssa/core/adapters/azure/mssql';

await updateRow({
  serviceType: 'users',
  table: 'Users',
  data: {
    department: 'Product',
    updated_at: new Date().valueOf()
  },
  where: { id: 123 }
});
```

#### Delete a Row

```typescript
import { deleteRow } from '@usssa/core/adapters/azure/mssql';

await deleteRow({
  serviceType: 'users',
  table: 'Users',
  where: { id: 123 }
});
```

#### Using Transactions

```typescript
import { withTransaction } from '@usssa/core/adapters/azure/mssql';

await withTransaction('users', async (transaction) => {
  // Get a request from the transaction
  const request = transaction.request();

  // Execute queries within the transaction
  await request.input('userId', 123).query('DELETE FROM UserRoles WHERE user_id = @userId');
  await request.input('userId', 123).query('DELETE FROM UserPreferences WHERE user_id = @userId');
  await request.input('userId', 123).query('DELETE FROM Users WHERE id = @userId');

  // The transaction will be automatically committed if all queries succeed
  // or rolled back if any query fails
});
```

#### Batch Operations

```typescript
import { executeBatch } from '@usssa/core/adapters/azure/mssql';

const results = await executeBatch({
  serviceType: 'users',
  queries: [
    {
      query: 'SELECT * FROM Users WHERE department = @dept',
      params: { dept: 'Engineering' }
    },
    {
      query: 'SELECT * FROM Departments WHERE name = @name',
      params: { name: 'Engineering' }
    }
  ]
});

// results[0] contains the results of the first query
// results[1] contains the results of the second query
```

### API Reference

#### Configuration

```typescript
initializeSQLAdapter(config: TMSSQLAdapterConfig): void
```

Initializes the SQL adapter with configuration for different service types.

- `config`: Configuration object with the following properties:
  - `dbConfig`: Record mapping service types to database configurations
  - `defaultConfig`: Default database configuration to use if a service type is not found in dbConfig

#### Connection Management

```typescript
getConnection(serviceType: string): Promise<sql.ConnectionPool>
```

Gets or creates a connection pool for the specified service type.

```typescript
disconnectAll(): Promise<void>
```

Closes all open database connections.

#### Query Operations

```typescript
query(props: TMSSQLQueryProps): Promise<any>
```

Executes a raw SQL query.

- `props`: Query properties
  - `serviceType`: The service type to connect to
  - `query`: The SQL query to execute
  - `params`: Parameters for the query (optional)

```typescript
getRow(props: TMSSQLGetRowProps): Promise<any>
```

Gets a single row from a table.

- `props`: Query properties
  - `serviceType`: The service type to connect to
  - `table`: The table to query
  - `columns`: Columns to select (optional, defaults to ['*'])
  - `where`: Where conditions (optional)

```typescript
getRows(props: TMSSQLGetRowsProps): Promise<any[]>
```

Gets multiple rows from a table.

- `props`: Query properties
  - `serviceType`: The service type to connect to
  - `table`: The table to query
  - `columns`: Columns to select (optional, defaults to ['*'])
  - `where`: Where conditions (optional)
  - `orderBy`: Order by clause (optional)
  - `limit`: Maximum number of rows to return (optional)
  - `offset`: Number of rows to skip (optional)

```typescript
getRowCount(props: TMSSQLGetRowProps): Promise<number>
```

Gets the count of rows in a table matching the given conditions.

#### Modification Operations

```typescript
insertRow(props: TMSSQLInsertRowProps): Promise<any>
```

Inserts a row into a table.

- `props`: Insert properties
  - `serviceType`: The service type to connect to
  - `table`: The table to insert into
  - `data`: The data to insert
  - `returnInserted`: Whether to return the inserted row (optional, defaults to false)

```typescript
updateRow(props: TMSSQLUpdateRowProps): Promise<any>
```

Updates rows in a table.

- `props`: Update properties
  - `serviceType`: The service type to connect to
  - `table`: The table to update
  - `data`: The data to update
  - `where`: Where conditions
  - `returnUpdated`: Whether to return the updated rows (optional, defaults to false)

```typescript
deleteRow(props: TMSSQLDeleteRowProps): Promise<any>
```

Deletes rows from a table.

- `props`: Delete properties
  - `serviceType`: The service type to connect to
  - `table`: The table to delete from
  - `where`: Where conditions
  - `returnDeleted`: Whether to return the deleted rows (optional, defaults to false)

#### Transaction Operations

```typescript
withTransaction(serviceType: string, callback: (transaction: sql.Transaction) => Promise<any>): Promise<any>
```

Executes a callback within a transaction.

- `serviceType`: The service type to connect to
- `callback`: Function that receives a transaction object and executes queries

```typescript
executeBatch(props: TMSSQLBatchProps): Promise<any[]>
```

Executes multiple queries in a transaction.

- `props`: Batch properties
  - `serviceType`: The service type to connect to
  - `queries`: Array of query objects, each with a query string and optional parameters

## Best Practices

1. **Initialize Early**: Initialize the adapter with all necessary configurations at application startup.
2. **Use Service Types**: Organize your databases by service type to maintain clear separation of concerns.
3. **Parameterize Queries**: Always use parameterized queries to prevent SQL injection.
4. **Use Transactions**: When performing multiple related operations, use transactions to ensure data consistency.
5. **Close Connections**: Call `disconnectAll()` when shutting down your application to properly close all connections.
6. **Error Handling**: Always handle errors from database operations appropriately.

## Error Handling

The MS SQL adapter uses the core library's ErrorUtils for consistent error handling. All adapter functions will throw errors with appropriate messages and status codes when operations fail.

Example error handling:

```typescript
import { getRow } from '@usssa/core/adapters/azure/mssql';

try {
  const user = await getRow({
    serviceType: 'users',
    table: 'Users',
    where: { id: 123 }
  });

  if (!user) {
    // Handle user not found
  }
} catch (error) {
  // Handle database error
  console.error('Database error:', error.message);
}