When the Dialect IS the API: Reading Army and jOOQ Side by Side, Through PostgreSQL Source

Army (the subject of this article): https://github.com/PillArmy/army
jOOQ (the comparison target): https://github.com/jOOQ/jOOQ

Versions & methodology: Army at 0.6.8-SNAPSHOT (Java 25); jOOQ from a local source checkout at 3.22.0-SNAPSHOT (commit 41e84edc85, 2026-06-25, git describe = version-3.21.0.RC1-140-g41e84edc85, root pom <java.version>25</java.version>). Every Army code block below is a verbatim excerpt (file path + line numbers given). The jOOQ open-source checkout contains no tests and no example modules (see §7.3), so jOOQ code appears in two flavors: verbatim quotes of source signatures/javadoc (cited) and hand-written sketches against verified API signatures (explicitly marked). Every claim is reproducible from both codebases unless stated otherwise. Data collected 2026-09-20.

0. TL;DR

Both projects let you write SQL in Java, type-safely — with opposite philosophies:

A delightful coincidence in scale: jOOQ's single DSL.java is 39,746 lines — almost exactly the size of Army's entire army-postgre module: 39,742 lines. One file holds the API for every dialect; one module holds the API for one dialect. That's where this article begins.

1. The Contenders

Line counts measured with find + wc (2026-09-20); link counts via grep -rho postgresql.org | wc -l. Neither number says who is "better" — they show two styles: Army stitches the PostgreSQL manual into its Javadoc; jOOQ weaves dialect knowledge into its renderer.

2. Two Worldviews: Dialect-as-API vs Dialect-as-Render-Parameter

2.1 Army: Postgres.java, one door per dialect

Every Army database gets its own entry class. PostgreSQL's is Postgres.java with 18 static factories (verified one by one):

singleInsert()   query()            batchQuery()       subQuery()
scalarSubQuery() singleUpdate()     batchSingleUpdate() singleDelete()
batchSingleDelete() valuesStmt()    subValues()        declareStmt()
closeCursor(String) closeAllCursor() singleMerge()     setStmt()
show(String)     showAll()

Two observations:

1. No multiUpdate()/multiDelete() — grep for multiUpdate|multiDelete|batchMulti across army-postgre returns zero hits. Multi-table UPDATE/DELETE is a MySQL-module-only API; on the PG side the method simply does not exist. Nonexistence = unsupported = the compiler blocks it on the database's behalf.
2. declareStmt() is PG-exclusive — server-side cursors have no counterpart on the MySQL entry class. Dialect capability shows up directly as "which factories exist".

2.2 jOOQ: one DSL plus one enum switch

All jOOQ dialects share a single DSL facade (org/jooq/impl/DSL.java, 39,746 lines). The dialect is a SQLDialect enum plus render-time branches. In the open-source checkout there is exactly one PostgreSQL constant — the family (SQLDialect.java:425-435, verbatim):

/**
 * The PostgreSQL dialect family.
 * <p>
 * This family behaves like the versioned dialect {@link #POSTGRES_18}.
 ...
 */
POSTGRES("Postgres", false, true, new RequiredVersion(18, null, null), SQLDialectCategory.POSTGRES),

Its class javadoc (SQLDialect.java:70-74) is blunt: "The open source jOOQ distributions only support the dialect family, which corresponds to the latest supported dialect version of the commercial distribution." — the OSS edition ships exactly one PostgreSQL flavor: "latest" (18). Versioned dialects (POSTGRES_9_3…POSTGRES_14 etc.) are a commercial-edition capability (in this checkout those names appear only in javadoc prose, not as enum constants).

2.3 Three corollaries of the philosophy gap

Corollary 1: when errors surface. Army expresses "this dialect doesn't support it" as "this method doesn't exist" — the error stops at compile time. jOOQ expresses it as a render branch: the same statement behaves differently under a different SQLDialect. jOOQ pays with emulation; e.g., ILIKE is rewritten as LOWER(x) LIKE LOWER(y) on dialects that lack it (Like.java:193-198, verbatim):

