← mskql

Getting Started

Build, connect, query—in under a minute. Ten queries walk you from CREATE TABLE with foreign keys to recursive CTEs, window functions, and upserts. All through a standard psql connection.

Prerequisites

A C11 compiler and make. Nothing else.

make                                          # build
./build/mskql                                 # start server on port 5433
psql -h 127.0.0.1 -p 5433 -U test -d mskql   # connect

Set MSKQL_PORT to listen on a different port:

MSKQL_PORT=5444 ./build/mskql

A small company in ten queries

Step 1 — Schema. Two tables with constraints, a serial primary key, and a foreign key.

CREATE TABLE departments (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL UNIQUE
);

CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    dept_id    INT REFERENCES departments(id),
    salary     INT DEFAULT 0,
    hired      DATE
);

INSERT INTO departments (name) VALUES
    ('Engineering'), ('Design'), ('Sales');

INSERT INTO employees (name, dept_id, salary, hired) VALUES
    ('Alice',   1, 130000, '2022-03-15'),
    ('Bob',     1,  95000, '2023-01-10'),
    ('Carol',   2, 110000, '2021-07-01'),
    ('Dave',    2,  90000, '2024-02-20'),
    ('Eve',     3, 105000, '2023-06-01'),
    ('Frank', NULL,  80000, '2024-11-01');

Step 2 — Filter. Who earns above six figures?

SELECT name, salary
FROM employees
WHERE salary > 100000
ORDER BY salary DESC;

Step 3 — LEFT JOIN + COALESCE. Show every employee’s department, including unassigned.

SELECT e.name,
       COALESCE(d.name, 'Unassigned')
         AS department
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.id;

Step 4 — Aggregation + HAVING. Average salary per department, filtering to departments above 100k.

SELECT d.name AS department,
       COUNT(*)    AS headcount,
       AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.name
HAVING AVG(e.salary) > 100000;

Step 5 — Window function. Rank employees within each department by salary.

SELECT e.name, d.name AS dept,
       e.salary,
       RANK() OVER (
         PARTITION BY d.name
         ORDER BY e.salary DESC
       ) AS rnk
FROM employees e
JOIN departments d
  ON e.dept_id = d.id;

Step 6 — Upsert. Insert-or-update with ON CONFLICT DO UPDATE.

INSERT INTO departments (id, name)
VALUES (2, 'Product Design')
ON CONFLICT (id) DO UPDATE
  SET name = 'Product Design';

SELECT * FROM departments
ORDER BY id;

Step 7 — View. Create a reusable view for department summaries.

CREATE VIEW dept_summary AS
SELECT d.name AS department,
       COUNT(*) AS headcount,
       AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY d.name;

SELECT * FROM dept_summary
ORDER BY avg_salary DESC;

Step 8 — Sequence + UUID. Use a sequence and generate a UUID.

CREATE SEQUENCE invoice_seq
  START WITH 1000;

SELECT NEXTVAL('invoice_seq') AS inv,
       GEN_RANDOM_UUID()      AS ref;

Step 9 — Window frame. Running salary total within each department.

SELECT e.name, d.name AS dept,
       e.salary,
       SUM(e.salary) OVER (
         PARTITION BY d.name
         ORDER BY e.salary
         ROWS BETWEEN UNBOUNDED
           PRECEDING AND CURRENT ROW
       ) AS running_total
FROM employees e
JOIN departments d
  ON e.dept_id = d.id;

Step 10 — Recursive CTE. Build a running headcount at each hire date using a recursive series.

WITH RECURSIVE hire_dates AS (
    SELECT MIN(hired) AS dt
    FROM employees
    UNION ALL
    SELECT (SELECT MIN(e.hired)
            FROM employees e
            WHERE e.hired > hire_dates.dt)
    FROM hire_dates
    WHERE dt < (SELECT MAX(hired)
                FROM employees)
)
SELECT dt AS hire_date,
       (SELECT COUNT(*)
        FROM employees e
        WHERE e.hired <= dt)
          AS headcount
FROM hire_dates
ORDER BY dt;

Next steps

SQL Reference  ·  Tutorials  ·  Architecture  ·  Try it in the browser