Actually, I think SQL is great.  It’s one of the earliest declarative languages, handing the work of figuring out how best to get the desired result to where it should be— the computer.  It’s also so well designed that it’s still in widespread use today in relationship databases, with little complaint.

No, my problem is that almost all of the SQL that I’m called upon to write has to live within another programming language, like Java or Python.  Most language environments support connection to a SQL data source in some form or another.  This generally means writing additional wrapper code, but still placing SQL in local strings.

The consequence of this lack of support is that

• developer tools that can help me assure that my types and logic are correct don’t help me
• I can’t directly execute the SQL code, because it’s formatted in my source language, wrapped in quotes, and formatted incorrectly.
• I can’t easily refactor my SQL.

A typical query might look like this:

String query = "SELECT firstName, lastName, id FROM my_table WHERE state = ? AND category = ?";

try {
    PreparedStatement stmt = connection.prepareStatement(query);
    stmt.setString(1, state);
    stmt.setInt(2, category);
    ResultSet rs = stmt.executeQuery();
    List<Record> records = new ArrayList<>();

    while (rs.next()) {
        records.append(new Record(rs.getString(1), rs.getString(2));
    }

    return records;
} catch (SQLException e) {
    throw new RuntimeException(e);
}

This is dumb.  Dumb like burning fossil fuels to power passenger vehicles.  Expedient once, perhaps, but on the wrong side of history.

The code could look like this:

return l.stream().filter(b-> b.hasState(state)).filter(b -> b.inCategory(category).collect(Collectors.toList());

How about that?  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.  The set of items being queried could then be incorporated into Java refactoring.

Why stop there?  In debugging at least, SQL errors could be handled much more intelligently.  Actually query the schema of the database and propose solutions!  Check that the model in use at the client is the model actually in use at the database server.

jOOQ is the closest tool to actually self-writing SQL I know of.  Still looking for other solutions.