Comparing approaches to editing database entries • blog.metters.dev

Running a stateful application with frontend, backend, and a database, sometimes requires persisted data to be edited - usually this is the case for bugfixes or the clean-up after a bug. Different situations require different solutions, but all those have their own upsides and downsides.

Let’s take a closer look at three approaches. I ordered them by how ‘over-engineered’ they are, in my personal opinion.

Directly manipulating data via sql query.
Running a Flyway script
Running a Spring component on application start-up

1. Directly manipulating data via sql query.

Sometimes the quickest and “easiest” way indeed is the best: Connect to your database and directly manipulate the data via sql. It is very fast and, well, direct.

If possible, test the query on another stage. Definitely, let another pair of eyes see what you are going to do (four-eyes principle) - when editing code this would be the review of a pull request.

But be carful: The more complex the filter/query, the higher the risk. There are no log messages that later might help debugging! The data just changed. Also, a rollback might not be possible! But you don’t have to execute a (hotfix)release and deployment - and all that red-tape that sometimes comes with that.

I once, tried to add a dry run in SQL. All changes needed to be logged, so we would be able to investigate any possible issues later on. I ended up with a script similar (the scrip below is made-up of course, surely there are even errors in there!) to the one below, but this just does not feel satisfying (or safe tbh).

Click to expand

BEGIN;

DO
$$
  BEGIN
    IF NOT EXISTS (SELECT 1
                   FROM information_schema.columns
                   WHERE table_name = 'items'
                     AND column_name = 'name') THEN
      ALTER TABLE items
        ADD COLUMN name CHAR
    END IF;
  END
$$;

CREATE TEMP TABLE dryrun_check
(
  uuid   UUID PRIMARY KEY,
  name   CHAR(1),
  status TEXT CHECK (status IN ('UPDATED', 'NOT_UPDATED_OR_NULL'))
) ON COMMIT PRESERVE ROWS;

WITH upd AS (
  UPDATE items item
    SET
      name = CASE
               WHEN item.type IN ('REGULAR', 'LARGE') THEN 'B'
               WHEN item.type = 'SMALL' THEN 'S'
               ELSE 'X'
        END
    FROM collections col
    WHERE col.uuid = item.col_uuid
    RETURNING item.uuid)
INSERT
INTO dryrun_check (uuid, name, status)
SELECT *
FROM (SELECT item.uuid,
             item.name,
             'UPDATED' AS status
      FROM items item
             JOIN upd u ON u.uuid = item.uuid
      UNION ALL
      SELECT item.uuid,
             item.name,
             item.payment_scheme_2,
             'NOT_UPDATED_OR_NULL' AS status
      FROM items item
             LEFT JOIN upd u ON u.uuid = item.uuid
      WHERE u.uuid IS NULL
         OR item.name IS NULL) src
ON CONFLICT (uuid) DO NOTHING;

DO
$$
  DECLARE
    r RECORD;
  BEGIN
    FOR r IN
      SELECT *
      FROM dryrun_check
      ORDER BY uuid
      LOOP
        RAISE NOTICE '%',
          json_build_object(
            'uuid', r.uuid,
            'name', r.name,
            'status', r.status
          )::text;
      END LOOP;
  END
$$;

ROLLBACK;

2. Running a Flyway script

The application of one project I used to work on had a spring boot backend. Usually, we would use Flyway scripts to update the Postgres database alongside the backend code. The usual scenarios, like renaming a column, are simple and need no explanation, but updating persisted data, because the business logic changed, might become more complex!

review (pull request)
relative transparency, due to the flyway script being persisted in the source code
all or nothing: if something goes wrong on deployment all changes will be reverted
no “logging”

3. Running a Spring component on application start-up

A better approach?

An event listener triggered by the ApplicationReadyEvent is perfect for this scenario. First, flyway scripts are executed, then the application boots up, and lastly, the migration method gets executed.

Applying new logic to data for me as a developer with a higher focus on backend is much more intuitive using backend code instead of SQL scripts. The compiler is extremely helpful, too. Also, it is possible to write tests for such methods to assert the outcome is as intended. If there are any issues or unexpected results during the migration, it is much easier to log what has happened. I would rather have to analyse my structured log files, instead of trying to figure out the root cause of the problem from error messages thrown during the script execution.

@Service
class ItemsMigrationProcessor(
    private val itemRepository: ItemRepository,
) {
    private val logger = LoggerFactory.getLogger(javaClass)

    @EventListener(ApplicationReadyEvent::class)
    @Transactional
    fun migrateItems() {
      logger.atDebug().log { "Starting to migrate items" }

      val items = itemRepository.findAll()
      items.forEach {
        it.editSomeStuff()
      }

      logger.atDebug().log { "Migrated [${items.size}] items" }
    }

    private fun Item.editSomeStuff() {
      logger.atDebug().log { "Migrating item ${this.uuid}" }
      this.name = when (this.type) {
        Type.REGULAR, Type.LARGE -> "B"
        Type.SMALL -> "S"
      }
      logger.atDebug().log { "Migrated item ${this.uuid} to name ${this.name}" }
  }
}

Disadvantages

Using the spring boot application to migrate data has some downsides too!

1. Limited memory on large datasets

Make sure the application has enough memory to update all entries at once. Alternatively, do not update all data in one run, but use chunks instead.

Issues like that are easy to overlook, because usually tests focus on correct logic, but not on performance/memory restrictions.

2. Execution time on large datasets

Using Flyway, the script gets executed before the application has booted. Our migration processor needs to wait until the application has finished starting in order to execute.

In most deployment processes, the application gets live as soon as it is started up and processes, such as the data migration, get executed on a live system. Keep in mind that users work with outdated data, if a freshly deployed application is accessible, before the migration has finished.

3. Testability does not mean all scenarios get tested

Tests need realistic data and a proper test setup to be able to predict issues.

4. Clean up

Do not forget to remove migration methods: even with idempotency, the new business logic of the application already gets applied and Database entries only need to be migrated one time. In general, it is preferable to remove unnecessary code after its execution.

Conclusion

Four-eyes principle
I am more familiar with writing backend code
The migration code is testable, but requires proper test setup
Logged errors are easier to analyse