vajram-sql-data-modelling

name

vajram-sql-data-modelling

description

Models SQL table schemas AND writes SELECT/INSERT queries using the vajram-sql plugin of the Krystal framework (flipkart-incubator/Krystal). Covers @Table/@ModelRoot model interfaces (columns, @PrimaryKey, @UniqueKey, @ForeignKey/@IncomingForeignKey, nullability, defaults, JSON columns, matching raw DDL) and query traits — @Selection projections, @WHERE/ColumnPredicate/SqlOrPredicate filters, @SQL @SELECT/@INSERT @Trait definitions with @LIMIT/@ORDER/@ReturnOnInsert. Use whenever the user wants to define/change a SQL table, or fetch/insert rows, look up a record, add a query, or write a repository-style method against a Krystal/Vajram data layer — even if they just say "add a table for X" or "write a query to fetch Z" without naming vajram-sql, as long as the repo uses Krystal/Vajram. Not for generic (non-Krystal) DB work, or UPDATE/DELETE (no codegen support yet).

Modeling SQL schemas and queries with vajram-sql

vajram-sql is a compile-time query-contract codegen layer inside the Krystal/Vajram framework, not a JPA-style ORM. A table is declared once as a plain Java interface (@Table), and everything else — SELECT/INSERT queries — is built as a Krystal Trait checked against that declaration. This skill covers both halves: defining/evolving @Table model interfaces (Steps 1-5) and writing queries against them (Steps 6-8).

The one thing to internalize before touching any schema code: vajram-sql never generates DDL. The @Table interface and the physical database table are two independent things that you are responsible for keeping in sync by hand. Every schema step below reflects that — whenever the Java model changes, the matching SQL changes with it, in the same edit. Queries don't have this problem — they compile down to a generated Vajram that executes real SQL, so a query that compiles is already checked against the table shape.

Step 1 — Find how the repo already does this

Before writing anything, check for precedent:

• Search the repo for existing @Table/@ModelRoot interfaces (any module) to match its column-naming, package layout (.../model/<Table>.java is the common convention seen in Krystal samples), and default-value idioms.
• Search for where raw schema DDL already lives — a schema.sql, a migrations/ directory, a Flyway/Liquibase setup, or (least ideally) DDL only inside test setup. vajram-sql has no opinion here; the surrounding repo's existing convention wins. If there's no existing convention and none is obvious, ask the user where the DDL for this table should live rather than inventing a new location.
• Check gradle/libs.versions.toml and a sibling module's build.gradle.kts for how Krystal/vajram deps are referenced (version-catalog aliases vs. project(...)) — see references/build-setup.md for why this matters.

Step 2 — Define or update the @Table interface

