# MSSQL Storage

The MSSQL storage implementation provides a production-ready storage solution using Microsoft SQL Server databases.

## Installation

**npm**:

```bash
npm install @mastra/mssql@latest
```

**pnpm**:

```bash
pnpm add @mastra/mssql@latest
```

**Yarn**:

```bash
yarn add @mastra/mssql@latest
```

**Bun**:

```bash
bun add @mastra/mssql@latest
```

## Usage

```typescript
import { MSSQLStore } from "@mastra/mssql";

const storage = new MSSQLStore({
  id: 'mssql-storage',
  connectionString: process.env.DATABASE_URL,
});
```

## Parameters

**connectionString:** (`string`): MSSQL connection string (e.g., Server=localhost,1433;Database=mydb;User Id=sa;Password=password;Encrypt=true;TrustServerCertificate=true)

**schemaName?:** (`string`): The name of the schema you want the storage to use. Will use the default schema if not provided.

## Constructor Examples

You can instantiate `MSSQLStore` in the following ways:

```ts
import { MSSQLStore } from "@mastra/mssql";

// Using a connection string only
const store1 = new MSSQLStore({
  id: 'mssql-storage-1',
  connectionString: "Server=localhost,1433;Database=mydb;User Id=sa;Password=password;Encrypt=true;TrustServerCertificate=true",
});

// Using a connection string with a custom schema name
const store2 = new MSSQLStore({
  id: 'mssql-storage-2',
  connectionString: "Server=localhost,1433;Database=mydb;User Id=sa;Password=password;Encrypt=true;TrustServerCertificate=true",
  schemaName: "custom_schema", // optional
});

// Using individual connection parameters
const store4 = new MSSQLStore({
  id: 'mssql-storage-3',
  server: "localhost",
  port: 1433,
  database: "mydb",
  user: "user",
  password: "password",
});

// Individual parameters with schemaName
const store5 = new MSSQLStore({
  id: 'mssql-storage-4',
  server: "localhost",
  port: 1433,
  database: "mydb",
  user: "user",
  password: "password",
  schemaName: "custom_schema", // optional
});
```

## Additional Notes

### Schema Management

The storage implementation handles schema creation and updates automatically. It creates the following tables:

- `mastra_workflow_snapshot`: Stores workflow state and execution data
- `mastra_evals`: Stores evaluation results and metadata
- `mastra_threads`: Stores conversation threads
- `mastra_messages`: Stores individual messages
- `mastra_traces`: Stores telemetry and tracing data
- `mastra_scorers`: Stores scoring and evaluation data
- `mastra_resources`: Stores resource working memory data

### Initialization

When you pass storage to the Mastra class, `init()` is called automatically before any storage operation:

```typescript
import { Mastra } from "@mastra/core";
import { MSSQLStore } from "@mastra/mssql";

const storage = new MSSQLStore({
  connectionString: process.env.DATABASE_URL,
});

const mastra = new Mastra({
  storage, // init() is called automatically
});
```

If you're using storage directly without Mastra, you must call `init()` explicitly to create the tables:

```typescript
import { MSSQLStore } from "@mastra/mssql";

const storage = new MSSQLStore({
  id: 'mssql-storage',
  connectionString: process.env.DATABASE_URL,
});

// Required when using storage directly
await storage.init();

// Access domain-specific stores via getStore()
const memoryStore = await storage.getStore('memory');
const thread = await memoryStore?.getThreadById({ threadId: "..." });
```

> **Warning:** If `init()` is not called, tables won't be created and storage operations will fail silently or throw errors.

### Direct Database and Pool Access

`MSSQLStore` exposes the mssql connection pool as public fields:

```typescript
store.pool; // mssql connection pool instance
```

This enables direct queries and custom transaction management. When using these fields:

- You are responsible for proper connection and transaction handling.
- Closing the store (`store.close()`) will destroy the associated connection pool.
- Direct access bypasses any additional logic or validation provided by MSSQLStore methods.

This approach is intended for advanced scenarios where low-level access is required.