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

## この記事は？
この記事は[Treasure Advent Calendar](https://qiita.com/advent-calendar/2018/treasure)13日目の記事として書かれています。

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

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

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

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

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

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

## 主キーのINT? SERIALでしょ?
こちらDDL内のお話です。

MySQLでは

```sql
CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
```

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

```sql
CREATE TABLE users (
  id SERIAL,
```

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

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

## ON UPDATE CURRENT_TIMESTAMPなんてものはない
これもDDLの中での話です。

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

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

MySQLでは

```sql
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
```

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

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

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

```sql
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
```

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

## Quotationに気をつけろ!
```sql
INSERT INTO users (name) VALUES
  ("koukyo1994"),
```

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

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

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

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

## FOREIN KEYとCONSTRAINT
```sql
CONSTRAINT fkpt1 FOREIGN KEY(topic_id) REFERENCES topics(id) ON DELETE CASCADE,
```

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

```sql
ALTER TABLE users DROP FOREIGN KEY fkpt1;
```

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

```sql
ALTER TABLE users DROP CONSTRAINT fkug1;
```

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

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

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

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

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

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

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

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

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

```sql
stmt, err := tx.Prepare(`INSERT INTO ACTIONS (post_id, action_type, action_count) VALUES ($1,$2,$3) RETURNING id;`)
```
となります。

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

```shell
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
```

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

```shell
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書き換えるだけで終わるとかそんなところだろうと思うのですが、似たようなことをやっていて詰まった人の助けになったらいいなくらいに思っています。