Read references/annotations.md for the full, source-verified annotation reference (it also documents where the plugin's own READMEs disagree with the actual source — trust that file over the upstream docs). In short:

• New table → new interface, @ModelRoot + @Table(name = "..."), extends TableModel, exactly one @PrimaryKey method, one method per column.
• New column → new abstract method on the interface. Nullable columns are Optional<T>. Pick the narrowest correct Java type since it drives how the value gets bound/read at query time — get this right the first time, since a nullable/type mismatch fails at runtime, not compile time.
• Constraint or relationship → the matching annotation from references/annotations.md. Foreign keys are the one place that touches two files: adding @ForeignKey to the child table also requires adding @IncomingForeignKey to the parent, in the same change — the codegen will reject one without the other, but only once something actually references the relationship in a query.
• Renaming a column in the DB without renaming the Java accessor → use @Column("dbName") rather than renaming the method, if the desired Java name and DB name genuinely need to differ; otherwise just rename the method plus the DDL together.
• Auto-incrementing/DB-assigned columns and timestamp defaults → @DefaultValueStrategy (see reference for the AUTO_ASSIGN_ID / CURRENT_TIMESTAMP / CUSTOM_STATIC_VALUE options and the MySQL-only-one-auto-id-column caveat).
• A column holding a nested object or list of objects → @SerdeWith(Json.class) + @JsonConfig(serializeAs = STRING), with the nested type itself being a plain Krystal Model.

Step 3 — Write or update the matching raw SQL in the same change

Since there's no DDL codegen, treat this as non-optional, not an afterthought:

• New table: write the CREATE TABLE statement column-for-column matching the interface — same names (post @Column overrides), same nullability (NOT NULL wherever the Java type isn't Optional/@Nullable), same PK/ unique/FK constraints.
• Adding a column: ALTER TABLE ... ADD COLUMN ..., matching nullability and any default.
• Adding a constraint or FK: ALTER TABLE ... ADD CONSTRAINT ... alongside the new annotation(s).
• Renaming/dropping: the corresponding ALTER TABLE ... RENAME COLUMN / DROP COLUMN, checking first whether the column participates in any existing FK/unique constraint elsewhere that also needs updating.
• Put this SQL wherever Step 1 found the repo already keeps its schema DDL. If the repo has a real migration tool (Flyway/Liquibase/etc.), add it as a new migration rather than editing a prior one in place — vajram-sql has no opinion on this, but the migration tool almost certainly does.

Step 4 — Build wiring (new module/table only)

If this is the first table model in a module, it needs the vajram-sql dependencies and annotation processors wired into build.gradle.kts — see references/build-setup.md for the exact dependency set and how to match the repo's existing dependency-declaration style (version catalog vs. project(...)). If the module already has other @Table interfaces, this is already done — skip it.

Note that a bare @Table interface with no @Selection/@WHERE/@SQL trait referencing it triggers no codegen at all. Don't be surprised that ./gradlew build produces nothing new immediately after adding just the model — that's expected, not a sign the wiring is broken.

Step 5 — Sanity-check before calling it done

• Exactly one @PrimaryKey on the interface.
• Every @ForeignKey has a matching @IncomingForeignKey on the other side.
• Every nullable-in-the-DDL column is Optional<T> (or @Nullable) in Java, and vice versa — a mismatch here is silent until a query actually hits the null case.
• The DDL you wrote/updated in Step 3 actually matches the interface, column by column.
• If you touched an existing table, check for other @Table/@Selection interfaces elsewhere in the repo that reference it (via @ForeignKey(toTable = ...) or @Selection(from = ...)) and might need a matching update.

For the fully worked example this is all drawn from (a User/Order/OrderItem schema with every annotation in play, plus its hand-written DDL), see the end of references/annotations.md.

If the task is only about the schema (no query being asked for), stop here — Steps 6-8 below are for writing SELECT/INSERT queries against a table that's already modeled.

Step 6 — Find how the repo already organizes queries

Same instinct as Step 1, applied to queries: check for an existing clause//statement/ split (the convention used in the plugin's own samples — projections and predicates in clause/, the actual @Trait interfaces in statement/) or whatever pattern the repo already has for its query-side Vajram traits, and match it rather than inventing a new one.

Step 7 — Write the query

Read references/queries.md for the full, source-verified reference (projections, predicates, operators, and worked SELECT/INSERT examples pulled directly from the plugin's real samples module). The shape of the work:

• What columns come back → a @Selection(from = Table.class) projection interface, one method per column/ alias. A method returning List<OtherSelection> (or a single one) becomes a join, riding on the @ForeignKey/ @IncomingForeignKey pair already declared on the tables (Step 2) — if that pair doesn't exist yet on the tables involved, add it first.
• Which rows (SELECT only) → a ColumnPredicate (@WHERE(inTable = Table.class)) with one comparison-operator annotation per filtered column (@IsEqualTo, @IsGreaterThan, @IsInRange, etc.), AND'ed if there are several. Wrap multiple predicates in a SqlOrPredicate for OR. Give every predicate interface the static _builder() method the samples use — calling code needs it to actually construct one.
• The trait itself → @SQL(dialect = ...) + @SELECT or @INSERT + @Trait, extending TraitDef<ResultType>. For SELECT, _Inputs holds the predicate (where()); for INSERT, _Inputs holds one or more same-table @Table-typed values to insert, and the result type is either Integer (rows inserted) or a @ReturnOnInsert projection if you need generated columns (like an auto-assigned id) back.
• Always set @SQL(dialect = ...) explicitly to match whichever Vert.x SQL client the module actually depends on — don't rely on the SQL_2023 default, especially for INSERT traits (see the dialect gotcha in references/queries.md).
• @LIMIT is mandatory on every List<T> result (top-level or a nested join field) — it's a compile error otherwise, by design. For a non-list result, choose deliberately between no @LIMIT (asserts exactly one row, runtime error if that's violated — use when the WHERE clause is provably unique) and @LIMIT(1) (silently takes the first row — use only when "any one match" is genuinely the intent).

Step 8 — Sanity-check the query before calling it done

• Every List<T> result has an explicit @LIMIT; every non-list result has a deliberate choice between no @LIMIT and @LIMIT(1), not just whichever one happened to compile.
• @SQL(dialect = ...) is set and matches the module's actual DB driver dependency.
• A join field's underlying tables actually have the @ForeignKey/@IncomingForeignKey pair — add it to the @Table models first if not (this loops back to Step 2).
• An INSERT trait's _Inputs only references one table type across all its inputs.
• A @ReturnOnInsert model's methods match real column names/types on the target table exactly.