This question is from an architectural perspective.
Encapsulating the domain business logic in a single place is a software engineering best practice. However, the logic often percolates to the database layer in the form of SQLs, both simple and complex.
For the domain layer, Scala 3 case classes provide a good foundation for concisely defining the domain. In the Scala space, Slick and Doobie provide functional libraries to connect to relational databases.
By using functional libraries like Slick, the dispersion of business logic to the database layer can be prevented to an extent, by writing the SQL logic in a functionally equivalent manner. I am exploring Slick in preference to Doobie, as Slick provides for not using SQL at all.
However, I want to take business logic encapsulation one step further by having middleware handle the database in whatever manner it pleases. In other words, the domain cases classes would be the basis for the all the business logic. The middleware would use defaults for table and column names etc. It could use primary keys based on a PrimaryKey type in the domain layer. The advantage of this approach, in my opinion, is that the domain layer is truly independent and can be ported to any software framework using middleware that provides the hooks for the domain layer to connect to the database. This way the database becomes a black box to the domain layer.
To accomplish the database as black box approach in Slick, the references to table names, columns and types has to use defaults, and be under the hood. To put it concretely, can Table[T] implementation where T is a case class, be automatically generated at compile time using Scala 3 (because Scala 3 has the metadata available for case classes)? However this needs to be done for nested classes also.
Also, even if the above is feasible, are there fundamental issues, in terms of design and feasibility with this approach of treating the database as a black box?
Using Show[T] as a proof of concept, I was able to automatically derive instances for case classes, and types like Option[Int] and Seq[Long]. However, there is a long way to go, and it is like reinventing the wheel, when database libraries like Slick already exist. Hence the questions.