Would like to pitch the idea for a function builder DSL for generating SQL queries. Proposal would have 2 different DSL's, one string-ish for sql-kit and one stronger typed using property wrappers and key paths for fluent-kit. I find myself often having to write queries that normal fluent cant handle, and I dont like the idea of always doing:
(req.db as! SQLDatabase)
.raw("SELCT 'probably will typo select/as/from' AAS message FRM table")
.all(decoding: String.self)
Taking some influence from the great SwifQL I think we can enhance this and make a nice raw (but not as error prone) query generation tool using function builders that sort of reads like regular sql.
Ive sorta started an initial implementation for sql-kit (fork with implementation & tests) and it could potentially look something like this:
SQL {
Select {
SQLColumn("col4", table: "table3")
}
From {
SQLIdentifier("table1")
}
Join(SQLIdentifier("table2")) {
SQLRaw("`table2`.`col1`=`table1`.`col1`")
SQLRaw("`table2`.`col2`=`table1`.`col2`")
}
Join(SQLIdentifier("table3"), method: SQLJoinMethod.left) {
SQLRaw("`table3`.`col3`=`table2`.`col3`")
}
Where {
SQLQueryString("cool='hello'")
SQLQueryString("beans=\(bind: "nice")")
And {
SQLQueryString("cool='hello'")
SQLQueryString("beans=\(bind: "nice")")
}
Or {
SQLQueryString("cool='hello'")
SQLQueryString("beans=\(bind: "nice")")
}
}
GroupBy {
SQLColumn("col1", table: "table1")
SQLColumn("col2", table: "table2")
}
OrderBy {
SQLOrderBy(expression: SQLColumn("col1", table: "table1"), direction: SQLDirection.ascending)
}
}
This contrived example would then generate:
var serializer = SQLSerializer(database: db)
sql.serialize(to: &serializer)
print(serializer.sql) // no line breaks normally
/*
SELECT `table3`.`col4`
FROM `table1`
INNER JOIN `table2` ON `table2`.`col1`=`table1`.`col1` AND `table2`.`col2`=`table1`.`col2`
LEFT JOIN `table3` ON `table3`.`col3`=`table2`.`col3`
WHERE cool='hello' AND beans=? AND (cool='hello' AND beans=?) AND (cool='hello' OR beans=?)
GROUP BY `table1`.`col1`, `table2`.`col2` ORDER BY `table1`.`col1` ASC
*/
For the fluent-kit side of things, I think we could do something like this package I started: PSQLKit (Postgres specific) and borrowing from SwifQL which could look like something like this:
let ce = CompanyEmployee.as("ce")
let jce = JobCompanyEmployee.as("jce")
let t = Timesheet.as("t")
let tl = TimesheetLine.as("tl")
let tle = TimesheetLineEmployee.as("tle")
let tlec = TimesheetLineEmployeeCost.as("tlec")
QUERY {
SELECT {
t~\.$id
t~\.$date
t~\.$status
ce~\.$code
Fn.sum(tlec~\.$hours) => "hours"
}
FROM { t.table }
JOIN(tl.table) { tl~\.$timesheetId *==* t~\.$id }
JOIN(tle.table) { tle~\.$timesheetLineId *==* tl~\.$id }
JOIN(tlec.table) { tlec~\.$timesheetLineEmployeeId *==* tle~\.$id }
JOIN(jce.table) { tle~\.$jobCompanyEmployeeId *==* jce~\.$id }
JOIN(ce.table) { ce~\.$id *==* jce~\.$companyEmployeeId }
WHERE {
ce~\.$employeeId *==* employeeId // couldn't get == overload to work, added custom *==*
t~\.$date *><* PSQLTouple(start.psqlDate, end.psqlDate) // BETWEEN
t~\.$status *<>* pending.includePendingStatus // IN
}
GROUPBY {
t~\.$id
t~\.$date
t~\.$status
ce~\.$code
}
ORDERBY {
t~\.$date
}
}
I implemented my own @Column property wrapper to hold the column name but I think we could use all the fluent @ID/@Field/etc wrappers for this purpose.
Hopefully there is some shared interest in this and together we can make a rock solid implementation. Additionally, I dont think this would be a breaking addition at all and would be purely additive.
Excited to see what you all think!