Limit & Offset

Providing additional conditions on a SQL statement to limit how many rows are returned, and offsetting them allow you to page through results in a computationally cheaper manner than requesting all entries at once.

Limit

By specifying the LIMIT in a SQL statement you can tell the database the maximum number of rows you want to have returned. To add a limit constraint to your statement via the query builder you can chain the .limit() function before exectuing the query.

const { data, error } = db
    .selectFrom([
        { table: 'table_name', columns: ['*'] }
    ])
    .limit(10)
    .query()

Offset

When you need to paginate through database results, it's common to use both LIMIT and OFFSET together to fetch X number of results at a time. For example, if you want to see 10 items at a time your first request may be a LIMIT of 10 and an OFFSET of 0. The second set of 10 would still have the same limit value, but the offset value now is 10 – as you've already seen the first ten results.

const { data, error } = db
    .selectFrom([
        { table: 'table_name', columns: ['*'] }
    ])
    .limit(10)
    .offset(1)
    .query()

The above query would return results 11 through 20 from the table_name table in your database.