Build SQL query like you modify a list

Libraries
1

Is there any library that makes you build SQL queries in the same way as you modify a list?

Example

personsDbTable.all
              .filter   { it.age < 30 }
              .sortedBy { it.age  }
              .slice(5..10)
              .toSql()

Produces

SELECT   *
FROM     persons
WHERE    age < 30
ORDER BY age
LIMIT    5
OFFSET   5

Example 2

personsDbTable.all
              .map { it.age }
              .max()
              .toSql()

Produces

SELECT   MAX(age)
FROM     persons

Not sure yet how to handle joins, or if this can be handled with such a library. What do you think?

Is there anything like this out there? If not, would it be useful?

2 Likes
2

I hadn’t tested them myself yet, but there are SQL DSLs like Ktorm or Exposed.

2 Likes
3

Thanks! Ktorm is pretty close to what I’m looking for! I would like the operations to be more list-like (filter, map etc), but it would work pretty fine I think :smile:

3 Likes
4

Maybe also take a look at the Arrow Query Language!

5

A pretty awesome Java library for something like this is jOOQ. I haven’t tried it, but it looks very promising. Surely it’s Java, but Java interop is there in Kotlin so we can use it, right?

6

What’s the advantage of adding another layer on top of the existing SQL? If someone knows SQL, the plain query is more readable.

7

The biggest advantage most likely would be a type & structure safety. Normally you represent your queries as plain strings, but by having a proper model any changes to it would also affect all your queries.

1 Like
8

Okay, that makes sense.

9

The problem is with the lambda expressions like { it.age < 30 }. In Kotlin, there is no reasonable way for the filter function to process that and turn it into an SQL expression.

C# has LINQ, which solves exactly that problem. It needs to be built into the language, and in Kotlin it’s not.

Without that sort of thing, these kinds of libraries will always be pretty awkward.

10

I’m afraid you are right here. Just for fun I started to write some library like this myself, but it’s not really possible to make things turn into SQL. You come in with a Comparator (I tried in Java), but how to get it to SQL? I don’t think it’s possible… There should be something like LINQ in Java or Kotlin! I will come with a feature request.

2 Likes