Getting started

Gelite compiles a Gel-like schema and query language to SQLite. The current CLI can plan an initial schema, apply initial and supported append-only schemas, and compile or execute queries through the REPL.

Apply the example schema

From the repository root:

cargo run -p gelite-cli -- schema apply examples/organization.geli --database organization.db

This creates a SQLite database for the organization example. Re-running the command with a supported append-only schema preserves existing rows and records the new schema version. Identical schemas are a no-op.

Open the REPL

cargo run -p gelite-cli -- repl --database organization.db

Enter each query without a trailing semicolon. Regular Enter continues while braces are unbalanced. Alt+Enter inserts a newline without submitting input.

Use --schema instead when only compilation and rendered SQL are needed:

cargo run -p gelite-cli -- repl --schema examples/organization.geli --debug \
  'select Employee { name } filter .active = true'

The --schema mode does not open a database or execute the query. The --database mode loads the catalog stored in the database and executes the current select, insert, update, and delete subsets, including explicit multi-link add/remove updates.

Continue with the Examples overview and choose one of the three runnable domains.

Build this documentation

Install the same mdBook version used by CI, then run:

cargo install mdbook --version 0.5.4 --locked
mdbook serve docs

The Markdown under docs/src is also readable directly on GitHub.

Examples

The examples are small adaptations of familiar SQL teaching domains. Together they cover the current Gelite schema, query, and execution surface without requiring one large application model.

Character names are drawn from Magical Girl Witch Trials. Roles, departments, products, albums, and other values are fictional, spoiler-free sample data made for these examples.

Organization

This EMP/DEPT-style example stores each employee’s required department link and exposes its readonly inverse as Department.employees. It also includes an optional self-referencing manager link.

type Department {
  required unique code: str
  required name: str
  multi link employees: Employee inverse department
}

type Employee {
  required unique employee_no: str
  required name: str
  required title: str
  required salary: int64
  required active: bool
  required hired_at: datetime
  required link department: Department
  link manager: Employee
}

Create data

Apply the schema to a new database:

cargo run -p gelite-cli -- schema apply examples/organization.geli --database organization.db

Open gelite repl --database organization.db, then insert two departments:

insert Department { code := "INVESTIGATION", name := "Investigation" }

insert Department { code := "ARCHIVE", name := "Records Archive" }

Insert the manager first. Link assignments look up existing objects through their unique fields, so generated IDs do not need to be copied from earlier commands:

insert Employee {
  employee_no := "MG-667",
  name := "Sheri Tachibana",
  title := "Chief Investigator",
  salary := 92000,
  active := true,
  hired_at := "2026-04-01T09:00:00Z",
  department := (
    select Department { id }
    filter .code = "INVESTIGATION"
  )
}

insert Employee {
  employee_no := "MG-001",
  name := "Emma Sakuraba",
  title := "Investigator",
  salary := 68000,
  active := true,
  hired_at := "2026-04-15T09:00:00Z",
  department := (
    select Department { id }
    filter .code = "INVESTIGATION"
  ),
  manager := (
    select Employee { id }
    filter .employee_no = "MG-667"
  )
}

insert Employee {
  employee_no := "MG-002",
  name := "Hiro Nikaido",
  title := "Archivist",
  salary := 64000,
  active := true,
  hired_at := "2026-05-01T09:00:00Z",
  department := (
    select Department { id }
    filter .code = "ARCHIVE"
  ),
  manager := null
}

Department.employees is derived from the stored Employee.department link. No separate population or synchronization step is needed. Assignments to the inverse field are rejected; change the stored employee link instead.

Query a multi link

select Department {
  code,
  name,
  employees: {
    employee_no,
    name,
    title,
    manager: { name }
  }
}
order by .code asc

The REPL renders employees as a collection of nested objects. A department without linked employees receives []. Multi-link collection order is not defined by the language.

Filter through the inverse link

