SqlServerにmssqlを使って接続してJsonの入ったカラムから値を取り出したメモ

## 概要
[SQLバインドを使う](https://qiita.com/hibohiboo/items/4e8c6f8e841df1909b20)、[tediousを使う](https://qiita.com/hibohiboo/items/235decf3ec7713729408)、は試したので今回は[mssql](https://tediousjs.github.io/node-mssql/)を使ってSQL Serverに接続を試す。
題材とするSQLとしてJSONが格納されたカラムから配列をレコードとして取り出すことを試す。

## データ

```json
{
  "name": "キャラ名",
  "cards": [{ "name": "カード名1"},{ "name": "カード名2"}]
}
```

## ソース
[注意書き](https://www.npmjs.com/package/mssql#connection-pools)より、接続は使いまわすソースとする。

```ts: api/src/lib/database.ts
import * as sql from 'mssql';
import type { ISqlType, ConnectionPool, IRecordSet } from 'mssql';

interface ExecuteQueryArgs {
  query: string;
  params: {
    name: string;
    data: unknown;
    type: () => ISqlType;
  }[];
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  logger: (...args: any[]) => void;
}

export default class Database {
  private config: string;
  private poolconnection: ConnectionPool | null = null;
  private connected = false;

  constructor(connectionsString: string) {
    if (connectionsString === '') {
      throw new Error('Database connection string is empty');
    }
    console.log(`Database: config: ${JSON.stringify(connectionsString)}`);
    this.config = connectionsString;
  }

  private async connect() {
    try {
      console.log(`Database connecting...${this.connected}`);
      if (this.connected === false) {
        this.poolconnection = await sql.connect(this.config);
        this.connected = true;
        console.log('Database connection successful');
      } else {
        console.log('Database already connected');
      }
    } catch (error) {
      console.error(`Error connecting to database: ${JSON.stringify(error)}`);
    }
  }

  private async executeQuery<T>(args: ExecuteQueryArgs) {
    await this.connect();
    if (this.poolconnection == null) {
      args.logger('Database connection failed');
      return null;
    }
    const request = this.poolconnection.request();
    for (const { name, data, type } of args.params) {
      request.input(name, type, data);
    }
    args.logger(`Database query start`);
    const result = await request.query<T>(args.query);
    args.logger(
      `Database query end. records: ${result?.recordset?.length} affected: ${result?.rowsAffected[0]}`,
    );
    return result;
  }

  async readAll<T>(args: ExecuteQueryArgs) {
    const result = await this.executeQuery<T>(args);
    if (
      (function hasSets(sets?: unknown[]): sets is IRecordSet<T>[] {
        return sets != null && sets.length !== 0;
      })(result?.recordsets)
    ) {
      return result.recordsets[0];
    }
    return [] as T[];
  }

  async read<T>(args: ExecuteQueryArgs) {
    const result = await this.executeQuery<T>(args);
    const ret = result?.recordset[0];
    return ret;
  }

  async execute(args: ExecuteQueryArgs) {
    await this.connect();
    const result = await this.executeQuery(args);
    return result?.rowsAffected[0];
  }
}
```

接続文字列の設定。ここの接続文字列は[SQLバインドで取得したもの](https://qiita.com/hibohiboo/items/4e8c6f8e841df1909b20#sql%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E3%81%8B%E3%82%89%E6%8E%A5%E7%B6%9A%E6%96%87%E5%AD%97%E5%88%97%E5%8F%96%E5%BE%97)と同じものを使用。
```api/src/lib/constants.ts
export const connectionStringSetting = 'DATABASE_CONNECTION_STRING';
export const connectionString = process.env[connectionStringSetting] || '';
```

```ts:api/src/functions/getCharacterCards.ts
import {
  app,
  HttpRequest,
  HttpResponseInit,
  InvocationContext,
} from '@azure/functions';
import { connectionString } from '@yakumi-api/lib/constants';
import Database from '@yakumi-api/lib/database';
import * as sql from 'mssql';

// 接続を使いまわすため、関数の外で定義
const db = new Database(connectionString);

export async function getCharacterCards(
  request: HttpRequest,
  context: InvocationContext,
): Promise<HttpResponseInit> {
  context.log(`Http function processed request for url "${request.params.id}"`);
  if (!request.params.id) {
    return {
      status: 400,
      body: JSON.stringify({ message: 'Missing character id' }),
    };
  }
  const ret = await db.readAll<{
    Id: string;
    CharacterName: string;
    CardName: string;
  }>({
    query: `
    WITH CharacterData (Id, CharacterName, Cards) AS (
        select id AS Id
             , JSON_VALUE(data, '$.name') AS CharacterName
             , JSON_QUERY(data, '$.cards') as Cards 
        from YakumiCharacter
        where id = @id
      )
      SELECT Id, CharacterName, CardName
      FROM CharacterData
           CROSS APPLY OPENJSON (Cards) WITH (CardName NVARCHAR(100) '$.name');
    `,
    params: [{ name: 'id', data: request.params.id, type: sql.NVarChar }],
    logger: (args) => context.log(args),
  });

  return { body: JSON.stringify(ret) };
}

app.http('getCharacterCards', {
  route: 'get-character-cards/{id}',
  methods: ['GET'],
  authLevel: 'anonymous',
  handler: getCharacterCards,
});
```

## 実行結果
select結果をO/Rマッピングした配列を返してくれる。

```json
[
  {
    "Id": "hoge",
    "CharacterName": "キャラ名",
    "CardName": "カード名1"
  },
  {
    "Id": "hoge",
    "CharacterName": "キャラ名",
    "CardName": "カード名2"
  }
]
```


## 参考
[組み込み関数を使用した JSON データの検証、クエリ、変更 (SQL Server)](https://learn.microsoft.com/ja-jp/sql/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server?view=sql-server-ver16#JSONCompare)
[OPNE_JSON - 例 3 - CROSS APPLY を使用してテーブルのセルに格納されている JSON データを行と結合する](https://learn.microsoft.com/ja-jp/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16#example-3---join-rows-with-json-data-stored-in-table-cells-using-cross-apply)
[WITH common_table_expression (Transact-SQL)](https://learn.microsoft.com/ja-jp/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16)

[node-mssql](https://tediousjs.github.io/node-mssql/)
[Node.js と mssql npm パッケージを使用して Azure SQL Database に接続し、クエリを実行する](https://learn.microsoft.com/ja-jp/azure/azure-sql/database/azure-sql-javascript-mssql-quickstart?view=azuresql&tabs=sql-auth%2Cservice-connector%2Cportal)
[Azure SQL Server の Session が減らない](https://note.milldea.com/amp/posts/azure-sql-server-session-percentage)