Dev Bootstrap & Seed Data#

Palmyra reads the DB schema at startup — so every new dev environment needs the same tables, constraints, and seed rows before the first request goes through. This guide covers the three problems every project hits:

DDL that belongs in version control but isn’t managed by Hibernate.
Seed rows for lookup tables and an admin user.
Order-of-operations with Hibernate’s ddl-auto.

1. `ddl-auto` — what to set, and why#

Profile	Setting	Reasoning
Dev against a stable schema	`ddl-auto: none`	Palmyra reads metadata at startup; ddl-auto re-diffing churns the schema and has fought with our `DevBootstrap`-managed columns in practice
Dev, actively evolving the schema	`ddl-auto: update`	Fast iteration; JPA entity changes show up in the DB. Accept the churn
Test (testcontainers / H2)	`ddl-auto: create-drop`	Throw-away
Prod	`ddl-auto: validate`	Fail fast on schema drift

Our working recommendation: start with update in dev, flip to none once your schema stabilises — at that point DevBootstrap owns the DDL deltas and Hibernate stops interfering.

spring:
  jpa:
    hibernate:
      ddl-auto: none     # see decision table above

2. DevBootstrap — a `CommandLineRunner` pattern#

Bind a @Profile("dev") CommandLineRunner that runs after Spring Boot’s context is ready but before the web server accepts traffic. It does four things, in order:

ALTER TABLE for columns the extensions expect but your base DDL doesn’t have (e.g. xpm_user.random / salt / lock_expire from palmyra-dbpwd-mgmt).
Execute SQL scripts (DDL + seed) via ResourceDatabasePopulator.
Upsert a dev admin user with a known password.
Ensure admin ∈ admin-group via xpm_acl_user.

@Component
@Profile("dev")
@RequiredArgsConstructor
@Slf4j
public class DevBootstrap implements CommandLineRunner {

    private static final String LOGIN    = "admin";
    private static final String PASSWORD = "Passw0rd!";
    private static final String GROUP    = "AppAdmin";

    private final JdbcTemplate jdbc;
    private final DataSource   dataSource;

    @Override
    public void run(String... args) {
        applyAuthColumns();
        applyAclSchemaAndSeed();
        upsertAdminUser();
        ensureGroupMembership();
    }

    private void applyAuthColumns() {
        jdbc.execute("ALTER TABLE xpm_user "
                + "ADD COLUMN IF NOT EXISTS `random`    varchar(128) NULL, "
                + "ADD COLUMN IF NOT EXISTS salt        varchar(128) NULL, "
                + "ADD COLUMN IF NOT EXISTS lock_expire datetime     NULL");
    }

    private void applyAclSchemaAndSeed() {
        ResourceDatabasePopulator p = new ResourceDatabasePopulator();
        p.addScript(new ClassPathResource("sql/xpm_acl_ddl.sql"));
        p.addScript(new ClassPathResource("sql/xpm_acl_seed.sql"));
        p.setContinueOnError(false);
        p.execute(dataSource);
    }

    private void upsertAdminUser() {
        Integer existing = jdbc.queryForObject(
                "SELECT COUNT(*) FROM xpm_user WHERE login_name = ?", Integer.class, LOGIN);
        if (existing != null && existing > 0) {
            jdbc.update("UPDATE xpm_user SET `random` = NULL, salt = ?, lock_expire = NULL "
                    + "WHERE login_name = ?", PASSWORD, LOGIN);
            return;
        }
        jdbc.update("INSERT INTO xpm_user (login_name, display_name, `random`, salt, "
                + "created_by, last_upd_by, created_on, last_upd_on) "
                + "VALUES (?, ?, NULL, ?, 'dev-bootstrap', 'dev-bootstrap', NOW(), NOW())",
                LOGIN, "Dev Admin", PASSWORD);
    }

