flyway × postgres × jpa のid自動採番がうまくいかないときの対処法

entity を　JPA により永続化する際に primary key の自動採番に苦戦したためメモ。

JPA により DML,DDL を用意

以下のような、いたってシンプルな entity を用意する。

@Entity
public class Todo {

    @Id
    private Long id;

    private String activityName;

    private String color;

    private String category;
}

次にテストコードを用意し、JPA が発行した DDL を参考に、データソースを h2 から postgres に変更する。

@DataJpaTest
class TodoTests {

    @Autowired
    TestEntityManager em;

    @Test
    void mapping() {
        var todo1 = this.em.find(Todo.class, 1000L);
        assertThat(todo1.getActivityName()).isEqualTo("go to supermarket");
        assertThat(todo1.getCategory()).isEqualTo("housework");
        assertThat(todo1.getColor()).isEqualTo("white");
    }
}

※発行された DDL

Hibernate: create table todo (id bigint generated by default as identity, activity_name varchar(255), category varchar(255), color varchar(255), primary key (id))

なお、アプリケーション起動時に、flyway の sql-based-migrations を使用し、postgres に初期テーブル、データを作成する。

ちなみに、postgres コンテナを docker-compose で起動する際に初期DBを作成する手順は以前の投稿を参考までに。

DML の定義ファイルを用意。

※あとでハマる原因となる、 primary key である id の型が bigint(Long 型の entity の定義から自動的に設定されたもののようです)

-- V1.0.0__add_todo_table.sql
create table todo (id bigint not null, activity_name varchar(255), category varchar(255), color varchar(255), primary key (id));

DDL も JPA に自動生成してもらうために、テストケースを作成。

@Test
void newTodo() {
    var todo = new Todo("new", "white", "test");
    this.em.persistAndFlush(todo);
}

Hibernate: insert into todo (id, activity_name, category, color) values (null, ?, ?, ?)

参考にし DDL の定義ファイルも用意。

-- V1.0.1__insert_todo_records.sql
insert into todo (activity_name, category, color, id) values ('go to supermarket', 'housework', 'white', 1000);
insert into todo (activity_name, category, color, id) values ('listen to music', 'hobby', 'white', 1001);
insert into todo (activity_name, category, color, id) values ('make a presentation', 'job', 'black', 1002);

idの自動採番ができずに怒られる

アプリを起動し、

docker-compose up -d

./mvn spring-boot:run

POST リクエストをすると、

curl 'http://localhost:8081/todo/' -i -X POST \
    -d 'activityName=test&color=black&category=test'

insert 時に id が null で怒られます。

Hibernate: insert into todo (activity_name, category, color) values (?, ?, ?)
2021-01-18 22:45:01.523  WARN 2262 --- [nio-8081-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23502
2021-01-18 22:45:01.523 ERROR 2262 --- [nio-8081-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: null value in column "id" violates not-null constraint

org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint
  Detail: Failing row contains (null, test, test, black).

flyway の migration によりアプリ起動時に作成されたテーブルの定義を確認してみます。 postgres のコマンドをコンテナ内部に渡して、確認すると、

# `-d todo` で指定の todo は DB
# `\d todo` で todo table の定義を確認
docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "\d todo"'

V1.0.0__add_todo_table.sql で定義した通り、bigint です。

                           Table "public.todo"
    Column     |          Type          | Collation | Nullable | Default 
---------------+------------------------+-----------+----------+---------
 id            | bigint                 |           | not null | 
 activity_name | character varying(255) |           |          | 
 category      | character varying(255) |           |          | 
 color         | character varying(255) |           |          | 
Indexes:
    "todo_pkey" PRIMARY KEY, btree (id)

解決策

連番で採番し null を回避するには、 serial 型が使えそうです。

※serial 型のドキュメント

デフォルト値が連番を発生させる仕組みから割り当てられるようにしました。また、NOT NULL 制約を適用することによって、null 値が明示的に挿入されないようにします

bigint を使用していたので、 bigserial を使用してみます。

-- V1.0.0__add_todo_table.sql
create table todo (id bigserial not null, activity_name varchar(255), category varchar(255), color varchar(255), primary key (id));

コンテナを破棄、再生成して、アプリを起動しテーブルの定義を確認してみます。

docker-compose down && docker-compose up -d

./mvn spring-boot:run

$ docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "\d todo"'

bigint で自動採番される定義になっています。

                                       Table "public.todo"
    Column     |          Type          | Collation | Nullable |             Default              
---------------+------------------------+-----------+----------+----------------------------------
 id            | bigint                 |           | not null | nextval('todo_id_seq'::regclass)
 activity_name | character varying(255) |           |          | 
 category      | character varying(255) |           |          | 
 color         | character varying(255) |           |          | 
Indexes:
    "todo_pkey" PRIMARY KEY, btree (id)

todo_id_seq という設定についても確認すると、

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "\d"'

sequence という Type で、

                  List of relations
 Schema |         Name          |   Type   |  Owner   
--------+-----------------------+----------+----------
 public | flyway_schema_history | table    | postgres
 public | todo                  | table    | postgres
 public | todo_id_seq           | sequence | postgres
(3 rows)

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "\d todo_id_seq"'

1からインクリメントしていくもののようです。

                        Sequence "public.todo_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.todo.id

あとは、 entity の定義を変更します。

@Entity
public class Todo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY) // 自動インクリメントで一意の値を生成
    private Long id;

    private String activityName;

    private String color;

    private String category;
}

