DEV Community

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

Posted on Edited 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)