Spring Boot & Postgres - CQRS (Multiple Database)
Overview
Spring boot implementation of CQRS pattern
Github: https://github.com/gitorko/project99
Main Topic
CQRS (Command and Query Responsibility Segregation) a pattern that separates read and update operations for different data store. This maximizes application performance, scalability, and security. We will start 2 database servers where writes goto the primary database and reads are done on the secondary database. Replication happens from primary db to secondary db. This is an AP model (CAP Theorem) as replication will result in eventual consistency.
Code
1package com.demo.project99.config;
2
3import java.util.HashMap;
4import java.util.Map;
5import javax.sql.DataSource;
6
7import com.zaxxer.hikari.HikariConfig;
8import com.zaxxer.hikari.HikariDataSource;
9import liquibase.integration.spring.SpringLiquibase;
10import org.springframework.beans.factory.annotation.Qualifier;
11import org.springframework.context.annotation.Bean;
12import org.springframework.context.annotation.Configuration;
13import org.springframework.core.env.Environment;
14import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
15import org.springframework.orm.jpa.JpaTransactionManager;
16import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
17import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
18import org.springframework.transaction.PlatformTransactionManager;
19import org.springframework.transaction.annotation.EnableTransactionManagement;
20
21@Configuration
22@EnableTransactionManagement
23@EnableJpaRepositories(
24 basePackages = "com.demo.project99.repository.primary",
25 entityManagerFactoryRef = "primaryEntityManagerFactory",
26 transactionManagerRef = "primaryTransactionManager"
27)
28public class PrimaryDataSourceConfig {
29
30 @Bean(name = "primaryDataSource")
31 public DataSource primaryDataSource(Environment env) {
32 HikariConfig config = new HikariConfig();
33 config.setJdbcUrl(env.getProperty("spring.datasource.primary.url"));
34 config.setUsername(env.getProperty("spring.datasource.primary.username"));
35 config.setPassword(env.getProperty("spring.datasource.primary.password"));
36 config.setDriverClassName(env.getProperty("spring.datasource.primary.driver-class-name"));
37 config.setMaximumPoolSize(Integer.parseInt(env.getProperty("spring.datasource.primary.hikari.maximum-pool-size")));
38 config.setMinimumIdle(Integer.parseInt(env.getProperty("spring.datasource.primary.hikari.minimum-idle")));
39 config.setIdleTimeout(Long.parseLong(env.getProperty("spring.datasource.primary.hikari.idle-timeout")));
40 config.setMaxLifetime(Long.parseLong(env.getProperty("spring.datasource.primary.hikari.max-lifetime")));
41 config.setConnectionTimeout(Long.parseLong(env.getProperty("spring.datasource.primary.hikari.connection-timeout")));
42 return new HikariDataSource(config);
43 }
44
45 @Bean(name = "primaryEntityManagerFactory")
46 public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(
47 @Qualifier("primaryDataSource") DataSource primaryDataSource, Environment env) {
48 HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
49 LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
50 factory.setDataSource(primaryDataSource);
51 factory.setPackagesToScan("com.demo.project99.domain.primary");
52 factory.setJpaVendorAdapter(vendorAdapter);
53 factory.setJpaPropertyMap(hibernateProperties(env));
54 return factory;
55 }
56
57 @Bean(name = "primaryTransactionManager")
58 public PlatformTransactionManager primaryTransactionManager(
59 @Qualifier("primaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory) {
60 return new JpaTransactionManager(primaryEntityManagerFactory.getObject());
61 }
62
63 @Bean
64 public SpringLiquibase primaryLiquibase(@Qualifier("primaryDataSource") DataSource primaryDataSource) {
65 SpringLiquibase liquibase = new SpringLiquibase();
66 liquibase.setDataSource(primaryDataSource);
67 liquibase.setChangeLog("classpath:db/changelog/db.changelog-primary.yaml");
68 liquibase.setContexts("primary");
69 return liquibase;
70 }
71
72 private Map<String, Object> hibernateProperties(Environment env) {
73 Map<String, Object> properties = new HashMap<>();
74 properties.put("hibernate.hbm2ddl.auto", env.getProperty("spring.jpa.properties.hibernate.hbm2ddl.auto"));
75 properties.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect"));
76 properties.put("hibernate.show_sql", env.getProperty("spring.jpa.properties.hibernate.show_sql"));
77 return properties;
78 }
79}
80
81
1package com.demo.project99.config;
2
3import java.util.HashMap;
4import java.util.Map;
5import javax.sql.DataSource;
6
7import com.zaxxer.hikari.HikariConfig;
8import com.zaxxer.hikari.HikariDataSource;
9import liquibase.integration.spring.SpringLiquibase;
10import org.springframework.beans.factory.annotation.Qualifier;
11import org.springframework.boot.jdbc.DataSourceBuilder;
12import org.springframework.context.annotation.Bean;
13import org.springframework.context.annotation.Configuration;
14import org.springframework.core.env.Environment;
15import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
16import org.springframework.orm.jpa.JpaTransactionManager;
17import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
18import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
19import org.springframework.transaction.PlatformTransactionManager;
20import org.springframework.transaction.annotation.EnableTransactionManagement;
21
22@Configuration
23@EnableTransactionManagement
24@EnableJpaRepositories(
25 basePackages = "com.demo.project99.repository.secondary",
26 entityManagerFactoryRef = "secondaryEntityManagerFactory",
27 transactionManagerRef = "secondaryTransactionManager"
28)
29public class SecondaryDataSourceConfig {
30
31 @Bean(name = "secondaryDataSource")
32 public DataSource secondaryDataSource(Environment env) {
33 HikariConfig config = new HikariConfig();
34 config.setJdbcUrl(env.getProperty("spring.datasource.secondary.url"));
35 config.setUsername(env.getProperty("spring.datasource.secondary.username"));
36 config.setPassword(env.getProperty("spring.datasource.secondary.password"));
37 config.setDriverClassName(env.getProperty("spring.datasource.secondary.driver-class-name"));
38 config.setMaximumPoolSize(Integer.parseInt(env.getProperty("spring.datasource.secondary.hikari.maximum-pool-size")));
39 config.setMinimumIdle(Integer.parseInt(env.getProperty("spring.datasource.secondary.hikari.minimum-idle")));
40 config.setIdleTimeout(Long.parseLong(env.getProperty("spring.datasource.secondary.hikari.idle-timeout")));
41 config.setMaxLifetime(Long.parseLong(env.getProperty("spring.datasource.secondary.hikari.max-lifetime")));
42 config.setConnectionTimeout(Long.parseLong(env.getProperty("spring.datasource.secondary.hikari.connection-timeout")));
43 return new HikariDataSource(config);
44 }
45
46 @Bean(name = "secondaryEntityManagerFactory")
47 public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(
48 @Qualifier("secondaryDataSource") DataSource secondaryDataSource, Environment env) {
49 HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
50 LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
51 factory.setDataSource(secondaryDataSource);
52 factory.setPackagesToScan("com.demo.project99.domain.secondary");
53 factory.setJpaVendorAdapter(vendorAdapter);
54 factory.setJpaPropertyMap(hibernateProperties(env));
55 return factory;
56 }
57
58 @Bean(name = "secondaryTransactionManager")
59 public PlatformTransactionManager secondaryTransactionManager(
60 @Qualifier("secondaryEntityManagerFactory") LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory) {
61 return new JpaTransactionManager(secondaryEntityManagerFactory.getObject());
62 }
63
64 @Bean
65 public SpringLiquibase secondaryLiquibase(@Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
66 SpringLiquibase liquibase = new SpringLiquibase();
67 liquibase.setDataSource(secondaryDataSource);
68 liquibase.setChangeLog("classpath:db/changelog/db.changelog-secondary.yaml");
69 liquibase.setContexts("secondary");
70 return liquibase;
71 }
72
73 private Map<String, Object> hibernateProperties(Environment env) {
74 Map<String, Object> properties = new HashMap<>();
75 properties.put("hibernate.hbm2ddl.auto", env.getProperty("spring.jpa.properties.hibernate.hbm2ddl.auto"));
76 properties.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect"));
77 properties.put("hibernate.show_sql", env.getProperty("spring.jpa.properties.hibernate.show_sql"));
78 return properties;
79 }
80
81 /**
82 * Non connection pool approach to get data source.
83 */
84 private DataSource createDataSource(Environment env) {
85 return DataSourceBuilder.create()
86 .url(env.getProperty("spring.datasource.secondary.url"))
87 .username(env.getProperty("spring.datasource.secondary.username"))
88 .password(env.getProperty("spring.datasource.secondary.password"))
89 .driverClassName(env.getProperty("spring.datasource.secondary.driver-class-name"))
90 .build();
91
92 }
93} 1package com.demo.project99.service;
2
3import java.util.List;
4
5import com.demo.project99.domain.primary.EmployeeWrite;
6import com.demo.project99.domain.secondary.EmployeeRead;
7import com.demo.project99.repository.primary.PrimaryEmployeeRepository;
8import com.demo.project99.repository.secondary.SecondaryEmployeeRepository;
9import lombok.RequiredArgsConstructor;
10import org.springframework.stereotype.Service;
11
12@Service
13@RequiredArgsConstructor
14public class EmployeeService {
15
16 final PrimaryEmployeeRepository primaryEmployeeRepository;
17
18 final SecondaryEmployeeRepository secondaryEmployeeRepository;
19
20 public EmployeeWrite saveEmployee(EmployeeWrite employeeWrite) {
21 return primaryEmployeeRepository.save(employeeWrite);
22 }
23
24 public List<EmployeeRead> getAllEmployees() {
25 return secondaryEmployeeRepository.findAll();
26 }
27}
Postman
Import the postman collection to postman
Setup
1# Project 05
2
3Spring Boot Postgres - CQRS (Multiple Database)
4
5[https://gitorko.github.io/post/distributed-locking-postgres](https://gitorko.github.io/post/distributed-locking-postgres)
6
7### Version
8
9Check version
10
11```bash
12$java --version
13openjdk 21.0.3 2024-04-16 LTS
14```
15
16### Postgres DB
17
18Start 2 Database Servers
19
20```bash
21docker-compose -f docker/docker-compose.yaml up -d
22```
23
24Enable replication after liquibase creates the tables, run this only after spring boot application is started.
25
26```bash
27docker-compose -f docker/enable-publication.yaml up -d
28docker-compose -f docker/enable-subscription.yaml up -d
29```
30
31Command to verify replication
32
33```bash
34docker exec -it pg-source psql -U test -d source_db -c "SELECT * FROM pg_roles WHERE rolname = 'replicator';"
35docker exec -it pg-source psql -U test -d source_db -c "SELECT * FROM pg_publication;"
36docker exec -it pg-target psql -U test -d target_db -c "SELECT * FROM pg_subscription;"
37
38docker exec -it pg-source psql -U test -d source_db -c "SELECT * FROM pg_replication_slots;"
39docker exec -it pg-target psql -U test -d target_db -c "SELECT * FROM pg_stat_subscription;"
40
41docker exec -it pg-source psql -U test -d source_db -c "SELECT * FROM pg_create_logical_replication_slot('employee_slot', 'pgoutput');"
42```
43
44Clean up
45
46```bash
47docker-compose -f docker/docker-compose.yaml down --rmi all --remove-orphans --volumes
48```
49
50### Dev
51
52To run the backend in dev mode.
53
54```bash
55./gradlew clean build
56./gradlew bootRun
57```
References
comments powered by Disqus