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​

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

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

Without a table

The scaffold starts from a table. For a lookup or a sequence built on a join or any hand-written SELECT, create the record in Liberty Next → Build → Dictionary (Lookups or Sequences tab) and create its query from there — a blank query, or Generate from a table with the columns mapped to the record's fields.

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: when a row is inserted, the server runs the sequence query inside the insert's transaction and writes the result into the column.

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 fileThe query, in the connector's Sequences section.
Dictionary file[connectors.<conn>.sequences.<seqId>] query = "<queryName>", dd_id = "<keyCol>", optional description

A dictionary entry (or a screen column) with the rule SEQUENCE and Rules values = <seqId> gets its number from this query on each insert. With dd_id set, every column whose entry has that key is numbered too.

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 with the rule LOOKUP and Rules values = <lookupId> shows the label instead of the code and offers the lookup's rows in a searchable list.

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 fileThe query, in the connector's Lookups section.
Dictionary file[connectors.<conn>.lookups.<lookupId>] query = "<queryName>", value = "<valueCol>", label = "<labelCol>", optional description

Cascading lookups​

When the list should depend on another field of the same row — Role depends on Application — the lookup query takes a parameter, and the screen column binds it:

  • in the dialog and grid editing, the column's lookup_param_binds — { param = "APP_ID", source = "APP_ID" };
  • in the grid's filter panel, the column's filter_from, which narrows the filter's options on the value of another filter.

See Parameter binding and Columns.

SELECT ROLE_ID, ROLE_NAME
FROM   roles
WHERE  APP_ID = :APP_ID
ORDER BY ROLE_NAME

What's next​