Database access 2 - fidransky/kiv-pia-labs GitHub Wiki

TOTD:

learn about blocking vs. non-blocking database access
implement repositories using Spring's R2DBC DatabaseClient
introduce database migration engine to the project

Terms and theory

Throughout KIV/PIA labs, we've been discussing two request processing approaches:

blocking I/O
non-blocking I/O

To embrace non-blocking request processing fully, all application I/O must be non-blocking - even database access.

Reactive Relational Database Connectivity (R2DBC)

In Java, JDBC is used as the long-established and fully supported database access approach. It is, however, blocking by design. In order to enable reactive apps to access databases in non-blocking manner, a new specification was introduced - R2DBC.

Because using R2DBC is a completely separate DB access approach, none of the standard JDBC techniques can be used with it. As a result, special database drivers as well as Java clients are needed.

Database migrations

Typically, as application evolves, database schema (i.e. database tables, their structure and relations) evolves with it. To make schema changes easier to manage, we usually introduce a database migration engine to our apps.

Generally, database migration engines store all applied migration scripts somewhere (e.g. in a database table). When the migration engine is started, it first looks into its store to decide which migration scripts to run, executes and saves them back to its store for future reference. The engine can be started either at the start of the app or before that.

In Java, two migrations engines are commonly used:

Practice

Starting with the last lab, the chat rooms app stores its data in database and it uses JDBC (blocking) for database access. Today, we're going to create a non-blocking repository implementation using R2DBC.

1. Run MySQL in Docker

Repeat steps from the last lab to run MySQL in Docker.

2. Implement repositories using R2DBC

2.1 Create R2DBC submodule

Create pia-labs-dao-r2dbc (DAO meaning data access object ~ repository) submodule.

Make it depend on pia-labs-core so that it gains access to repository interfaces.

Add org.springframework:spring-r2dbc:5.3.22 dependency for JDBC access.

Add dev.miku:r2dbc-mysql:0.8.2.RELEASE dependency for MySQL implementation of R2DBC SPI.

2.2 Configure R2DBC connection properties

Define R2DBC connection properties in application.properties file:

host: localhost
port: 3306
schema: pia_labs
username: root
password: root

2.3 Configure R2DBC connection

Create a new @Configuration class called R2dbcConfig in the pia-labs-dao-r2dbc submodule.

Define io.r2dbc.spi.ConnectionFactory and org.springframework.r2dbc.connection.R2dbcTransactionManager beans there using @Value to get the externalized connection properties from application.properties file.

Annotate the JdbcConfig with @EnableTransactionManagement annotation to activate Spring's transaction processing.

2.4 Populate the database

Load the init.sql file as a classpath Resource in R2dbcConfig:

@Value("classpath:init.sql")
private Resource initializerScript;

Define org.springframework.r2dbc.connection.init.ConnectionFactoryInitializer bean in R2dbcConfig:

var initializer = new ConnectionFactoryInitializer();
initializer.setConnectionFactory(connectionFactory);
initializer.setDatabasePopulator(new ResourceDatabasePopulator(initializerScript));
return initializer;

Run the app, then open MySQL client and see the DB contents again.

3. Implement repositories using Spring `DatabaseClient`

Define org.springframework.r2dbc.core.DatabaseClient bean in R2dbcConfig using previously defined ConnectionFactory:

return DatabaseClient.builder()
		.connectionFactory(connectionFactory)
		.build();

Create a new implementation of RoomRepository interface using DatabaseClient yourself. Don't forget to annotate it as @Repository.

Implement java.util.function.Function functional interface to map DB rows into Java objects and use it in your new RoomRepository implementation.

4. Use R2DBC submodule

Add pia-labs-dao-r2dbc as a dependency of pia-labs-web submodule.

When you run the app now, Spring will complain that there are two implementations of repositories available - the original in-memory ones and JDBC ones. Make JDBC @Repository implementations @Primary and run the app again.

5. Add database migration engine

5.1 Add dependencies

Add org.flywaydb:flyway-core:9.8.3 and org.flywaydb:flyway-mysql:9.8.3 dependencies to pia-labs-core submodule.

Move com.mysql:mysql-connector-j:8.0.31 dependency to pia-labs-core submodule so that MySQL JDBC driver is available for Flyway even when R2DBC repository implementation is used because Flyway doesn't support R2DBC yet.

5.2 Configure Flyway

Create a new @Configuration class called FlywayConfig in the pia-labs-core submodule.

Use externalized JDBC connection properties from application.properties file to define org.flywaydb.core.Flyway bean:

return Flyway.configure()
		.dataSource(dataSourceUrl, dataSourceUsername, dataSourcePassword)
		.load();

Set the bean to run Flyway's migrate method on initialization using @Bean annotation's initMethod property.

5.3 Make database initializers depend on Flyway bean

Use @DependsOn annotation to make database initializer beans in JdbcConfig and R2dbcConfig run only after Flyway migration is finished.

Run the application to verify that everything is set up correctly. The app starts up as usual but in the database, a new table flyway_schema_history is created. Since we haven't created any database migration scripts yet, the table is empty.

6. Move DDL SQL scripts from database init script to migration script

Create a new database migration script called V1__Base.sql in pia-labs-code submodule's src/main/resources/db/migration/ folder.

Move CREATE TABLE statements from init.sql script to the newly create migration script.

Build and run the app again. Now, the flyway_schema_history table contains one row corresponding with the migration script.