Select departments with at least one employee earning 90000 or more:

select Department { code, name, employees: { name, salary } }
filter .employees.salary >= 90000
order by .code asc

The filter selects departments; the returned employees collection still contains all employees in each selected department. Each multi-path comparison has its own existence scope. Two comparisons combined with and may be satisfied by different employees. Use an explicit scope when one employee must satisfy both conditions:

select Department { code, name, employees: { name, salary } }
filter exists .employees {
  .name = "Sheri Tachibana" and .salary >= 90000
}
order by .code asc

Inside the braces, paths refer to one employee. The returned collection still contains all employees in the selected department. not exists .employees { .salary >= 90000 } also selects departments with no employees, whereas an existence predicate with a negated body requires at least one employee.

Change the stored relationship

Move the archive employee to the investigation department:

update Employee
filter .employee_no = "MG-002"
set {
  department := (
    select Department { id }
    filter .code = "INVESTIGATION"
  )
}

select Department { code, employees: { employee_no, name } }
order by .code asc

The archive department now returns [], and the investigation department returns three employees. No inverse-side update is needed.

Query nested links

select Employee {
  employee_no,
  name,
  title,
  department: {
    code,
    name
  },
  manager: {
    name,
    title
  }
}
filter .active = true
  and .department.id in (
    select Department { id }
    filter .code in ["INVESTIGATION", "ARCHIVE"]
  )
  and .employee_no not in ["MG-999"]
order by .salary desc, .name asc
limit 10
offset 0

The nested select finds matching department identities in its own query scope. The employee-number condition keeps a literal-list membership example beside it.

Find top-level employees whose optional manager link is absent:

select Employee { employee_no, name, title }
filter .manager.id = null
order by .name asc

The REPL preserves the top-level field order and renders selected links as nested objects. A missing optional link is NULL:

employee_no\tname\ttitle\tdepartment\tmanager
MG-667\tSheri Tachibana\tChief Investigator\t{code: INVESTIGATION, name: Investigation}\tNULL

Store

This Northwind-style example models a purchase order as a header plus explicit line items. OrderItem represents the many-to-many relationship between purchase orders and products while keeping mutations executable with the current single-link syntax.

type Customer {
  required unique email: str
  required name: str
  required tier: str
}

type Product {
  required unique sku: str
  required name: str
  required price: float64
  required active: bool
}

type PurchaseOrder {
  required unique order_no: str
  required status: str
  required ordered_at: datetime
  required link customer: Customer
}

type OrderItem {
  required quantity: int64
  required unit_price: float64
  required link purchase: PurchaseOrder
  required link product: Product
}

Create data

cargo run -p gelite-cli -- schema apply examples/store.geli --database store.db
cargo run -p gelite-cli -- repl --database store.db

Insert a customer and two products:

insert Customer {
  email := "[email protected]",
  name := "Margo Hosho",
  tier := "gold"
}

insert Product {
  sku := "CASE-NOTEBOOK",
  name := "Detective Notebook",
  price := 12.5,
  active := true
}

insert Product {
  sku := "OWL-PIN",
  name := "Owl Enamel Pin",
  price := 8.0,
  active := true
}

Create the purchase order and its items in one interactive transaction. Each link assignment finds the related object through a unique business key:

start transaction

insert PurchaseOrder {
  order_no := "TRIAL-0001",
  status := "paid",
  ordered_at := "2026-08-03T10:00:00Z",
  customer := (
    select Customer { id }
    filter .email = "[email protected]"
  )
}

insert OrderItem {
  quantity := 2,
  unit_price := 12.5,
  purchase := (
    select PurchaseOrder { id }
    filter .order_no = "TRIAL-0001"
  ),
  product := (
    select Product { id }
    filter .sku = "CASE-NOTEBOOK"
  )
}

