PHP SQL DSL

Purpose

• 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?

SQL

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
	->select(
		$campaigns->column("campaign_id")->count(),
		$number_of_campaigns
	)
	->where($number_of_campaigns->greaterThan(10))
	->select($users->column("user_id"));

$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

Reflection

Assumptions

• There's a good reason SQL is popular enough and works and hasn't been changed substantially in three decades