---
title: PostgreSQLのJSON型とVIEWを使って異なるRead ModelとWrite Modelを作る (Spring Boot版)
tags: ["PostgreSQL", "Java", "Spring Boot"]
categories: ["Middleware", "RDBMS", "PostgreSQL"]
date: 2021-05-23T04:08:17Z
updated: 2021-05-23T04:11:43Z
---

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

<blockquote class="twitter-tweet"><p lang="en" dir="ltr">One of my favourite things with Postgres is being able to think about read/write models separately. <br><br>1) Lazily dump data in as json. <br>2) Build a read model with [materialized] views<br>3) Refactor the write model underneath<br><br>Get the convenience of sql for reads before (3) <a href="https://t.co/31xhuEK7ty">pic.twitter.com/31xhuEK7ty</a></p>&mdash; Benji Weber (@benjiweber) <a href="https://twitter.com/benjiweber/status/1373362733467455489?ref_src=twsrc%5Etfw">March 20, 2021</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

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

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

```sql
-- CREATE WRITE MODEL

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

Spring Bootで実装する。

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

```java
package com.example.demopostgresjson.writemodel;

import java.util.Map;

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

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

```java
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クラス。

```java
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"]}'
```

次のレスポンスが返る。

```json
{"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を作成する。

```sql
-- 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`クラスを作成する。

```java
package com.example.demopostgresjson.readmodel;

import java.util.List;

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

```java
package com.example.demopostgresjson.readmodel;

public record Book(String name) {
}
```

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

```java
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クラスを作成。

```java
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なんかに使えそう。

完全なソースコードは[こちら](https://github.com/making/demo-postgres-json)。