Read data: filter, sort, page

What you will achieve

A list request that returns exactly the rows and fields you asked for, in the order you asked for, one page at a time, with a total count when you want one. About fifteen minutes, starting from the vehicle-registry stack of Quick start.

Before you start

• The stack from Quick start, serving examples/vehicle-registry, run from the directory that holds docker-compose.quickstart.yml, with ALVO_DEMO_KEY_SECRET and ALVO_ADMIN_PASSWORD exported in this shell.
• The demo key (roles admin, authenticated). Any authenticated caller may list vehicles.

Start from an empty database so your results match the ones below. The first command deletes the stack’s database:

Terminal

docker compose -f docker-compose.quickstart.yml down --volumes
unset ALVO_DESCRIPTOR
docker compose -f docker-compose.quickstart.yml up --wait --wait-timeout 90

The responses below were captured from a real host when the site was built, so your ids, timestamps and cursors will differ.

1. Add rows to query

Create an owner under an id you choose, then five vehicles in one request:

Request

curl -sS -X PUT http://localhost:8080/api/owners/2c4e6a80-1b3d-4f5a-8c7e-9d0f1a2b3c4d \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"name":"Fleet Desk Ltd","email":"office@fleetdesk.example"}'

201 Created

Response

HTTP/1.1 201 Created
Content-Type: application/json; charset=utf-8
ETag: "639273155274969830"
Location: /api/owners/2c4e6a80-1b3d-4f5a-8c7e-9d0f1a2b3c4d

{
  "id": "2c4e6a80-1b3d-4f5a-8c7e-9d0f1a2b3c4d",
  "name": "Fleet Desk Ltd",
  "email": "office@fleetdesk.example"
}

Request

curl -sS -X POST http://localhost:8080/api/vehicles/batch \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"rows":[{"vin":"TMBJJ7NE8L0123456","plate":"BA-101AA","make":"Skoda","model":"Octavia","year":2020,"color":"red","owner_id":"2c4e6a80-1b3d-4f5a-8c7e-9d0f1a2b3c4d"},{"vin":"TMBEG7NJ1N0234567","plate":"BA-202BB","make":"Skoda","model":"Fabia","year":2022,"color":"blue","owner_id":"2c4e6a80-1b3d-4f5a-8c7e-9d0f1a2b3c4d"},{"vin":"WVWZZZAUZKW345678","plate":"KE-303CC","make":"Volkswagen","model":"Golf","year":2019,"color":"red","owner_id":"2c4e6a80-1b3d-4f5a-8c7e-9d0f1a2b3c4d"},{"vin":"WVWZZZ3CZPE456789","plate":"KE-404DD","make":"Volkswagen","model":"Passat","year":2023,"owner_id":"2c4e6a80-1b3d-4f5a-8c7e-9d0f1a2b3c4d"},{"vin":"VF1RJA00X67567890","plate":"ZA-505EE","make":"Renault","model":"Clio","year":2017,"color":"white","owner_id":"2c4e6a80-1b3d-4f5a-8c7e-9d0f1a2b3c4d"}]}'

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "affected": 5
}

PUT with an id creates the row when it does not exist, and the batch writes every row or none. Write data safely covers both.

2. Filter rows

A filter is a query parameter named after a field: ?<field>=<operator>.<value>. Every parameter must hold, and a field may appear more than once (year=gte.2019&year=lte.2022). The examples add select to keep the responses short; step 4 explains it.

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?make=eq.Skoda&year=gte.2021&select=plate,make,model,year' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "BA-202BB",
      "make": "Skoda",
      "model": "Fabia",
      "year": 2022
    }
  ],
  "next": null,
  "count": null
}