    private void ensureGroupMembership() {
        Integer userId  = jdbc.queryForObject(
                "SELECT MIN(id) FROM xpm_user  WHERE login_name = ?", Integer.class, LOGIN);
        Integer groupId = jdbc.queryForObject(
                "SELECT MIN(id) FROM xpm_group WHERE name = ?",       Integer.class, GROUP);
        if (userId == null || groupId == null) return;

        Integer linked = jdbc.queryForObject(
                "SELECT COUNT(*) FROM xpm_acl_user WHERE user_id = ? AND group_id = ?",
                Integer.class, userId, groupId);
        if (linked != null && linked > 0) return;

        jdbc.update("INSERT INTO xpm_acl_user (group_id, user_id, active, "
                + "created_by, last_upd_by, created_on, last_upd_on) "
                + "VALUES (?, ?, 1, 'dev-bootstrap', 'dev-bootstrap', NOW(), NOW())",
                groupId, userId);
    }
}

The plaintext-password shortcut#

When xpm_user.random IS NULL, PasswordMgmtServiceImpl.isValid does a plain salt.equals(input) compare — we exploit that in the admin upsert so the seed doesn’t need to reproduce the MD5(random + password) math. Fine for dev; not for prod.

`SELECT MIN(id)` over `SELECT id`#

Re-running DevBootstrap on a DB with duplicate rows (from an earlier buggy seed) with queryForObject and a plain SELECT id throws IncorrectResultSizeDataAccessException. MIN(id) tolerates duplicates — take the earliest and move on.

3. Seed SQL files — idempotence patterns#

`INSERT IGNORE` when a unique key exists#

INSERT IGNORE INTO zcm_gender (id, name) VALUES
  (1, 'Male'), (2, 'Female'), (3, 'Other');

Primary key id is unique → re-runs are no-ops.

`INSERT ... SELECT ... WHERE NOT EXISTS` when there’s no natural key#

INSERT INTO mrcp_patient (patient_code, first_name, last_name, /* ... */)
SELECT 'PT0001', 'Ada', 'Lovelace', /* ... */
WHERE NOT EXISTS (SELECT 1 FROM mrcp_patient WHERE patient_code = 'PT0001');

`ON DUPLICATE KEY UPDATE` to refresh non-key fields#

INSERT INTO xpm_acl_class (class_code, class_name) VALUES
  ('XpmUser',  'User Management'),
  ('XpmGroup', 'Group & ACL Management')
ON DUPLICATE KEY UPDATE class_name = VALUES(class_name);

Avoid id-only referencing — join on natural keys#

Given xpm_group.id may differ across environments, seed grants by joining on name / code:

INSERT INTO xpm_acl_group_permission (group_id, permission_id, mask, ...)
SELECT g.id, p.id, 1, ...
FROM xpm_group g
CROSS JOIN xpm_acl_permission p
WHERE g.name = 'AppAdmin'
  AND NOT EXISTS (
    SELECT 1 FROM xpm_acl_group_permission x
    WHERE x.group_id = g.id AND x.permission_id = p.id
  );

4. Prod migration path#

DevBootstrap is explicitly dev-only (@Profile("dev")). In prod, replace each of its four concerns:

Dev	Prod
`applyAuthColumns()`	A Flyway / Liquibase migration at a known version
`applyAclSchemaAndSeed()`	Same — but seed rows go through the migration tool too
`upsertAdminUser()`	Set once during deployment; rotate password out-of-band
`ensureGroupMembership()`	Managed by the admin UI (group CRUD + user-group join)

Don’t let DevBootstrap code leak into prod — one wrong profile activation and it rewrites your admin password.

5. Ordering around Hibernate#

Hibernate’s schema sync runs before CommandLineRunner. If you ALTER a table in DevBootstrap with a nullable column, then later an entity expects it non-nullable, Hibernate’s next validation round catches the mismatch. Patterns to avoid drift:

Keep DDL in your Flyway/Liquibase migrations (or DevBootstrap SQL scripts) and in the JPA entities. They describe the same thing; disagreement = bug.
When using ddl-auto: update, seed only rows in DevBootstrap — let Hibernate own DDL.
When using ddl-auto: none, DevBootstrap owns DDL too — verify with an explicit SELECT in DevBootstrap if needed.

Dev Bootstrap & Seed Data#

1. ddl-auto — what to set, and why#

2. DevBootstrap — a CommandLineRunner pattern#

The plaintext-password shortcut#

SELECT MIN(id) over SELECT id#