• Is there a saner way to write SQL queries?
  • Can an additional programming language aid in the authorship of SQL queries?
  • How can SQL be improved upon?
  • What are the problems, hang-ups, missteps people experience when writing SQL queries today?
SELECT u.user_id, count(c.campaign) as number_of_campaigns
FROM users u
LEFT JOIN campaigns c ON u.user_id = c.user_id
WHERE number_of_campaigns < 10
  • Pros
    • standard everywhere
    • documentation abound
    • portable
      • eg. no additional language requirement
  • Cons
    • order of operations doesn't match mental model
    • fragile to refactoring
      • eg. changing name of number_of_campaigns
SQL, rearranged
FROM users u
LEFT JOIN campaigns c
	ON u.user_id = c.user_id
WHERE count(c.campaign) < 10
SELECT u.user_id
  • Pros
    • order of operations more closely matches mental model
  • Cons
    • not standard SQL
SQL DSL, take #1
$users_with_10_or_more_campaigns = (new Table("users", "u"))
	->leftJoin(new Table("campaigns", "c"), "u.user_id = c.user_id")
	->select("count(c.campaign) as c")
	->where("c > 10");
  • Pros
    • order matches mental model
  • Cons
    • fragile
    • lots of string (just broke up the SQL across method calls)
SQL DSL, take #2
$number_of_campaigns = new Alias("number_of_campaigns");

$users = new Table("users", "u");
$campaigns = new Table("campaigns", "c");
$joined = new Join($users, $campaigns, ["user_id"]);

$builder = $joined

$user_ids_and_counts = $session->query($builder->asSql());
  • Pros
    • order of operations even more closely matches mental model
      • also, order can be rearranged
    • editor code highlighting without requiring special directive for SQL embedded in host language
  • Cons
    • Stuck in language
    • Another thing to learn
    • Obscures SQL, possibly leaky abstraction, another layer of indirection
  • There's a good reason SQL is popular enough and works and hasn't been changed substantially in three decades