Pitch: sql-kit/fluent-kit Function Builder Support

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!

Terms of Service

Privacy Policy

Cookie Policy