Skip to content

SQL Maven Plugin

The Tentackle SQL Maven Plugin

The tentackle-sql-maven-plugin turns the Tentackle entity model into database DDL. Where the tentackle-maven-plugin generates the Java- and service metadata of an application, this plugin is responsible for everything that touches the database schema:

  • generating a CREATE TABLE script for a fresh database,
  • generating an incremental migration script by comparing the model against the metadata of an existing database,
  • validating that a database is still in sync with the model, and
  • running arbitrary SQL scripts against the configured backend(s).

All goals derive the schema from the same model and emit backend-specific SQL through Tentackle's org.tentackle.sql.Backend abstraction, so the very same model can be materialized for PostgreSQL, MySQL/MariaDB, Oracle, H2, and the other supported backends without changing a single model file.

The goals share the prefix tentackle-sql, so they can be invoked directly from the command line, e.g. mvn tentackle-sql:create.

Prerequisites

The plugin requires the same JDK and Maven versions as the rest of the framework (JDK >= 25, Maven >= 3.9.0). The migrate, validate and run goals connect to a live database and therefore need a reachable backend and the matching JDBC driver on the plugin's classpath (add it to the plugin's <dependencies>).

Where the Model Comes From

None of the goals parse Java sources directly. They read the model — the entity definitions that the wurbelizer extracts from the PDO interfaces during code generation and writes to ${project.build.directory}/wurbel/model (one file per entity). This is the default value of the modelDir parameter, so in a normal Tentackle build the SQL plugin just picks up what the wurbelizer already produced earlier in the same build. You can point it elsewhere with modelDir or override the selection entirely with explicit filesets.

Because all goals are aggregators (they operate on the whole reactor at once), they are typically run on the root project rather than per module.

Overview of Goals

Goal Aggregator Connects to DB Purpose
create yes no Generate a CREATE TABLE script (plus indexes, foreign keys, schemas, sequences) for the whole model.
migrate yes yes Compare the model against the live database metadata and generate an incremental migration script.
validate yes yes Like migrate with validate=true: fail the build if the database differs from the model.
run yes yes Execute an arbitrary SQL script against the configured backend(s).
help — no Print plugin usage information (generated by the maven-plugin-plugin).

The generated scripts are written per backend to ${project.build.directory}/sql/<backend-name>/, e.g. target/sql/postgres/createmodel.sql.

Common Configuration

Like all Tentackle mojos, the goals inherit the common parameters verbosity, skip, charset and jdkToolchain (see the tentackle-maven-plugin docs). On top of that, AbstractSqlMojo adds the parameters shared by all SQL goals.

Model selection

  • modelDir (tentackle.modelDir): directory holding the model files. Defaults to ${project.build.directory}/wurbel/model. Ignored if filesets is given.
  • filesets: explicit file sets to process instead of modelDir.
  • modelName (tentackle.modelName): the model to use, for the rare case that a project defines more than one. Defaults to the standard model.
  • modelDefaults (tentackle.modelDefaults): model defaults applied to every entity (same syntax as the [defaults ...] block in a model file).
  • entityAliases (tentackle.entityAliases): entity alias definitions.
  • mapSchemas (tentackle.mapSchemas): map schema-qualified table names to flat table names, for backends that do not support schemas.

Backend selection

The set of backends to generate SQL for can be specified in three ways, which may be combined. Later definitions override earlier ones for the same backend name:

  1. By name via backendNames — a comma-separated list of backend names. The special values all (all non-deprecated backends), none and deprecated are recognized. This is the usual choice for the create goal, which does not need a connection.
  2. From property files via backendProperties — file sets of EncryptedProperties files that describe a backend including its JDBC URL and credentials. If no <directory> is given, the project's resource directories are scanned. These backends are connectable.
  3. Inline in the pom via backends — a list of <backend> elements (see below). Also connectable.

A connectable backend is required for migrate, validate and run; create works with name-only backends because it never touches a database.

