I was thinking about this exact problem, one year ago, and even started to sketch out what a better "meta-sql" would look like. However, i did lose myself in the weeds.
Notably :
- the metasql has to be embedded in the host language, the way normal sql is. This means, it also has to handle in/out bound parameters from the host. I guess this could be handled by a codegen step, where the functions of the host language are generated based on metasql. The alternative is to provide a runtime for each host language.. So this one is out
- i wanted a way to compose "columns sets". This is because you might want for example, to indicate that the query returns "all the columns in this table, minus thia and that". Or, for example "the union of this column set union in-column set". This feature looks like adding another meta-language on top of the metasql already lol I stopped at that point because i could find a graceful/looknice solution, let alone implement it.
Sounds a bit like the Active Record/Repository pattern? The "meta sql" in that case is a natural language DSL, e.g.
@Repository interface Employees : CrudRepository<Employee, Integer> {
fun findBySalaryGreaterThanForUpdate(salary: Int): List<Employee>
data class NameAndSalary(val name: String, val salary: Int)
fun findByDepartment(department: String): List<NameAndSalary>
}
The compiler reads the method name and writes the equivalent SQL query at compile time. The custom data class acts as a projection. This sort of thing can be found in Micronaut Data, or Spring or similar.
I implemented a data access permissions layer in SQL stored procs with setbased logic with allow/deny and include/exclude functionality and some 'security rules composability'.
Implementing it definitely took some of the hardest thinking at that job. But it's possible.
Notably :
- the metasql has to be embedded in the host language, the way normal sql is. This means, it also has to handle in/out bound parameters from the host. I guess this could be handled by a codegen step, where the functions of the host language are generated based on metasql. The alternative is to provide a runtime for each host language.. So this one is out
- i wanted a way to compose "columns sets". This is because you might want for example, to indicate that the query returns "all the columns in this table, minus thia and that". Or, for example "the union of this column set union in-column set". This feature looks like adding another meta-language on top of the metasql already lol I stopped at that point because i could find a graceful/looknice solution, let alone implement it.