AkurAI-Framework / site/content/docs/sql.md

Latest change fa37e2544d31a1199954a395f9e5b3ebb237b17f - feat(sql): aggregates (COUNT/SUM/AVG/MIN/MAX) + GROUP BY (gpt-5.5) by Ólafur Búi Ólafsson

# SQL

The framework is growing its own query engine — a from-scratch SQL
implementation in pure Rust, zero dependencies, like everything else here. This
page documents the first slice: the **lexer and parser**. They turn SQL text
into an abstract syntax tree (AST). Running that AST over the storage engine is
the job of the [SQL executor](/docs/sql-executor), which now executes
`CREATE TABLE`, `INSERT`, `UPDATE`, `DELETE`, and `SELECT` (joins and indexes
are still planned — see the [Roadmap](/docs/roadmap)).

## The front half of the engine

`crates/sql` is **parser-only** today. A query travels through two stages:

1. **Lexer** (`tokenize`) — splits the SQL text into a stream of position-tagged
   tokens: keywords (case-insensitive), identifiers, string and number literals,
   operators, and punctuation. Whitespace is skipped; every token remembers the
   character offset where it began, so errors can point at the exact spot.
2. **Parser** (`parse`) — a recursive-descent parser that consumes the tokens
   and builds a `Statement` AST node. Malformed input returns a positioned
   error, never a panic.

```rust
use akurai_sql::{parse, Statement};

let stmt = parse("SELECT id, name FROM users WHERE age >= 18 LIMIT 10").unwrap();
assert!(matches!(stmt, Statement::Select(_)));
```

The public API is a single function plus the AST types:

```rust
pub fn parse(sql: &str) -> Result<Statement, SqlError>;
```

## Supported subset

Five statement kinds are parsed today:

```sql
-- Query
SELECT id, name FROM users
WHERE age >= 18 AND (active = 1 OR role = 'admin')
ORDER BY name DESC
LIMIT 10;

SELECT dept, COUNT(*), SUM(score), AVG(score), MIN(name), MAX(score)
FROM users
WHERE active = 1
GROUP BY dept
ORDER BY dept ASC
LIMIT 5;

-- Insert
INSERT INTO users (id, name) VALUES (1, 'Ann');

-- Update
UPDATE users SET name = 'Anna', active = 1 WHERE id = 1;

-- Delete
DELETE FROM users WHERE active = 0;

-- Create
CREATE TABLE users (id INTEGER NOT NULL, name TEXT, score REAL);
```

- **SELECT** — `*`, a comma-separated column list, or aggregate calls
  (`COUNT(*)`, `COUNT(col)`, `SUM(col)`, `AVG(col)`, `MIN(col)`, `MAX(col)`), a
  single `FROM` table, and optional `WHERE`, `GROUP BY <col>`,
  `ORDER BY <col> [ASC|DESC]` (defaults to `ASC`), and `LIMIT <n>` clauses.
- **INSERT** — an explicit column list and a matching `VALUES` list of literals.
- **UPDATE** — a target table, a `SET` list of `<col> = <value>` assignments, and
  an optional `WHERE` clause.
- **DELETE** — `DELETE FROM <table>` with an optional `WHERE` clause.
- **CREATE TABLE** — column definitions of `<name> <type> [NOT NULL]`, where the
  type is `INTEGER`, `TEXT`, or `REAL`.

### WHERE expressions

`WHERE` is a real expression tree, not a flat list of conditions:

- **Comparisons:** `= != <> < <= > >=` between a column and a literal (in either
  order).
- **Boolean combinators:** `AND`, `OR`, and `NOT`, with parentheses for grouping.
- **Precedence:** `AND` binds tighter than `OR`. So `a=1 AND b=2 OR c=3` parses
  as `(a=1 AND b=2) OR c=3`. Parentheses override this.

Literals are `NULL`, integers, floats, and single-quoted strings. Inside a
string, two single quotes (`''`) are an escaped quote: `'it''s ok'` is the value
`it's ok`.

## The AST shape

`parse` returns a `Statement`:

```rust
pub enum Statement {
    Select(SelectStmt),
    Insert(InsertStmt),
    Update(UpdateStmt),
    Delete(DeleteStmt),
    CreateTable(CreateTableStmt),
}
```

The key types underneath it:

- `SelectStmt { columns, table, where_clause, group_by, order_by, limit }`,
  where `columns` is a `Vec<SelectColumn>` (`All` for `*`, `Named(String)`, or
  `Aggregate(AggregateExpr)`), `where_clause` is an `Option<Expr>`, `group_by`
  is an `Option<String>`, `order_by` is an `Option<OrderBy>`, and `limit` is an
  `Option<i64>`.
- `AggregateExpr { func, arg }`, where `func` is `Count | Sum | Avg | Min | Max`
  and `arg` is either `All` for `COUNT(*)` or `Column(String)`.
- `InsertStmt { table, columns: Vec<String>, values: Vec<Literal> }`.
- `UpdateStmt { table, assignments: Vec<(String, Literal)>, where_clause: Option<Expr> }`,
  where each assignment is a `<column, value>` pair from the `SET` list.
- `DeleteStmt { table, where_clause: Option<Expr> }`.
- `CreateTableStmt { table, columns: Vec<ColumnDef> }`, where each `ColumnDef`
  is `{ name, ty: DataType, not_null: bool }`.
- `Expr` is the `WHERE` tree: `Literal`, `Column`, `Comparison { left, op, right }`,
  `And`, `Or`, and `Not`.
- `Literal` is `Null | Int(i64) | Float(f64) | Str(String)`.

## Errors

Any lex or parse failure is a `SqlError { message, position }` — a clear message
plus the 0-based character offset in the source. The parser is total: bad input
yields an `Err`, never a panic. Missing `FROM`, an unclosed paren, an unknown
statement keyword, or a stray character all surface as positioned errors.

## The executor

This crate stops at the AST. The [SQL executor](/docs/sql-executor) walks the
parsed `Statement` against the [storage engine](/docs/storage) B+tree — a catalog
of tables and columns, evaluating `WHERE` over each row, applying aggregate
projection and single-column `GROUP BY`, then `ORDER BY` and `LIMIT`, and
materializing `INSERT`/`UPDATE`/`DELETE`/`SELECT`/`CREATE TABLE`. A query planner,
joins, and indexes are still ahead (see the [Roadmap](/docs/roadmap)).