insert OrderItem {
  quantity := 1,
  unit_price := 8.0,
  purchase := (
    select PurchaseOrder { id }
    filter .order_no = "TRIAL-0001"
  ),
  product := (
    select Product { id }
    filter .sku = "OWL-PIN"
  )
}

commit

Query line totals

select OrderItem {
  line_total := f64(.quantity) * .unit_price,
  product: {
    sku,
    name
  },
  purchase: {
    order_no,
    status,
    customer: {
      name,
      tier
    }
  }
}
filter .purchase.id in (
  select PurchaseOrder { id }
  filter .status in ["paid", "shipped"]
)
  and .product.active = true
order by f64(.quantity) * .unit_price desc

The membership select finds purchase identities by status without requiring the caller to collect IDs first. Computed projections are executed by SQLite. Their current REPL column labels are generated implementation aliases rather than the logical output names.

Music catalog

This Chinook-style example demonstrates several consecutive single links and an explicit playlist association object.

type Artist {
  required unique name: str
  country: str
}

type Album {
  required unique title: str
  required release_year: int64
  required link artist: Artist
}

type Track {
  required unique title: str
  required track_no: int64
  required duration_seconds: int64
  required genre: str
  required link album: Album
}

type Playlist {
  required unique name: str
}

type PlaylistTrack {
  required position: int64
  required link playlist: Playlist
  required link track: Track
}

Create data

cargo run -p gelite-cli -- schema apply examples/music.geli --database music.db
cargo run -p gelite-cli -- repl --database music.db

Insert an artist, album, two tracks, and a playlist in that order. Links use unique names and titles instead of copied generated IDs:

insert Artist { name := "Coco Sawatari", country := "Japan" }

insert Album {
  title := "Midnight Testimony",
  release_year := 2026,
  artist := (
    select Artist { id }
    filter .name = "Coco Sawatari"
  )
}

insert Track {
  title := "First Deduction",
  track_no := 1,
  duration_seconds := 214,
  genre := "mystery pop",
  album := (
    select Album { id }
    filter .title = "Midnight Testimony"
  )
}

insert Track {
  title := "After the Bell",
  track_no := 2,
  duration_seconds := 188,
  genre := "mystery pop",
  album := (
    select Album { id }
    filter .title = "Midnight Testimony"
  )
}

insert Playlist { name := "Sheri's Case Notes" }

insert PlaylistTrack {
  position := 1,
  playlist := (
    select Playlist { id }
    filter .name = "Sheri's Case Notes"
  ),
  track := (
    select Track { id }
    filter .title = "First Deduction"
  )
}

Query through the catalog

select PlaylistTrack {
  position,
  track: {
    label := concat(.title, " / ", .album.title),
    duration_seconds,
    genre,
    album: {
      title,
      artist: {
        name,
        country
      }
    }
  },
  playlist: {
    name
  }
}
filter .playlist.name = "Sheri's Case Notes"
  and .track.duration_seconds >= 180
order by .position asc
limit 20
offset 0

The filter and ordering traverse stored single links, and the selected links are reconstructed as nested objects at runtime.

CLI reference

The current executable command paths are:

gelite schema plan <schema.geli>
gelite schema apply <schema.geli> --database <app.db>
gelite query plan <query.geliql> --schema <schema.geli>
gelite query run <query.geliql> --database <app.db>
gelite repl --schema <schema.geli> [--debug] [QUERY]...
gelite repl --database <app.db> [--debug] [QUERY]...

When running from this repository, prefix each command with cargo run -p gelite-cli --.

Schema modes

schema plan prints initial SQLite DDL and metadata bind values without opening a database. schema apply creates the initial schema in a new database or applies supported append-only additions to an existing Gelite database.

Existing databases are verified against their latest stored checksum and logical catalog before migration planning. New objects, nullable scalar fields, optional single links, and multi links are supported. An identical schema is a no-op; unsupported changes fail before migration DDL executes. Successful non-empty migrations append one schema-version row in the same transaction as their DDL and catalog metadata.

