DEV Community

Cover image for Why your Supabase query stops at exactly 1000 rows (and never tells you)
Michel Faure
Michel Faure

Posted on • Originally published at dev.to

Why your Supabase query stops at exactly 1000 rows (and never tells you)

#supabase #postgrest #postgres #eslint

The night a dropdown lied to me

Eleven at night, late April. A user of my internal tool reports an incomplete filter on a dropdown. My first diagnosis blames a sub-filter on the UI side. I take it apart, I isolate the dataset source, I re-run without filters. The counter returns a thousand on the nose. The table holds one thousand two hundred and three rows. I walk up the pipeline, level by level, all the way to the source query. Four levels higher, the culprit appears: a .select() chained on .from(), no .order(), no .limit(). The query succeeds. It lies.

The mechanism

Any PostgREST query that doesn't declare its ORDER BY receives an internal ctid sort — the physical tuple identifier in Postgres — plus a 1000-row Range HTTP cap applied by Supabase. The query succeeds. No exception, no warning, no Sentry breadcrumb. The client gets a subset whose order depends on the table's UPDATE and DELETE history, reshuffled after a VACUUM FULL or pg_repack. The bug only exists above one thousand rows in production.

// silently capped at 1000 rows
await supabase.from('events').select('*').eq('type', 'login')

// the Range HTTP cap applies on a stable sort
await supabase.from('events').select('*').eq('type', 'login').order('id')
Enter fullscreen mode Exit fullscreen mode

The ESLint rule that closes the door

The pattern is too quiet to live in code review alone. I moved it to lint, as an AST visitor on CallExpression, that requires a .select() chained on .from() to carry an .order() somewhere downstream, unless the chain terminates with .single(), .maybeSingle(), or an explicit .limit() below or equal to a thousand. It's one of five structural guards a workable Supabase rule needs. Without the others, the noise drowns the rule in under an hour.

export default {
  meta: { type: 'problem', messages: { unordered:
    'select() without .order() falls back to ORDER BY ctid.' } },
  create(context) {
    return {
      CallExpression(node) {
        if (node.callee?.property?.name !== 'select') return
        if (!chainContainsFromCall(node.callee.object)) return
        if (chainHasSafeTerminator(node)) return        // .single, .order, .csv...
        if (selectOptsHasHeadTrue(node)) return         // count head
        if (chainEndsAtAssignment(node)) return         // let q = supabase...
        if (chainIsInsideHelper(node, 'fetchAll')) return
        context.report({ node, messageId: 'unordered' })
      },
    }
  },
}
Enter fullscreen mode Exit fullscreen mode

The real scale

Once the rule was promoted to error, the first audit raised one hundred and seventy-eight alerts, spread over fifty-six files. Forty percent were false positives: variable reassigned at a distance, write returning, pagination helper injecting its own .order(), count head, single-row terminator. The five structural guards brought the noise down to one hundred and eight real targets before touching a single line of application code.

The rule

Every non-trivial .from(X).select(...) chain carries an explicit .order(). No option, no lukewarm.

Full rule, before/after pair and fetchAll helper, pseudonymized:
github.com/michelfaure/rembrandt-samples/tree/main/postgrest-row-cap

This silent PostgREST default is exactly the archetypal case of R12 of the Counterpart Toolkit ("cite the official text, materialise vendor defaults"). 14 rules, install in 1 command: github.com/michelfaure/doctrine-counterpart

Top comments (0)