Skip to main content

Sequences and lookups

Two query patterns recur often enough that Liberty ships a wizard for each:

PatternPurposeTypical SQL
SequenceCompute the next value for an id column.SELECT COALESCE(MAX(<col>), 0) + 1 AS NEXT_ID FROM <table>
LookupSupply value + label pairs for a dropdown bound to a screen column.SELECT <value>, <label> FROM <table> ORDER BY <label>

Both go through the Scaffold modal — tick a table, tick column(s), get a live SQL preview, save. The save writes two files: the query into the connector and the matching entry into the dictionary, atomically.

Where they live​

Settings → Connectors → pick a connector → mode bar: Sequences (or Lookups) → ＋ Add sequence (or ＋ Add lookup).

The modal opens, introspects the pool, lists the schemas / tables.

Sequence — generate the next id​

A sequence in Liberty is a query that returns one number — the next id to assign to a new row. It backs the dictionary's SEQUENCE rule: a screen field marked SEQUENCE reads from the sequence query each time the dialog opens for INSERT.

New sequence · [connectors.crm]Tablepublic.customers ▾Key column(MAX returns the next +1)customer_id ▾ · INT4Optional WHEREe.g. region = :regionQuery nameget_customer_id_from_customers_getSequence idget_customer_id_from_customersDescriptionGet customer_id from customersLIVE SQL PREVIEW · editableSELECT COALESCE(MAX(customer_id), 0) + 1 AS NEXT_IDFROM public.customers

Field by field​

FieldNotes
TableThe source. Schema picker shows up on multi-schema pools.
Key columnThe column whose MAX(...) + 1 gives the next value. The pool introspection lists every column with its SQL type.
Optional WHEREThe body of a WHERE clause (without the keyword). Use :placeholder params if the filter needs to vary per call — e.g. region = :region to scope the sequence per region.
Query nameAuto-suggested as get_<keyCol>_from_<table>_get (kept short via slugify). Override freely. The _get suffix is convention — sequences are read queries.
Sequence idThe key under [sequences.*] in the dictionary. Auto-suggested as get_<keyCol>_from_<table>.
DescriptionShown in listings and tooltips. Auto-filled as "Get <col> from <table>".
Generated SQLLive preview. Editable — your edits stick until you change the column / table / WHERE inputs, which restores the generated form.

What gets saved​

FileEntry
Connector file{ name = "<queryName>", type = "sequence", sql = "..." }
Dictionary file[connectors.<conn>.sequences.<seqId>] query = "<queryName>", dd_id = "<keyCol>", optional description

The dictionary entry is what the SEQUENCE rule looks up. A column on a screen marked rule SEQUENCE with sequence = "<seqId>" calls this query when the dialog opens for INSERT and pre-fills the field with the result.

Concurrency note​

SELECT MAX(...) + 1 is not atomic — two simultaneous INSERTs can both read the same MAX and produce a collision. Adequate when:

  • Your INSERT happens behind a transactional UI (the user sees one dialog at a time).
  • The id column has a database-level uniqueness constraint that will reject the duplicate (the screen reports the error and the operator retries).

For high-concurrency workloads, define the column as a database SEQUENCE (Postgres SEQUENCE, Oracle SEQUENCE, Identity column) and have the sequence query return nextval(...) instead — the database guarantees uniqueness.

Lookup — supply a dropdown's options​

A lookup in Liberty is a query that returns rows of (value, label) pairs — the source for a screen column's dropdown. It backs the dictionary's LOOKUP rule: a column marked LOOKUP with lookup = "<id>" renders as a dropdown sourced from the lookup query.

New lookup · [connectors.crm]Tablepublic.regions ▾Value column(the code stored on the row)region_code ▾ · VARCHAR(8)Label column(the description shown to the user)region_name ▾ · VARCHAR(60)Optional WHEREactive = 'Y'Query nameget_regions_getLookup idget_regionsLIVE SQL PREVIEW · editableSELECT region_code, region_nameFROM public.regionsWHERE active = 'Y' ORDER BY region_name

Field by field​

FieldNotes
TableThe source of the dropdown rows.
Value columnThe code stored on the row when the user picks an option. Type matters — VARCHAR(8) value column expects a VARCHAR target column on the screen.
Label columnThe text shown to the user. Pick Same as value column when the value is already human-readable (e.g. country code displayed as code).
Optional WHEREBody of a WHERE clause without the keyword. Common patterns: active = 'Y', deleted_at IS NULL. Use :placeholder params for filter-cascaded lookups (e.g. show only regions for the selected country — see Parameter binding).
Query nameAuto-suggested as get_<table>_get.
Lookup idThe key under [lookups.*] in the dictionary. Auto-suggested as get_<table>.
DescriptionShown in listings and tooltips.
Generated SQLLive preview, editable. The ORDER BY <label> is added automatically — drop it if the rows have an intrinsic order.

What gets saved​

FileEntry
Connector file{ name = "<queryName>", type = "lookup", sql = "..." }
Dictionary file[connectors.<conn>.lookups.<lookupId>] query = "<queryName>", value = "<valueCol>", label = "<labelCol>", optional description

Cascading lookups​

When the dropdown should be filtered by another field on the same screen — e.g. Role depends on Application — the lookup needs a :placeholder in the WHERE clause, and the screen's column declares a filter_from referencing the parent. The wiring lives on the screen side (Concepts → Form conditions until the Screens build section ships); the lookup query itself just needs the :placeholder.

SELECT role_id, role_name
FROM   roles
WHERE  app_id = :app_id
ORDER BY role_name

What's next​