POST リクエストをすると、

curl 'http://localhost:8081/todo/' -i -X POST \
    -d 'activityName=test&color=black&category=test'

curl 'http://localhost:8081/todo/' -i -X POST \
    -d 'activityName=test2&color=white&category=test'

1番から採番されて、entity が永続化できています。

curl -s http://localhost:8081/todo/ | jq -r '.[] | select(.id == 1 or .id == 2)'

{
  "id": 1,
  "activityName": "test",
  "color": "black",
  "category": "test"
}
{
  "id": 2,
  "activityName": "test2",
  "color": "white",
  "category": "test"
}

DB も確認してみる。

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "select * from todo"'

  id  |    activity_name    | category  | color 
------+---------------------+-----------+-------
 1000 | go to supermarket   | housework | white
 1001 | listen to music     | hobby     | white
 1002 | make a presentation | job       | black
    1 | test                | test      | black
    2 | test2               | test      | white

もう一つのやり方

entity の定義に関して調べていると、自動採番の定義を @SequenceGenerator で設定するというやり方も見つけました。

@Entity
public class Todo {

    @Id
    @GeneratedValue(generator = "todo_id_gen")
    @SequenceGenerator(name = "todo_id_gen", sequenceName = "todo_id_seq", allocationSize = 1)
    private Long id;

    private String activityName;

    private String color;

    private String category;
}

sequence の定義も追加。

-- V1.0.0__add_todo_table.sql
create table todo (id bigint not null, activity_name varchar(255), category varchar(255), color varchar(255), primary key (id));
create sequence todo_id_seq start 10001;

relation の定義はどうなるかみてみましょう。(コンテナの disposability は便利)

docker-compose down && docker-compose up -d

./mvn spring-boot:run

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "\d"'

sequence がつくられています。

                  List of relations
 Schema |         Name          |   Type   |  Owner   
--------+-----------------------+----------+----------
 public | flyway_schema_history | table    | postgres
 public | todo                  | table    | postgres
 public | todo_id_seq           | sequence | postgres
(3 rows)

詳しくみてみる。

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "\d todo_id_seq"'

で POST リクエストをすると、

curl 'http://localhost:8081/todo/' -i -X POST \
    -d 'activityName=test&color=black&category=test'

curl 'http://localhost:8081/todo/' -i -X POST \
    -d 'activityName=test2&color=white&category=test'

10001番から採番されて、entity が永続化できています。

curl -s http://localhost:8081/todo/ | jq -r '.[] | select(.id == 10001 or .id == 10002)'

{
  "id": 10001,
  "activityName": "test",
  "color": "black",
  "category": "test"
}
{
  "id": 10002,
  "activityName": "test2",
  "color": "white",
  "category": "test"
}

DB も確認してみる。

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "select * from todo"'

  id   |    activity_name    | category  | color 
-------+---------------------+-----------+-------
  1000 | go to supermarket   | housework | white
  1001 | listen to music     | hobby     | white
  1002 | make a presentation | job       | black
 10001 | test              | test    | black
 10002 | test2               | test    | white
(5 rows)

さらにもう一つのやり方

DBの設定として、 sequence の定義と id フィールドとの関連付けをするという意味では、以下のように設定することも可能。

-- V1.0.0__add_todo_table.sql
-- id は bigint として定義しつつ、sequence によるインクリメントを設定
create sequence todo_id_seq start 10001;
create table todo (id bigint not null DEFAULT nextval('todo_id_seq'), activity_name varchar(255), category varchar(255), color varchar(255), primary key (id));

DB側で設定しているので、 @GeneratedValue,@SequenceGenerator は不要。

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

docker-compose down && docker-compose up -d

./mvn spring-boot:run

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "\d todo"'

                                       Table "public.todo"
    Column     |          Type          | Collation | Nullable |             Default              
---------------+------------------------+-----------+----------+----------------------------------
 id            | bigint                 |           | not null | nextval('todo_id_seq'::regclass)
 activity_name | character varying(255) |           |          | 
 category      | character varying(255) |           |          | 
 color         | character varying(255) |           |          | 
Indexes:
    "todo_pkey" PRIMARY KEY, btree (id)

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "\d todo_id_seq"'

                        Sequence "public.todo_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint | 10001 |       1 | 9223372036854775807 |         1 | no      |     1

POST したら想定通りに id が採番されている。

docker-compose exec postgres bash -c 'psql -Upostgres -d todo -c "select * from todo"'

  id   |    activity_name    | category  | color 
-------+---------------------+-----------+-------
  1000 | go to supermarket   | housework | white
  1001 | listen to music     | hobby     | white
  1002 | make a presentation | job       | black
 10001 | test                | tests     | black
(4 rows)

以上です。