Skip to content

DB Get All ​

DB Get All block on the canvas

The DB Get All block reads many records from a table and passes them on as a list. You can filter them, sort them, pick the columns, join related tables, and read them page by page.

When to use it ​

  • Return a list to the caller: users, orders, products.
  • Load the records you then go through with a For Each Loop.
  • Build a paginated endpoint, with a limit and offset or with a cursor.
  • Don't use it to look up one record by its id. DB Get Single is made for that.
  • Don't use it just to count. DB Count counts without loading every row.
  • Don't use it to check if something exists. DB Row Exists gives you a success and a failure path.

Inputs ​

FieldRequiredDefaultWhat it does
ConnectionYesnoneThe database integration to use.
Table NameYesnoneThe table (or collection) to read.
ConditionsNonone (all records)Rules that pick the records, see DB Conditions. A condition whose value is undefined is skipped.
JoinsNononeOther tables to combine, SQL only. See DB Joins.
ColumnsNoevery columnWhich columns to return, see Columns.
SortNoprimary key orderThe order of the records. See Sorting and paging.
PagingNoOffsetOffset or Cursor. See Sorting and paging.
LimitNo1000The most records to return. -1 means no limit.
OffsetNo0How many records to skip. Offset paging only.
AfterNoemptyWhere a cursor page starts. Cursor paging only.
Tiebreaker columnsNoprimary keyColumns that, with Sort, make each record unique so no record is skipped between pages. Cursor paging only. See Cursor paging.
Save output to variableNooffStore the result in outputs.<name>.

Limit, Offset and After accept a JS expression, for example js:getQueryParam('limit').

Outputs ​

HandleWhen it is used
Next (right)The query worked. The next block receives the records.
  • With Offset paging, the output is a list: [{ ... }, { ... }]. It is an empty list [] when nothing matches.
  • With Cursor paging, the output is { "rows": [...], "nextCursor": "..." }. nextCursor is null on the last page.

Example ​

A GET /users?status=active route lists active users, newest first, 20 at a time.

FieldValue
Connectionmain-db
Table Nameusers
Conditionsstatus = js:getQueryParam('status')
Columnsid, name, email
Sortcreated_at Desc
Limit20

Result passed to the next block:

json
[{ "id": 9, "name": "Avery", "email": "[email protected]" }, { "id": 4, "name": "Kai", "email": "[email protected]" }]

Without ?status=, the condition is skipped and users of every status come back.

Columns ​

By default every column is returned. To narrow the result, list the columns you want:

  • name: a plain column.
  • books.title: a column qualified by table (needed once you add a join).
  • books.title AS bookTitle: rename a column in the output.
  • books.*: every column from one table in a join.

How it behaves ​

  • No match is not an error. The output is an empty list, and the flow continues on Next.
  • A limit always applies. With no Limit, at most 1000 records are returned.
  • -1 loads everything. On a big table that can be slow and use a lot of memory. Use cursor paging to read a large table page by page.
  • A bad limit or offset fails the block. Text that isn't a whole number, such as abc, stops the block with an error that names the value.
  • Ties keep a stable order. The primary key is added as the last sort, so pages never repeat or skip a record.
  • Database errors (bad connection, missing table or column) go to the Error Handler.
  • MongoDB: joins are skipped. To combine collections, use a DB Native block. id means _id unless the documents have their own id. See MongoDB ids.
  • Very large whole numbers and decimals come back as text, and dates as Date in UTC. See Value types.

Released under the Apache License 2.0. Enterprise features are under the Fluxify Enterprise Edition License.