Are there any Kotlin libraries with a nice DX that...

# getting-started

[Adam S]

Are there any Kotlin libraries with a nice DX that support streaming bytes in/out of a local database (like SQLite)?

I want to cache binary files. I tried using SqlDelight, and while it supports BLOBs, the entire ByteArray must be loaded into the program memory, which I'd prefer to avoid. I wanted to use an InputStream or OutputStream or Okio/kotlinx-io Buffer to stream the files in/out of the cache.

The only thing I found so far was https://github.com/xerial/sqlite-jdbc, which supports java.sql.PreparedStatement#setBinaryStream and java.sql.ResultSet#getBinaryStream, but I would prefer a nicer Kotlin wrapper - I would like to avoid manually writing database setup and queries, or at least only if there's a nice Kotlin DSL!

Kotlin JVM is preferred, but I could work with Native too.

(I'm not set on SQLite, or even a database, maybe there's another option? I just need need something with transaction support, that's local only and doesn't need an external process.)

[hfhbd]

For sqldelight, you could also write a custom dialect that delegates most parts to the main dialect but uses an inputstream for blob only

[ephemient]

sqldelight uses xerial's sqlite-jdbc for the JVM sqlite driver, so hypothetically you could use sqldelight for most things and then grab the DB from the driver to do other things directly

[ephemient]

however there isn't any benefit from trying to stream data into sqlite: the C API requires the whole blob in memory at once and xerial reads the InputSteam first: https://github.com/xerial/sqlite-jdbc/blob/master/src/main/java/org/sqlite/jdbc4/JDBC4PreparedStatement.java#L130

[ephemient]

after a blob is inserted into the database, it is possible to read and write bytes, but not change its size, at least in the C API https://www.sqlite.org/c3ref/blob_open.html
but I don't see any usage of that in Java libraries

[Jens Alfke]

SQLite’s low-level C API supports streaming blob I/O, but it’s quite obscure and I’ve never seen any wrapper libraries in any language implement it. From your requirements you do need a database, an embedded one, and SQLite is the best option.)

[ephemient]

you can insert a zeroed blob (of a given size) and then update the blob with the streaming api, but you can't stream a blob into the insert

[Adam S]

thank you very much for the investigation @ephemient! I didn't realise xerial's implementation loaded all bytes.

[Adam S]

I've found lmdb, which looks promising. I like that it supports multiple processes https://github.com/lmdbjava/lmdbjava, and there's a Kotlin implementation https://github.com/crowded-libs/kotlin-lmdb