or=(…) matches when any of its terms holds, and and=(…) groups terms that must all hold. Inside a group, a term is written field.operator.value:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?or=(color.eq.red,color.eq.blue)&select=plate,make,color' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "BA-202BB",
      "make": "Skoda",
      "color": "blue"
    },
    {
      "plate": "KE-303CC",
      "make": "Volkswagen",
      "color": "red"
    },
    {
      "plate": "BA-101AA",
      "make": "Skoda",
      "color": "red"
    }
  ],
  "next": null,
  "count": null
}

is.null finds rows where a field has no value:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?color=is.null&select=plate,color' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "KE-404DD",
      "color": null
    }
  ],
  "next": null,
  "count": null
}

like and ilike match a pattern: % stands for any run of characters and _ for one, and ilike ignores case. In a URL, % is written %25:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?model=ilike.%25a%25&select=plate,model' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "KE-404DD",
      "model": "Passat"
    },
    {
      "plate": "BA-202BB",
      "model": "Fabia"
    },
    {
      "plate": "BA-101AA",
      "model": "Octavia"
    }
  ],
  "next": null,
  "count": null
}

To negate a term, put not. in front of the parameter name (not.make=eq.Skoda) or in front of a group member (or=(not.color.eq.red,…)). PostgREST’s spelling, make=not.eq.Skoda, is refused with unknown-operator (#350):

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?not.make=eq.Skoda&or=(not.color.eq.red,year.gte.2023)&select=plate,make,color,year' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "KE-404DD",
      "make": "Volkswagen",
      "color": null,
      "year": 2023
    },
    {
      "plate": "ZA-505EE",
      "make": "Renault",
      "color": "white",
      "year": 2017
    }
  ],
  "next": null,
  "count": null
}

Each term takes one operator from this list, and the field’s type decides which ones it accepts:

Operator	Matches	Field types
eq, neq	equal, not equal	every type
in	one of a list: in.(Skoda,Renault)	every type
gt, gte, lt, lte	greater, at least, less, at most	string, text, enum, integer, decimal, date, datetime
like, ilike	a pattern, case-sensitive or not	string, text, enum
is	null; true or false on a boolean	every type for null

A parameter that is not a field is refused, never ignored, so a typo such as ?oder=year cannot quietly return rows in some other order. That is also why order, limit, offset, after, select, or, and and not are reserved: a descriptor cannot declare a field with one of those names. An operator the field’s type does not take is refused too:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?year=like.20%25' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

422 Unprocessable Entity

Response

HTTP/1.1 422 Unprocessable Entity
Content-Type: application/problem+json

{
  "type": "https://alvo.dev/errors/malformed-query",
  "title": "Unprocessable Entity",
  "status": 422,
  "detail": "A filter applies an operator the type of the field it names does not support.",
  "violations": [
    {
      "pointer": "filter",
      "code": "unsupported-operator-for-field",
      "message": "A filter applies an operator the type of the field it names does not support.",
      "fixSuggestion": "'like' cannot be applied to a 'integer' field: like/ilike are string pattern matches, and gt/gte/lt/lte need a type this port orders."
    }
  ]
}

3. Sort, and choose where nulls go

order takes a comma-separated list of fields, each with an optional .asc (the default) or .desc, and an optional .nullsfirst or .nullslast. Rows without a value sort last unless you say otherwise. Colours in reverse alphabetical order, vehicles without a colour first, ties broken by year:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?order=color.desc.nullsfirst,year&select=plate,color,year' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "KE-404DD",
      "color": null,
      "year": 2023
    },
    {
      "plate": "ZA-505EE",
      "color": "white",
      "year": 2017
    },
    {
      "plate": "KE-303CC",
      "color": "red",
      "year": 2019
    },
    {
      "plate": "BA-101AA",
      "color": "red",
      "year": 2020
    },
    {
      "plate": "BA-202BB",
      "color": "blue",
      "year": 2022
    }
  ],
  "next": null,
  "count": null
}

