Coming from SQL

This page compares SQL with SakaQL. It uses this small product list.

PostgreSQL

CREATE VIEW products (name, vendor, stock, price) AS
VALUES
  ('pen',  'acme', 2, 3),
  ('book', 'acme', 0, 10),
  ('lamp', 'nova', 4, 5);

SakaQL

set Product = (
  name: str
  vendor: str
  stock: num
  price: num
)

def products* Product = {
  (name: "pen",  vendor: "acme", stock: 2, price: 3)
  (name: "book", vendor: "acme", stock: 0, price: 10)
  (name: "lamp", vendor: "nova", stock: 4, price: 5)
}

Expressions, not queries or clauses

SQL

SELECT name, price * stock AS value
FROM products
WHERE stock > 0;

SakaQL

products
  if .stock > 0
  map (name: .name, value: .price * .stock)

Read the SakaQL pipeline left to right: start with products, keep those in stock, then compute a Record for each.

One expression model

Every SakaQL data expression produces a Bag, whether it holds one Value or many:

1 + 2 // {3}

products map .price // {3, 5, 10}

Empty Bags, not NULL

SakaQL has no null. Missing data is an empty Bag:

num:{} // num:{}

A Boolean is only true or false.

Correlation without subqueries

SQL

SELECT
  product.name,
  (
    SELECT COUNT(*)
    FROM products AS candidate
    WHERE candidate.vendor = product.vendor
  ) AS vendor_products
FROM products AS product;

SakaQL

products map product => (
  name: product.name
  vendor_products:
    products
    if candidate => candidate.vendor == product.vendor
    agg count(.)
)

The inner pipeline can use the outer name product to count products from the same vendor.

Reuse without CTEs

SQL

WITH in_stock AS (
  SELECT * FROM products WHERE stock > 0
)
SELECT name FROM in_stock;

SakaQL

def names_in_stock = {
  let in_stock = products if .stock > 0
  in_stock map .name
}

let names an intermediate result inside a block; def names a reusable Flow.

Use let rec for recursion, like SQL’s WITH RECURSIVE:

SQL

WITH RECURSIVE reached(id) AS (
  SELECT 1
  UNION
  SELECT edges.target FROM edges JOIN reached ON edges.source = reached.id
)
SELECT id FROM reached;

SakaQL

def edges* (num -> num) = {1 -> 2, 2 -> 3, 3 -> 1, 4 -> 5}

def reached_from_one = {
  let rec reached* num = 1 |% (reached >> edges)
  reached
}

reached_from_one // {1, 2, 3}

>> follows the edges. |% combines results without repeats. This example starts at 1 and follows edges until it finds no new Values. A recursive binding must write its full contract. It also has rules for using its own results; for example, it cannot count them with count.

Grouping is explicit

SQL

SELECT
  vendor,
  COUNT(*) AS products,
  SUM(price * stock) AS total_value
FROM products
WHERE stock > 0
GROUP BY vendor;

SakaQL

products if .stock > 0 by .vendor
  aggv (products: count(.), total_value: sum(. map .price * .stock))

by gives each product a vendor key. aggv groups equal keys and computes a summary for each group.

Types describe the dataflow

products* Product allows zero or more Values shaped like Product. Reuse the Set for another Flow:

def available_products* Product = products if .stock > 0

SakaQL can work out types from expressions, or check them against contracts you write.

One canonical language

SQL syntax varies between databases. SakaQL has one syntax and implementation.