【TypeORM】パラメータを使いまわすときは型に注意

はじめに

先日、TypeORMでパラメータを使っていたときに少しハマったので、それに関して調べた内容を忘備録として残しておきます。

ハマっていたのは下記のようなSQL文です。

const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth" = $1', ['20230822'])

エラー内容は以下の通りです。

operator does not exist: character varying = date

結論としては、以下のように同じ値の別のパラメータを使って解決できました。

const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth" = $2', ['20230822', '20230822'])

以下、調べた内容です。

事前準備

環境構築

まずは調べるための環境を準備します。

以前、TypeORM CLIを使って環境を簡単に作成する記事を書きましたので、まずはこちらを実施します。

TypeORMで生成されるSQLをCLIを使って確認してみた | DevelopersIO

この記事の「SQLがログに出力されるようにする」までを実施します。

DBマイグレーションを行う

User.tsに以下2行を追加します。updateDtはPostgreSQLのdate型で、birthはvarchar型になります。

@Column({
    type: 'date',
})
updateDt: Date

@Column()
birth: string

続いてテーブルを作成するためのマイグレーションファイルを作成します。src/migrationフォルダにファイルが作成されます。

npm run typeorm migration:generate src/migration/CreateUserTable -- --dataSource src/data-source.ts

data-source.tsを以下のように変更します。変更したのは、マイグレーションファイルのクラスをインポートすることと、migrationsにそのクラスを指定することの2点です。

import "reflect-metadata"
import { DataSource } from "typeorm"
import { User } from "./entity/User"
import { CreateUserTable1692676425496 } from "./migration/1692676425496-CreateUserTable"

export const AppDataSource = new DataSource({
    type: "postgres",
    host: "localhost",
    port: 5434,
    username: "test",
    password: "test",
    database: "test",
    synchronize: true,
    logging: false,
    entities: [User],
    migrations: [CreateUserTable1692676425496],
    subscribers: [],
})

マイグレーションを実行します。

npm run typeorm migration:run -- --dataSource src/data-source.ts

SQLクライアントでDBに接続するとUserテーブルが作成されています。テストデータを1件登録します。

insert into "user" ("firstName" , "lastName" , age, "updateDt" , birth ) values ('Tom', 'Green', 0, '20230822', '20230822' )

これで準備ができたので、パラメータの動きを確認していきます。

パラメータ変数の動きを確認

色々なパターンで検索していきます。

varchar型を文字列で検索

index.tsを以下のように変更します。varchar型であるbirth列をyyyymmddの文字列で検索します。

import { AppDataSource } from "./data-source"

AppDataSource.initialize().then(async (manager) => {

    const data = await manager.query('select * from "user" where "birth" = $1', ['20230822'])
    console.log(data)

}).catch(error => console.log(error))

当然ですがこの文はエラーにならず、以下のようにデータが取得できます。

[
  {
    id: 1,
    firstName: 'Tom',
    lastName: 'Green',
    age: 0,
    updateDt: 2023-08-21T15:00:00.000Z,
    birth: '20230822'
  }
]

varchar型を数値で検索

ではvarchar型のbirth列に対してyyyymmddの数値で検索したらどうでしょうか。

const data = await manager.query('select * from "user" where "birth" = $1', [20230822])

こちらもエラーにならずデータが取得できました。

ちなみに、以下のようなSQLをSQLクライアントソフト（DBeaver）で実行してみると、型が異なるため以下のようにエラーになります。

select * from "user" where "birth" = 20230822

-- -> ERROR: operator does not exist: character varying = integer

int型を文字列で検索

続いてint型のage列に対して文字列で検索したらどうでしょうか。

const data = await manager.query('select * from "user" where "age" = $1', ['0'])

これはエラーにならずデータが取得できます。

しかし、'0'ではなく'a'など、数値に変換できない文字列にするとエラーになりました。

invalid input syntax for type integer: "a"

どうやらTypeORMはDBの列と渡されたパラメータの型が違っていたとしても、変換できる値ならば内部的に変換してくれているようですね。数値に対して'a'など変換できない値が渡された場合にエラーになるようです。

date型を文字列で検索

冒頭で述べたエラーになったSQL文では、date型であるupdateDt列をyyyymmddの文字列で検索していました。この条件だけを単独で実行してみます。

const data = await manager.query('select * from "user" where "updateDt" = $1', ['20230822'])

こちらを実行するとエラーにならずデータは取得できました。date型に対して文字列で検索したこと自体はエラーの原因ではないことがわかります。

ちなみに、文字列の形式を'2023.08.22'や'2023/08/22'や'Aug-22-2023'などにしても実行できました。これらはSQLクライアントソフトでも問題なく実行できます。

ただし、'Aug-32-2023'など存在しない日付にするとエラーになります。

date/time field value out of range: "Aug-32-2023"

PostgreSQLで日付として入力可能な形式は以下を参考にしてみてください。

日付/時刻データ型

varchar型とdate型を同一の文字列で検索

上記のことから、TypeORMの動きとして以下のようになっていると思われます。

• 最初にパラメータに値を当てはめた時点で、そのパラメータは内部的に適切な型に変換される
• 以降、そのパラメータは変換された型のまま保持される

そのため、今回エラーになった以下の文では、

const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth" = $1', ['20230822'])

• '20230822'という文字列は、date型のupdateDtと比較する際にdate型に変換された
• varchar型のbirthとdate型の'20230822'を比較しようとしてエラー

ということが起きていたと考えられます。

ちなみに検索する順番を変えて、"birth" = $1を先にしてみると、エラーの内容が以下のように変わります。

operator does not exist: date = text

この場合は、最初にbirthと比較した際に'20230822'がvarchar型になったため、date型であるupdateDtと比較しようとしてエラーになったことを示しています。

これを解決するためには、パラメータを使いまわさず別々で用意します。

const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth" = $2', ['20230822', '20230822'])

もしくは、型が一致するように変換することでも解決できます。

const data = await manager.query('select * from "user" where "updateDt" = $1 and "birth"::date = $1', ['20230822'])

おわりに

解決してみれば非常にささいなことかもしれませんが、ハマっているときは全然気づけませんでした。この記事ではdate型とvarchar型を取り上げましたが、他の型（int型とvarchar型など）でも同様です。パラメータを使いまわすときは気をつけてみてください。