<backends>
  <backend>
    <url>jdbc:postgresql://localhost/muz</url>
    <user>muz</user>
    <password>secret</password>
    <schemaNames>muz, tx</schemaNames>   <!-- or a <schemas> list of <schema> elements -->
  </backend>
</backends>

Instead of an inline <user>/<password> you can reference a <serverId> from settings.xml; the plugin then reads the (optionally encrypted) credentials from the maven settings. The <minVersion> element is used for migration-hint version filtering (see below).

Name checks

  • checkReservedTables (default true): assert that no model table reuses a name reserved by the Tentackle framework.
  • checkBackendSpecificNames (default true): assert that column, schema, table, and index names do not collide with reserved words of the configured backends.
  • alienTables: a list of regular expressions matching tables that exist in the database but do not belong to the model (without schema prefix). They are excluded from the "unexpected tables" reporting and must not be claimed by a model entity.

Diagnostics and dumps

These parameters help inspect what the plugin understood from the model — useful for debugging and for packaging a model so downstream projects can build on it:

  • dumpDir: dump every parsed entity (plus _aliases_.txt, _tables_.txt, _classid_.txt index files). dumpCompact, dumpAsComment, dumpVariables, dumpColumnGap and dumpAnnotationsAsOptions control the formatting.
  • dumpModelSource: dump the original model source instead of the parsed entity.
  • dumpModelIndex: also write an index file listing the dumped files — combined with dumpModelSource this produces a META-INF/MODEL-INDEX.LIST so the model can be loaded from a jar (see prependModelFromClassPath / loadModelFromClassPath).
  • bundleDir: write *.properties bundles containing the attribute- and relation comments, ready to copy into the project for auto table configuration or default editors.

The create Goal

Generates a script that creates the whole schema from scratch. For every table-providing entity it emits, in order: the CREATE SCHEMA (unless mapSchemas or createSchemas=false), the CREATE TABLE, its indexes, and finally the foreign-key constraints for the generated entities.

mvn tentackle-sql:create -DbackendNames=postgres

The default output file is createmodel.sql (tentackle.createSqlFile). Important parameters:

  • createSchemas (default true): emit CREATE SCHEMA statements for schema-qualified tables. If a backend does not support schemas, set mapSchemas instead.
  • sequences: a list of <sequence> elements (name, comment, start, increment) to create. Backends that do not support sequences get a commented-out note instead.
  • prependFilesets / appendFilesets: file sets whose content is copied verbatim to the beginning resp. end of the generated script (e.g., for grants or seed data).
  • prependModelFromClassPath / appendModelFromClassPath: also generate the tables for a model found on the classpath, e.g., the Tentackle framework's own tables. Requires the dependency to ship its model resources (built with dumpModelSource + dumpModelIndex).

The migrate Goal

Connects to each connectable backend, reads its metadata via JDBC, compares it table by table against the model, and generates the SQL needed to bring the database in line: adding/altering/dropping columns and indexes, adjusting foreign keys, and renaming or dropping tables where migration hints say so. If nothing differs, the script just records that no migration is necessary.

mvn tentackle-sql:migrate

