PostgreSQLのJSON型とVIEWを使って異なるRead ModelとWrite Modelを作る (Spring Boot版)

このツイートを見て、面白そうだったのでSpring Bootで実装してみた。

One of my favourite things with Postgres is being able to think about read/write models separately.

1) Lazily dump data in as json.
2) Build a read model with [materialized] views
3) Refactor the write model underneath

Get the convenience of sql for reads before (3) pic.twitter.com/31xhuEK7ty
— Benji Weber (@benjiweber) March 20, 2021

特にRead用のデータモデルが決まっていない状態で、まずはとにかくデータを受け付けて後からRead Modelを設計したいケースを考える。

まずはPostgreSQLのJSONB型を使って、雑に任意のデータを受け付けるように作る。

-- CREATE WRITE MODEL

CREATE TABLE IF NOT EXISTS data
(
    id   SERIAL,
    data JSONB
);

Spring Bootで実装する。

まずは雑なWrite ModelとしてDataクラスを用意する。ここではJava 16のRecordsを使用する。

package com.example.demopostgresjson.writemodel;

import java.util.Map;

public record Data(Integer id, Map<String, Object> data) {
}

次にMapperクラス。JSON型でinsertするのでプレースホルダに::JSONをつける必要がある。

package com.example.demopostgresjson.writemodel;

import java.io.UncheckedIOException;
import java.util.Map;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class DataMapper {
    private final JdbcTemplate jdbcTemplate;

    private final ObjectMapper objectMapper;

    public DataMapper(JdbcTemplate jdbcTemplate, ObjectMapper objectMapper) {
        this.jdbcTemplate = jdbcTemplate;
        this.objectMapper = objectMapper;
    }

    @Transactional
    public Data insert(Map<String, Object> data) {
        try {
            final String json = this.objectMapper.writeValueAsString(data);
            final Integer id = this.jdbcTemplate.queryForObject("INSERT INTO data(data) values(?::JSON) RETURNING id", Integer.class, json);
            return new Data(id, data);
        }
        catch (JsonProcessingException e) {
            throw new UncheckedIOException(e);
        }
    }
}

最後にControllerクラス。

package com.example.demopostgresjson.writemodel;

import java.util.Map;

import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class DataController {
    public final DataMapper dataMapper;

    public DataController(DataMapper dataMapper) {
        this.dataMapper = dataMapper;
    }

    @PostMapping(path = "data")
    public Data postData(@RequestBody Map<String, Object> data) {
        return this.dataMapper.insert(data);
    }
}

このアプリケーションに次の不定な型のリクエストを送る。

curl -s -H "Content-Type: application/json" -w '\n' localhost:8080/data -d '{"role":"author", "name": "foo", "books":["foo", "bar"]}'
curl -s -H "Content-Type: application/json" -w '\n' localhost:8080/data -d '{"foo":"bar"}'
curl -s -H "Content-Type: application/json" -w '\n' localhost:8080/data -d '{"role":"author", "name": "bar", "books":["baz"]}'

次のレスポンスが返る。

{"id":1,"data":{"role":"author","name":"foo","books":["foo","bar"]}}
{"id":2,"data":{"foo":"bar"}}
{"id":3,"data":{"role":"author","name":"bar","books":["baz"]}}

PostgreSQL上には次のようにデータが格納されている。

demo=> select * from data;
 id |                            data                            
----+------------------------------------------------------------
  1 | {"name": "foo", "role": "author", "books": ["foo", "bar"]}
  2 | {"foo": "bar"}
  3 | {"name": "bar", "role": "author", "books": ["baz"]}
(3 rows)

ここでRead Model(AuthorとBook)が固まり、次のViewを作成する。

-- CREATE READ MODEL
CREATE INDEX IF NOT EXISTS role ON data ((data ->> 'role'));

CREATE OR REPLACE VIEW author AS
SELECT id AS author_id, data ->> 'name' AS author_name
FROM data
WHERE data ->> 'role' = 'author';

CREATE OR REPLACE VIEW book AS
SELECT id AS author_id, jsonb_array_elements_text(data -> 'books') AS book_name
FROM data
WHERE data ->> 'role' = 'author'

Read ModelとしてAuthorクラスとBookクラスを作成する。

package com.example.demopostgresjson.readmodel;

import java.util.List;

public record Author(Integer id, String name, List<Book> books) {
}

package com.example.demopostgresjson.readmodel;

public record Book(String name) {
}

次にAuthorに対するMapperを作成する。

package com.example.demopostgresjson.readmodel;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

@Repository
public class AuthorMapper {
    private final JdbcTemplate jdbcTemplate;

    private final ResultSetExtractor<Optional<Author>> authorResultSetExtractor = rs -> {
        Integer authorId = null;
        String authorName = null;
        final List<Book> books = new ArrayList<>();
        while (rs.next()) {
            if (authorId == null) {
                authorId = rs.getInt("author_id");
            }
            if (authorName == null) {
                authorName = rs.getString("author_name");
            }
            books.add(new Book(rs.getString("book_name")));
        }
        if (authorId == null) {
            return Optional.empty();
        }
        return Optional.of(new Author(authorId, authorName, books));
    };

    public AuthorMapper(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public Optional<Author> findById(Integer authorId) {
        return this.jdbcTemplate.query("SELECT a.author_id, a.author_name, b.book_name FROM author AS a INNER JOIN book AS b ON a.author_id = b.author_id WHERE a.author_id = ?", this.authorResultSetExtractor, authorId);
    }

}

最後にControllerクラスを作成。

package com.example.demopostgresjson.readmodel;

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class AuthorController {
    private final AuthorMapper authorMapper;

    public AuthorController(AuthorMapper authorMapper) {
        this.authorMapper = authorMapper;
    }

    @GetMapping(path = "authors/{authorId}")
    public ResponseEntity<Author> getAuthor(@PathVariable("authorId") Integer authorId) {
        return ResponseEntity.of(this.authorMapper.findById(authorId));
    }
}

このGET APIに対して次のリクエストを送る。

curl -s -w '\n' localhost:8080/authors/1
curl -s -w '\n' localhost:8080/authors/3

次のレスポンスが返る。

{"id":1,"name":"foo","books":[{"name":"foo"},{"name":"bar"}]}
{"id":3,"name":"bar","books":[{"name":"baz"}]}

PostgreSQL上には次のようにデータが格納されている。

demo=> select * from author;
 author_id | author_name 
-----------+-------------
         1 | foo
         3 | bar
(2 rows)

demo=> select * from book;
 author_id | book_name 
-----------+-----------
         1 | foo
         1 | bar
         3 | baz
(3 rows)

面白い。
データモデルを定まらない場合や軽量なCQRSなんかに使えそう。

完全なソースコードはこちら。

IK.AM

PostgreSQLのJSON型とVIEWを使って異なるRead ModelとWrite Modelを作る (Spring Boot版)