Fork me on GitHub
#sql
<
2020-06-23
>
seancorfield00:06:48

(the nested-tx and multi-rs branches have both been updated to match develop and I will continue working on those features for future releases)

seancorfield06:06:09

seancorfield/next.jdbc {:mvn/version "1.0.476"} -- extends the "default options" behavior to the friendly SQL functions in next.jdbc.sql

posobin19:06:20

Is there a way to iterate over the result set lazily in next.jdbc?

seancorfield19:06:24

@posobin There are two "lazy" things in play in questions like that: lazy streaming of results from the database, and lazy sequence processing of result sets. The former is possible via next.jdbc/plan. The latter is not -- because something has to manage all of the resources around connections and prepared statements etc.

seancorfield19:06:33

In clojure.java.jdbc it was possible to provide a lazy :result-set-fn but the behavior was pretty much always wrong because the lazy sequence escaped the connection context and you nearly always got an exception from attempting to operate on a closed connection!

seancorfield19:06:02

In next.jdbc, you can use plan and reduce/transduce/etc to process result sets that will not fit in memory, and you can terminate processing early via reduced (see docs for reduce and reduced in clojure.core).

seancorfield19:06:40

You can use plan for results that would fit in memory too, and you can still control fetch size and other ways to lazily stream the result set. The processing itself must be eager, however. Also, note that persuading some databases to actually stream very large sets is tricky -- and the exact options needed tend to vary from database to database.

seancorfield19:06:37

@posobin Can you explain your particular use case, if you're looking for a lazy sequence?

seancorfield19:06:37

@posobin Can you explain your particular use case, if you're looking for a lazy sequence?

posobin03:06:59

I wanted to save a couple of million rows to a JSON, in the end I just made a lazy seq that did batched queries to the db.

posobin03:06:32

So basically pagination with offsets and limits.

seancorfield04:06:55

Hmm, so streaming rows into a JSON file? You could do that with reducing over plan by appending the JSON of each row to a file, with trailing , added I guess? (and a leading [ and trailing ])

seancorfield15:06:17

Reduce is eager. The "lazy" part refers to how the rows are read from the database

posobin15:06:46

So the bottleneck for me is in ram, I didn't want to have the whole result set in memory.

seancorfield16:06:04

Right, so you must "lazily stream" the result sets from the database, but you can eagerly reduce them.

seancorfield16:06:08

Getting the driver to lazily stream results is database-dependent -- some suggestions are given in the next.jdbc docs, on the Tips &amp; Tricks page as I recall.

posobin16:06:48

Ah, that's great, I'll take a look, thank you!

posobin12:06:34

Works great, thank you!

posobin13:06:50

I should have just tried it instead of googling for that SO question...