Discussed how to build SQL queries with code in this topic, but figured out that this is not really possible to do in a nice way. We can’t build SQL natively in the language, as @mtimmerm explained in the other topic:
The problem is with the lambda expressions like
{ it.age < 30 }. In Kotlin, there is no reasonable way for thefilterfunction to process that and turn it into an SQL expression.
Why not make something like C#'s LINQ in Kotlin?
After a super brief look at LINQ, it looks neat! But I have have reservations when it comes to Kotlin–especially since Kotlin makes a big effort to support easy creation of typesafe DSLs–maybe there’s an even more impactful improvement that we’re missing for Kotlin DSLs in general.
Let me know if I’m understanding it correctly:
LINQ is a query DSL embedded in C# that allows you to more easily write queries. The DSL is transpiled into SQL or used to query object collections or querying XML documents. The DSL is more restrictive on what properties and types can be used since it doesn’t support translating arbitrary C#.
So back to the use-case. It sounds like your use-case is writing SQL queries more easily in Kotlin. For that, what is lacking in Kotlin DSLs? What is (or are) your top pain points?
Those are the ones I could think of off the top of my head. The reason I want to focus on use-cases and pain points is that o think they can lead to a much clearer picture of what values a solution may or may not bring.
Depending on what the real need is, Kotlin may already have a good solution or be able to change in a way that allows users to build systems that solve LINQ style problems with thier own libraries.
Could you link to the SQL topic also? I don’t see it in the OP.
It’s mainly the lack of being able to use the same query on SQL as on other objects, like I described in https://discuss.kotlinlang.org/t/build-sql-query-like-you-modify-a-list/19978 (Added link to OP now).
Yes, Kotlin do have a great support for DSLs. I have used Exposed and really love it, but in some cases it is easier to just handle things like a list with regular list operations, at least when selecting only from one table.
The critical feature is a limited ability to use code as data.
We all know that, in Kotlin, when you call a method that takes an instance of a Java functional interface, you can specify the parameter with a lambda expression. Kotlin will compile it into code that produces an instance of that interface, which the method can then call.
C# has an extra feature along similar lines. When you call a method that takes an Expression<...>, you can specify it with a lambda expression, and the compiler will compile it into code that produces an instance of the Expression type.
The Expression object can be called like a function, and that is how it would be used if you use an Expression to filter a list.
But the Expression object is also an abstract syntax tree. This provides the receiver of the expression with an easy way to inspect the actual code that you provided in the lambda expression, and translate it into a different language, like SQL. That is how it’s used when you use an expression to filter in a database.
Hmmmm. That sounds like something that could be implemented right now using a compiler plugin, but I’m not experienced enough to know if it’s fully possible. It sounds like that a compiler plugin could analyse the could in a manner similar to C#'s expressions and then spit out some Kotlin code that does exactly the same thing. Maybe even it could then be optimised for performance with some form of inlining? I’m not entirely sure, but this sure sounds interesting. Maybe something for the Arrow folks to also look at as a possible improvement on the Arrow Query Language.
LINQ is about (1) kind of “link comprehensions” which are similar to do notation in Haskell allowing you to express nested flatMap’s sequentially and (2) code as data via Expression<> type.
SQl/XML etc is just an example of application.
Something like this can help open up the language to support all kinds of useful abstractions. For an example what can be done when it’s made as flexible as possible, Common Lisp implements a very strong macro facility that is based on this concept.
However, JB has on several occasions said that they don’t want to implement such facility. I believe the argument is that it can be abused.
Having a type safe way to build and parse expression trees could really help open the language up to support many new powerful libraries.
Leaving this as a reference
I agree that it would be an interesting feature. Not that it is not possible right now on the library level (for example in KMath we use expression builders for mathematical operations). But it would help a lot.
Oh cool! Would love to see how you are doing it at KMath if you have any links/examples on hand?
Here is @postovalovya’s AST module which uses DSL builder to create mathematical expressions and dynamically generate bytecode from them: kmath/kmath-ast at master · SciProgCentre/kmath · GitHub
Here is a popular article: Optimizing expressions by generating bytecode | Iaroslav Postovalov | Medium and here is ArXiv preprint: [2102.07924] Compilation of mathematical expressions in Kotlin.
The same expression builder could be used for more complex things, even mixing boolean expressions in numeric ones, but it is not implemented yet.
This thread is missing one of the key enablers for LINQ in C# (and other .NET languages): Anonymous Types. Consider this LINQ expression:
var query = from student in students
join department in departments on student.DepartmentID equals department.ID
select new { Name = $"{student.FirstName} {student.LastName}", DepartmentName = department.Name };
the select statement performs a projection from the bound student and department elements. This is achieved using C#'s anonymous types:
In effect in reduces the overheads of create arbitrary data classes for each result type and the gymnastics of mapping query values to that result type. This is one of C#'s winning features fwiw.
Also, it’s worth noting that LINQ is not just for SQL. It’s used for in-memory collections, remote REST services etc. Think of it as a general purpose type-safe equivalent to GraphQL.
You actually can use anonymous types with properties/functions if the type is inferred from an anonymous object, as long as it’s not internal or public:
// private val query: <anonymous object : Any>
private val query = object { val name = "firstName lastName"; val departmentName = "departmentName" }
// private fun createName(...): <anonymous object : Any>
private fun createStudent(firstName: String, lastName: String, departmentName: String) =
object { val name = "$firstName $lastName"; val departmentName = departmentName }
val queryName = query.name
val createdName = createStudent("first", "last", "dept").name
Java / the JVM will get method body reflection (a.k.a code reflection) soon. I would imagine that one of the first use cases for this functionality will be to take Java code (e.g. streams) and turn it into SQL queries.
If Kotlin followed along, then this feature could be used to convert a sequence to an SQL statement at runtime.
C# does it differently. It annotates certain methods to instruct the compiler that it should generate a runtime-readable model of the code, rather than generating the usual bytecode. This model can then be queried by linq to produce SQL. The JVM approach is more universal in the sense that it works for ANY method.
I built this: GitHub - ExoQuery/ExoQuery: Language Integrated Query for Kotlin
ExoQuery is a Language Integrated SQL Querying system for Kotlin. It is specifically for SQL databases, not Arrays but if there’s an enthusiastic reaction I can build the latter type of system as well.
Why ExoQuery?
==, if, when, and more!