dbCreate in production: validate with manual SQL or the Database Migration plugin?

The Recommended Production Strategy

For production environments, the generally accepted setup is to set dbCreate: validate and utilize the Database Migration plugin (which wraps Liquibase). This combination ensures that the application will not start if the schema diverges from the domain model, while providing a versioned, repeatable history of all changes.

Comparison: Plugin vs. Manual SQL

While manual SQL is technically viable, it is typically reserved for environments with extreme security restrictions on application-level schema changes or very small projects with infrequent updates. The trade-offs are as follows:

• Database Migration Plugin: Provides a source-controlled audit trail, allows for automated deployments across multiple environments (staging, production), and prevents "drift" by tracking which changesets have been applied.
• Manual SQL: High risk of human error, no built-in mechanism to track which scripts have run on which environment, and requires manual coordination between developers and DBAs.

Baselining an Existing "Update" Schema

When transitioning a production database that was evolved via dbCreate: update to the migration plugin, you must establish a baseline. This prevents the plugin from attempting to recreate tables that already exist.

1. Generate the Baseline: Run the baseline command against a copy of your production schema to capture the current state as the starting point (Revision 0). Depending on your plugin version, this is typically:

   grails liquibase:baseline

2. Verify the Snapshot: Review the generated changelog to ensure all constraints, indexes, and triggers were captured, as automated snapshots can occasionally miss complex database-specific objects.
3. Deploy the Baseline: Apply the baseline marker to the production database. This tells Liquibase that all changes up to this point are already present.
4. Apply New Changes: Create new changelogs for any domain changes moving forward.

Version-Specific Considerations

The recommendation remains consistent across Grails versions, but the implementation details vary:

• Grails 3/4: Typically uses Database Migration plugin v1.x.
• Grails 5/6: Uses v2.x of the plugin, which aligns with newer Hibernate versions (5/6) and updated Liquibase syntax.

Note: If you are using a custom Hibernate dialect or a non-standard database version, verify that the specific plugin release supports that dialect before deploying to production.

Verification Steps

To ensure the transition is safe, perform these steps in a staging environment that mirrors production:

• Run grails liquibase:update to confirm the baseline and first migration apply without errors.
• Verify the schema via grails dbconsole or a JDBC client to ensure constraints match the domain model.
• Start the application with dbCreate: validate to confirm the Hibernate validation pass succeeds.