Actually, I think SQL is great.\u00a0 It\u2019s one of the earliest declarative languages, handing the work of figuring out how best to get the desired result to where it should be\u2014 the computer.\u00a0 It\u2019s also so well designed that it\u2019s still in widespread use today in relationship databases, with little complaint.<\/p>\n
No, my problem is that almost all of the SQL that I\u2019m called upon to write has to live within another programming language, like Java or Python.\u00a0 Most language environments support connection to a SQL data source in some form or another.\u00a0 This generally means writing additional wrapper code, but still placing SQL in local strings.<\/p>\n
The consequence of this lack of support is that<\/p>\n
A typical query might look like this:<\/p>\n
String query = \"SELECT firstName, lastName, id FROM my_table WHERE state = ? AND category = ?\";\r\n\r\ntry {\r\n PreparedStatement stmt = connection.prepareStatement(query);\r\n stmt.setString(1, state);\r\n stmt.setInt(2, category);\r\n ResultSet rs = stmt.executeQuery();\r\n List<Record> records = new ArrayList<>();\r\n\r\n while (rs.next()) {\r\n records.append(new Record(rs.getString(1), rs.getString(2));\r\n }\r\n\r\n return records;\r\n} catch (SQLException e) {\r\n throw new RuntimeException(e);\r\n}<\/pre>\nThis is dumb.\u00a0 Dumb like burning fossil fuels to power passenger vehicles.\u00a0 Expedient once, perhaps, but on the wrong side of history.<\/p>\n
The code could<\/i> look like this:<\/p>\nreturn l.stream().filter(b-> b.hasState(state)).filter(b -> b.inCategory(category).collect(Collectors.toList());<\/pre>\nHow about that?\u00a0 If SQL queries were built into Java 8 streams, the actual SQL could be generated on the fly within the streams mechanism, allowing optimization of the number of items queried on the client side.\u00a0 The set of items being queried could then be incorporated into Java refactoring.<\/p>\n
Why stop there?\u00a0 In debugging at least, SQL errors could be handled much more intelligently.\u00a0 Actually query the schema of the database and propose solutions!\u00a0 Check that the model in use at the client is the model actually in use at the database server.<\/p>\n
jOOQ is the closest tool to actually self-writing SQL I know of.\u00a0 Still looking for other solutions.<\/p>\n","protected":false},"excerpt":{"rendered":"
Actually, I think SQL is great.\u00a0 It\u2019s one of the earliest declarative languages, handing the work of figuring out how best to get the desired result to where it should be\u2014 the computer.\u00a0 It\u2019s also so well designed that it\u2019s still in widespread use today in relationship databases, with little complaint. No, my problem is…<\/p>\n