NextSQL Language Reference

NextSQL is a modern SQL-compatible DSL with type safety. This reference covers all current features. Always refer to nextsql-core/src/nextsql.pest as the authoritative grammar source.

Query Syntax

Queries are declared with query and use method-chained clauses starting from from().

nsql
1query findUsers($minAge: i32, $limit: i32?) {
2  from(users)
3  .where(users.age >= $minAge)
4  .select(users.id, users.name, users.email)
5}

Clauses (chained with .)

• from(table) - FROM clause (required). Supports explicit joins via method calls on the table.
• .where(condition) - WHERE filter.
• .select(expr, ...) - SELECT columns. Supports alias: expr for aliased columns and table.* for all columns.
• .distinct() - SELECT DISTINCT.
• .groupBy(expr, ...) - GROUP BY.
• .having(condition) - HAVING (used after groupBy).
• .aggregate(alias: expr, ...) - Named aggregation columns.
• .orderBy(col.asc(), col.desc()) - ORDER BY with direction methods.
• .limit(n) - LIMIT.
• .offset(n) - OFFSET.
• .when(condition, clause) - Conditional clause application (dynamic queries).
• .union(from(...).select(...)) - UNION.
• .unionAll(from(...).select(...)) - UNION ALL.
• .forUpdate() - SELECT FOR UPDATE.

Explicit JOINs

JOINs are expressed as method calls on tables inside from():

nsql
1from(table1
2  .innerJoin(table2, table1.id == table2.table1_id)
3  .leftJoin(table3, table2.id == table3.table2_id)
4  .rightJoin(table4, table3.id == table4.table3_id)
5  .fullOuterJoin(table5, table4.id == table5.table4_id)
6  .crossJoin(table6))

WITH (CTE)

Common Table Expressions are defined with with before the main query body:

nsql
1query example() {
2  with activePosts = {
3    from(posts).where(posts.is_active == true).select(posts.*)
4  }
5  from(activePosts)
6  .select(activePosts.title)
7}

Mutation Syntax

Mutations are declared with mutation and support INSERT, UPDATE, and DELETE.

INSERT

nsql
1mutation createUser($name: string, $email: string) {
2  insert(users)
3  .value({
4    name: $name,
5    email: $email,
6  })
7  .returning(users.*)
8}

Multi-row insert with .values():

nsql
1mutation createUsers($records: [Insertable<users>]) {
2  insert(users)
3  .values($records)
4  .returning(users.*)
5}

UPDATE

nsql
1mutation updateUser($id: uuid, $name: string) {
2  update(users)
3  .where(users.id == $id)
4  .set({
5    name: $name,
6  })
7  .returning(users.*)
8}

Use Updatable<T> for partial updates with a variable:

nsql
1mutation updateUser($id: uuid, $data: Updatable<users>) {
2  update(users)
3  .where(users.id == $id)
4  .set($data)
5  .returning(users.*)
6}

DELETE

nsql
1mutation deleteUser($id: uuid) {
2  delete(users)
3  .where(users.id == $id)
4  .returning(users.*)
5}

onConflict (UPSERT)

nsql
1mutation upsertUser($email: string, $name: string) {
2  insert(users)
3  .value({
4    email: $email,
5    name: $name,
6  })
7  .onConflict(email).doUpdate({
8    name: excluded(name),
9  })
10  .returning(users.*)
11}

Use .doNothing() to ignore conflicts:

nsql
1.onConflict(email).doNothing()

RETURNING

All mutation types support .returning(table.*, col1, col2).

Type System

Built-in Types

i16, i32, i64, f32, f64, string, bool, uuid, timestamp, timestamptz, date

Modifiers

• Optional: type? (e.g., string?) - parameter may be null
• Array: [type] (e.g., [uuid]) - array of values

Utility Types

• Insertable<TableName> - represents a full row for insertion
• Updatable<TableName> - represents a partial row for updates

Value Types (valtype)

Define named type aliases:

nsql
1// With column binding
2valtype UserId = uuid for users.id
3valtype Email = string for users.email
4
5// Standalone named type
6valtype Amount = f64

Use valtypes as parameter types:

nsql
1query findUser($id: UserId) {
2  from(users).where(users.id == $id).select(users.*)
3}

The types.nsql Convention

Projects should place shared type definitions (valtypes, relations, aggregations) in a types.nsql file to centralize reusable definitions across query/mutation files.

Expressions

Variables

Prefixed with $: $id, $name, $values

Operators

• Logical: && (AND), || (OR)
• Equality: ==, !=
• Comparison: <, <=, >, >=
• Arithmetic: +, -, *, /, %
• Unary: ! (NOT)

Method Expressions

Called on columns or expressions with dot notation:

• col.isNull() - IS NULL
• col.isNotNull() - IS NOT NULL
• col.like(pattern) - LIKE pattern matching
• col.ilike(pattern) - Case-insensitive LIKE
• col.between(from, to) - BETWEEN range
• col.eqAny($arr) - = ANY (array contains)
• col.neAny($arr) - != ANY (array not contains)
• col.in([val1, val2]) - IN values list
• col.asc() - ORDER BY ascending (inside .orderBy())
• col.desc() - ORDER BY descending (inside .orderBy())

Aggregate Functions

SUM(expr), COUNT(expr), AVG(expr), MIN(expr), MAX(expr)

Subqueries

Subqueries are wrapped in $():

nsql
1.where(users.id.in($(from(active_users).select(active_users.id))))

EXISTS

nsql
1.where(exists($(from(orders).where(orders.user_id == users.id).select(orders.id))))

Conditional Expressions

when - conditional clause application:

nsql
1.when($name != null, .where(users.name == $name))

switch/case - pattern matching in select:

nsql
1switch(users.status) {
2  case "active": "Active User"
3  case "inactive": "Inactive User"
4  default: "Unknown"
5}

cast()

Type casting:

nsql
1cast(expr, typename)

Relations (Auto-JOIN)

Relations define table connections and enable automatic JOIN generation via dot-access syntax.

Defining Relations

nsql
1// Basic relation (INNER JOIN)
2relation author for posts returning users {
3  users.id == posts.author_id
4}
5
6// Optional relation (LEFT JOIN, values may be null)
7optional relation profile for users returning user_profiles {
8  user_profiles.user_id == users.id
9}
10
11// Public relation (accessible from other modules)
12public relation category for products returning categories {
13  categories.id == products.category_id
14}
15
16// Public optional relation
17public optional relation metadata for posts returning post_metadata {
18  post_metadata.post_id == posts.id
19}

Using Relations (auto-JOIN via dot access)

Access related columns through the relation name as a property of the source table:

nsql
1query getPostWithAuthor($postId: uuid) {
2  from(posts)
3  .where(posts.id == $postId)
4  .select(posts.title, posts.author.name, posts.author.email)
5  // posts.author.name auto-joins users via the "author" relation
6}

Nested Relation Access

Relations chain for multi-level navigation:

nsql
1// posts -> author (users) -> organization -> parent_org
2.select(posts.author.organization.parent_org.name)

Aggregation Relations

Define computed aggregate values as virtual columns:

nsql
1aggregation post_count for users returning i32 {
2  count(posts.id)
3}
4
5public aggregation avg_rating for products returning f64 {
6  avg(reviews.rating)
7}

Use like regular columns:

nsql
1.select(users.name, users.post_count)

References

See the following for the full grammar and comprehensive examples:

• Grammar and AST: @references/grammar.md
• Example files: @references/examples.md