DB Get All

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
| Field | Required | Default | What it does |
|---|---|---|---|
| Connection | Yes | none | The database integration to use. |
| Table Name | Yes | none | The table (or collection) to read. |
| Conditions | No | none (all records) | Rules that pick the records, see DB Conditions. A condition whose value is undefined is skipped. |
| Joins | No | none | Other tables to combine, SQL only. See DB Joins. |
| Columns | No | every column | Which columns to return, see Columns. |
| Sort | No | primary key order | The order of the records. See Sorting and paging. |
| Paging | No | Offset | Offset or Cursor. See Sorting and paging. |
| Limit | No | 1000 | The most records to return. -1 means no limit. |
| Offset | No | 0 | How many records to skip. Offset paging only. |
| After | No | empty | Where a cursor page starts. Cursor paging only. |
| Tiebreaker columns | No | primary key | Columns that, with Sort, make each record unique so no record is skipped between pages. Cursor paging only. See Cursor paging. |
| Save output to variable | No | off | Store the result in outputs.<name>. |
Limit, Offset and After accept a JS expression, for example js:getQueryParam('limit').
Outputs
| Handle | When 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": "..." }.nextCursorisnullon the last page.
Example
A GET /users?status=active route lists active users, newest first, 20 at a time.
| Field | Value |
|---|---|
| Connection | main-db |
| Table Name | users |
| Conditions | status = js:getQueryParam('status') |
| Columns | id, name, email |
| Sort | created_at Desc |
| Limit | 20 |
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.
-1loads 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.
idmeans_idunless the documents have their ownid. See MongoDB ids. - Very large whole numbers and decimals come back as text, and dates as
Datein UTC. See Value types.
Related blocks
- DB Get Single: fetch one record.
- DB Count: count matching records.
- DB Conditions, DB Joins and DB Sorting and Paging: the details of the filter, join and paging options.
- For Each Loop: go through the records one by one.
- Response: send the list back to the caller.