Alvo decides where a null sorts, not the database, so SQLite and PostgreSQL return the same order. Sorting by a field that can be empty costs more than sorting by a required one, because no index serves it yet (#178).

4. Choose the fields: select

select names the fields each row carries, and the database stops reading the others. alias:field renames a field in the response:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?select=plate,label:make' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "KE-404DD",
      "label": "Volkswagen"
    },
    {
      "plate": "BA-202BB",
      "label": "Skoda"
    },
    {
      "plate": "KE-303CC",
      "label": "Volkswagen"
    },
    {
      "plate": "BA-101AA",
      "label": "Skoda"
    },
    {
      "plate": "ZA-505EE",
      "label": "Renault"
    }
  ],
  "next": null,
  "count": null
}

Without select, a row carries every field the caller may read, including the columns Alvo manages: id, and with audit the created_* and updated_* stamps. A field that is hidden for the caller is refused in select, in a filter and in order exactly like a field that does not exist.

5. Page through the results

Every list is paged. Without limit a page holds 50 rows; limit may ask for up to 200, and a larger value is refused rather than quietly reduced (Limits and budgets). The response always carries items, next and count:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?order=year.desc&limit=2&select=plate,year' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "KE-404DD",
      "year": 2023
    },
    {
      "plate": "BA-202BB",
      "year": 2022
    }
  ],
  "next": "S-8JN-ye3UuFew6ckFY0Yg",
  "count": null
}

next is a cursor for the following page. Send the same request again with after set to it:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?order=year.desc&limit=2&select=plate,year&after=S-8JN-ye3UuFew6ckFY0Yg' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "BA-101AA",
      "year": 2020
    },
    {
      "plate": "KE-303CC",
      "year": 2019
    }
  ],
  "next": "HOHIha0euUyNN4pNyq-Kew",
  "count": null
}

On the last page next is null. Treat the cursor as an opaque string of at most 512 characters: it carries no data, and a stale or forged one returns an empty page; an empty one, or one over 512 characters, is refused (invalid-cursor). Cursor paging neither skips nor repeats a row while other callers write. offset=<n> is the alternative, but a request may not send both after and offset.

6. Count the matches

Send Prefer: count=exact to fill count with the number of rows the whole query matches, not the number on this page. Preference-Applied confirms it:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?make=eq.Skoda&limit=1&select=plate' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET" \
  -H "Prefer: count=exact"

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
Preference-Applied: count=exact

{
  "items": [
    {
      "plate": "BA-202BB"
    }
  ],
  "next": "S-8JN-ye3UuFew6ckFY0Yg",
  "count": 2
}

A count is a second query over every matching row, so ask for it only when you show it. It counts only the rows your rules let you see. Because it runs beside the page rather than in the same statement, a write in between can make it differ from the rows by one. count=planned and count=estimated are accepted and return the exact count; any other preference is ignored, and Preference-Applied is then absent.

7. Send a long query as a body

Proxies limit a URL to a few kilobytes, and a filter over a few hundred ids is longer than that. POST /api/<entity>/query takes the same parameters as a JSON object, each value written exactly as it would follow the = in the URL:

Request

curl -sS -X POST http://localhost:8080/api/vehicles/query \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET" \
  -H "Content-Type: application/json" \
  -d '{"make":"in.(Skoda,Renault)","model":"ilike.%a%","order":"year","select":"plate,make,model,year"}'

200 OK

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{
  "items": [
    {
      "plate": "BA-101AA",
      "make": "Skoda",
      "model": "Octavia",
      "year": 2020
    },
    {
      "plate": "BA-202BB",
      "make": "Skoda",
      "model": "Fabia",
      "year": 2022
    }
  ],
  "next": null,
  "count": null
}

The body carries decoded values, so % is just % here, not %25. To repeat a parameter, give it an array of values. It is still a read: the caller needs the list rule, Prefer: count=exact works, and like every request with a body it needs Content-Type: application/json.

How it works

Alvo compiles the query together with the entity’s list rule into one SQL statement, with every value bound as a parameter. The rule is part of the WHERE clause, so a row your rule excludes is never in a page, never counted and never reachable through a cursor. That is why a caller the rule shuts out gets an empty page rather than a 403; Security model explains the design.