The default output file is migratemodel.sql (tentackle.migrateSqlFile). Important parameters:

  • validate: fail the build if any migration would be required (see the validate goal).
  • failOnUnexpectedTables (default false): fail if the database contains user tables that are neither in the model nor declared as alienTables. When false, such tables are only listed as a comment with the suggestion to add them to <alienTables> or remove them.
  • loadModelFromClassPath: migrate the model found on the classpath instead of the local model files (e.g., to migrate only the framework's tables). When set, the model files in the file sets are ignored; usually combined with checkReservedTables=false.
  • useDropIfExists: use DROP ... IF EXISTS for dropping indexes, constraints, and columns where the backend supports it (a warning is logged otherwise).
  • split: write one file per migrated table into numbered sub-folders instead of a single script — handy when the migrations are post-processed by shell scripts. before all/after all and the unconditional always before/always after hints get their own folders (0… and the highest number).
  • asTemplate (tentackle.migrateSqlAsTemplate): treat the generated migration as a FreeMarker template. For each entity a boolean template variable <entity-name> is set (TRUE if it needs migration), so the final SQL can be tailored per entity.

Migration hints

The automatic migrator handles structural changes that can be derived mechanically, but some changes need human guidance — renaming a table or column (which otherwise looks like a drop plus an add), ordering migrations that depend on each other, choosing between alternative automatic migrations, or injecting handwritten SQL. This is what migration hints are for.

Hints are plain text files supplied per backend via the <migrationHints> file sets inside a <backend>. Each file is a sequence of sections; a section starts at the beginning of a line with a keyword immediately followed by a colon and extends until the next keyword:

-- rename the customer table and one of its columns, then add hand-written SQL
rename kunde: customer
rename customer#kdnr: customer_no

before customer:
ALTER TABLE customer ADD COLUMN migrated_at timestamp;

depend invoice: customer

Recognized keywords:

Keyword Effect
always before / always after SQL emitted unconditionally before/after everything else.
before all / after all SQL emitted before/after everything else, only if something changed.
before <table> / after <table> SQL emitted before/after the migration of that table (if changed).
migrate <table> Hand-written SQL that replaces the automatic migration of that table (if changed).
migrate <table>#<col>[/<newcol>] Hand-written SQL for migrating a specific column (if changed).
rename <table>: <newtable> Rename a table (same schema) when the old name exists in the DB and the new one is in the model.
rename <table>#<col>: <newcol> Rename a column under the same condition.
drop <table>: <comment> Generate a DROP TABLE for a table that exists but is no longer in the model.
hint <table>: <regex> Select one of the alternative automatic migrations the table migrator commented out.
depend <table>: t1, t2, … Delay the migration of <table> until the listed tables are migrated.
-- A comment describing the hint; not copied into the generated SQL.

Multiple sections for the same table/keyword are concatenated. Custom validators discovered via CustomMigrationValidator are run against each generated migration and can fail the build.

Version filtering of hints

Hint files can be organized in version-named sub-directories so that only the hints newer than the database's current version are applied. A backend's <minVersion> provides that current version and may be:

  1. a fixed version string such as 3.1.0,
  2. an SQL SELECT returning the version from the database, or
  3. the fully-qualified class name of a DatabaseVersionLoader implementation (which must be on the plugin classpath).

The default SemanticVersionFileFilter interprets the hint directory name as a semantic version (major.minor.hotfix, dots or underscores, optional -SNAPSHOT/-M1 suffix) and applies the hint only if its version is greater than the database version. Directories whose name does not start with a digit are treated as permanent (always applied). The filter is pluggable via the VersionFileFilter service.

The validate Goal

A thin wrapper around migrate that forces validate=true: it generates the migration script as usual but fails the build (database meta data differs from object model) as soon as any migration would be necessary. Use it in CI to guarantee that a deployed database matches the committed model.

mvn tentackle-sql:validate

It accepts the same parameters as migrate.

The run Goal

Executes a single SQL script against every configured connectable backend, using the backend's script runner. It inherits the same backend configuration as migrate, which is convenient because the target database is usually the same.

mvn tentackle-sql:run -Dscript=src/sql/myscript.sql
  • script (script, required): the SQL file to execute.

How It Fits Into the Build

For a typical Tentackle application, the database tooling builds on the output of the wurbelizer:

  1. The wurbelizer extracts the entity model from the PDO interfaces and writes it to target/wurbel/model.
  2. tentackle-sql:create turns that model into the initial schema script — run once to bootstrap a database, or wired into a profile that initializes the test database.
  3. As the model evolves, tentackle-sql:migrate (guided by migration hints) produces the incremental scripts that bring existing databases forward, and tentackle-sql:validate guards that databases stay in sync.

Further Reading