Drizzle Team•2y ago

Iterator for result set

I've got some big ass database tables that I'd rather not read into memory. In other languages there is an iterator attached to result set, but I haven't seen that in the JS/TS world anywhere. Is that something I'm going to have to write for myself, or have I missed it? What I'm imagining is either something that uses a DB cursor, or something that basically works like a paginator, where it will select 100 rows at a time, iterate over those rows, and keep doing that each time you call .next() until it has reached the end of the result set.

6 Replies

Revan•15mo ago

better-sqlite3 supports this https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#iteratebindparameters---iterator not sure about other databases for drizzle its still in the roadmap backlog: roadmap: https://github.com/orgs/drizzle-team/projects/1/views/1 issue: https://github.com/drizzle-team/drizzle-orm/issues/456 while drizzle works on this I have created the greatest function in the world that will turn a query into an async iterator

Revan•15mo ago

bigAssQueryToAsyncIt...

Luxaritas•15mo ago

FWIW, that doesn't address the fact all of them are loaded into memory at once - this just adds extra steps OH sorry, I see you add the limit/offset, ignore me

Angelelz•15mo ago

We are still missing a proper iterator for all the dialects. Although this solution works, we all know the issues with offsets in very large tables

NeonCop•9mo ago

any idea how to do this in postgres? can't quite seem to get the types right on thsi util function

Revan•9mo ago

drizzle updated the types system since I wrote this function, I didnt have time to properly inspect the new drizzle types so I rewrote the function signature to just use structural types style the new function signature is

export async function* queryToAsyncIterable
    <
        TQuery extends {
            limit: (limit: number) => {
                offset: (offset: number) => {
                    execute: () => Promise<TRow[]>
                }
            }
        },
        TRow,
    >(query: TQuery, limit = 64): AsyncIterable<TRow>

export async function* queryToAsyncIterable
    <
        TQuery extends {
            limit: (limit: number) => {
                offset: (offset: number) => {
                    execute: () => Promise<TRow[]>
                }
            }
        },
        TRow,
    >(query: TQuery, limit = 64): AsyncIterable<TRow>

Gaming

Programming

Iterator for result set