Note: Doobie’s row->case class mapping is not based on case class property name

Instead it’s based on position. To be specific, the first column in the select sql will be mapped to the first property of the case class.

Here is an example:

Let’s say in the db there is a table called foo_bar

foo	bar
fff	bbb

case class FooBar(foo: String, bar: String)

sql"select Top 1 foo, bar from foo_bar".query[FooBar].unique   //  The value will be  FooBar(foo = 'fff', bar = 'bbb')

sql"select Top 1 bar, foo from foo_bar".query[FooBar].unique   //  The value will be  FooBar(foo = 'bbb', bar = 'fff') !!! 

This is not desirable. But the author of Doobie won’t provide a column-based solution.

To workaround it, you can use raw ResultSet. See this