Options and variations

Parameter	Example	Notes
a field name	year=gte.2020	Repeat it for a range: year=gte.2019&year=lte.2022.
not. + a field name	not.make=eq.Skoda	One not. only; not.not. is refused.
or, and	or=(color.eq.red,color.eq.blue)	Nest a group with = inside it: or=(make.eq.Skoda,and=(year.gte.2020,color.eq.red)).
order	order=year.desc.nullslast,plate	Several keys, comma-separated.
select	select=plate,label:make	At most as many keys as you can read fields.
limit	limit=100	1 to 200, default 50.
after	after=<next>	The cursor from the previous page.
offset	offset=100	Not together with after.

The full grammar, headers and route shapes are in Data API conventions, and the routes and fields this descriptor generates are in Data API: example.

Not in this build

gt, gte, lt and lte are refused on uuid, ref and boolean fields, so the common id=gt.<uuid> paging idiom does not work; use after instead (#95). On a sort over several fields, the cost of a page grows the deeper you page (#100). Neither appears on Capabilities in this build, which lists descriptor blocks only.

What can go wrong

A malformed query is refused with every reason at once, each pointing at the parameter it is about:

Request

curl -sS -X GET 'http://localhost:8080/api/vehicles?colour=eq.red&limit=500' \
  -H "X-Alvo-Api-Key: demo.$ALVO_DEMO_KEY_SECRET"

422 Unprocessable Entity

Response

HTTP/1.1 422 Unprocessable Entity
Content-Type: application/problem+json

{
  "type": "https://alvo.dev/errors/malformed-query",
  "title": "Unprocessable Entity",
  "status": 422,
  "detail": "The query references a field that is not available to this caller. The requested page size is not a whole number between 1 and the maximum this API allows.",
  "violations": [
    {
      "pointer": "filter",
      "code": "unavailable-field",
      "message": "The query references a field that is not available to this caller.",
      "fixSuggestion": "Name a field this entity declares and your policy lets you read. The reserved query parameters are order, limit, offset, after, select, or, and, not."
    },
    {
      "pointer": "limit",
      "code": "invalid-page-size",
      "message": "The requested page size is not a whole number between 1 and the maximum this API allows.",
      "fixSuggestion": "Ask for between 1 and 200 rows. A larger request is refused rather than quietly reduced, because a clamped page makes a client's own paging arithmetic wrong."
    }
  ]
}

Status	Problem type	When	Fix	Returned by
422	malformed-query	A parameter names no readable field, an operator does not fit the field’s type, limit is over 200, after and offset are both sent, after is empty or over 512 characters, or a filter is deeper or wider than the limits.	Fix the parameter each violation’s pointer names.	every host
403	forbidden	The entity has no list rule, or the rule reads @user.id and the request carries no key.	Add the rule, or send a key.	every host
403	out-of-scope	The key’s scopes do not cover reading this entity.	Grant the key a read scope.	every host
401	unauthenticated	The key cannot be used.	See Authentication and API keys.	every host
404	not-found	GET /api/<entity>/<id> names a row that does not exist, or the get rule hides it.	Check the id and the rule.	every host
414	none	A proxy refused a URL that was too long, before Alvo saw it.	Send the query as a body to POST …/query.	a proxy, not Alvo

An empty page is not an error: either nothing matches, or your list rule excludes the rows. Handle errors tells the two apart.

Reference

• Data API conventions: the grammar, paging and headers, one table each.
• Limits and budgets: page size, filter depth and terms, in candidates, body size.
• Problem types: malformed-query, forbidden, out-of-scope, unauthenticated.
• Design notes: the URL grammar and its allow-lists, keyset paging and the opt-in count.

Next

Write data safely: create, replace and update rows without losing a concurrent change, and retry a create without writing it twice.