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

概要

SQLバインドを使う、tediousを使う、は試したので今回はmssqlを使ってSQL Serverに接続を試す。
題材とするSQLとしてJSONが格納されたカラムから配列をレコードとして取り出すことを試す。

データ

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

ソース

注意書きより、接続は使いまわすソースとする。

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バインドで取得したものと同じものを使用。

api/src/lib/constants.ts

export const connectionStringSetting = 'DATABASE_CONNECTION_STRING';
export const connectionString = process.env[connectionStringSetting] || '';

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マッピングした配列を返してくれる。

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

参考

組み込み関数を使用した JSON データの検証、クエリ、変更 (SQL Server)
OPNE_JSON - 例 3 - CROSS APPLY を使用してテーブルのセルに格納されている JSON データを行と結合する
WITH common_table_expression (Transact-SQL)

node-mssql
Node.js と mssql npm パッケージを使用して Azure SQL Database に接続し、クエリを実行する
Azure SQL Server の Session が減らない