Append-only migration example

Start with schema.geli:

type User {
  required name: str
}

Apply it to a new database:

cargo run -p gelite-cli -- schema apply schema.geli --database app.db

Then extend the same file with supported additions:

type User {
  required name: str
  nickname: str
  link manager: User
  multi link projects: Project
}

type Project {
  required title: str
}

Run the same command again. Gelite preserves existing User rows, adds the nullable nickname and manager storage, creates the Project and multi-link tables, and records the complete updated schema as the next version. Running the command once more without changing the schema is a no-op.

Query modes

query plan reads query and schema files, compiles the complete script, then prints each data statement’s rendered SQL and bind values plus transaction SQL without opening a database or executing the query. Multi-link selects also report how many follow-up plans may render batched queries. The number of query batches is determined after parent identities are known at execution time.

query run loads the schema catalog from an existing Gelite database, validates the complete script, then executes its statements in order on one connection. It prints clear statement boundaries, select columns and rows, the generated UUID for an insert, affected_rows for an update or delete, and OK for a transaction command. It does not create a missing database.

Query scripts use semicolons as statement terminators and may contain multiline data statements plus start transaction, commit, and rollback. Semicolons inside strings are ignored. A single data statement without a semicolon remains supported. Nested or unmatched transaction commands and scripts ending inside a transaction are rejected before execution. A runtime failure rolls back the active transaction while preserving earlier autocommit statements.

REPL modes

repl --schema compiles queries without executing them. repl --database loads the schema catalog from the database and executes supported data queries.

Insert compilation generates a fresh UUID v4 for the implicit id bind value. Its rendered bind output changes between runs and is not suitable for stable snapshot comparison or use as a reproducible plan artifact.

With no query argument, either mode starts an interactive REPL. Enter each statement without a semicolon. Regular Enter continues while braces are unbalanced; Alt+Enter inserts a newline without submitting.

Database-backed interactive sessions also accept these inputs:

start transaction
commit
rollback

Transaction commands must be entered separately in the interactive REPL and are not accepted in compile-only REPL sessions. Query script files may include them.

Current limitations

The examples document behavior that exists in the current pipeline. The main limitations are:

• The REPL reconstructs selected single links as nested objects and selected multi links as collections while keeping top-level rows tab-separated. Multi-link collection order is unspecified.
• Declared inverse links are readonly and always multi. Stored forward links own the foreign keys or join tables; inverse links create no duplicate storage.
• Filters support a multi path compared with a literal using independent existence conditions. Same-target predicate scopes are deferred to #68. Multi paths remain unsupported in ordering, computed values, arithmetic, function arguments, membership operands, and path-to-path comparisons.
• gelite repl --schema compiles and renders queries but cannot execute them. Use --debug to inspect SQL and bind values.
• gelite repl --database executes select, insert, update, and delete. It does not provide a JSON result format.
• Schema application supports new objects, nullable scalar fields, optional single links, and multi links. Removal, rename inference, required or unique additions to existing objects, table rebuilds, backfills, and concurrent or online migration coordination are not implemented.
• Inserts and regular updates accept scalar literals, single-link ID strings, and single-link selects narrowed by an implicit id or declared unique scalar field. Multi-link updates support one += or -= operation per statement with a target select that projects implicit id; replacement, literals, and mixed regular assignments are not supported. Membership filters also accept uncorrelated selects that project one compatible required scalar field. Nested inserts and subqueries in other expression positions are not implemented.
• Composite unique constraints are not available in the current schema syntax. Association objects such as OrderItem and PlaylistTrack rely on the application to reject duplicate link pairs when needed.
• Update and delete filters are optional. The CLI does not ask for confirmation before an unfiltered mutation.
• Transaction commands work only in an interactive database-backed REPL. Enter start transaction, commit, or rollback as separate inputs.

The authoritative syntax and semantic contracts remain in spec/schema.md and spec/query.md.