// [#1423] [#9889] PostgreSQL and H2 support ILIKE natively. Other dialects
// need to emulate this as LOWER(lhs) LIKE LOWER(rhs)
switch (op) {
    case LIKE_IGNORE_CASE:
    case NOT_LIKE_IGNORE_CASE:
        if (NO_SUPPORT_ILIKE.contains(ctx.dialect())) {

On the Army side, ILIKE appears only in the PG keyword table (_PostgreDialectUtils.java:253: keywords.add("ILIKE")) — where there is nothing to emulate, there is no emulation; where the database is native, the SQL goes native.

Corollary 2: portability is paid for differently. With jOOQ one statement can run its unit tests on H2 and its production on PostgreSQL (emulation bridges the gap). With Army, switching from SQLs.query() to Postgres.query() locks the dialect — in exchange for determinism about target-database behavior.

Corollary 3: the shape of documentation. Army anchors 1,243 official-doc links into its Javadoc; jOOQ encodes the same knowledge as dialect-branch code comments. The first favors learners; the second favors maintaining 660k lines.

3. Type Systems: Two Different PG Dictionaries

3.1 Army: PgType — broad and literal

The full PG type set lives in PgType.java (in army-core). Its dialect slice, verbatim:

JSON("JSON", ArmyType.JSON, String.class),
JSONB("JSONB", ArmyType.JSONB, String.class),
JSONPATH("JSONPATH", ArmyType.DIALECT_TYPE, String.class),
MACADDR("MACADDR", ArmyType.DIALECT_TYPE, String.class),
MACADDR8("MACADDR8", ArmyType.DIALECT_TYPE, String.class),
INET("INET", ArmyType.DIALECT_TYPE, String.class),
CIDR("CIDR", ArmyType.DIALECT_TYPE, String.class),

INT4RANGE("INT4RANGE", ArmyType.RANGE, String.class),
INT8RANGE("INT8RANGE", ArmyType.RANGE, String.class),
NUMRANGE("NUMRANGE", ArmyType.RANGE, String.class),
TSRANGE("TSRANGE", ArmyType.RANGE, String.class),
TSTZRANGE("TSTZRANGE", ArmyType.RANGE, String.class),
DATERANGE("DATERANGE", ArmyType.RANGE, String.class),

INT4MULTIRANGE("INT4MULTIRANGE", ArmyType.RANGE, String.class),
... (6 multiranges total, L112-117)

(From PgType.java:95-117)

Also present: MONEY, PG_LSN, PG_SNAPSHOT, TSVECTOR/TSQUERY, the geometric family (POINT/CIRCLE/PATH/BOX/LINE/LSEG/POLYGON, all mapped to String), RECORD (→ SqlRecord), REF_CURSOR, ACLITEM.

Arrays are Army's showcase: from ACLITEM_ARRAY to RECORD_ARRAY there are 54 *_ARRAY constants, built by a constructor referencing the element type (PgType.java:126-135):

ACLITEM_ARRAY(ACLITEM),
BOOLEAN_ARRAY(BOOLEAN),
...
TIMESTAMPTZ_ARRAY(TIMESTAMPTZ),
JSONB_ARRAY(JSONB),
INT4RANGE_ARRAY(INT4RANGE),
INT4MULTIRANGE_ARRAY(INT4MULTIRANGE),

Even range/multirange get their own arrays. The parser counterpart is PostgreArrays in army-postgre (parseArrayText handles '{{1 , 2} , { 3, 4}}' and custom-subscript forms like '[-1:0][-2:-1]=...' — see the test comments in PostgreArraysUnitTests.java:38-67).

3.2 jOOQ: an internal core catalog plus a strongly-typed extensions module

jOOQ's PG types come in two layers:

• Internal core catalog: org/jooq/impl/SQLDataTypes.java hosts a package-private PostgresDataType (L2606-2608) covering system types like INT2/INT4/INT8/FLOAT8/BOOL/TEXT/TIMESTAMPTZ/BYTEA/INTERVAL/OIDVECTOR — internal implementation, no public PostgreSQLDataType class.
• The open-source jOOQ-postgres-extensions module (63 files / 4,790 LOC): the real "PG dictionary" — a Range<T> interface with discrete-range base classes, plus one class per type:

Each type ships a Converter/Binding (including *ArrayBinding variants).

3.3 Range types: String vs Range<T> — the biggest fork in this comparison

The same tstzrange column:

• Army maps it to String at the PgType level (TSTZRANGE(..., String.class)). Pro: zero magic, WYSIWYG. Con: range arithmetic happens at the string/SQL-expression level, by hand. But that is only the fallback layer — object semantics are one optional module away: attach army-guava and you get Guava Range<T> directly (§3.7).
• jOOQ maps it to an OffsetDateTimeRange object with a Range<T> interface (contains, discrete stepping, etc.) and a dedicated Binding. Pro: business code manipulates objects. Con: one more abstraction and binding protocol.

A direct clash of design values: Army picks "literal + broad" (54 arrays, RECORD, REF_CURSOR all present); jOOQ picks "deeply typed" (Range family, Inet prefixes, Hstore). It depends on the team — and let me preempt a misconception you get from reading PgType alone: although Army's range/multirange map to String at the SQL-catalog level, there is a strongly-typed channel at the MappingType layer (the optional army-guava module maps them to Guava Range<T>/RangeSet<T>, detailed in §3.7); the jOOQ open-source extensions still have no multirange classes (none exist in the module).

3.4 pgvector: a correction — Army has it; the jOOQ OSS edition does not

The first draft of this article claimed "pgvector is absent on both sides" — a wrong conclusion reached by grepping only the PgType constants. Corrected here, with apologies: Army's PgType enum genuinely has no VECTOR constant (vector is an extension type, not a built-in SQL type), but pgvector is fully supported at three layers — MappingType, the expression DSL, and the Spring AI integration; the jOOQ open-source checkout genuinely lacks it. Evidence and comparison in §3.9.

3.5 Deep-dive map: array / range / composite / vector across four dimensions

The next four sections compare each type family with the same framework: mapping (how Java types correspond to PG types), design (why it is modeled that way), implementation (what actually happens on the JDBC wire), and usage (what user code looks like).

Start with one counter-intuitive finding that runs through all four: neither framework uses the JDBC 4 java.sql.Array / java.sql.Struct channels. A grep for createArrayOf/createStruct across the whole army-jdbc module returns zero hits; jOOQ core does not call them either (matches only in the JDBC wrappers DefaultConnection/MockConnection). The two projects independently made the same choice — PostgreSQL's text input/output protocol (arrays '{1,2,3}', ranges '[1,5)', composites '(a,b)', vectors '[1,2,3]' plus a server-side cast), letting PostgreSQL parse the complex types itself. jOOQ even writes the reason into its code (§3.6). So the comparison is not about which protocol — it is about what kind of type system each side built on top of it.

3.6 ARRAY

Mapping.

• Army: a two-tier "prefab catalog + auto-inference" system. The SQL-catalog tier is PgType's 54 *_ARRAY constants (§3.1); the MappingType tier is the standalone army-array module — 43 named array types (IntegerArrayType, TextArrayType, UUIDArrayType, JsonbArrayType, BitSetArrayType, IntervalArrayType… and even CompositeArrayType, VectorArrayType, MappingTypeArrayType). Primitive/primitive-wrapper array fields are inferred automatically (PgMappingHandler.java:134-173 returns XxxArrayType.UNLIMITED per element type); other arrays fall back to StringArrayType (L179-180: type.isArray() → StringArrayType), or are declared explicitly with @Mapping.
• jOOQ: one "generic lifting" mechanism. Any DataType<T> becomes DataType<T[]> via getArrayDataType() (ArrayDataType.java:66-81; the constructor takes elementType.getBinding().arrayBinding() and converts elements reflectively with Array.newInstance); DDL rendering emits the suffix "[]" for the POSTGRES family (ArrayDataType.java:227-244). Arrays of Range/Inet/Hstore etc. are covered by the extensions' *ArrayBinding classes.

Design. The catalog gives every PG array flavor (multi-dimensional, fixed bit, interval) a named home with docs; the generic mechanism is orthogonal — a new element type gains arrays automatically, no 44th class needed.

Implementation. Army's text codec is PostgreArrays.java in army-core (note: core, not the dialect module — the text protocol is shared knowledge). The default serializer/deserializer is comma-delimited (L45-53); element decoding follows the PG manual's array I/O syntax (the L68-72 javadoc links Array Input and Output Syntax — double-quote wrapping and backslash escaping). jOOQ's bind site is DefaultBinding.java:1610-1615; the POSTGRES branch, verbatim:

case POSTGRES:
    ctx.statement().setObject(ctx.index(), toPGArrayString(value), Types.OTHER);

Inline (non-prepared) contexts render cast(inline(PostgresUtils.toPGArrayString(value)), arrayType) (DefaultBinding.java:1482-1492); toPGArrayString handles {…} wrapping, quoting non-numbers, escaping, and multi-dim arrays (PostgresUtils.java:546-583). On read, jOOQ calls rs.getArray() and binds element by element; Army's *ArrayTypes restore from text via PostgreArrays.

Usage. Army side — verbatim from the real entity PostgreTypes.java:

// L370-376: 1-D auto-inferred, 2-D declared explicitly
//@Mapping("io.army.mapping.array.IntegerArrayType")
@Column(comment = "int array type")
public int[] intArray;

@Mapping("io.army.mapping.array.IntegerArrayType")
@Column(comment = "int 2d array type")
public int[][] int2dArray;

The same mechanism covers composite arrays and vector arrays (L460-466: @Mapping("...CompositeArrayType") public ProductInfo[] productInfoArray;, @Mapping("...VectorArrayType") public float[][] vectorArray;).

jOOQ side (no tests in the OSS checkout; this quotes the DSL javadoc verbatim, DSL.java:32579-32584):

The PostgreSQL array(select) function. …
Example: {1, 2, 3} = array(select 1 union select 2 union select 3)

The unnest(Object[]) javadoc (DSL.java:13502-13513) states: rendered as UNNEST on Postgres, TABLE on H2, and "emulated using several UNION ALL connected subqueries" elsewhere — one function, three renderings. jOOQ's philosophy in an array-shaped specimen.

3.7 RANGE / MULTIRANGE

Mapping.

• jOOQ: the extensions module ships a complete set of dependency-free value types. The Range<T> interface has exactly five methods (Range.java:47-75, verbatim): isEmpty(), lower(), lowerIncluding(), upper(), upperIncluding(); six concrete types IntegerRange/LongRange/BigDecimalRange/LocalDateRange/OffsetDateTimeRange/...; no multirange classes.
• Army: it does not invent Range objects — the strongly-typed mapping lives in the optional army-guava module: GuavaRangeType.java:47 — public abstract class GuavaRangeType ... implements MappingType.SqlRange, UnaryGenericsMapping — mapping a column to Guava's Range<T>; multirange maps via GuavaRangeSetType to Guava RangeSet<T>; arrays via GuavaRangeArrayType. Projects without Guava fall back to the PgType String mapping.

Design. Both faithfully implement PG's bound/empty-range semantics. jOOQ hard-codes the rules in AbstractRange.java:51-68: // In PostgreSQL, there is no [,] range, only (,) (null bounds forced open), isEmpty() = lowerIncluding && !upperIncluding && Objects.equals(lower, upper), and toString() renders the [1,5) text. Army reuses Guava Range's mature open/closed-interval semantics, and its deserializer knows PG empty ranges and infinite bounds (allowNothing(true)). An archaeological detail: PgRangeTypeUniteTests.java has its entire body commented out (the referenced PgRangeType/Int4Range classes no longer exist in the source) — a fossil of an attempted in-house value type; the final design choice was to reuse Guava instead of reinventing Range.

Implementation. Text protocol again. Army's range text parser is a configurable builder (GuavaRangeType.java:57-73, verbatim excerpt):

public static final RangeDeserializer PG_DESERIALIZER = RangeDeserializer.builder()
        .dataTypeLabel("PostgreSQL Range")
        .leftBoundaries(new char[]{'[', '('})
        .delim(_Constant.COMMA)
        .rightBoundaries(new char[]{']', ')'})
        .quoteChar(_Constant.DOUBLE_QUOTE)
        .backSlashEscapeOn(true)
        .quoteEscapeOn(true)
        .nullAsNull(false)
        .allowQuote(true)
        .allowNothing(true)   // unbounded
        .allowWhitespace(true)
        .build();

jOOQ's AbstractRangeConverter (L60-96): if ("empty".equals(s)) return empty();; otherwise split on ,, read the first char for [/(, strip double quotes, blank bound → null; the Binding extends AbstractPostgresVarcharBinding (setString/getString), and IntegerRangeBinding.castType() returns "int4range" (IntegerRangeBinding.java:50-67).

Usage. Army side — PostgreTypes.java:192-247, verbatim excerpt:

@Column(comment = "int4range type")
public Range<Integer> int4RangeGuava;

@Mapping("io.army.mapping.guava.GuavaRangeType")
...
public Range<OffsetDateTime> tstzrange;
...
@Mapping("io.army.mapping.guava.array.GuavaRangeArrayType")
public Range<OffsetDateTime>[] tstzrangeArray;
...
@Mapping("io.army.mapping.guava.GuavaRangeSetType")
public RangeSet<Long> int8multirange;

On the jOOQ side you attach IntegerRangeBinding to a column in the codegen configuration and the generated field type becomes Field<IntegerRange> (mechanism description, not a code quote — no examples exist in the OSS checkout).

Army also has a DDL ace: PostgreDdlParser.java:365-378, createType() emits CREATE TYPE/DOMAIN for the four user-defined categories SqlComposite / SqlEnum / SqlDefinedRange / SqlDomain; and the database's "ranges can't be altered" limit is encoded as a deterministic error message (L391-392: "PostgreSQL don't support alter range type").

3.8 COMPOSITE (CREATE TYPE … AS)

Mapping.

• Army (Java-first): CompositeType.java:47-62 maps a top-level POJO annotated with @DefinedType(category = COMPOSITE, fieldOrder = {...}) to a PG composite type; from() validates annotation completeness and class placement at build time. The real example ProductInfo.java:14-41 — note composite fields can embed arrays and another composite type:

@MappedSuperclass
@DefinedType(name = "PRODUCT_INFO",
        fieldOrder = {"productId", "productName", "price", "available", "releaseDate", "intArray", "textArray", "managerInfo"})
public class ProductInfo implements FieldAccessPojo {
    @Column public Long productId;
    ...
    @Column public int[] intArray;
    @Column public String[] textArray;
    @Column public ManagerInfo managerInfo;   // nested composite

The annotation processor recognizes FieldType.COMPOSITE and generates the metamodel (MirrorFieldTypeParser, CompositeSourceCodeGen in army-annotation).

• jOOQ (database-first): composites are reverse-generated UDTs — UDT.java:53-62: "Instances of this type cannot be created directly. They are available from generated code.", plus the ROW-construction expression Field<R> construct(Field<?>... args) (L99-104, @Support({DUCKDB, POSTGRES, YUGABYTEDB})); the runtime carrier is UDTRecord/UDTRecordImpl, the data type is UDTDataType holding the UDT reference (UDTDataType.java:50-63). Reverse engineering is done by jOOQ-meta's PostgresUDTDefinition (querying information_schema.ATTRIBUTES/pg_catalog) and jOOQ-codegen's JavaGenerator.generateUDTRecords. jOOQ also deliberately distinguishes EmbeddableRecord — "implemented purely as a code generation feature where columns in a Table should be grouped to form a synthetic UDTRecord type" (EmbeddableRecord.java:42-46): a UDT is a real database type; an embeddable is pure client-side column grouping. The two are not conflated.

Implementation. Text protocol again, arrived at independently. Army uses a dedicated PG composite text parser (CompositeType.java:65-80): ( ) boundaries, comma delimiters, double-quote escaping, nothing for null, whitespace kept inside field values. jOOQ's DefaultRecordBinding.set0 does setString(..., PostgresUtils.toPGString(value)) for the PG family (DefaultBinding.java:4629-4633), with a UDT-name cast when inlined (pgRenderRecordCast detecting UDTDataType, L4596-4606).

Metadata & DDL. On the reverse side Army fetches ENUM/RANGE/DOMAIN/COMPOSITE metadata in one big SQL (PostgreParser.java:112-164: CASE(t.typcategory) WHEN 'C' THEN 'COMPOSITE' WHEN 'E' THEN 'ENUM' WHEN 'R' THEN 'RANGE' ..., composite attributes coming from a pg_attribute LATERAL subquery); on the forward side it can not only CREATE TYPE but modifyType() also emits ALTER TYPE ... ADD/DROP/ALTER ATTRIBUTE diffs for composites (the compositeDropFieldList/compositeNewFieldList/compositeModifyFieldList at PostgreDdlParser.java:983/1007/1044) — field-level schema evolution of composite types is inside the DDL sync.

Usage. Army side, PostgreTypes.java:347-348,460-462: public ProductInfo productInfo;, public ManagerInfo managerInfo;, public ProductInfo[] productInfoArray; — inside the domain object they are plain POJO fields, stored and fetched like ordinary columns.

3.9 VECTOR (pgvector)

Army: a built-in citizen, three layers complete.

1. Mapping: VectorType.java:41-79, verbatim excerpt — one type serves both pgvector and MySQL 9:

public final class VectorType extends _ArmyNoInjectionType
        implements MappingType.SqlUserDefined, MappingType.SqlVector {

    public Class<?> javaType() { return float[].class; }

    public DataType map(final ServerMeta meta) throws UnsupportedDialectException {
        switch (meta.serverDatabase()) {
            case PostgreSQL: dataType = obtainDataType(); break;   // CustomType "VECTOR"
            case MySQL:
                if (meta.meetsMinimum(9, 0, 0)) { dataType = MySQLType.VECTOR; break; }
            default: throw mapError(this, meta);
        }
        return dataType;
    }

The array form is VectorArrayType in army-array (arrayTypeOfThis() → float[][]).

1. Query operators: TypedField.java:243-277 defines six pgvector distance operators, all @Support({PostgreSQL}): spaceL1Distance (<+> L1), spaceL2Distance (<-> L2), spaceCosineDistance (<=> cosine), spaceHammingDistance (<~> binary-vector Hamming), spaceJaccardDistance (<%> Jaccard), spaceNegDot (<#> negative inner product); implementation in OperationTypedField.
2. Ecosystem: the army-spring-ai-vector-store module's ArmyVectorStore.java:64 class javadoc says "Built-in pgvector integration for similarity search", with a companion PgVectorFilterExpressionConverter. Real entity field: public float[][] vectorArray; at PostgreTypes.java:464-466.

jOOQ: absent in OSS; do-it-yourself via the Binding SPI. Repo-wide (all modules) greps for pgvector, halfvec, <=>, cosine_distance return zero hits; the jOOQ-postgres-extensions supported-types list (cidr/citext/daterange/hstore/inet/int4range/int8range/ltree/numrange/tsrange/tstzrange) contains no vector (official docs: https://www.jooq.org/doc/latest/manual/code-generation/codegen-extensions/codegen-extension-postgres/ ); the community approach is a hand-written Binding with a ::vector cast in SQL (example: https://gist.github.com/yahorbarkouski/bcfbf2bf1b10ff7757f2c629eab33a46 ). To be precise: this conclusion is based on source plus the official supported-types page; a commercial-roadmap item may exist — but in the open-source checkout under comparison, it is genuinely absent.

Commentary: this contrast shows the two temperaments best — Army treats the post-2024 AI infrastructure as a dialect first-class citizen (type + operators + Spring AI, one stop), while jOOQ leaves the extension point and lets users plug in via the Binding SPI. The former is batteries-included for AI scenarios; the latter refuses to compromise on "stable core, externalized extensions".

3.10 Cross-family summary

One-line verdict: jOOQ's type extension is "one orthogonal core + external extension modules"; Army's is "an explicit catalog + optional ecosystem modules (guava/array/spring-ai)". Fewer abstractions versus more out-of-the-box types — the same worldview as the statement layer's "dialect-as-parameter vs dialect-as-API" divide, continued into the type layer.

4. Statement Capabilities, Item by Item

4.1 ON CONFLICT: different strengths

Army's conflict target goes down to collation and operator classes (PostgreInsert.java:109-160: _ConflictCollateSpec.collation(...), _ConflictOpClassSpec.space(String operatorClass), _ConflictTargetOptionSpec.onConstraint(String), plus indexed columns and a WHERE index predicate inside parens(...)); jOOQ's onConflictOnConstraint accepts typed UniqueKey<R>/Constraint/Name directly (InsertOnDuplicateStep.java:114-161) — stronger when combined with codegen metadata. On the action side the two align (jOOQ signature verbatim):

// InsertOnConflictDoUpdateStep.java:90-99
@NotNull @CheckReturnValue
@Support({ CUBRID, DERBY, DUCKDB, FIREBIRD, H2, HSQLDB, MARIADB, MYSQL, POSTGRES, SQLITE, YUGABYTEDB })
InsertOnDuplicateSetStep<R> doUpdate();
...
InsertReturningStep<R> doNothing();

4.2 MERGE: an entrée vs an emulator

MERGE, introduced in PG 15, is a first-class factory in Army (singleMerge(), javadoc linking the official doc), and MergeTests.java contains real MERGE ... RETURNING tests (L59, L111). jOOQ has MERGE APIs too — but the more interesting part is its cameo on PG: when an UPSERT conflict involves multiple unique keys and there's no RETURNING, it rewrites into MERGE as an emulation (InsertQueryImpl.java:479-494, excerpt):

case POSTGRES:
    ...
    if ((ctx.dialect().supports(POSTGRES) || ctx.dialect().supports(DUCKDB))
        && onConstraint == null && onConflict == null
        && returning.isEmpty() && table().getKeys().size() > 1) {
        acceptMerge(ctx);
    }

Same statement: one is "the user orders it off the menu", the other is "a stage prop inside the renderer" — the two philosophies in miniature.

4.3 Cursors: the deepest block of Army's PG dialect

Army ships a complete server-side cursor statement family: declareStmt() (the PostgreCursor chain) → execute to ResultStates → on SyncStmtCursor: next()/fetchOneObject(Direction...)/move(Direction.LAST)/fetch(Direction.FORWARD_ALL...), plus closeCursor(name)/closeAllCursor(). The declareStmt() javadoc embeds a complete runnable example (Postgres.java:280-317, verbatim excerpt):

stmt = Postgres.declareStmt()
        .declare("my_china_region_cursor").cursor()
        .forSpace()
        .select("c", PERIOD, ChinaRegion_.T)
        .from(ChinaRegion_.T, AS, "c")
        .where(ChinaRegion_.id.in(SQLs::rowParam, extractRegionIdList(regionList)))
        .orderBy(ChinaRegion_.id)
        .limit(SQLs::literal, regionList.size())
        .asQuery()
        .asCommand();
states = session.updateAsStates(stmt);
try (SyncStmtCursor cursor = states.nonNullOf(SyncStmtCursor.SYNC_STMT_CURSOR)) {
    while ((region = cursor.next(ChinaRegion_.CLASS)) != null) { ... }
    cursor.move(Direction.LAST);
    cursor.fetch(Direction.FORWARD_ALL, ChinaRegion_.CLASS, ResultStates.IGNORE_STATES)
            .forEach(System.out::println);

jOOQ's counterpart is client-side streaming: no DECLARE CURSOR DSL (0 grep hits repo-wide); the Cursor interface describes itself as "Cursors allow for lazy, sequential access to an underlying JDBC ResultSet." (Cursor.java:59-63), entered via ResultQuery.fetchLazy() (ResultQuery.java:438), with its own javadoc example (Cursor.java:120, verbatim):

 * try (Cursor<TRecord> cursor = ctx.select(T.A, T.B).from(T).fetchLazy()) {

Fairness first: this is not better-vs-worse. jOOQ's fetchSize streaming is sufficient for most business code and portable across dialects; but "explicit DECLARE / named cursors / bidirectional FETCH/MOVE" can only be expressed in Army's API.

5. Head-to-Head: Four Same-Problem Use Cases

Round 1: batch UPSERT + RETURNING (both flexing)

Army — verbatim from InsertTests.java:274-286 (note the row alias as("c") and the existence comment on ignoreReturnIds() — this is real test code):

final ReturningInsert stmt;
stmt = Postgres.singleInsert()
        .ignoreReturnIds()  // required ,because exists doNothing
        .insertInto(ChinaRegion_.T).as("c")
        .parens(s -> s.space(ChinaRegion_.name, ChinaRegion_.parentId)
                .comma(ChinaRegion_.regionGdp)
        )
        .defaultValue(ChinaRegion_.visible, SQLs::literal, Boolean.TRUE)
        .values(regionList)
        .onConflict()
        .doNothing()
        .returningAll()
        .asReturningInsert();

jOOQ — the open-source checkout has no tests, so this is a hand-written sketch against verified API signatures (onConflict(Field...) at InsertOnDuplicateStep.java:114, doUpdate() at :92, set(Field,T) at InsertOnDuplicateSetStep.java:117, excluded(Field) at DSL.java:19243, returningResult at InsertReturningStep.java:178):

// Sketch (hand-written), not a verbatim excerpt; every method signature verified in 3.22.0-SNAPSHOT sources
Result<Record1<Long>> r = ctx.insertInto(REGION)
        .columns(REGION.NAME, REGION.PARENT_ID, REGION.GDP)
        .values("South China", 0, new BigDecimal("78000"))
        .onConflict(REGION.ID)
        .doUpdate().set(REGION.GDP, excluded(REGION.GDP))
        .returningResult(REGION.ID)
        .fetch();

Impressions: equal information density. Army's .as("c") row alias makes the excluded semantics explicit as a reference to c (same design spirit as MySQL 8.0.19 row aliases); jOOQ's excluded() reads closer to the PG manual. Army's staged interfaces mean wrong clause order doesn't compile (e.g., trying .doUpdate() before a conflict target — no such type path exists).

Round 2: cursors (see §4.3 — a shape difference, decisively in Army's favor)

Round 3: range-typed columns

• Army: @Column metadata declares PgType.TSTZRANGE; the entity field is String; comparisons (=, &&, @>) happen in SQL expressions.
• jOOQ: the record field is OffsetDateTimeRange; the extension Binding converts between PG text and the object.

Round 4: array columns

• Army: 54 prefab *_ARRAY static types + the PostgreArrays parser (multi-dimensional and custom-subscript forms included — see §3.1 test comments).
• jOOQ: any DataType<T> upgrades to DataType<T[]> via getArrayDataType() (ArrayDataType.java:65-69) plus DSL.array()/unnest() and the extensions' *ArrayBinding. A general mechanism vs a prefab catalog — philosophy again.

6. Version Strategy: Explicit Enum vs Commercial Versioned Dialects

Army's PG version enum, verbatim (PostgreDialect.java:24-36):

POSTGRE11(11),
POSTGRE12(12),
POSTGRE13(13),
POSTGRE14(14),
POSTGRE15(15),
POSTGRE16(16),
POSTGRE18(18);

Three remarks (all reproducible from source):

1. from(ServerMeta) maps 11–16 explicitly; everything else (including 17) falls to the default branch → POSTGRE18 (L74-99) — there is genuinely no POSTGRE17 constant.
2. Version constants are referenced barely once inside army-postgre main code (PostgreUtils.DIALECT = POSTGRE16, a parse-time constant) — a contrast with army-mysql's dense version gating; the PG module leans on the static "capability absent = API absent" expression instead.
3. Version detection happens at runtime (ServerMeta.major()); a mismatched statement errors at render time rather than being quietly emulated.

jOOQ's OSS edition ships only the family constant (≙POSTGRES_18); the RequiredVersion(18,null,null) and precedes() machinery (SQLDialect.java:1438-1500) serve the commercial edition's versioned dialects. For open-source users this means: everything renders as "latest PG"; compatibility with older servers comes from emulation branches, not version switches.

7. Metamodel Direction, Doc Density, and Openness of Tests

7.1 Metamodel: Java-first vs database-first

Army's metamodel comes from an annotation processor: @Table entities → generated ChinaRegion_ static metamodel (T, FieldMeta<T> fields); type safety grows from the Java side. army-postgre also reaches the other way: PostgreParser can query PG custom-type metadata (ENUM/RANGE/DOMAIN/COMPOSITE, reading pg_range subtype/operClass — PostgreParser.java:109-194) for DDL comparison.

jOOQ is classic database-first: jOOQ-codegen + jOOQ-meta reverse-generate Records/metamodels from schema (this checkout contains the jOOQ-codegen* and jOOQ-meta* modules). Opposite directions, different fits: legacy-schema-first teams pick codegen; domain-model-first teams pick annotation processing.

7.2 Doc density: 1,243 vs 18

army-postgre main sources contain 1,243 postgresql.org links — PostgreSyntax and the function classes cite the PG manual function by function (geometry, text search, JSON, windows…). jOOQ core has 18. Not a quality verdict — a maintenance strategy: jOOQ relies on its commercial team and tests; Army nails the evidence into the source where readers can see it.

7.3 Test openness: a difference that matters to this article's methodology

All of Army's use cases are open source: army-example carries a full PG sync-session suite (session/sync/postgre/: InsertTests/UpdateTests/DeleteTests/MergeTests/CursorTests, …), mapping tests (PostgreFullType, PostgreArraysUnitTests), dialect tests (PostgreDdlTests), and criteria unit tests (criteria/postgre/statement/PostgreUnitTests); army-postgre itself has PostgreDialectUtilsTests.

jOOQ's open-source checkout contains no tests at all: no examples module at the top level, jOOQ/src has only main (repo-wide find -type d -name test: zero hits). jOOQ's PostgreSQL integration tests live in its commercial codebase. Hence the signature-level citations plus marked hand-written sketches for jOOQ in this article — not laziness, but an objective difference in open-source transparency that happens to be one of Army's quiet advantages: anyone can clone and run every one of its use cases.

8. Summary Table

9. Closing: This Comparison Is Really Army's Self-Introduction

Having used jOOQ as a mirror, here is a fair introduction to Army's PostgreSQL dialect:

• It expresses dialect capability through API existence, not emulation — everything PG offers that the module covers (RETURNING, ON CONFLICT, MERGE, DISTINCT ON, named cursors, SET/SHOW, 54 array types, 12 range/multirange, RECORD, REF_CURSOR, pgvector) has a typed place on Postgres's chains; what it doesn't cover (COPY, LISTEN/NOTIFY, multi-table UPDATE/DELETE) simply doesn't exist in the API. The compiler is its first piece of documentation.
• On server-side semantics it goes further than the jOOQ open-source edition: the full named-cursor family, conflict targets down to collation and operator classes — evidence of people who know PostgreSQL writing the API.
• It is equally honest about being young: strongly-typed ranges live in an optional Guava module rather than as a built-in value type, no version-17 constant, use cases concentrated on the sync session — concrete, checkable "not dones" you can file as issues one by one, not vague beta warnings.
• Its biggest lever is transparency: every use case is open source and runnable; every javadoc anchors the official manual. For teams willing to read source, Army's PG dialect is a mine you can assay yourself.

And jOOQ remains the respected yardstick: 660k lines, 20 years, a cross-dialect emulation machine. Choose jOOQ to buy "one API everywhere + commercial versioned dialects"; choose Army to buy "dialect capability fixed at compile time + fully open test cases." If your team lives heavily in PostgreSQL and wants database capability to live explicitly inside the Java type system, Army's army-postgre deserves an afternoon of cloning and running its tests — after all, only claims that can be verified verbatim deserve to be called an introduction.

All Army-side citations are reproducible from https://github.com/PillArmy/army; jOOQ-side citations are based on the locally checked-out open-source sources (3.22.0-SNAPSHOT). Hand-written sketches are marked as such. Corrections welcome.