SQL Queries

# Build the server with DataFusion SQL support
cargo build -p topgun-server --features datafusion

// SqlQueryPayload — sent by the client
{
  "type": "SQL_QUERY",
  "sql": "SELECT name, price FROM products WHERE price > 50 ORDER BY price DESC",
  "queryId": "q-abc123"
}

// SqlQueryRespPayload — returned by the server
{
  "type": "SQL_QUERY_RESP",
  "queryId": "q-abc123",
  "columns": ["name", "price"],
  "rows": [
    ["Widget Pro", 99.99],
    ["Widget Basic", 59.99]
  ],
  "error": null
}

import { WebSocket } from 'ws';
import { encode, decode } from '@msgpack/msgpack';

const ws = new WebSocket('ws://localhost:8080');

ws.on('open', () => {
  const payload = {
    type: 'SQL_QUERY',
    sql: 'SELECT name, price FROM products WHERE price > 50 ORDER BY price DESC',
    queryId: 'q-abc123',
  };
  ws.send(encode(payload));
});

ws.on('message', (data) => {
  const resp = decode(data);
  if (resp.type === 'SQL_QUERY_RESP' && resp.queryId === 'q-abc123') {
    if (resp.error) {
      console.error('SQL error:', resp.error);
    } else {
      // resp.columns: ['name', 'price']
      // resp.rows: [['Widget Pro', 99.99], ['Widget Basic', 59.99]]
      const results = resp.rows.map((row) =>
        Object.fromEntries(resp.columns.map((col, i) => [col, row[i]]))
      );
      console.log('Query results:', results);
    }
  }
});

-- Basic column selection
SELECT name, price, category FROM products

-- Wildcard: includes the synthetic _key column plus all schema fields
SELECT * FROM products

-- Filtering with comparison operators
SELECT name, price FROM products WHERE price > 100

-- Multiple conditions
SELECT name, price FROM products
WHERE price >= 20 AND price <= 200 AND inStock = true

-- NULL checks
SELECT name FROM products WHERE description IS NOT NULL

-- Pattern matching
SELECT name FROM products WHERE name LIKE '%wireless%'

-- IN list
SELECT name, category FROM products WHERE category IN ('electronics', 'accessories')

-- Sorting
SELECT name, price FROM products ORDER BY price ASC

-- Limit and offset
SELECT name, price FROM products ORDER BY price DESC LIMIT 10 OFFSET 20

-- DISTINCT values
SELECT DISTINCT category FROM products

-- Column aliases and arithmetic
SELECT name, price, price * 1.1 AS price_with_tax FROM products

-- Aggregation
SELECT category, COUNT(*) AS total, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY total DESC

-- Given a 'products' map with fields: name (string), price (number),
-- category (string), inStock (boolean)

-- Find all in-stock electronics under $150, newest first
SELECT _key, name, price
FROM products
WHERE category = 'electronics'
  AND inStock = true
  AND price < 150
ORDER BY name ASC

-- Given a 'orders' map with fields: customerId (string), total (number),
-- status (string), createdAt (number / timestamp millis)

-- Revenue summary per customer, only completed orders
SELECT customerId,
       COUNT(*)   AS order_count,
       SUM(total) AS total_revenue,
       AVG(total) AS avg_order_value,
       MAX(total) AS largest_order
FROM orders
WHERE status = 'completed'
GROUP BY customerId
ORDER BY total_revenue DESC
LIMIT 20

-- Given a 'users' map with fields: country (string), age (number),
-- plan (string), active (boolean)

-- Active users by country, filtered to paying plans, sorted by count
SELECT country,
       plan,
       COUNT(*)   AS user_count,
       AVG(age)   AS avg_age
FROM users
WHERE active = true
  AND plan IN ('pro', 'enterprise')
GROUP BY country, plan
HAVING COUNT(*) > 5
ORDER BY user_count DESC