Postgresql Java Cloner

A lightweight Java library that allows cloning entity data from one PostgreSQL database to another using COPY. Providing additional functionality to make it easier to use when having to pick rows from multiple tables to represent a single entity.

Read more about the development on my blog

Features:

• Minimal external dependencies. Only dependencies are the official PostgreSQL JDBC driver (org.postgresql:postgresql) and Apache Commons CSV (org.apache.commons:commons-csv)
• Selective cloning. Clone precisely the rows you want by specifying filters.
  • Repeatable cloning. The cloning process is repeatable and will ensure only the cloned rows exist for the specific entity without affecting other entities.
• Graph-based configuration builder. Define root tables and their joins recursively with a builder pattern for type safety.
• Streaming support. Handle large imports and exports without memory overhead.
• Minimal privileges. Requires only SELECT and INSERT privileges on the target tables; no superuser or special permissions are needed.
• Row Level Security support. Clone data based on user permissions.
• Verify configuration. Verify the configuration before running the clone and ensure correctness through unit tests.
• Mask sensitive data. Mask sensitive data such as passwords, credit card numbers, and other sensitive information.

Test coverage near 100% for cloning implementation:

Usage

Create a clone configuration with the builder pattern specifying the main tables and how other tables should be joined to them:

CloneConfiguration CLONE_CONFIGURATION = CloneConfiguration.builder("main_table", "id", Long.class)
        .joinByJoinTableForeignKey("sub_table", "main_id")
        .joinByMainTableForeignKey("another_sub_table", "another_sub_table_id")
        .build();

Use the Cloner to export the data and import it:

// Specify the ids
final var ids = List.of(1L);

// Export the data to a ZIP file
final byte[] zipBytes;
try (final var out = new ByteArrayOutputStream()) {
    cloner.exportClone(out, exportConn, CLONE_CONFIGURATION, ids);
    zipBytes = out.toByteArray();
}

// Import the data from the ZIP file to another environment
try (final var in = new ByteArrayInputStream(zipBytes)) {
    cloner.importClone(in, importConn, CLONE_CONFIGURATION);
}

Joining tables

Join other tables recursively by specifying the foreign keys between the tables.

CloneConfiguration CLONE_CONFIGURATION = CloneConfiguration.builder("main_table", "id", Long.class)
        .joinByJoinTableForeignKey("sub_table", "main_id", join -> join
            .joinByMainTableForeignKey("another_sub_table", "another_sub_table_id", join2 -> join2
                .joinByMatchingColumns("matching_table", "common_value_column_in_main_table", "common_value_column_in_matching_table")))
        .build();

Either use:

• Join by joined table foreign key: where the foreign key is on the joined table.
• Join by main table foreign key: where the foreign key is on the main table.
• Join by matching columns: where both tables contain a common value.

The builder will automatically adjust the ordering of the inserts such that they are inserted in the correct order with the dependent tables being inserted after the table values they point to.

Verifying the configuration

Before running the clone, it is recommended to verify the configuration to ensure correctness. This can be done by running unit tests against the ConfigurationVerifier class.

@Test
void verifyConfiguration(final Connection connection) {
  // Arrange
  final var verifier = ConfigurationVerifier.create();

  final var expected = List.of(
          // The central_config table is constant across environment so is expected to not be configured.
          new ReferencedTableNotConfigured("central_config", "entity")
  );

  // Act
  final var errors = verifier.verify(connection, CENTRAL_CONFIGURATION);

  // Assert
  assertEquals(
          expected,
          errors,
          "An unexpected configuration error. Add it to the expected list if the entry is constant."
  );
}

This way you can ensure that all the required tables are properly configured.

Masking sensitive data

Mask data by adding a table mask to the clone configuration:

CloneConfiguration CLONE_CONFIGURATION = CloneConfiguration.builder("main_table", "id", Long.class)
        .masker("main_table", row -> row.mask("password", "not cloned"))
        .build();

All "password" data will now contain "not cloned" instead of the password.

Project structure

The project contains the following main Gradle modules:

• core the main project where all the features live.
• spring a small wrapper for working with the Spring DataSource.
• example a small example project that demonstrates how to use the library.

Future work

There are currently no plans for further development.

This is a list of ideas that could be implemented:

• Support for automatic generation of the clone configuration based on the entity structure in Hibernate.
  • Hibernate's meta model could be used to generate the configuration.