Terpal-SQL for Kotlin Multiplatform is out! Curren...

# feed

[Alexander Ioffe]

Terpal-SQL for Kotlin Multiplatform is out! Currently supporting Android, IOS, Linux, and Windows (in addition to JVM)!
https://github.com/deusaquilus/terpal-sql/blob/main/README.md

[Alexander Ioffe]

The latest version is:
Terpal SQL - 1.0.0.PL-1.0.0

to be used with:
Terpal - 2.0.20-1.0.0.PL

Have a look at the README for more info. Also have a look at the sample project for Android and IOS here:
https://github.com/deusaquilus/terpal-sql-sample

[arve]

Hi @Alexander Ioffe. This library looks very nice indeed!

I’m looking into adopting a more modern SQL library in our code base (currently using a mix of Kotliquery and JDBI). The usea of coroutines, Flow support, and especially the interpolation features looks very promising.

However, we have a widely used query pattern which I don’t see how to approach using this library:

Throughout our code we need to filter on a list of ids, e.g. give me all foos with id matching listOf(id1, id2, id3, etc).

Concretely, we model this as either where..in quries with JDBI magic _(_`select * from foo where foo.id in (<idList>)` )

or with kotliquery with parameter bound array
(select * from foo where foo.id in = ANY(:idArray) where :idArray is bound to a SQL array via connection.createArray() )


Are there any mechanisms in terpal-sql to pass list-like parameters somehow? Or perhaps extension points where i can hook into the parameter binding and do this myself? I couldn’t find any examples for this in the docs 🙂

Any pointers as to where i can start digging would be greatly appreciated

[Alexander Ioffe]

@arve sounds like I missed a feature! Give me a couple days and I’ll implement it. It’s going to look like this …where person.name in ${Params(liofOfNames)}.

[arve]

Man, I sure can dig an SLA like that 😄
Keep in mind that there are subtle differences between where person.name in (…) and where person.name = ANY(...). The former would require one bound param per element, while the latter expects a single param bound as an array. I know at least when using Postgres there are various perf and limit tradeoffs between the two.

Of course I am beyond happy with either case being supported - just mentioning it in case you want to aim for completeness

[Alexander Ioffe]

So for = Any(...) I need to support encoders/decoders to/from postgres lists which is possible to do with primitives easily but is much harder to do with user-defined types. I need a couple weeks to figure that out. Supporting regular IN(...) is much simpler, that I'll have in a couple days. Already have a working implementation.

[Alexander Ioffe]

@arve Terpal-SQL 1.0.0.PL-1.1.0 is out supporting IN (...) clauses. You can see documentation here: https://terpal.io/#/?id=in-clauses

Also, it turns out that if you build a custom wrapper (e.g. value-class) and combine it with @Contextual and a low-level encoder you can indeed use arbitrary data-structures given your database supports them. Example of how to do that including how to use = ANY(...) is in the documentation here: https://terpal.io/#/?id=using-array-columns.

[arve]

🤩 Very nice, I am very impressed over the iteration speed 👏 Thank you

[arve]

One last suggestion: In many cases it would be nice to have an overload for queryOf<T>() (and actionReturing<T>()) that takes an extractor function / row mapper, e.g. queryOf<T>( (ResultSet) -> T?)) . Some times it’s nice to be allowed to access “under the hood”

If i understand correctly this would let you bypass the kotlinx.serialization loop and would be useful in complex cases where you would otherwise have to tailor your entity dataclasses a lot in order to make things work. E.g. custom serializers, @Contextual and @SerialName annotations, etc while still allowing for the frankly delicious interpolation features of Terpal.

val query = Sql("...").queryOf<Member> { row -> Member(
    row.int("id"), 
    row.stringOrNull("name"), 
    row.zonedDateTime("created_at")
  )
}
query.runOn(db)

I realize this might go against the objective / philosophy of Terpal, and I’m not familiar enough with KMP to know how feasible this would be WRT other platforms, but it has proven a very useful pattern for us with kotliquery so far. Consither this a suggestion for improvement rather than a feature request, I don’t want to nag 🙂

[Alexander Ioffe]

I was going to put this on a run function but I like your idea more. Make a GitHub issue for this and I’ll add it.

[arve]

Done ✅