MySQL -> PostgreSQLでシンタックスの違いでちょっと苦労した話

この記事は？

この記事はTreasure Advent Calendar13日目の記事として書かれています。

インターンの時に作っていたアプリケーションの継続開発を行うためのテスト環境を、インターン中はAWS上にあったところからHerokuに移植するということをしたのですが、その時にDBをMySQLからPostgreSQLに変更しようとして苦労したのでここに書き残しておこうかな、と思います。

ちなみにMySQLは8.0, PostgreSQLは10.1です。

超ざっくりアプリの説明

スマホアプリのバックエンドをGoで書いています(最近活動が止まり気味でしたがもうすぐ一旦リリースします)。

バックエンドはインターン中にもらったサンプルプログラムの影響が濃いです。具体的には、ハイエンドなフレームワークを使わず標準ライブラリでなんとかしているところが多かったり、ORマッパー使ってなかったり。

今回のお話もORマッパー使ってたら多分経験しなかったはずですが勉強になったのでよかったよかった。(この時までPostgresは一切触ったことがない)

今回コード移植が発生したのはマイグレーション用のDDLと、モデル中に書かれたSQLのクエリ文です。ちなみにマイグレーションもsql-migrate -> gooseに変えたのですがこれもそこそこ苦労しました。

主キーのINT? SERIALでしょ?

こちらDDL内のお話です。

MySQLでは

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,

みたいに書かれる主キーの記述ですが、PostgreSQLはUNSIGNEDもAUTO_INCREMENTもありません。代わりにSERIAL型があります。なので

CREATE TABLE users (
  id SERIAL,

となります。シンプルでいいですね！

残念なことにUNSIGNEDがないので丁寧にUNSIGNEDを書いていたところはことごとく記述を帰る羽目になりました。

ON UPDATE CURRENT_TIMESTAMPなんてものはない

これもDDLの中での話です。

これは結構有名な話らしく、MySQLのON UPDATE CURRENT_TIMESTAMPはPostgresではどうすればいいの？系の記事がいっぱいあったので助かりました。

これはupdated_atみたいなカラムを作りたくなった時によく出てきます。

MySQLでは

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

シンプルですよね。Postgresでは

CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

みたいに関数定義をしておいた上で、該当部分を

updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

のように変えるといいようです。ポイントは、CURRENT_TIMESTAMP自体は定義されているのでON UPDATEみたいな書き方ができないということなんでしょうか。

Quotationに気をつけろ!

INSERT INTO users (name) VALUES
  ("koukyo1994"),

MySQLではしっかり通ってくれるSQLですがPostgresでは失敗します。

INSERT INTO users (name) VALUES
  ('koukyo1994'),

こうしないとダメですね。こいつのせいでマイグレーションファイル内のダブルクォーテーションを山ほど書き直す羽目になりました。

ちなみにkoukyo1994はgithubの名前です。

FOREIN KEYとCONSTRAINT

CONSTRAINT fkpt1 FOREIGN KEY(topic_id) REFERENCES topics(id) ON DELETE CASCADE,

こいつは問題ありません。MySQLでも　Postgresでも動きます。

ALTER TABLE users DROP FOREIGN KEY fkpt1;

こいつはダメです。MySQLではいけますが、Postgresでは

ALTER TABLE users DROP CONSTRAINT fkug1;

こっちにしなければいけません。

クエリ文の話

今までは、マイグレーションファイル内でのシンタックスの違いでしたが、次はモデルに書いてあったクエリの違いについてです。と言ってもこっちはそこまで多くないのと、実際シンタックスの違いで動かなかったという話ではなくGoのSQLライブラリの中での問題なので、Goを書かない人には関係ないかもしれません。

GoのSQLライブラリとしてはjmoiron/sqlxを使っていました。そこでプリペアドステートメントを作成するようなコードとして

stmt, err := tx.Prepare(`INSERT INTO ACTIONS (post_id, action_type, action_count) VALUES (?,?,?)`)

のようなコードがあったのですが、このプリペアドステートメントはMySQLでは動作しますが、Postgresでは動作しません。

stmt, err := tx.Prepare(`INSERT INTO ACTIONS (post_id, action_type, action_count) VALUES ($1,$2,$3)`)

のように変更する必要があります🤔

さて、このクエリ文はこのままだとエラーを出します。MySQLはこのままでもよかったのですが、PostgreSQLではINSERT文に返り値をもたせたい場合は明示的にRETURNINGを付記しなければいけません。

したがって、Postgresで動くのは

stmt, err := tx.Prepare(`INSERT INTO ACTIONS (post_id, action_type, action_count) VALUES ($1,$2,$3) RETURNING id;`)

となります。

その他

CIの設定で、データベースのDockerコンテナが起動しているかどうかのヘルスチェックなんかをしているのですが、MySQLを使っていた時は

command:
  for i in $(seq $HEALTH_CHECK_RETRY_LIMIT)
    do
      mysql -h 127.0.0.1 -u root -p<password> -e 'show databases' || (sleep $HEALTH_CHECK_RETRY_WAIT; false) && break
    done

みたいなことを書いていたのが、

command:
  for i in $(seq $HEALTH_CHECK_RETRY_LIMIT)
    do
      psql -h 127.0.0.1 -U postgres -p 5432 -c '\d' || (sleep $HEALTH_CHECK_RETRY_WAIT; false) && break
    done

みたいに変わっていたりします。exitも\qだったりと微妙に使い勝手が違う・・・

まとめ

MySQLとPostgreSQLの微妙な違いをまとめてみました。最近はBigQueryを叩いているのですが、こっちはこっちでstandard SQLとlegacy SQLの微妙な違いでミスることがあって🤔という気分になることが稀にあります。

まあ、多分SQLのコード移植とかORマッパーがしっかりついてるフレームワークとか使っていたらconfig書き換えるだけで終わるとかそんなところだろうと思うのですが、似たようなことをやっていて詰まった人の助けになったらいいなくらいに思っています。